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.


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 environment
( 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 measures then the calculated members is 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) Record set 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 locations 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.


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


Enroll for Live Instructor Led Online MSBI TRAINING


RELATED COURSES

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