A Multidimensional Expressions (MDX) script defines the calculation process for a cube in Microsoft SQL Server 2005 Analysis Services (SSAS).

...Hello all, I¡¯m a beginner on AS2005 (but I know pretty well AS2000), I migrated a cube from AS2000 to AS2005 but in this cube I have a Custom Rollup Formula on two levels of my Time dimension (month and week), an example of the week level formula is the next:

iif( [Time].CurrentMember.Name = [Time].CurrentMember.NextMember.Name,

null,

iif( [Time].CurrentMember.Name = [Time].CurrentMember.PrevMember.Name,

Sum({[Time].CurrentMember.Children,[Time].CurrentMember.PrevMember.Children}),

Sum([Time].CurrentMember.Children)

)

)

<<this formula overrides the week aggregations>>

and I really need this but I cannot find on AS2005 where to do the same thing, someone can tell me where can I define Custom Rollup Formulas and how .

Thanks in advance!

Deepak Puri

This recent Forum thread might help:

SSAS - Custom Rollup in Snapshot Cube - SQL 2000 vs SQL 2005

...

Working with Custom Member Formulas

...

Note: |
---|

To apply an MDX expression to members of a particular level instead of to members of all levels based on a particular attribute, you can define the expression as an MDX script on the level. For more information, see MDX Scripting Fundamentals (MDX). |

Odin_dark

Hey Deepak! thanks!,

That thread really helped me! My problem was very similar and I've solved it now (I had difficulties finding where to put the Cube MDX script but I found that it was creating a script command on the Calculations Tab )

By the way I have a last question about this Cube MDX Script: Is this mdx stored on the Cube as an aggregation or something, or is calculated at runtime (I mean, does it impact performance )

I ask this because my cube takes much time while calculating all the calc. measures I need and I'm looking after tuning up performance now.

I'd appreciate your response! thanks in advance!

Hey Deepak! thanks!,

That thread really helped me! My problem was very similar and I've solved it now (I had difficulties finding where to put the Cube MDX script but I found that it was creating a script command on the Calculations Tab )

By the way I have a last question about this Cube MDX Script: Is this mdx stored on the Cube as an aggregation or something, or is calculated at runtime (I mean, does it impact performance )

I ask this because my cube takes much time while calculating all the calc. measures I need and I'm looking after tuning up performance now.

I'd appreciate your response! thanks in advance!

Deepak Puri

"*Is this mdx stored on the Cube as an aggregation"* - the cube MDX script relates to calculations, rather than to aggregations:

SQL Server 2005 Books Online

A Multidimensional Expressions (MDX) script defines the calculation process for a cube in Microsoft SQL Server 2005 Analysis Services (SSAS).

...If the query performance of cube calculations is poor, the **MDX Script Performance Analyser** tool might help you to investigate it:

The MDX Script Performance Analyser allows the user to identify how much each calculated member, named set and scoped assignment in an Analysis Services cube's MDX script contributes to the overall performance of a given MDX query.

To use the tool, all you need to do is connect to the database and cube you'll be querying and then enter an MDX query in the main textbox and hit the 'Run Query' button. When you do this, the tool does the following:

Generally speaking, whenever you see a steep (and sustained) increase in the amount of time taken to run your query then you know that the accompanying MDX Script statement has had that effect and might be worth optimising.

To use the tool, all you need to do is connect to the database and cube you'll be querying and then enter an MDX query in the main textbox and hit the 'Run Query' button. When you do this, the tool does the following:

- Connects to the cube using ADOMD.Net
**and**AMO - Read's the cube's MDX Script and splits it up into its constituent statements, storing them in an array
- Executes a Clear Cache command to ensure that all queries are run on a cold cache
- Executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
- For each statement in the array of statements from the MDX Script, it then:
- Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
- Runs the query you entered in the textbox
- Stores how long the query took to run, plus other interesting metrics

- Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

Generally speaking, whenever you see a steep (and sustained) increase in the amount of time taken to run your query then you know that the accompanying MDX Script statement has had that effect and might be worth optimising.

Odin_dark

Hello Deepak!, thanks for your advice, I've used the tool and I've found what MDX calculation is the one that hits performance the most, this calculation very complex because is is a **SUM of the Products**.

Just as example, the formula is something like:

Iif ( [TestSt].[Hierarchy].CurrentMember.Name="All",

Sum( [Materials].[Hierarchy].Children,

Excel!Product(

SetToArray(

[TestSt].[Hierarchy].Children,

[Measures].[Passed] / [Measures].[Processed]

)

)

),

[Measures].[Passed] / [Measures].[Processed]

)

I've done some tests thinking that the Excel!Product function might be the problem, so I changed it for a simple SUM function, and it is equally slowly, I mean something like:

Iif ( [TestSt].[Hierarchy].CurrentMember.Name="All",

Sum( [Materials].[Hierarchy].Children,

Sum( [TestSt].[Hierarchy].Children, [Measures].[Passed] / [Measures].[Processed] )

),

[Measures].[Passed] / [Measures].[Processed]

)

