Tecflix

MDX Calculated Members Purchase the entire course

4 December 2013 · · 9714 views

Expanding the Cube

Calculated members are the most commonly used way of performing calculations in MDX. Let Chris Webb explain the different ways in which they can be created in this 45-minute video.

When you create a calculated member, you are creating a new member on a hierarchy of one of your dimensions. Most often, calculated members are created on the Measures dimension, and are called Calculated Measures, but it is important to stress that they can be created on any hierarchy.

Calculated members can be created in three places:
1. In the WITH clause of a query. Calculated members created here are only accessible by the query that they are defined in.
2. Using a CREATE MEMBER statement executed on the client, to create calculated members that are accessible to all queries on a single session.
3. Using a CREATE MEMBER statement on the Calculations tab of the Cube Editor in Visual Studio, to create calculated members that are available to all queries run on that cube.

Generally speaking, it is better to create calculated members on the Calculations tab on your cube—this is because most calculations will be needed by more than one query, and defining the calculation once, on the cube, makes maintenance much easier. Also, because Analysis Services is able to cache the values returned by calculated members, but can only do this while the calculated member actually exists, defining your calculated member on the cube can lead to better query performance.

When writing calculated members, tuples are used to reference values from elsewhere inside the cube. When a tuple is used inside a calculated member, Analysis Services follows a three-step process to find the cell value that it references. This process, as well as other aspects of calculated member behaviour, such as recursion, are further explained in this video, which is part of our online MDX course.

Log in or purchase access to play the video.

  • MDX Basic Concepts 29-min Watch with Free Subscription

  • MDX Queries 24-min Watch with Free Subscription

  • MDX Calculated Members 52-min

  • MDX: Member and Set Functions 38-min

  • What is the MDX Current Member Function? 5-min Free—Watch Now

  • MDX Numeric Functions and CURRENTMEMBER 33-min

  • MDX: Previous Period Growths, Shares & Moving Averages 38-min

  • More MDX Member & Set Functions 1-hour 37-min

  • What is a Subselect in MDX? 3-min Watch with Free Subscription

  • Exists, MDX Solve Order, and Subselects 49-min

  • MDX Performance Diagnosis with Profiler 29-min

Purchase a Full Access Subscription

 
Individual Subscription

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase
 
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo