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.
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 environments
( 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 measured then the calculated members are called ” Calculated Measure”.
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.
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) Recordset Activity
b. Drill through Actions.
c. Repeat 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.
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)
–> 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 go 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
Dimensions Return columns
Measures Actual Cost
Raw Material Raw material Function
Build –> Deploy –> GoTo Browser –> Reconnect –> RC –> Actual cost –> click Drill through Action
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
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.