But performance is still VERY VERY poor, so any ideas of how can I accomplish this complex calculation any other way or any other solution If you need some other info I'll provide it.

I really appreciate your help!!! thanx!!!

Deepak Puri

The most obvious question is: could cube aggregations be leveraged for these custom rollups instead If you could describe, with examples of data, what this rollup is accomplishing, and the size of dimensions/attributes involved, that would help. I think that you'd want to avoid using Excel functions where possible - even if there's not much impact now (because of other issues), this may limit the possible improvement.

The most obvious question is: could cube aggregations be leveraged for these custom rollups instead If you could describe, with examples of data, what this rollup is accomplishing, and the size of dimensions/attributes involved, that would help. I think that you'd want to avoid using Excel functions where possible - even if there's not much impact now (because of other issues), this may limit the possible improvement.

Odin_dark

I really really appreciate your help!, if you need something else please tell me. I hope I explained myself.

Thanks a million!

Thanks again!, Well I really have thought a lot trying to apply aggregations and other approachs but I cannot find a way to accomplish a good result because of the complex formula, the data is not much now, I¡¯m working on these tests with about 400 000 rows on the fact, but they¡¯ll increase above 3 million when it goes live.

Dimensions

********************************

[Materials]

|-Hierarchy

|-Material (the only level)

-----------------------------------------------

[TestSt]

|-Hierarchy

|-Station (the only level)

---and some other simple dimensions that I think doesn¡¯t affect---

Measures

*********************************

[Passed] (SUM) (I have no set size for the measure, it is type Integer and size 0 by default)

[Processed] (SUM) (the same as "Passed")

FACT table

*********************************

FKTime (foreign key to Time dim¡) (int)

¡some other FK¡¯s¡ (int)

FKMaterial (int)

FKTestSt (int)

Passed (int)

Processed (int)

In this scenario, I have materials and stations, a material ¡°has¡± stations:

Material | Station | Passed | Processed | PP(calculated on cube, Passed / Processed)

Mat1 St1 100 15 0.66

Mat1 St2 55 75 0.73

Mat1 St3 75 80 0.93

Mat2 St4 70 75 0.93

Mat2 St5 30 40 0.75

Mat2 St6 16 22 0.72

Let¡¯s call ¡°PP¡± the calculation ¡°[Measures].[Passed] / [Measures].[Processed]¡± (just for explanation, remember that this calc. NEED to be calculated on the cube, by design)

When I¡¯m at the ¡°All¡± level of [TestSt], I need to calculate the PRODUCT of [PP] over the [Stations] that the [Material] Has, this gets a result ¡°PP_M¡± for each [Material], then, I need to SUM these products of each material and get the result ¡°R¡±

Using the data in the example, PP_M will be the product of all the individual PP¡¯s for each material

PP_M (Mat1) = 0.66 * 0.73 * 0.93 = 0.44

PP_M (Mat2) = 0.93 * 0.75 * 0.72 = 0.50

and "R" will be the sum of PP_M(Mat1) + PP_M(Mat2)

**R = 0.44 + 0.50 = 0.94**

And yes, I would like to avoid Excel formulas, but I just cannot find any other way to get a "PRODUCT" over a set...If only a PRODUCT aggregation existed....

I really really appreciate your help!, if you need something else please tell me. I hope I explained myself.

Thanks a million!

Deepak Puri

Could you explain this calculation:

Material | Station | Passed | Processed | PP(calculated on cube, Passed / Processed)

Mat1 St1 100 15 0.66

How is 100/15 = 0.66 - or is there some other calculation involved

*"..but I just cannot find any other way to get a "PRODUCT" over a set...If only a PRODUCT aggregation existed.." *this blog entry discusses how, with the use of log and power in MDX:

How to aggregate values as a product (multiplication) in MDX

...

Unfortunately there is no MDX function called 'Product' which does such aggregation, but we can work around it. We just need to remember two math rules related to logarithms that we all learned in the 6th grade:

1. ln(a*b) = ln(a) + ln(b) 2. a =e^(ln(a))

Combining these rules we get the following:

a*b =e^(ln(a*b)) =e^(ln(a)+ln(b))

This equation allows us to convert multiplication into addition, which we can do easily with MDX's Sum function. So we need to compute logarithm at leaves, then Sum them up, and then use this Sum as exponent for power operator.

...
Odin_dark

I'm very Sorry, my mistake there, is passed=10

