The purpose of this document is to describe how to create Calculated Members in the cube and how to utilize it. Calculated member is very useful when we need to implement complex formulas/calculations in cube to achieve business requirements.
2. What is Calculated Members and how to create it in Cube?
Calculated members are members of a dimension or a measure group that is defined based on a combination of cube data, arithmetic operators, numbers, and functions.
For example, you can create a calculated member that calculates the sum of two physical measures in the cube. Calculated member definitions are stored in cubes, but their values are calculated at query time.
- Open the Analysis Services solution for your cube
- Go to the Calculation tab of the solution then New Calculated Member
- It will open the window for creating new calculated member:
- Give the name of the new calculated member ‘Expected Profit’ and drag the measures from Calculation Tools and create a formula as per the business requirement.
- Click on save button to Save the Calculated member.
- Process the cube and go to the ‘Browse’ tab of the cube. Here you can find the newly created calculated member ‘Expected profit’.
- Check the data for the Calculated member:
- So we are able to see the data for the newly created calculated member.
3. Advantages of Calculated Members
- Custom calculation requirements can be achieved by using calculated member only.
- We can create complex expressions by combining data with arithmetic operators, numbers, and a variety of functions.
- Calculated members can be highlighted with colored fore/Background.
4. Disadvantages of Calculated Members
- Cube will store only definition of calculated member. It is calculated at runtime so it will affect the performance of the Cube as compared to native measures especially in case of complex calculation. Calculated members have little to no effect on processing nor on other queries so we can add as much as possible.
- Calculated members can be created only on existing measures and dimensions
- Drill through does not operate on calculated measures or any other calculations that reference calculated measures / calculated members. This means, for example, if you have created a calculated measure on the cube which the user might opt to use as a part of drill through dataset, this means that now you are stuck and you need to find a workaround.
- Calculated measures cannot be secured using Dimension Security in a straight forward manner, in fact they won't be listed at all in the Dimension tab of the role where we define the Dimension security. Also when security is applied on regular members, and due to the same, if they are not available to calculated members, they would fail i.e. when such measures are browsed in client tools like Excel, the value that would be displayed is an error value like #VALUE.
---------------------------End of Document------------------------
