Project Botticelli

MDX Calculated Members Purchase this course

4 December 2013 · 3 comments · 9217 views

Expanding the Cube

Chris Webb explains three ways to create calculated members in MDX

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 below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

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

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

pspandya · 3 January 2014

Amazing Chris. I must i referred many books on MDX but the way you had explained the whole concept of MDX with its use is just unmatched. You just made MDX so easy that too in just few hours is unbelievable. Thanks a ton for publishing these videos. I must say its worth becoming a paid member of this site rather then keep reading junk books.

avv · 22 September 2014

Very good tutorial I must say indeed. I like it that you explain every concept with multiple examples. Keep the videos coming!

aaronlebato · 12 March 2017

I agree. This made MUCH more sense than trying to catch MDX in a book. I learn in books great but MDX was elusively to me. Thanks Chris!

Online Courses