Calculated Member as Regular Measure

One of my favorite modeling tricks when I need an UDM calculated measure is implementing it as a regular measure by creating a named calculated column in DSV. I set the expression of the named calculation column to NULL to minimize storage. This approach has a couple of advantages:

  1. It allows you to set a default aggregation function, e.g. SUM(). In comparison, a calculated member (created with CREATE MEMBER) cannot have a default aggregated function. This could be useful if you need to perform a calculation only at the leaf members of a dimension and then sum the results up.
  2. You can scope across several measures. For some obscure reason, the SCOPE operator doesn’t support calculated members. For example, the following statement will trigger a “The Measures dimension is used multiple times” (or similar) error on deploy if Member 1 and Member 2 are calculated members:

    SCOPE

    (

    [Date].[Date].[Date].Members,

    {

        [Measures].Member1,

        [Measures].Member2

    }

    );

     

    but it will work if they are regular measures. If you want to use calculated members you can get away by changing the statement to:

     

    SCOPE

    (

    [Date].[Date].[Date].Members

    );

    {

        [Measures].Member1,

        [Measures].Member2

    } = <some assignment>

    )

     

    but this may have some side effects, e.g. if you can’t format this scope because everything that intersects [Date].[Date].[Date].Members will be formatted that way as well.

One potential gotcha that bit me when implementing a calculated member as a regular measure is that DSV defaults the named calculation to Integer data type (because its expression is NULL) and the Integer type propagates to the regular measure. As a result, if your calculated expression is a ratio, the results may be rounded in some clients (e.g. Report Builder) although the actual query returns the correct results. To correct this, open DSV source and change the data type of the named calculation in DSV to <xs:decimal> (you can’t change it in design mode).

  • furmangg

    Two comments… I think you can get around the int issue by making your DSV named calculation be:
    cast(null as decimal(20,2))

    Also, in one cube I’ve worked on, we had to make this very decision in deciding whether to make the placeholder for assignments physical or calculated. It appeared that there was a 10% performance penalty if the measure was physical when the calc script is doing a bunch of leaf level assignments. (We theorized that the reason for this was that the calc script was aggregating after the assignments, but I can’t prove that.) In our case, we weren’t interested in those assignment aggregating, so there was no point in making the calculated measure physical. Just wanted to mention that it’s worth checking the performance of both alternatives.

  • tlachev

    Good point. Did you evaluate the performance impact before SQL Server 2005 SP2?

  • Yes, that “10% performance penalty” was tested on SP2.

    By the way, I also discovered that doing a cast in the DSV is unnecessary. Just right click on the measure, choose Properties, expand Source, then change the DataType appropriately. Most of my research on this subject got worked into the following BIDS Helper feature:

    http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Measure%20Group%20Health%20Check