Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 140

• Session-scoped calculated members : These are calculated members that

exist in the context of the session created when a user connects to a cube.
These calculated members are available for use in all queries executed in a
particular session until either the session is closed or the calculated members
are dropped. Session calculations are a convenient means for a client tool to
simplify the queries it generates, but apart from that, they are rarely used.
They are created by the client tool executing a CREATE MEMBER statement.

• Globally-scoped calculated members : These are calculated members

defined in the MDX Script of the cube using CREATE MEMBER statements.
They are available for use in queries to all users and can be used in the very
same way as physical members are. They are the most common kind of
calculated members.

At first glance, we might think that the only difference between these three ways to
define a calculated member is how long they are available and to whom. However,
as we will see in Chapter 8 , Query Performance Tuning , the scope of a calculated
member determines how long the values it returns can be cached, and this, in turn,
can have a significant impact on query performance.

At this stage, though, all we need to understand is that globally-scoped calculated
members are best from a performance point of view. Remember also that by defining
a calculated member on the cube, we are removing the need for other developers
(for example, Reporting Services report authors) to define these calculations in their
own queries. If too many calculations end up being defined in queries rather than
on the cube it can lead to duplication of effort, confusion when different calculations
are given the same name in different queries, and maintenance problems. In fact,
fixing a bug in a calculation involves tracking down multiple instances of the same
calculation and, possibly, we end up having different calculations in different queries
for the same formula. For all of these reasons, we recommend that you should
always try to create calculated members on the cube, and only use session-scoped or
query-scoped calculations when you have no other choice.

Common calculations
We are now going to show some very common types of calculated members. As
stated before, the goal here is not to spend a lot of time explaining how the MDX
code actually works, but to show some examples of calculations that can be copied
and adapted easily for almost any cube.