Here's my cube structure in a nutshell.
I've got a measure group called MG1 which contains measure "Units". I've got another measure group called MG2 which contains measure "Price".
I want to add a new measure called "Value" that should correspond to Units * Price.
I'm trying to use MDX script to do this calculation:
[Measures].[Value] = [Measures].[Units] * [Measures].[Price]
This seems to produce the right results at leaves level, but when i check the Totals in the Cube Browser they are incorrect because in the totals calculation Prices are summed up and multiplied by the sum of Units. The Totals should instead be calculated by doing a sum of all the leaves Values if you see what i mean.
Is there anything obvious i'm missing here?
To try and explain further, here're the (wrong) results i'm getting:
Units Price Value
Product A -27,880 0.00570 -158.92
Product B -11,845 0.00560 -66.33
Total -39,725 0.01130 -448.89
And here're the results i would like to get:
Units Price Value
Product A -27,880 0.00570 -158.92
Product B -11,845 0.00560 -66.33
Total -39,725 0.01130 -225.25
As you can see from above the problem is to do with the way Value is calculated for Total.
Any suggestion on how i could achieve the desired results through MDX script and/or changes in cube design ?
|||Do you have the option of moving Price to a dimension? This is often how price is recorded if it is describing something like a product.
Barring this, I would recommend calculating Units * Price in your DSV and then simply recording it in a new measure in your MG1 measure group using an additive function.
Good luck,
Bryan
Price is in measure group MG2 because it also depends on the value of another dimension "Catalog". The Catalog dimension is not directly linked to MG1. So depending on which catalog is chosen we have different prices accross products. So i really would need to keep Units and Price in 2 separate fact tables (measure groups) to avoid having a huge single fact table with all combinations.
I think what i'm trying to achieve is fairly simple, ie. the only issue is to do with Totals. But maybe i'm missing something.
I would really need to get this working through MDX script because i will need to use the "Value" measure inside other calculated measures so i cannot even use a Measure Expression, i think.
|||So, if a product can have multiple prices depending on the catalog you have to know the catalog used in the purchase. In that case, does MG1 also have a direct relationship with the Catalog dimension?
Alternatively, can this be resolved in the DSV? Could you create a named calculation in the DSV that multiplies the units by price to give you a transaction total? The total seems to be additive. This might be an easier solutoin for you.
Bryan
This seems like a good scenario for "measure expressions" - if you inspect the Adventure Works cube, various Sales Amount measures are multiplied by currency rates. For example, you could create a new "sum" measure like [Value] on the MG1 "Units" field. Then, for this new measure, configure the "Measure Expression" property as:
[Measures].[Value] * [Measures].[Price]
This entry in Chris Webb's blog discusses measure expressions:
...
But here's a cool cube design feature that doesn't cause any conflict of interest for me - measure expessions. The easiest way to explain what they are is to explain one scenario where they're useful. In AS2K, if you've tried to model currency conversion, you're probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can't then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house - they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define.
...
|||I could not get the cube calculations correct with the 2 Fact tables as described above. So I've finally opted for 1 Fact table with all the "combinations": CatalogID, ProductID, Units, ProductPriceForCatalog, Value, etc. This means that Value is calculated as part of my SSIS package which updates the Fact table with new entries every day, so this calculation is not performed in the cube via MDX script any more. The downside of this solution is that my Fact table will contain many more rows...