Database Reference
In-Depth Information
Creating measure groups from dimension
tables
Measure groups don't always have to be created from fact tables. In many cases,
it can be useful to build measure groups from dimension tables too. One common
scenario where you might want to do this is when you want to create a measure
that counts the number of days in the currently selected time period; so if you had
selected a year on your Time dimension's hierarchy, the measure would show the
number of days in the year. You could implement this with a calculated measure in
MDX, but it would be hard to write a code that worked in all possible circumstances,
such as when a user multi-selects time periods. In fact, it's a better idea to create a
new measure group from your Time dimension table containing a new measure with
AggregateFunction Count , so you're simply counting the number of days as the
number of rows in the dimension table. This measure will perform faster and always
return the values you expect. This post on the blog of Mosha Pasumansky discusses
the problem in more detail: http://tinyurl.com/moshadays .
MDX formulas versus pre-calculating values
If you can somehow model a calculation into the structure of your cube, or perform
it in your ETL, you should do so in preference to doing it in MDX only as long as
you do not compromise the functionality of your cube. A pure MDX approach will
be the most flexible and maintainable since it only involves writing code, and if
calculation logic needs to change, then you just need to redeploy your updated MDX
Script; doing calculations upstream in the ETL can be much more time-consuming
to implement and if you decide to change your calculation logic, then it could
involve reloading one or more tables. However, an MDX calculation, even one that
is properly tuned, will of course never perform as well as a pre-calculated value or
a regular measure. The day count measure, discussed in the previous paragraph,
is a perfect example of where a cube-modeling approach trumps MDX. If your aim
was to create a measure that showed average daily sales, though, it would make
no sense to try to pre-calculate all possible values since that would be far too time-
consuming and would result in a non-aggregatable measure. The best solution here
would be a hybrid; create real measures for sales and day count, and then create an
MDX calculated measure that divides the former by the latter. However, it's always
necessary to consider the type of calculation, the volume of data involved, and the
chances of the calculation algorithm changing in the future before you can make an
informed decision on which approach to take.
 
Search WWH ::




Custom Search