Home / MSBI

Introduction to SSAS Calculations & Actions

Rating: 4.0Blog-star
Views: 4524
by Raunaak
Last modified: March 25th 2021

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:

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:

 SSIS  Power BI
 SSRS  SQL Server
 SCCM  SQL Server DBA
 SharePoint  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator

 

About Author

author
NameRaunaak
Author Bio

As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.