Mindmajix

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:

  Screenshot_98

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


 

0 Responses on Introduction to SSAS Calculations & Actions"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.