Terms and Concepts Related to MDX

Exclusive offer: get 50% off this eBook here
MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook — Save 50%

80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries with Microsoft SQL Server 2008 R2 Analysis Services with this book and eBook

$29.99    $15.00
by Tomislav Piasevoli | August 2011 | Enterprise Articles Microsoft

MDX is a powerful, yet complex language. Many terms and concepts need to be understood well enough if you want to master it. In order to help you in that mission, this article will provide with a short explanation of all important terms related to MDX.

In this article by Tomislav Piasevoli, author of MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook, we start by explaining what MDX queries are made of, followed by the terms and concepts specific to the execution of MDX queries. The middle part of the article covers the most important things related to dimension design, cube design, and MDX script. Next, we explain terms related to query optimization and finally finish the article with types of queries that can be used with SQL Server Analysis Services.

 

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

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

        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

 

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.

 

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries with Microsoft SQL Server 2008 R2 Analysis Services with this book and eBook
Published: August 2011
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

 

MDX query in action

In this part we cover topics relevant to execution of MDX queries: explicit and implicit members, current context, attribute overwrites, recursion, and so on.

Explicit members

Explicit members are either regular or calculated members specified in a tuple using their unique name or a function. Members not being specified in the tuple are called implicit members. Explicit regular members are never overwritten by attribute relation mechanisms, unless they refer to the current member, that is, unless they use the .CurrentMember function, and there's also a related attribute in the same tuple. In that case they might get overwritten.

Implicit members

Implicit members are regular members not specified in the current context. In order to get the full coordinate of the current context, these members are evaluated by the SSAS engine and internally added to every tuple.

Current members of dimensions not specified in the tuple remain on their previous positions, positions established by an outer context, be it one of the outer parts of that calculation (starting from the innermost one), the slicer, or MDX script. Current members of dimensions specified in the tuple shift the current members of all related hierarchies not specified in the tuple to their implicit positions. The implicit position is the lowest position in the hierarchy that doesn't interfere with the context. This is evaluated in respect to the dimension design, not the outer context which varies.

If there is a calculated member inside the tuple, current members of all related hierarchies not present in the tuple are shifted to their default member, the All member if not designed differently.

Data members

Data members are system-generated, non-leaf members that occur in parent-child hierarchies whose value is not an aggregate of their descendants but their own contribution coming from the underlying fact table. These additional members may or may not be visible, depending on the option chosen for the MembersWithData property: NonLeafDataVisible or NonLeafDataHidden. When the system-generated data members are left visible (which is the default option), a name suffix or prefix can be used to distinguish data members from their parents. Otherwise, they will have the same name. The asterisk in the name is as a placeholder for the parent's name, for example, like this: "(* data)".

The .DataMember function can get a member's individual value regardless of it being hidden or not.

Current context

Current context is a coordinate (more generally a subcube) formed by combining existing members from all cube hierarchies. It is a very important concept because knowing what is currently in context in each particular step of the query evaluation is crucial for writing good MDX queries, evaluating them, and debugging problematic calculations. Current context can be detected using the EXISTING function in every situation and using the .CurrentMember function in the case of a single member only.

Dimensionality

Dimensionality is a vector that shows which hierarchies are explicitly referred to in a tuple or a set and in what order. Only the sets and tuples of the same dimensionality can be combined together.

Attribute overwrites

Attribute overwrites is a mechanism where explicit members in a tuple overwrite the previously established context by forcing themselves inside the current coordinate. Therefore, they shift the current members of all related hierarchies not present in that tuple to their lowest compatible position, compatible in a sense that implicit members don't change the coordinate but merely adjust to it. The latter behavior is also known as strong relationships algorithm.

Visual Totals

Visual Totals is a SSAS feature for modifying a non-leaf member's value so that it matches the aggregated value of the subcube formed only from descendants specified in that subcube. In case of the VisualTotals() function, members must be sorted hierarchically, the upper level members must be specified before the lower level members, otherwise it will not work as expected.

Iteration

Iteration is a process of repeating a task by going through each item in the collection, usually a set. All set functions incorporate that mechanism although most of them manage to avoid the iteration by turning to the block-computation mode whenever possible. In situations when this is not possible, a much slower process of cell-by-cell evaluation happens.

