Calculating Semi-Additive Measures in BI Office
by Sandesh Nagaraj , Senior Sales Engineer, and Ian Macdonald , Principal Technologist
Semi-additive measures are values that you can summarize across any related dimension except time. For example, sales and transactions are fully additive; if you sell 50 yesterday and 10 today then you’ve sold 60 in total. You can add them up over time.
Stock levels however are semi-additive; if you had 50 in stock yesterday, and 20 in stock today, your total stock is 20, not 70. It doesn’t make sense to add up the measures over time, you need to find the most recent value.
When we were doing a proof of concept (PoC) for an insurance company, we noticed that the value of active members/policy holders was very high. Our dashboard was running on top of a SSAS OLAP cube. The default aggregation function for the active member measure was set to “Sum” on a snapshot fact table. The measure in the cube needed to be changed to a semi-additive property, but the change request would have taken too long for the PoC. We had to create custom measures in BI Office that were semi-additive to solve the issue.
Whether semi-additive measures are important to you or not depends entirely on what you are trying to do with your cube. If all of your required measures are fully additive, then you really don’t need to worry about anything. However as soon as you want to include measures such as stock levels, salary, share prices or test results then they become pretty much essential.
Semi-additive measures which do not uniformly aggregate across all dimensions, are very common in many business scenarios (with a fact table that is based on snapshots of fact measures over time). Those scenarios can be:
- Quantity on hand
- End of the month balance
- Inventory of a good
- Number of employees
- Insurance policies
In BI Office, it is very easy to create semi-additive custom measures. Consider the following example in which we have a monthly snapshot table with balance for three customers.
Balance for Customers.
When the data is rolled by quarterly or yearly level, we get incorrect answers. It is aggregating the monthly snapshot balance to quarterly or yearly, rather than taking the last value in the time period.
Incorrect Values After Aggregating
How to Create Semi-Additive Measures
Step 1: Create a Custom Measure in BI Office Using Custom Member Wizard.
Step 2: Use the MDX Function “ClosingPeriod”
This function is primarily intended to be used against a dimension with a type of Date.
- If a level expression is specified, the ClosingPeriod function uses the dimension that contains the specified level and returns the last sibling among the descendants of the default member at the specified level.
- If both a level expression and a member expression are specified, the ClosingPeriod function returns the last sibling among the descendants of a specified member at the specified level.
- If neither a level expression nor a member expression is specified, the ClosingPeriod function uses the default level and member of the dimension (if any) in the cube with a type of Time.
The below will return the last month’s balance of whatever date period is shown in the grid. To get the correct balance, put the below calculation in the script section and call the new member as Correct Balance:
(CLOSINGPERIOD ([Data].[Date Year Month].[Date Day],[Data].[Date Year Month].CURRENTMEMBER),[Measures].[Sum Balance])
Now, we get the correct balance: