Selecting a Specific member in a dimension

[claws2rip]

i was wondering, if when a create a new dimension i can only display the a specific member of it.

EX: dimension "State" includes NY, NJ, CT, CA, RI
suppose in my cube i only want to see the state CT

i was able to accomplish half of it
i was able to get the cube to only display the data for one state, however, it also included the other states as categories(but with no data)

 

[issahar]

Hi, claws2rip!

if you are using OLAP 2000 -
you can use Dimension filter - Table Source Filter property - exactly matching which value you want to include to this dimension - and making this dimension to be without "All" level...as a consequence you will see only the member that you want to see...

In OLAP 7...mmmm...I shall give you an answer later.

I hope it helped you.


Issahar
senior software engineer

http://www.softwatch.com

 

[issahar]

....in Olap 7 you can use the Cube filter so you will see only the data for the specific State...

Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

it didnt work,
i put in the criteria for the dimension but then my cube wouldnt process

 

[issahar]

Please,
send me the exact criteria that you put in our dimension...
It is better to do it today because I go to 2 weeks vacation tomorrow...

Have a nice day.


Issahar
senior software engineer

http://www.softwatch.com

 

[issahar]

I tried to put criteria for dimension and it worked perfectly...

May be your problem the next thing:
You Must mention in 'Table Source Filter' the full field name,
e.g.
It is not enough to put there

'UserID>0'

but you have to put there

'"Customers"."UserId">0'

and it is clear that this field must be the part of the dimension table, isn't it?

Have a nice day.


Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

i hope u get this before u go on vacation

this is what i put in the table source filter:
[WorkType].[Work Order Type Code].[CU]

 

[issahar]

You should put in table source filter place the SQL filter and not MDX filter!

Please, put to table source filter the exact SQL criteria on the table that your dimension is based on, e.g. like I wrote you in my previous message

...




Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

i did that, and here's the error i keep on getting:

Data source provider error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.;37000; Time:12/20/2001 13:48:39


Sam

 

[issahar]

Hi
I turned back from my vacation now.
Please, can you send me the exact SQL criteria that you have inserted...
have a nice day Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

hi..
hope u had a nice vacation

this is the SQL statement:

select workordertypecode from t_WorkOrder
where workordertypecode= 'cu'

and this is what i put in the table source filter line:
"WorkType"."Work Order Type Code"="CU"

thanx
Sam

 

[padelg]

take a look to these properties in SQL Server 2000
Members
Descendants
Children

 

[claws2rip]

i understand how to use them outside Analysis Server, like in VB, but what i'm looking for is :

ex:

suppose i have 2 states "RI" and "MA"
i dont just want to display the data for "RI" but when i'm looking at the cube data in analysis services, i dont want to even have an option of selection "MA"

sam

 

[issahar]

Hi, Sam
I think the problem is that
in place of
"WorkType"."Work Order Type Code"="CU"

you should put
"WorkType"."Work Order Type Code"='CU'

(with condition that WorkType is the source table for your dimension)

Have a nice day Issahar
senior software engineer

http://www.softwatch.com

 

[tredfern]

I am trying to accomplish the same thing and it is not working.

I am using the Foodmart 2000 database. I only want to view info for Canada. I have applied a Source Table Filter to the Store Dimension ("store"."store_country" = 'Canada'). I process the Dimension. I receive the following error when I process the cube:

A member with key '15' was found in the fact table but was not found in the level 'Store Name' of the dimension 'Store'.; Time:2/26/2002 7:34:02 AM

What step am I missing??

Thanks in advance!

 

[Kelee]

I am having the same problem - did you ever resolve it and if so, how?

Thanks for the help
Kelee

 

[Kelee]

I found the solution. I am using a financial calendar and i was trying to utilize the dimension name, instead of the actual table name (dim name is "Financial Calendar". I used the following:

"dbo"."Calendar"."FinancialYear" = ('2000/2001') or "dbo"."Calendar"."FinancialYear" = ('2001/2002')

and it works great.

 

[claws2rip]

thank you for the suggestion

i found out a couple things:
1. i was selecting the dimension name instead of the field name in the table.
2. depending on how many tables u have, u may or may not need the dbo in front of the table name

although, the correct data does display, i still have an option to select other work order types even though it will not return data for those other options.

ex: State is one of my 5 dimensions.
i setup it up that the cube will only display data where state='CA'

however after i process the cube and i click on the drop down box for the "State" dimension, even though the cube will not give me data for other states, i still have the option of selection other states...

not only do i want to see the data for state "CA" i dont even want to have the option of selecting other states

any ideas...

Sam

 

[fubarsnafu]

tredfern -- I accomplished what you desire by also setting up the same filter on the cube and re-processing. Because of the join you need to filter both. Even then, you may have to drag country off of and then back onto the Cube Browser to see the other countries go away.

claws2rip -- you may have the same issue but without seeing your schema I can't be sure. If you filter something out of your dimension, reprocess, then reprocess the cube, and then you still see the other states, review your schemas for other existing relationships that refer to the states you are trying to exclude.

I just started working with this a week ago but I've been working on this very issue