fbpx

Statistical/Utility Dimension

A statistical dimension is a dimension that is not linked directly to a measure (fact table). It can be used for advanced cases where we want to define an operation (e.g. average, conversions) without the need to create new measures.

Example

Let's imagine we have the temperatures for a list of cities. These temperatures in our fact table are calculated in Celsius and we would like to give the end user the possibility to switch to other temperature scales (Fahrenheit, Kelvin).

A possible solution could be to add two new calculated measures, one for Kelvin and another for Fahrenheit. Imagine another scenario, though, where we have several temperatures (minimum, maximum, average). For this scenario we would need to add 6 new calculated measures. It's starting to become very impractical and clearly not scaling well. A solution would be to use a statistical/utility dimension:

The name of the utility dimension will be [Temperature Scale], with a hierarchy [Scale] and the default member [Celsius].

Once we create our utility dimension we can add two calculated members that will be responsible for the conversion (Analytics/Members):

[Temperature Scale].[Fahrenheit] = ([Temperature Scale].[Celsius])*9.0/5 + 32; °F = °C * 9/5 + 32
[Temperature Scale].[Kelvin] = ([Temperature Scale].[Celsius]) + 273.15; °K = °C + 273.15

After successfully deploying and loading the schema we can use the new dimension to query the cube:

    SELECT
        [Temperature Scale].[Scale].ALLMEMBERS on 0,
        [City].[City].MEMBERS on 1
    FROM
        [Cube]
    

Both the schema and the data of this example are available for download here.

Next chapter: Time Wizard Dimension explains how to create a time dimension.