SSAS Calculations

These are the intermediate operations perform between data source to cube database.
–> These are created at BIDS level.
–> It uses MDX syntax commands to perform operations.
–> It supports different operators, set functions, methods, members ..etc in its calculations.
We write calculations in ‘two’ ways
                1. FORM VIEW
                2. SCRIPT VIEW

If you would like to Enrich your career with an SSAS certified professional, then visit Mindmajix - A Global online training platform: “ SSAS Training Certification Course ". This course will help you to achieve excellence in this domain.

FORM VIEW:

Here one by one calculation prepared manually.
Eg: Creating a calculated member, finding the difference between actual & estimated costs.
Navigation:
   Navigation
Name: Diffcost
Parent Hierarchy: Measures
Expression: [Measures] . [Actual cost] – [Meaures] . [Estimated cost]
Format String: Standard
Visible: True
Associated Measure Group: (undefined)
color expression
                Font color : 10711080 /* BGlue */
                Back color : 12632256 /* Sliver */
                Click OK –> Save
Build –> Deploy, GoTo Browser, select Actual cos, Estimated cost, diffcost and see the result.

SCRIPT VIEW:

We write scripting for creating multiple calculations one by one if we are having hands-on experience in writing coding. In script view, it is easy to move the calculation from one environment to other environments
( Dev –> Test , Test –> Production )
Syntax: CALCULATE;
                CREATE MEMBER < MEMBER NAME > AS < EXPRESSION > ;
                < SETTINGS >
Eg:         CALCULATE;
                CREATE MEMBER CURRENT CUBE . [MEASURES] . SUMCOST as
                [MEASURES] . [ACTUAL COST] + [MEASURE] . [ESTIMATED COST] ,
                FORMAT_STRING = “STANDARD”;
                BACK_COLOR = 1234512 /* SILVER */ ,
                FORE_COLOR = 432345 /* BLUE */ ,
                VISIBLE = 1;

CALCULATED MEASURE:

If the parent hierarchy is measured then the calculated members are called ” Calculated Measure”.

NON – EMPTY BEHAVIOR:

Subscribe to our youtube channel to get new updates..!

It is used to resolve the non-empty queries in MDX while calculations.
–> If the Non-empty behavior property is blank the calculation must be evaluated repeatedly to determine a member is empty.
–> The Non-empty behavior property contains the name of the measure the calculation is empty is measure is empty.
–> Generally we go for Ratio calculations.
Eg: There is a calculation ACIEC, If ‘EC’ is empty it throws an error. In this situation, if we set up non-empty behavior property to ‘EC’ then the system avoids calculations and displays the calculated results are empty if the EC is empty.

Additional Expressions:

1) Display Actual cost as ‘99999’ , if its empty
Syntax: If (Is empty ([measures] . [Actual cost]), 99999, [measures] . [Actual cost]).
2) Display in time hierarchy each member and its participating in the entire actual cost sales.
Syntax: ([measures] . [Actual cost] , [Time] . [Time-Hierarchy] . & [1] / ([measures] . [Actual cost], [Time] . [Time-Hierarchy] . & [Alt])
3) Display Actual cost of the member to its parent .
Syntax:
([measures] . [Actual cost] , [Time] . [Time-Hierarchy] . & [1] / ([measures] . [Actual cost], [Time] . [Time-Hierarchy] . PARENT)

ACTIONS

These are performed at the time of Events.
–> Generally the event is “Clicking” the call.
–> There are ‘3’ types of Actions
a. Statement (or) URL (or) Recordset Activity
b. Drill through Actions.
c. Repeat Action.

a) URL ACTION:

Calling the URL, while analyzing the data is called URL action.
Ex: Going to the location's website, when we click location attribute value.
Navigation:
Action –> New Action
Name: URL-Action
Action Target
Target type: Attribute numbers
Target Object: [Location] . [Loc Name]
Action Context
Type: URL
Action Expression : “HTTP://WWW.ALLOCATE.COM“
–> Event is clicking ‘Location’ , Action is opening the URL.
Build –> Deploy –> Go To cube browser , highlight Location –> RC –> click Action (URL-Action)

b) REPORT ACTION:

–> Calling a report while analyzing the data is called report action.
–> It requires report server name, report name. (server name and their report name)
Eg: COGNOS, BO, HYPERION ..etc
Navigation:
Action –> New Action
Name: Report_Action
Action Target
Target Type: Attribute members
Target Object: Location . Loc Name
Report Server
Server Name: RAWAN : 8080
Report Path: Report server_dw11 /—-
Report Format: HTML 5
Build –> Deploy –> GoTo cube browser –> RC –> Location –> click Report Action.
Note: It takes parameters to display the parameterized report content.

Checkout SSAS Interview Questions

c) DRILL THROUGH ACTION:

–> Drilling to other columns (or) for opening a separate analytical window for the required columns, we must go for drill-through action.
–> Simply when we are moving from one analytical to another analytical window for required details, we go for drill-through action.
Eg: Drilling through locations, Time, Raw materials, Dimensions while Browsing the Actual cost.
Navigation :
Action –> New drill through Action
Name: Drill through_Action
Action Target
Measure group members: Text_fact

Drill through columns

Dimensions        Return columns
Measures            Actual Cost
Location               LocName
Time                      Year
Raw Material     Raw material Function
Build –> Deploy –> GoTo Browser –> Reconnect –> RC –> Actual cost –> click Drill through Action

Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

 

List of Related Microsoft Certification Courses: