Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

MSAS – Implementing Calculations Using MDX Part 2

By Exforsys | on April 21, 2005 |
MSAS
In this part 2 of  Implementing Calculations Using MDX, we will be learning about Renaming Calculated members, Creating Non Measure Calculated members, Using Functions in Calculated Members, Setting calculations at Member level and Setting a Calculation for a subcube or a selection of cells.

Renaming Calculated members

1. In the Analysis Manager tree pane, under the database that contains the calculated member, expand the Cubes folder.

2. Right-click the cube that contains the calculated member, and then click Edit.

3. In the Cube Editor tree pane, right-click the calculated member, and then click Rename.

4. In the box next to the calculated member icon, type a new name, and then click outside the box.

Creating Non Measure Calculated members

In the Analysis Manager tree pane, right-click the Sales cube, and then click Edit.

In Cube Editor, on the Insert menu, click Calculated Member.

In Calculated Member Builder, in the Parent dimension box, select the dimension that will include the calculated member.

 

In the Parent member box, specify the member that will include the calculated member. Click Change to select a member other than the displayed member(if any).

In the Member name box, type a name for the non measure calculated member to be created. Let us say we want to display the data for total sales_cost and unit_sales for Canada and Mexico in the sales cube.

In the Value expression box, construct an expression to produce the values of the non measure calculated member.


WITH

MEMBER[STATE].[NORTH AMERICA].[NON-US] AS

‘[Canada] +[Mexico]’

SELECT

[Measures].Members ON COLUMNS,

[State].[Country].Members ON ROWS

FROM Sales

Use any combination of the following methods to add to the expression:

Drag items from the Data and Functions boxes.

Click an item in the Data or Functions box, and then click Insert.

Click the arithmetic operator and number buttons.

Run the query and browse the data. Note that the new members do not appear. The Column axis supplies the member from the Measures dimension(unit_sales) and the row axis supplies the members from the State Dimension(Non-US). Note that using the + sign to add values works well if the user has only two or three values to aggregate. It becomes cumbersome if there are multiple values.

Unlike the measures dimension, the Members function does not retrieve calculated members of the non-measure dimensions. When a calculated member is created on a non measure dimension the member will by definition intersect with all the members of the Measures dimension. Each of the measures already has an aggregated function defined. Using an Aggregate function will help the user take advantage of the previously defined aggregation function.

To save the calculated member, in the cube editor on the File menu, click Save.

Using Functions in Calculated Members

Multidimensional Expressions provide a lot of flexibility to calculated members. The variety of intrinsic functions available for use in MDX, the variety of functions built into the MSSQL 2000 Analysis services function Libraries and the capability of including and registering external libraries, all add to the flexibility of Calculated members. The variety of functions available and the study of them would require a separate tutorial series on its own. Hence only the commonly used functions are highlighted in this series.

A variety of arithmetic, logical and comparison operators are supported by MDX expressions. The Arithmetic operators include +(Addition),-(Subtraction),*(Multiplication) and /(Division). Comparison operators include <(Less than),>(greater than), <=(Less than and equal to), >=(greater than and equal to), <>(Not equal to) and =(equal to). Comparison operators compare the two strings, numeric expressions or date expressions and return a True or False. Null values are treated as zero when compared with a non null value. Null values can be checked using the function IsEmpty or Is function to return a true or false. Bitwise operators return True or False based on logical expressions. The expressions are evaluated against logical values. Numeric values are converted implicitly into logical values before a logical comparison is made. Numerical expressions that evaluate to 0 or Null are considered false, else True. String expressions are not implicitly converted and usage of string expressions with bitwise operators results in errors. Bitwise operators are AND ,OR, NOT and XOR. Set Operators deal with the creation, separation and joining of sets. They use + (Union), * (CrossJoin), – (Except) and : (naturally ordered set with the members as endpoints).

