MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook
Parts of an MDX query
This section contains the brief explanation of the basic elements of MDX queries: members, sets, tuples, axes, properties, and so on.
Regular members
Regular dimension members are members sourced from the underlying dimension tables. They are the building blocks of dimensions, fully supported in any type of drill operations, drillthrough, scopes, subqueries, and probably all SSAS front-ends. They can have children and be organized in multilevel user hierarchies. Some of the regular member’s properties can be dynamically changed using scopes in MDX script or cell calculations in queries – color, format, font, and so on.
Measures are a type of regular members, found on the Measures dimension/hierarchy. The other type of members is calculated members.
Calculated members
Calculated members are artificial members created in a query, session, or MDX script. They do not exist in the underlying dimension table and as such are not supported in drillthrough and scopes. In subqueries, they are only supported if the connection string includes one of these settings: Subqueries = 1 or Subqueries = 2. See here for examples:
http://tinyurl.com/ChrisSubquerySettings
They also have a limited set of properties compared to regular members and worse support than regular members in some SSAS front-ends.
An often practiced workaround is creating dummy regular members in a dimension table and then using MDX script assignments to provide the calculation for them. They are referred to as “Dummy” because they never occur in the fact table which also explains the need for assignments.
Tuples
A tuple is a coordinate in the multidimensional cube. That coordinate can be huge and is often such. For example, a cube with 10 dimensions each having 5 attributes is a 51 dimensional object (measures being that extra one). To fully define a coordinate we would have to reference every single attribute in that cube. Fortunately, in order to simplify their usage, tuples are allowed to be written using a part of the full coordinate only. The rest of the coordinate inside the tuple is implicitly evaluated by SSAS engine, either using the current members (for unrelated hierarchies) or through the mechanism known as strong relationships (for related hierarchies). It’s worth mentioning that the initial current members are cube’s default members. Any subsequent current members are derived from the current context of the query or the calculation.
Evaluation of implicit members can sometimes lead to unexpected problems. We can prevent those problems by explicitly specifying all the hierarchies we want to have control over and thereby not letting the implicit evaluation to occur for those hierarchies.
Contrary to members and sets, tuples are not an object that can be defined in the WITH part of the query or in MDX script. They are non-persistent. Tuples can be found in sets, during iteration or in calculations. They are often used to set or overwrite the current context, in other words, to jump out of the current context and get the value in another coordinate of the cube.
Another important aspect of tuples is their dimensionality. When building a set from tuples, two or more tuples can be combined only if they are built from the same hierarchies, specified in the exact same order. That’s their dimensionality. You should know that rearranging the order of hierarchies in a tuple doesn’t change its value. Therefore, this can be the first step we can do to make the tuples compatible. The other thing is adding the current members of hierarchies present only in the other tuple, to match the other tuple’s dimensionality.
Named sets
A named set is a user-defined collection of members, more precisely, tuples. Named sets are found in queries, sessions, and MDX scripts. Query-based named sets are equivalent to dynamic sets in MDX script. They both react to the context of subquery and slicer. Contrary to them, static sets are constant, independent of any context.
Only the sets that have the same dimensionality can be combined together because what we really combine are the tuples they are built from.
It is possible to extract one or more hierarchies from the set. It is also possible to expand the set by crossjoining it with hierarchies not present in its tuples. These processes are known as reducing and increasing the dimensionality of a set.
Set alias
Set aliases can be defined in calculations only, as a part of that calculation and not in the WITH part of the query as a named set. This is done by identifying a part of the calculation that represents a set and giving a name to that expression inside the calculation, using the AS keyword. This way that set can be used in other parts of the calculation or even other calculations of the query or MDX script.
Set aliases enable true dynamic evaluation of sets in a query because they can be evaluated for each cell if used inside a calculated measure. The positive effect is that they are cached, calculated only once and used many times in the calculation or query. The downside is that they prevent block-computation mode because the above mentioned evaluation is performed for each cell individually.
In short, set aliases can be used in long calculations, where the same set appears multiple times or when that set needs to be truly dynamic. At the same time, they are to be avoided in iterations of any kind.
Axis
An axis is a part of the query where a set is projected at. A query can have up to 128 axes although most queries have 1 or 2 axes. A query with no axis is also a valid query but almost never used.
The important thing to remember is that axes are evaluated independently. SSAS engine knows in which order to calculate them if there is a dependency between them. One way to create such a dependency is to refer to the current member of a hierarchy on the other axis. The other option would be to use the Axis() function.
Some SSAS front-ends generate MDX queries that break the axes dependencies established through calculations. The workaround calculations can be very hard if not impossible.
Slicer
The slicer, also known as the filter axis or the WHERE clause, is a part of the query which sets the context for the evaluation of members and sets on axes and in the WITH part of the query.
The slicer, which can be anything from a single tuple up to the multidimensional set, interacts with sets on axes. A single member of a hierarchy in slicer forces the coordinate and reduces the related sets on axes by removing all non-existing combinations. Multiple members of the same hierarchy are not that strong. In their case, individual members in sets on axes overwrite the context of the slicer during their evaluation.
Finally, the context established by the slicer can be overwritten in the calculations using tuples.
Subquery
The subquery, also known as the subselect, is a part of the query which executes first and determines the cube space to be used in the query. Unlike slicer, the subquery doesn’t set the coordinate for the query. In other words, current members of all hierarchies (related, unrelated, and even the same hierarchy used in the subquery) remain the same. What the subquery does is it applies the VisualTotals operation on members of hierarchies used in the subquery. The VisualTotals operation changes each member’s value with the aggregation value of its children, but only those present in the subquery.
Because the slicer and the subquery have different behaviors, one should not be used as a replacement for the other. Whenever you need to set the context for the whole query, use the slicer. That will adjust the total for all hierarchies in the cube. If you only need to adjust the total for some hierarchies in the cube and not for the others, subquery is the way to go; specify those hierarchies in the subquery. This is also an option if you need to prevent any attribute interaction between your subquery and the query.
The areas where the subquery is particularly good at are grouping of non-granular attributes, advanced set logic, and restricting members on hierarchies.
Cell properties
Cell properties are properties that can be used to get specific behaviors for cells. For example: colors, font sizes, types and styles, and so on. Unless explicitly asked for, only the Cell_Ordinal, Value, and Formatted_Value properties are returned by an MDX query.
Dimension properties
Dimension properties are a set of member properties that return extra information about members on axes. Intrinsic member properties are Key, Name, and Value; the others are those defined by the user for a particular hierarchy in the Dimension Designer. In client tools, dimension properties are often shown in the grid next to the attribute they are bound to or in the hint over that attribute.