SSAS: MDX Calculated Measures that Require Date Comparison

Often there is requirement to calculate measures based on two different date dimensions. For e.g. how many orders that were delivered this month was actually ordered last month? How many orders were delivered in the same month as they were ordered? How many orders were carried forward to next month?

Using MDX set functions EXISTS(),FILTER() and Range operator these calculations can be easily achieved provided the Date Dimension conform to same key columns. Continue Reading

SSAS: One Database, many Cubes Vs. One Database per Cube

So you have several cubes to build and perhaps wondering what is the best way to build and deploy your cubes. Should you build all the cubes within the same Analysis Service database or create one database per cube? The following guidelines might help you to choose the best approach to build and deploy your SSAS cubes. Continue Reading

SSAS: Process Incremental – What it is & isn’t

SSAS provides Process Incremental as one of the processing options for cubes, measure groups and partitions. It is important to understand how Process Incremental works because it differs significantly from the seemingly equivalent Process Update for dimensions. Continue Reading

SSAS: Troubleshooting this exception – “An unexpected error occurred (file ‘mdinfo.cpp’, line nnnn, function ‘MDInfo::Init’)”

What’s this exception?

[Analysis Services Execute DDL Task] Error: Internal error: An unexpected error occurred (file ‘mdinfo.cpp’, line nnnn, function ‘MDInfo::Init’).

My guess is that it’s most likely an unhandled exception from the product. I recently encountered this exception while processing index of a particular SSAS Cube using SSIS Analysis Services Processing Task. The cube did get processed in a previous step and only the process index step failed, the cube was still available for browsing minus the indexes.

How to work around this error?

Continue Reading

SSAS: What could be wrong if you get – ‘A fact relationship cannot be defined’ ?

‘A fact relationship cannot be defined. The dimension and measure group must be based on the same table in the same data source view.’

If you get the above message when defining the relationship type as Fact between the measure group and Degenerate/Fact dimension, what could possibly go wrong especially when the second part of the message clearly tells what needs to happen? Continue Reading