MDX to locate data on a higher aggregation from a lower aggregation

I think I have a strange problem with a cube. The problem does come from the source data provider, but since there are no means to change that, I have to live with it and make the best of it.

Let me try to describe my situation: I gather data on two levels, two different grades of aggregation (for me: Groups and individual lines). On the higher level, I see an exact amount of time (<-my target), on the finer one I only have a sum of my target value and another time span. I also have counts of members on each level.

What I need to do is "split up" the sum value I have on the fine grain level according to distribution on the higher granularity level.

Does MDX provide a way to "look up" one aggregation level from a cell and use that value as a base for calculation? what would be the typical commands to step up along one aggregation line and request a value from there?

This is a more detailed explanation of the situation in case you are interested:

I have two measures on a group level, like this:

Group 1 50 (a) 1000 (b)

Group 2 some numbers as well

The 50 (a) means 50 seconds average worktime that happened by 1000 (b) jobs that were handled in the group.

Each group consists of 2 to 5 lines. For each line I also have a counting of jobs and a time, but this time is a total time, consisting of a waiting period and the worktime, like this:

Group 1

Line 1 80 750

Line 2 120 250

So, what that means is Group 1 handled 1000 jobs with 50 seconds average worktime, and that resulted out of 750 jobs handled in line 1 with an average (waiting + working time) of 80 seconds and 250 jobs that had 120 seconds work and wait time.

What I need to do is find out how long the work time was in each line, how the 80 seconds distributes between work and wait time. The only way to do that is to estimate the the waiting time in both lines is equal (in my real project it really is, or I do estimate it to be equal as there is not reason not to think so)

If you resolve this little riddle, you find that Line 1 has a waiting of 40 and work of 40, and Line 2 is waiting 40 as well but working is 120.

Hi Ralf_From_Europe:

Perhaps I'm simplifying this too much. If I am simplifying too much please correct me and I can look at the problem again. I think you can get the value you want by using the .PARENT function. You can reference the <dim>.<hierarchy>.CURRENTMEMBER.PARENT and get the value from the group level for your calculation. To illustrate I included an MDX query in this reply which references the CurrentMember.Parent sales value, shows the value, and uses it in a calculation. The sample works against the AdventureWorks DW sample from Microsoft.

Hope this helps.

PaulG

WITH MEMBER Measures.ProductParentSales AS
'
([Product].[Product Categories].CurrentMember.Parent, [Measures].[Internet Sales Amount])
'

MEMBER Measures.MyNewCalc AS
'
Measures.ProductParentSales/[Measures].[Internet Sales Amount]
', FORMAT_STRING = "#,0.00"

SELECT
{[Measures].[Internet Sales Amount], Measures.ProductParentSales, Measures.MyNewCalc} ON COLUMNS,
{[Product].[Product Categories].[Subcategory].[Bottles and Cages]
,[Product].[Product Categories].[Subcategory].[Bottles and Cages].Children
,[Product].[Product Categories].[Subcategory].[Helmets]
,[Product].[Product Categories].[Subcategory].[Helmets].Children} ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003])

|||

"..you find that Line 1 has a waiting of 40 and work of 40, and Line 2 is waiting 40 as well but working is 120". Shouldn't it be 80, not 120?

Anyway, if you look at the Adventure Works MDX Calculation Script section for [Measures].[Sales Amount Quota], you'll find allocations like:

/*-- Allocate equally to months in FY 2002 --*/

Scope

(

[Date].[Fiscal Year].&[2002],

[Date].[Fiscal].[Month].Members

);

This = [Date].[Fiscal].CurrentMember.Parent / 3;

End Scope;

The Sales Targets Measure Group relates to Date at the Calendar Quarter granularity, so monthly [Measures].[Sales Amount Quota] aren't directly loaded from fact data. Instead, they are allocated - is this along the lines of what you want to do?

|||

Thanks both of you for your responses. I think I do understand a bit better now, I think I understand what the MDX parts in each answer is trying to do, but... it turns out I do not know how to "use" MDX in my case, I do not know where to "put it" in my SSAS project. There is measured columns, unary operators, semiadditive calculations and finally custom rollups. I think I need to do a lot of reading in the "advanced design" chapters of my books.

It seems I left out a crucial detail in my first description as well, the data on the two different levels is in different tables and therefore in different measuregroups. I hope that does not mean this makes it impossible. The book I try to learn from does work with adventure works as well and uses several measure groups at once, so I dare say it is possible.

But if I may, let me take out the first step of what I need to and see if someone can help me with it. This is not really different from the original question, it is only the very first step to calculate an intermediate result needed later to solve the "real wanted" value. Say my cube looks like this:

Group Line Counter Value

G1 L1 750 80 750x 80=60,000

G1 L2 250 120 250x120=30,000

G1Total 1000 200 90,000 / 1,000 = 90

The 200 marked in red is just summed up, which is wrong. The correct calculation is shown on the right, and the result is 90. In words you would say: For all children (L1, L2) of a Group (G1) sum up all the products of counter x value and divide by the sum of counter.

Now: Can that be put into MDX? And if it can, where does it have to be "installed" into the cube? I would go with custom rollup, which requires me to put the MDX into a cell of the table the dimension is based on, correct?

I'd really appreciate any hint or website or book recommendation! Thanks in advance.

Ralf

P.S. Deepak:You are of course absolutly right, I wrote down the result wrong. Thanks for pointing it out!

|||

Assuming that you have a dimension like [DimGroupLine], with [Line] attribute rolling upto [Group] attribute, you could add such a calculation statement to your cube MDX script:

([Measures].[Value], [DimGroupLine].[Group].Members)

= Sum(Existing [DimGroupLine].[Line].[Line].Members,

[Measures].[Counter] * [Measures].[Value]) /

[Measures].[Counter];