MDX with SSAS 2012 Cookbook by 2013

Author:2013
Language: eng
Format: epub
Publisher: Packt Publishing

Leaf and non-leaf calculations

The examples in this and the previous recipe are somewhat complex from a technical perspective, but they are perfectly valid in many reporting requirements. From an analytical perspective, it is often required to get the count of existing members on a non-leaf level, such as at the subcategory level in our example.

When it's required to get the count of members on a leaf level, designing a distinct count measure using the dimension key in the fact table might be a better option. It will work by the cube design in SSAS; there is no code maintenance and it's much faster than its MDX counterpart. Therefore, look for a by-design solution whenever possible; don't assume that things should be handled in MDX just because this recipe indicated such. Chapter 7, When MDX is Not Enough deals with that in more detail.

When it is required to get the count on a non-leaf attribute, that's the time when MDX calculations and relations between hierarchies and dimensions come in to play as valid solutions. Because either you are going to include that higher granularity attribute in your fact table (not likely, especially on large fact tables) and then build a distinct count measure from it, or you can build a new measure group at the non-leaf grain, or you will look for an MDX alternative like we did in this example and the one in the previous chapter. Additionally, the non-leaf levels will typically, although not always, have much lower cardinality than the leaf level, which means that MDX calculations will perform significantly better than they would on a leaf level.

This section serves the purpose of a reminder when it comes to the choice between cube design and MDX calculations. Knowing the pros and cons you should be well on your way to make the right decision.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.