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.
