Blog

  • Home
  • MSBI
  • Introduction to SSAS Calculations & Actions

Introduction to SSAS Calculations & Actions

  • (4.0)
  • | 2246 Ratings

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

List of Related Microsoft Certification Courses:


Subscribe For Free Demo

Free Demo for Corporate & Online Trainings.

Ravindra Savaram
About The Author

Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.


DMCA.com Protection Status

Close
Close