Create Calculated Member using multiple Dimension Attributes in MDX

Question

First of all my knowledge on MDX is very basic. We are currently trying to migrate a Tabular SSAS cube to Multidimensional cube due to the increasing volume of underlying data. As part of this, we are translating all the calculated fields in the Tabular model to MDX calculated measures. We have two type of calculated fields:

• Calculated measures based other dimension attributes. e.g. Sales in a particular currency (Sales in USD * GBP Rate). First issue we faced with calculated measure if the fact that while browsing the cube, these values are showing unless the related fields are selected (e.g. GBP Rate). THe current work around is to use Scope function on a unique ID in the Fact table to do the calculation. E.g.

  CREATE MEMBER [Measures].[Sales in GBP] As 0; Scope([Fact Table].ID.[All]); [Measures].[Sales in GBP]=[Measures].[Sales in USD] * [FxRate].[GBPRate]; End Scope;

Is this the correct way to handle this issue. Any advise on this would be really helpful.

• Calculated fields based on various "text" attributes in the dimension Data. Some examples for this are listed below: Populate "Artist" field with value fro DimArtist.Name if Fact.Artist is NULL/Blank. We are currently creating a calculated Measures (which is not ideal) to create this field. I am not sure on how to correctly use the scope function to get the desired output.

  If Fact.Source = 'XYZ', ProductID = XYZDimProduct.ProductID, if Fact.Source = 'ABC', ProductID = ABCDimProduct.ProductID else ProductID = DimProduct.ProductID.

The intend is to replicate the DAX calculated columns which is calculated at the lowest granular level.

PS: I have not posted the actual queries due to IP issues. Please let me know if I need explain it with a better example.

Thanks in Advance, Venki

Answer 1

Venki,

For you currency question, I would suggest you have two facts. The first fact will be very similar to your existing fact table (with Currency and time and everything else it already has). The second fact table is specifically for currency and would hold the exchange rates that in the simple case of converting from any currency to GBP it would have just Currency, Time, and Rate.

The calculation simplifies to GBPAmount = SalesMeasureFromFact1 * RateMeasureInFact2 if the granularity of the ExchangeRate fact table matches that of the SalesFact table.

For your other question . . .

"If Fact.Source = 'XYZ', ProductID = XYZDimProduct.ProductID, if Fact.Source = 'ABC', ProductID = ABCDimProduct.ProductID else ProductID = DimProduct.ProductID."

I would put the statement into the Fact table either as a calculated column or via ETL. You will find this performs better and is easier to maintain than your existing approach. MDX is on the fly but is not "in memory" necessarily. Therefore, you can get better performance for the End User Experience by pre-calculating on load what you can and then letting MDX do what it does very well, aggregate. Keep in mind that MDX is very powerful, but just because it can do the job, it might not be the best tool to use all of the time.