There are a variety of functions used in MDX expressions. Let us look at these functions on basis of the category of data they return. The first of these is the Numeric Function. Numeric functions are used to perform a variety of aggregations and statistical calculations. MDX Aggregate functions are used to quickly perform calculations across a number of members. These members are specified as a set. The aggregate function becomes powerful when combined with a measure that produces a sum. For instance let us assume that a query has to be built for producing a sum of the store sales for each state. The aggregated values for the first and second half of the year are to be supplied by the calculated members using aggregate functions. The difference between the two supplied by a third member. The structure of the MDX would be as under:

WITH

MEMBER[Time].[1ST Half Sales] AS ‘Aggregate{{Time.[Q1], Time.[Q2]}}’

MEMBER[Time].[2nd Half Sales] AS ‘Aggregate{{Time.[Q3], Time.[Q4]}}’,

MEMBER [Time].[Difference] AS ‘Time.[2nd Half Sales]- Time.[1st Half Sales]’,

SELECT

([Store].[Store State].Members) ON COLUMNS,

{Time.[1st Half Sales], Time.[2nd Half Sales], Time.Difference} ON ROWS

FROM Sales

WHERE [Measures].[Store Sales]

MDX also provides the user with a large number of statistical calculations. For instance statistical covariance and standard deviation can be calculated using the MDX functions. Other numerical functions are the Sum(), Count(), Avg().

String functions supplied by MDX not only process strings but also support user defined functions. For instance MemberToStr function converts a member reference to a string in the MDX format for use in user defined functions. It must be noted that user defined functions cannot accept object references from MDX.

Set Functions provide the user with the capacity to easily build dynamic sets and quickly create reusable named sets. For instance the commonly used set function –Members function– returns all the members of the set other than calculated members of a level. The structure of the function is as under:

SELECT

NON EMPTY { [Store].[Store Name].Members} ON COLUMNS,

{Measures.[Store Sales]} ON ROWS

FROM Sales

The above example returns total store sales figures for each store in the Sales cube.

Tuple Functions are used to return tuples. These functions aid user defined functions in MDX. As user defined functions cannot handle MDX object references, it must pass back a string return value in MDX format representing a tuple and then use the StrToTuple function to convert it to a valid tuple reference.

Member Functions allow calculated members to perform complex member retrieval. Hierarchies and sets are negotiated with ease.

Calculated members can be constructed based on iterations over the members of a set. Hence the resolution of calculated members can be iterative in nature. The CurrentMember function allows the user take advantage of this feature.

MDX provides users with other Functions that deal with dimensions, hierarchies, levels and arrays. Examples of such functions are SetToArray function which allows user defined functions to receive set references as a variant array of individual members represented as strings. This further permits user defined functions to supply set related functionality.

Time series functions provide a variety of capabilities critical to advanced data analysis and decision support systems. YTD(),QTD(),MTD() and WTD() functions are some of the time series functions commonly used.

Time series Analysis functions for performing simple linear regression analysis using the least squares method. Examples of these functions are LinRegIntercept() function, Covariance() function, Correlation() function and so on.

Miscellaneous MDX functions such as the Generate() function, the Parent() function, Decendants() function etc are also extremely useful in building MDX queries and expressions.

Understanding other calculation methods

Microsoft Analysis Services supports calculations up to the most granular levels in a cube. Calculations can be defined for members of a dimension, for a combination of cells or even a single cell.

The MDX set expression used to define the slice of the cube upon which the calculated cells feature will work is called a Calculation subcube. The calculation subcube is defined by a list of single dimension sets.

The MDX logical expression that further restricts the application of the calculated cells feature is known as Calculation condition. The calculated cells condition expression is compared to each cell in the calculation subcube. If the logical expression evaluates to True for the cell, the calculated cells formula is applied and the cell returns the calculated value. If it evaluates to False, then the cell returns the original cell value. The combination of the calculation subcube and the calculated cells condition is referred to as the calculation scope.

The MDX value expression used to calculate the value of the cells contained in the calculation subcube is called a Calculation formula. This functionality sounds in many ways similar to calculated members, custom members, and custom rollup formulas, and indeed can be used in place of these features. But, calculated cells are much more than that.

Setting calculations at Member level

Right click the Expense Budget cube and click Browse Data.

Drag and drop the scenario dimension to replace the measures dimension. Note that the current year’s Budget member does not contain any data.

112.gif

