A question about cube dimension

Question

Post reply

A question about cube dimension

Del Piero

SSCrazy

Points: 2265
More actions
April 13, 2005 at 7:43 pm

#185720

Hi all,
I have a cube with one measure and several dimensions. One of the dimensions has data from one column of a table. This column only has 3 distinct values in the table, namely, 'Paid','Pending','Cancelled'.
What I want to do is that, I want to group 'Pending' and 'Cancelled' values as one group, namely 'Not Paid', while the value 'Paid' is in another group, namely 'Paid'.
How can we make this grouping? When I process the cube and browse its data, I wish to view the cube's measure values in 2 columns, one is 'Paid' and the other is 'Not Paid', with the latter actually coming from 2 distinct values in the dimension.
For example:
Order type Paid Not Paid
Bread 17 35
Wine 21 77
Ice-cream 6 21
Tissue 27 36
Detergent 22 21
Do we need to use MDX or just modify some member properties?
Thanks for your help,
delpiero

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

One option.

A Computed column on the table, use case statement to cumpute the value.

Modify dimension, add extra level to the hierarchy.

Del Piero SSCrazy Points: 2265 More actions · Answer 2

Thanks for the reply.

I am quite new to the Analysis Services. Would you mind telling me if you mean create a computer column in the dimension table or in the base SQL Server table? I can't find the option of creating a computer column in the dimension wizard.

I tried to use the grouping options when creating aggregations for the cube. It does some "automatic" grouping but the grouping of members do not match my requirement and I am not sure how to change the grouping manually.

Thanks and best regards,

delpiero

Del Piero SSCrazy Points: 2265 More actions · Answer 3

I mean "computed column" instead of "computer column".

Sorry for the typo.

delpiero

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Delpiero,

(NB Have assumed a column name of 'Paid Status' that hold the Paid, Pending and Cancelled).

A quick way to get what you want is to create a new level in the dimension (click and drag 'Paid Status' field into the dimension *above* the current lowest level. In the Member Key properties textbox (for this level) you could type <code>CASE WHEN [Paid Status] = 'Paid' THEN 'Paid' ELSE 'Not Paid' END </code> and use the same thing for the member name property.

This should give you a hierarchy the looks like:

ALL

Paid Not Paid

Paid Cancelled Pending

HTH,

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 5

Thanks steve,

I used your method but afterI typed the <code> </code> part in the 2 properties, it would prompt me "the column property is invalid" and revert back to the original value of the property (i.e. merely the column name). I think I am not sure of the correct syntax to type in the property. Here's what I typed:

<code> case "dbo"."TBL1"."STATUS" when 'P' then 'Paid' else 'Not Paid' end </code>

Thanks,

delpiero

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

another assumption on my part was that you are using mssql as the data source, are you? If not, you will need different syntax for the case statement. For example, Foodmart uses MSAccess, so a similar exercise would use

IIF("customer"."country" = 'USA', 'USA' , 'other' )

rather than the case statement which access doesn't like.

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 7

I am using MSSQL and I know that such syntax is valid in Query Analyzer, but it just does not allow me to insert this code in the member properties ...

delpiero

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 8

Ok, you didn't type the '<code>' tags did you?

Assuming you didnt, the following works for me against MSSQL

case when LEFT("dbo"."vw_pub_test"."title", 1) = 'O' then 'O' else 'Other' END

and one a little closer to what you're after also works

case "dbo"."vw_pub_test"."title" WHEN 'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean' then 'O' else 'Other' END

Can you post *exactly* what you have tried to enter into the Member Key Column property field?

Cheers,

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 9

Steve,

Thanks for your reply. I think I have successfully typed in the case statement in the property field. I guess I made some careless mistakes before. So, now I got what I want: 'paid' and 'not paid'. even if I dun put the <code> tags, it works. Great!

Thanks,

delpiero