Introduction to SSAS Calculations & Actions
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
Here one by one calculation prepared manually.
Eg: Creating a calculated member , finding the difference between actual & estimated costs.
Parent Hierarchy: Measures
Expression: [Measures] . [Actual cost] – [Meaures] . [Estimated cost]
Format String: Standard
Associated Measure Group: (undefined)
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.
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 )
CREATE MEMBER < MEMBER NAME > AS < EXPRESSION > ;
< SETTINGS >
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;
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.
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] . &  / ([measures] . [Actual cost], [Time] . [Time-Hierarchy] . & [Alt])
3) Display Actual cost of the member to its parent .
Syntax: ([measures] . [Actual cost] , [Time] . [Time-Hierarchy] . &  / ([measures] . [Actual cost], [Time] . [Time-Hierarchy] . PARENT)
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.
Action –> New Action
Target type: Attribute numbers
Target Object: [Location] . [Loc Name]
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
Action –> New Action
Target Type: Attribute members
Target Object: Location . Loc Name
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.
Action –> New drill through Action
Name: Drill through_Action
Measure group members: Text_fact
Drill through columns
Dimensions Return columns
Measures Actual Cost
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