And about the logarithms, thanks I remember now (I knew there were workarounds using logs but I couldn't remember how ) it's great to know this, thanks!, I'll work on removing the Excel!Product.

By the way, the calculation mentioned before (sum of products) is still the top hitter...

Thanks!

I'm very Sorry, my mistake there, is passed=10

Material | Station | Passed | Processed | PP(calculated on cube, Passed / Processed)

Mat1 St1 10 15 0.66

And about the logarithms, thanks I remember now (I knew there were workarounds using logs but I couldn't remember how ) it's great to know this, thanks!, I'll work on removing the Excel!Product.

By the way, the calculation mentioned before (sum of products) is still the top hitter...

Thanks!

Vasko24

Dear Deepak,

I have a problem, I just can't figure out. Namely, I would like to calculate 3 accounting variances in procurement cube:

- price variance, shows the dollar effect of acctual prices that differ from the raw material budgeted prices;

- yield variance, difference between the actual total quantity of input and the budgeted total quantity input;

- mix variance, material mix variance measures the effect of substituting budgeted mix of procured materials with actual mix of materials, calculated as share of particular material quantity in total quantity.

So, we have budgeted price, budgeted quantity, budgeted mix of quantity, and actual price, actual quantity, actual mix.

Price vairance and yield variance are quite easy to calculate in mdx. The problem I encounter is with mix variance. In order to do that, I have to calculate the sum of the filtered product, looking like this:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(SUM(

{ FILTER(

{ DESCENDANTS( [Material].CurrentMember, , LEAVES ) }

, ( [Time].PrevMember, [Measures].[Quantity] ) > 0

AND ( [Time].CurrentMember, [Measures].[Quantity] ) > 0

AND ( [Time].CurrentMember, [Measures].[Price] ) > 0

AND ( [Time].PrevMember, [Measures].[Price] ) > 0

) },

([Time].PrevMember, [Measures].[Price]) * ([Measures].[Quantity] , [Time].CurrentMember, [Material].CurrentMember)

* (([Measures].[Quantity] , [Time].PrevMember)/([Material].CurrentMember, [Measures].[Quantity] , [Time].PrevMember)

)))

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[Time].PrevMember means budgeted figures, [Time].CurrentMember means actual figures.

The problem arises, when I have to calculate the last item in the product, that would have to calculate the share of quantity of particular material at the leaf level in the the quantity of the group of materials that were selected, however this quantity shouldn't be taken just from the level where we positioned our Current.Member, but would have to be filtered first (according to the filters stated), and only then summed up.

([Measures].[Quantity] , [Time].PrevMember)/([Material].CurrentMember, [Measures].[Quantity] , [Time].PrevMember)

Since this item is inside the descendants, I just can't write the mdx so, that the

([Material].CurrentMember, [Measures].[Quantity] , [Time].PrevMember) would work this for me.

Thanks in advance for your answer. If you need additional information, I have an excel file, where all this calculations are much more transparent.

Best regards,

Vasko

Dear Deepak,

I have a problem, I just can't figure out. Namely, I would like to calculate 3 accounting variances in procurement cube:

- price variance, shows the dollar effect of acctual prices that differ from the raw material budgeted prices;

- yield variance, difference between the actual total quantity of input and the budgeted total quantity input;

- mix variance, material mix variance measures the effect of substituting budgeted mix of procured materials with actual mix of materials, calculated as share of particular material quantity in total quantity.

So, we have budgeted price, budgeted quantity, budgeted mix of quantity, and actual price, actual quantity, actual mix.

Data: | ||||||||

Budget (B) | Actual (A) | |||||||

Budg Price | Budg Quantity | Budg Share of quantity | Budg Costs | Act Price | Act Quantity | Act Share of quantity | Act Costs | |

Material1 | 1 | 2 | 25,00% | 2 | 3 | 2 | 22,22% | 6 |

Material2 | 5 | 3 | 37,50% | 15 | 2 | 5 | 55,56% | 10 |

Material3 | 1 | 3 | 37,50% | 3 | 1 | 2 | 22,22% | 2 |

Total | 8 | 100,00% | 20 | 9 | 100,00% | 18 |

Price vairance and yield variance are quite easy to calculate in mdx. The problem I encounter is with mix variance. In order to do that, I have to calculate the sum of the filtered product, looking like this:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(SUM(

{ FILTER(

{ DESCENDANTS( [Material].CurrentMember, , LEAVES ) }

, ( [Time].PrevMember, [Measures].[Quantity] ) > 0

AND ( [Time].CurrentMember, [Measures].[Quantity] ) > 0

AND ( [Time].CurrentMember, [Measures].[Price] ) > 0

AND ( [Time].PrevMember, [Measures].[Price] ) > 0

) },

([Time].PrevMember, [Measures].[Price]) * ([Measures].[Quantity] , [Time].CurrentMember, [Material].CurrentMember)

* (([Measures].[Quantity] , [Time].PrevMember)/([Material].CurrentMember, [Measures].[Quantity] , [Time].PrevMember)

)))

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[Time].PrevMember means budgeted figures, [Time].CurrentMember means actual figures.

The problem arises, when I have to calculate the last item in the product, that would have to calculate the share of quantity of particular material at the leaf level in the the quantity of the group of materials that were selected, however this quantity shouldn't be taken just from the level where we positioned our Current.Member, but would have to be filtered first (according to the filters stated), and only then summed up.

([Measures].[Quantity] , [Time].PrevMember)/([Material].CurrentMember, [Measures].[Quantity] , [Time].PrevMember)

Since this item is inside the descendants, I just can't write the mdx so, that the

([Material].CurrentMember, [Measures].[Quantity] , [Time].PrevMember) would work this for me.

Thanks in advance for your answer. If you need additional information, I have an excel file, where all this calculations are much more transparent.

Best regards,

Vasko