Blog

Introduction to SSAS Calculations & Actions

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

FORM VIEW:

Here one by one calculation prepared manually.
Eg: Creating a calculated member, finding the difference between actual & estimated costs.
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.

Enhance your IT skills and proficiency by taking up the SSAS Training.

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:

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 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 gor 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!

 


RELATED COURSES

Get Updates on Tech posts, Interview & Certification questions and training schedules