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.
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?
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.
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.
Steps to
create calculated member in the Cube:
- 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
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
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————————
of Document————————