Recursion

Recursion is an iterative process in which the value to be evaluated is requesting another call to the same object. Once this process starts, it keeps repeating itself until a stopping condition occurs. The stopping condition occurs when the value being evaluated does not trigger another recursive call to the same object.

It's important to notice that we don't know in advance how many times the process will repeat as it will vary. This characteristic makes the recursion very useful when we don't want to iterate over the complete set. When we need to stop as soon as the condition is met, recursion is potentially our friend.

Context-aware calculations

Context-aware calculations are calculations which detect the current context and evaluate accordingly. This evaluation of the current context often triggers the cell-by-cell evaluation mechanism which means these calculations are slow on large sets. On the positive side, they can be made independent of the cube structure or a part of it and as such used in every cube.

Set-based operations

Set-based operations are mechanisms for operating with sets in a way that members in them can be intersected, unionized, or subtracted as a whole, not individually. Set operations are therefore very fast operations and should be the preferred way of performing complex calculations, preferred against much slower iterations where members are treated individually.

Errors

Errors occur for various reasons. However, that can be prevented by intercepting them with the IsError() function. In case of SSMS or BIDS, it's worth remembering that the error message is visible in the hint of the cell with an error (you just have to wait long enough).

 

Cube and dimension design

This part explains things you encounter during designing your dimensions and cubes: default members, attribute relationships, natural hierarchies, unnatural hierarchies, parent-child hierarchies, utility dimensions, granularity, and so on.

Default member

The default member is a member defined as such in a hierarchy or MDX script. It sets the context for queries and calculations unless overwritten by another member of the same hierarchy or implicitly shifted by a member from a related hierarchy. The latter case can lead to unexpected results, therefore, setting the default member should only be done after a careful consideration of which attribute combinations are allowed to be made by end-users.

A default member should be specified whenever the All member of that hierarchy is disabled (whenever the IsAggregatable property is set to False).

Attribute relationships

Attribute relationships initially exist only between the key attribute of a dimension and all other attributes of that dimension. Creating additional attribute relationships, whenever possible, improves the dimension design. That way, the SSAS engine knows what we as humans know – which attributes can be used to form multi-level structures called natural user hierarchies. This allows for many optimizations, however, it has to be done with extra care. Implicit members in tuples heavily depend on this, these relationships are used by the engine to determine them.

Natural versus unnatural hierarchies

Natural hierarchies are user hierarchies where members of lower levels have one (and only one) member above them, and so on for each level. Their neighboring levels are built from related attributes. Unnatural hierarchies are those built from unrelated attributes which means that members from lower levels might have more than one parent. For example, combining product color and size in a single user hierarchy would create an unnatural user hierarchy. Natural user hierarchies are those found in Adventure Works.

Parent-child hierarchies

Parent-child hierarchies are a type of unbalanced hierarchy where the number of levels can vary. However, that comes with a price in performance – aggregations can only be built on the root member and the leaves. If the number of levels can somehow be fixed, it is worth considering converting them into ordinary user hierarchies with the HideMemberIf option turned on to simulate the parent-child effect. This way, aggregates can be built on any level which should increase the performance. Small parent-child hierarchies can stay as such if the flexibility of levels they offer suits you. A significant performance boost (as a result of the conversion) will be noticeable on parent-child hierarchies with many members primarily.

Utility dimension

The utility dimension is a special dimension used for calculation purposes. It is often designed with the disabled root member and the default member being the first member of that dimension. Additionally, that dimension should not be linked to any measure group in the cube because its attribute doesn't exist in any measure group. Finally, members have calculations relative to the dimension's default member, assigned to them in the MDX script.

The assignments for members of the utility dimension apply to all measures, unless specified otherwise. Therefore, instead of having a lot of calculated measures, the utility dimension (hence the name) allows to limit that by an order of magnitude. Namely, calculated measures based on the repeating principle or formula (a ratio) can be replaced with a single member on a utility dimension with a same formula (written in a more generic way) assigned to them. In other words, we're transforming M measures *N variations into M measures + N members of the utility dimension.

Using regular members in utility dimension will prevent problems that some SSAS front-ends have with calculated members. For that you'll have to create a table in DW or a named query in DSV.

