How to design calculated members in SSAS – SQL Circuit

How to design calculated members in SSAS

1.     Background
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.

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
  • 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————————

Leave a Reply

Your email address will not be published. Required fields are marked *