AS2005 MDX-Question (Grouping while ignoring intermediate level)

Hi,

I'm quite new to MDX and try to solve the following problem: given is a dimension having the following hierarchy:

Business Unit A
Sales Area 1Customer XCustomer YCustomer ZSales Area 2Customer VCustomer WCustomer X
Business Unit BSales Area 3......

Now I need the sum of each business units' revenues grouped by customers. In the above example the result should look like:
Business Unit A

Customer VCustomer WCustomer X (sum of Sales Area 1 and Sales Area 2)
Customer YCustomer ZSales Area 2Business Unit BCustomer ...
...
Is this possible with MDX?

Thanx for any help and kind regards,
Gerald

Gerald, try this:

{GENERATE({[OrgDimName].[HierarchyName].[BusinessUnitLevelName].MEMBERS},

{[OrgDimName].[HierarchyName].CURRENTMEMBER,

DESCENDANTS([OrgDimName].[HierarchyName].CURRENTMEMBER,

[OrgDimName].[HierarchyName].[CustomerLevelName], SELF)})} ON ROWS

If you replace SELF with SELF_AND_BEFORE you will also get the Sales Area sub-totals.

HTH

Philip Taylor

|||Philip, thank you for your answer.
What I forgot to mention (and is quite important, I think) is that the dimension is a parent-child-dimension:
business units are on [Level 04]|||

Gerald,

Can you explain the meaning of revenue(X1) and revenue(X2) in relation to Customer X please.

Also you might want to create a level naming template ( see http://msdn2.microsoft.com/en-us/library/ms167115(d=ide).aspx and there is an equivalent in AS2000) which I think makes it easier to navigate your org structure and write MDX queries.

Regards

Philip

|||Philip,

the formula in brackets is just thought as an explanation of how the revenue should be calculated. So the revenue for business unit 1 should be the sum of the revenues of customers V, W, X, Y and Z where customer X appears twice, once with revenue value X1 and once with revenue value X2. In other words "revenue(n)" means the revenue value of customer n stored in the facts. I just see that I've made a typo at customer W: of course it should be revenue(W).

Anyway - I have just had a talk with the guy responsible for the cube design and it looks like we will make a second cube which is optimized for reporting. In this case I'll have a dimension which gives me the desired structure directly and I won't have any problems getting the results our customer would like to see.

thanx and kind regards,
Gerald

Are there any books about Fuzzy lookup/grouping implementation.

Hello,

I have been experimenting with Fuzzy lookup in SQL2005 for a while, but have lots of questions about its therory and how to customize it. I have read the article on MSDN
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

and read these 2 articles from Microsoft Research
1) eliminating Fuzzy Duplicates in Data Warehouses by Rohit, Surajit, Venkatesh
2)

are there any books or articles which is more detailed from a implementation/prorgamming perspective..

Please post any thing you may kow.

Thanks,
Saptagiri.Send me an email at Ashvini.Sharma@.microsoft.com and I'll hook you up with the MSR folks.

regards,
ash