Hello!
Again I am unable to solve a rather simple looking problem with MDX. My cube looks something like this:
Group Detail MesA MesB MesC
a 1 300 50 37 (300 * 50 / 400)
a 2 300 100 75 (300 * 100 / 400)
a 3 300 250 187 (300 * 250 / 400)
a Total 300 400 133 (300 * 400 / 900)
... more groups (b, c, d ...) of this style ...
Grand Total 700 900 <NUM>
As you already noticed, MesA is a Measure that existeds on group grain only, so the value is the same for every entry on detail. My first goal is to distribute the MesA according to what I found on MesB (with rounding or such, a detail we can leave out here). I managed to do that as a computed column C (using very kind help from here) with the following MDX:
[Measures].[MesA] * [Measures].[MesB] / ([Dim].[Hie].CurrentMember.Parent, [Measures].[MesB])
This MDX works as intended for a leaf, but when it hits the first subtotal, it calculates the value using the totals from the grand total line. On the grand total line, it fails completly because there is no parent available anymore.
How can I avoid that? I tried my luck using the ISLEAF function, but I cannot get that working. I was able to produce the correct number (300) for the orange field using another bit of MDX:
([Measures].[MesC], [Dim].[Hie].Members) = Sum(Existing [Dim].[Hie].[Detail].Members, [Measures].[MesC])
That works on the first impression, but the little I understand of MDX makes me wonder what I am doing there at all. It only works on first impression, because in my example, if you use your Frontend Cube Browser to hide that row a-2, the total for the orange field should go down to 225, the sum of the remaining visible. It does not, as I "enforce" the value stored at this cell fields instead of relying on the cube to simple total them up.
So... what can I do? Thanks in advance!
Ralf
(Sorry for the weird font sizes in this text. I do not know why, but I cannot get the upper and lower half to show the same size no matter what I try. Please excuse that!)
Hi Ralf,
Since you want MesC to roll up like a regular cube measure, one approach would be to initially define MesC as a "sum" measure on the same measure group as MesB (you can use the same source field, if you wish). Then add a leaf-level calculation to the cube MDX script, using your "distribution" MDX formula on the right side:
([Measures].[MesC], Leaves([Dim])) =
[Measures].[MesA] * [Measures].[MesB] / ([Dim].[Hie].CurrentMember.Parent, [Measures].[MesB])
|||Deepak,
thanks for the tip with the Leaves() set, it was very helpful to get my solution closer to where I need to go to, but...
Are any strange behaviours known for the Grand Total result known when using own calculations? I am getting the strangest results here.
I wonder if there is a good way to send Table Create scripts and Project Files in case someone wants to reproduce that - I am (almost) sure this is no error I am causing anymore, but a real bug in the analysis engine (although... hm... could I be the first to spot such an issue? Unlikely...)
I reduced my real project to the very smallest version for this example.
I use 4 Tables, 2 of those form a dimension with one hierarchie of 2 levels (2 items a and b on level 1 in one table, and 4 items a1,a2,b1,b2 on level 2 in the other), a 3rd table is another simple dimension (with only 2 items x and y on the top row) and finally the 4th is the measure table, connected to the finest grain of of the hierarchy dimension and the "other" dimension. The measure is a simple "sum up" integer, the fact table has 8 rows (4 x 2). Nothing in that setup that should raise any concern, everything totally straight forward. No properties of the dimensions or the measures were tuned or changed or... everything just as the wizards do create when you click through them.
The cube script has only the CALCULATE order followed by one custom line ([Measures].[Value], Leaves()) = 1... that is all.
After I set it up, it looks this way:
x y Grand Total
a a1 1 1 2
a2 1 1 2
Total 2 2 4
b b1 1 1 2
b2 1 1 2
Total 2 2 4
Grand Total 4 4 8
That is all, looks very easy and logical and ... ok, now hide a2 for example, or b1 or so... my Grand total row then changes to "102 - 502 - 902" or "202 - 602 - 804", depending which item you hide. If you unhide it again, everything works perfectly fine again. I want to add that the subtotal line for a or b does change to " 1 - 1 - 2" as expected. Subtotals is fine, only Grand total is wrong.
I think I can explain the numbers, at least their amount: Say I hide element a2. I know that element a1 has a value of 100 in the SQL table before overriding it with a "number one" in my script, therefore the grand total is calculated out of 100 (a1) + 1 (b1) + 1 (b2). Again, the value for a1 and the subtotal for a is correct, both display as 1. But when the grand total is calculated, the a section is not changed by my "set to 1" script.
Can anyone make sense of that? As I said, if someone wants to try it I will work on a way to either post create scripts or whatever needed to solve that.
