A Multidimensional Expressions (MDX) script defines the calculation process for a cube in Microsoft SQL Server 2005 Analysis Services (SSAS).
...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). |
"Is this mdx stored on the Cube as an aggregation" - the cube MDX script relates to calculations, rather than to aggregations:
A Multidimensional Expressions (MDX) script defines the calculation process for a cube in Microsoft SQL Server 2005 Analysis Services (SSAS).
...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",
( [Materials].[Hierarchy].Children,Sum
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",
( [Materials].[Hierarchy].Children,Sum
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!!!
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....
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.
...Material | Station | Passed | Processed | PP(calculated on cube, Passed / Processed)
Mat1 St1 10 15 0.66
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 |