Asymmetrical reporting, that is having arbitrary shaped sets on axis, might be another problem introduced with this approach. If that's the case with your tool, you should consider creating additional calculated measures so that those arbitrary shaped sets can be presented using calculated measures only and hence become non-arbitrary.

Dummy dimension

Dummy dimension is similar to the utility dimension, yet different. The root member remains this time enabled and no calculations are assigned to individual members.

The idea of having a dummy dimension is being able to show its members in the result of a query or iterate on that dimension in order to achieve some goal. Remember, SSAS operates on predefined structures only (dimensions, hierarchies, levels, and so on). If we have a set of artificial members, members not belonging to any dimension but still required in the result of the query, we can either define them, one by one, as calculated members on a hierarchy that is not used very often in queries (so that it doesn't interfere with other hierarchies), or we can build a new dimension with those members, a dimension not related to any measure group and hence very convenient for browsing and displaying result on it. The first option is a hack, the other is the dummy dimension.

In the case of a dummy dimension, assignments are not made on its members. Instead, they are made on existing cube structures, for example on one or more measures. This way we can allocate measure's value across the dummy dimension members, based on a particular calculation. Other measures will not change across dimension members. This suggests controlled exposure to end-users, for example, in a perspective or similar.

Granularity

Granularity is the term that describes the resolution at which dimensions are captured in a fact table or loaded into the cube. In other words, it represents the level of detail at which a particular process is recorded, both in DW and in the cube. The good thing about SSAS is that different measure groups can be mapped to the same dimension at different granularity.

Deployment versus processing

Deployment is a process of transferring the structure of a SSAS database or one of its major objects from a development computer to the SSAS server. Processing is a task issued on an already-deployed processable major SSAS object which fills that object with the underlying data. These are databases, cubes, dimensions, measure groups, and partitions.

If an operation modifies the structure of an SSAS database in a way that doesn't affect the aggregations, cube dimensionality, or other properties that invalidate existing dimensions and cubes, it is enough to deploy the changes; processing might be an unnecessary waste of time. An example of such operation is adding new calculations in MDX script. Don't click on the Process icon every time you do something simply because it's there. Yes, it is very convenient, but sometimes it might be faster to check whether deployment of changes is enough, particularly on large objects.

Here's a hint: by installing BIDS Helper, you'll get an icon on the toolbar that deploys changes you make in MDX script. Consider using that free tool available on CodePlex.

 

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries with Microsoft SQL Server 2008 R2 Analysis Services with this book and eBook
Published: August 2011
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

 

        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

 

MDX script

This part covers things you encounter when writing calculations in the MDX script: scopes, assignments, and similar.

Calculate statement

The Calculate statement, usually the first statement of the MDX script, aggregates the values of leaf members to their ascendants. Whenever you need to do something before that aggregation process, put those corrections before the Calculate statement. Otherwise, put the calculations after it. The latter is something you'll do in majority of cases; the former will rarely be required.

Scopes

The scope is a part of MDX script in which we modify the value for a specified subcube. It can be imagined as something similar to computed columns or triggers in the relational database, not in the sense of performance but in terms of behavior, of what it does. The definition of the scope determines when the scope will activate. Inside the scope, we can apply various calculations and assignments.

When scoping the utility dimension, we must remember to specify the default member in every tuple mentioned in the assignment part of that scope, otherwise calculations will be empty because individual members of the utility dimension don't occur in any measure group. Only the default member has a value equal to the value when that dimension is not present in the query.

Scopes can be nested inside each other. Nesting scopes of the same dimension can cause unpredictable results because of attribute relationships, unless done with great care. It is therefore always better to define a single scope for related hierarchies. Non-related hierarchies don't experience those problems and can be freely nested.

Assignments

Assignments are a mechanism for providing values inside the MDX script. They can be performed either inside a scope statement or outside of it, directly in the MDX script. Whatever the case is, they are valid for that particular context only. In the case of a scope, that context is the subcube defined by that scope. In the case of the whole MDX script, it is the whole cube. Their definition is preserved as long as they are not modified inside another subcube, be it inside an inner scope or a scope that follows in the MDX script.

Whenever there's a reference to an object of the cube (a member for example, not necessarily the current member to be precise), an assignment is merely a pointer to that member's value, not the value itself. In other words, assignments are functions which are evaluated in runtime. The control over that evaluation can be partially taken over using the Freeze() statement, though performance-wise it's better not to use it. It's better to redesign the assignment process, if possible, and let the assignments evaluation process flow without any intervention. The right hand side of an assignment is evaluated at query time, but the left hand side of an assignment and scope statements surrounding it are evaluated when the first user with a particular set of security roles connects to the cube.

A typical case where assignments are used, besides the scope of course, is to define a new calculated measure with the value of null and later scope it to something else for a particular subcube.

One of the most important things to realize about assignments is that assignments against regular members will subsequently aggregate up, but assignments against calculated members don't aggregate up. For example, if you run the following assignment and then query the All member, it will have a value:

( [Customer].[Customer].[Customer].Members,
[Measures].[Physical Measure] ) = 100;

But the All member will not have a value due to this assignment:

( [Customer].[Customer].[Customer].Members,
[Measures].[Calculated Measure] ) = 100;

Dynamic versus static sets

Sets defined in the MDX script can be either dynamic or static. Dynamic sets are re-evaluated against the subquery and the slicer of every query. Static sets are constant. Once evaluated in the MDX script when a user first connects, they are used as such in any query. The default option when creating a set in an MDX script is a static set. This means we have to use the prefix DYNAMIC if we want to define a dynamic set.

Both types of sets have their purpose. Use the one that's appropriate in a particular case. Also, see the explanation for Set aliases if you need sets that can evaluate differently for each cell.

 

Query optimization

The Query optimization section is dedicated to explaining concepts relevant to the process of optimizing queries: block computation, cell-by-cell mode, arbitrary shaped sets, varying attributes, sparse and dense calculations, types of cache, and so on.

Block-computation versus cell-by-cell evaluation mode

Block-computation mode, also known as subspace mode, is a process of evaluating cells in blocks. It is a mode that is many times faster than the cell-by-cell mode. Most MDX functions (http://tinyurl.com/Improved2008R2) are optimized to work in that mode. However, by applying non-optimized calculations we can prevent that, accidentally or on purpose.

Arbitrary shaped sets

Arbitrary shaped sets are sets which don't form a compact space. Usually, they result from operations with tuples, not sets.

What's specific about them is that they cannot be used in scopes. The solution is to cut them into several smaller non-arbitrary sets on which scopes can be applied. Deciding how to perform that cut should not be a problem and here's how. First, detect the outter boundaries of the scope, that is, hierarchies and the range that their min and max referred to members form. That's a potential space the scope's subcube can occupy. Now, visualize the part of the space that the specified scope occupies. If the two don't match, the shape is arbitrary. In other words, if there are holes inside the potential space, you need to break the scope's subcube into as little as possible smaller but compact subcubes (subcubes with no space in them) and use each in its own scope statement.

Varying attribute

Varying attribute is an attribute that is referred to inside a calculation and which depends on another cube object, for example, another dimension or a measure. The problem is that the varying attribute can prevent block-computation mode if the dependency is too complex to be resolved by the SSAS engine. The solution is, if possible at all, to expand the tuple carrying the varying attribute with the root member of dimensions not used in that tuple but mentioned elsewhere in the query. This can help SSAS to decide to evaluate the expression using the block-computation. Not always, but it's worth a try.

The concept of varying attribute is explained in more detail in the Analysis Services Performance Guide:

http://tinyurl.com/PerfGuide2008

Static versus dynamic calculation

A calculation is estimated as static if it returns the same value for every cell. The named set defined in the query is an example of such calculation because it is evaluated only once and used as such in all cell calculations. Dynamic calculations are those estimated to return different results per cell. These functions typically reference the current members of hierarchies.

If SSAS estimates a calculation as being static and we want it to become dynamic, we can correct that estimate by inserting a non-deterministic expression inside the calculation. For example, using the Rank() function over the current member. This will naturally slow down the calculation, but we will have achieved what we wanted.

Late binding functions

Late-binding functions like the StrToMember() function are functions that cannot be evaluated in advance. SSAS naturally always tries to determine the result to be returned in advance order to estimate whether the block-computation mode is a viable solution or not. If the engine doesn't know what the result will look like, it can only use the cell-by-cell mode. What might help to know the result in advance in this case are two things. First, that the string passed in is a static expression. Second, that an optional argument is supplied, the keyword CONSTRAINED.

Sparse versus dense expressions

Sparse expressions are expressions where only a small part of the subcube contains the values. This is where SSAS tries to use the block-computation mode, hence they are evaluated fast.

Dense expressions are expressions which are always (or most of the time) not null. They are usually evaluated in the cell-by-cell mode which is slow.

For performance reasons, it is always better to preserve the sparsity of expressions whenever possible by using nulls as the result of calculations, not 0 or some other scalar value.

Cache

The cache is an internal object where SSAS preserves the result of recent calculations. Cache can help speed up the query response time. We can also warm it up by firing a set of predetermined queries after the cube gets processed.

SSAS provides 3 types scopes of cache: global, session, and local cache. Only one cache scope can be used at a time.

 

Types of query

The last part of the article lists the type of queries you can write against SQL Server Analysis Services and briefly explains each of them.

MDX query

MDX query is a query written in MDX language and whose purpose is to return the result from an SSAS cube. It consists of the obligatory SELECT and FROM parts while the others are optional. If the query has axes, all axes up to the one with the highest ordinal must be used in the query. Providing an empty set (i.e. {}) is a way of bypassing one of the axes with a lower ordinal. In the WITH part of the query we can define calculated members, sets, and cells. In the WHERE part of the query we set the context to be used in query. That part is also known as the slicer. In the FROM part there can be a cube or another query, called the subquery or subselect. Cell properties and dimension properties are elements we can also include in the query. They bring additional information about objects used in the query.

XMLA query

XMLA query is a type of language or protocol for issuing XMLA commands to an SSAS server. The commands are written in XML format, as is the result returned by the XMLA query. Commands range from processing the cube to modifying cube structure.

Drillthrough

Drillthrough is a type of query and also a type of action available in SSAS cubes. This query returns detailed level data about a single cell. Data is returned directly from a single measure group in an SSAS cube, not its underlying fact table. Drillthrough works on regular members only.

DMVs

Dynamic Management Views or DMVs are a special collection of system tables which can be queried using SQL-like syntax executed inside an MDX query. The server recognizes the SQL-like dialect and returns the data in tabular format.

There are several categories of DMVs; some are used to explore the cube structure, others to analyze the server's current state.

Stored procedures

Stored procedures are one or more functions implemented in a .NET assembly or COM DLL that is registered on the SSAS server. They extend the functionality of the server by providing new functions to be used in queries or to be called directly when required, outside of the query. Their biggest problem in their usage is that they run in cell-by-cell mode when used inside the definition of cells. They require expensive unmanaged marshalling for each call. Look for the summary of best practices for usage in this thread:

http://tinyurl.com/DarrenStoredProcedures

In order to write them, programming skills are required.

 

Summary

In this article we took a look at some terms and concepts relevant to MDX.


Further resources on this subject:


About the Author :


Tomislav Piasevoli

Tomislav Piasevoli (tomislav@piasevoli.com) is a Business Intelligence Specialist with years of experience in Microsoft SQL Server Analysis Services (SSAS). He lives in Croatia and works for SoftPro Tetral d.o.o., a company specializing in development of SSAS frontends and implementation of BI solutions.

His main interests are dimensional modeling, cube design, and MDX about which he blogs at http://tomislav.piasevoli.com. Tomislav also writes articles and speaks at regional conferences and user groups. For his contribution to the community, Microsoft awarded him with the Microsoft SQL Server MVP title.

Books From Packt


Microsoft SQL Server 2008 R2 Master Data Services
Microsoft SQL Server 2008 R2 Master Data Services

Microsoft SQL Azure Enterprise Application Development
Microsoft SQL Azure Enterprise Application Development

Microsoft SQL Server 2008 R2 Administration Cookbook
Microsoft SQL Server 2008 R2 Administration Cookbook

Microsoft SQL Server 2008 High Availability
Microsoft SQL Server 2008 High Availability

Microsoft Data Protection Manager 2010
Microsoft Data Protection Manager 2010

Microsoft Visual Studio LightSwitch Business Application Development
Microsoft Visual Studio LightSwitch Business Application Development

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Microsoft Dynamics AX 2009 Administration
Microsoft Dynamics AX 2009 Administration


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software