Close the cube browser and click the Shared dimensions folder in the Analysis Manager tree pane.

Right click the Scenario dimension and then click Edit.

113.gif

Click on the Data tab to display the dimension tree.

Expand the Dimension to select the Current Year’s Budget member. Note that the Custom Member Formula pane is enabled.

114.gif

Click the ellipsis(…) button to open the MDX Builder Dialog box. Expand Level02 of the scenario dimension tree, and then drag Current Year’s Actuals to the MDX expression field.

The MDX expression field is updated with the Member Key definition of the member.

At the end of the expression let us enter “*1.1”. Click Ok.

115.gif

Save the changes. Notice that the Budget member has changed and shows the formulas associated with it.

116.gif

Close the Dimension Editor.

Now expand the Cubes folder and right click on the Expense Budget. Click on Browse Data.

Drag the Scenario dimension to replace the measures dimension. Note that the Current Year’s Budget member now contains data. This data is calculated at 110% of the actual.

117.gif

Close the Cube Browser.

{mospagebreak}

Setting a Calculation for a subcube or a selection of cells

Note that calculated cells are available only with Microsoft Analysis Services Enterprise edition. To set a calculation for a group of cells or a subcube follow the steps below.

In the Analysis Manager tree pane expand the cubes folder and right click the Expense Budget cube. Click Edit.

In the Cube Editor click on Data tab to display the data.

Drag the Scenario dimension in the columns to replace the measures dimension

Expand the Scenario dimension so that its four member columns are displayed.

122.gif

Now right click on the Calculated cells node in the Cube Editor tree pane and then click New Calculated Cells.

The Calculated cells Wizard appears.

Click Next to go on to Define the Calculation Subcube screen.

Select the Account Dimension.

Click Next in the Members Set box and select A Single Member option.

A dimension tree appears. Expand the Dimension and click on Gross Sales.

118.gif

Now select the Scenario dimension and in the Members Set box select A Single Member.
In the Dimension tree select the Current Year’s Actuals.

119.gif

Click Next and the Define the Calculation Condition(optional) dialog box appears.

In the MDX expression box enter “Lookup Cube(“[Sales]”, “(Measures.[Store Sales]”, + time.currentmember.Uniquename+ “.Store.[“+Store.currentmember.name+”])”)”.

120.gif

Click Next to navigate to the final screen. Enter “Gross Sales actual from the Sales cube” in the name box. This name will be used for the object in the Analysis Manager.

Click Finish.

The value for the Current year’s actual, Gross Sales cells have changed. The value shown is derived from Stores Sales measure in the sales cube. The Current year’s Budget cells are also updated automatically based on the member calculation previously defined.

121.gif

Save the changes and close the cube editor.

 













« « MSAS – Implementing Calculations Using MDX Part 1
MSAS – Introducing Solve Order » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 385 Followers
  • Popular
  • Recent
  • MSAS: Working with Parent-Child Dimensions

    March 31, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS : Preparing to Create the Cube

    March 7, 2005 - 0 Comment
  • MSAS: Analysis Server Cube Storage

    April 12, 2005 - 0 Comment
  • MSAS : The Wizards

    March 23, 2005 - 0 Comment
  • MSAS – Merging Partitions

    April 19, 2005 - 0 Comment
  • Introduction to Data Warehousing

    February 25, 2005 - 0 Comment
  • MSAS: Working with Levels and Hierarchies

    April 3, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS : Setting up the Database in Analysis Server

    March 7, 2005 - 0 Comment
  • MSAS – Browsing the Dependency Network

    May 6, 2005 - 0 Comment
  • MSAS – Building a Relational Decision Tree Model

    May 6, 2005 - 0 Comment
  • MSAS – Introduction to Data Mining

    May 6, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS – Introducing Analysis Services Security

    May 2, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • MSAS – Defining and Creating Drillthrough

    April 26, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • MSAS – Browsing the Dependency Network
  • MSAS – Building a Relational Decision Tree Model
  • MSAS – Introduction to Data Mining
  • MSAS – Applying security to a Dimension
  • Tutorial 65: MSAS – Managing Cube Roles

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT