THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

MDX functions in Analysis Services 2005

When I discuss with people new features in Analysis Services 2005, I am often asked - "So, what are the new MDX functions you introduced". I always explain, that the changes to MDX in AS2005 are huge and dramatic, but they are not in the area of MDX functions. The real story is about MDX Scripts, about attribute based calculation model which integrates into UDM vision, new language constructs and semantics etc. Of course, we have added few functions, but mostly to support other bigger features. And then I get the next question - "So how can I find out all new MDX functions you added". The good answer should have been - look in BOL, but unfortunately, BOL is not 100% correct, in a sense that it lists functions which are not implemented (e.g. Contains, InStr), lists things which aren't functions but rather statements as functions (e.g. Freeze) and doesn't list some functions which are implemented. It hurts me to say it, but if there is one single area in this product which is completely messed up - it is BOL. AS BOL is incomplete, outdated and sometimes plain wrong. Anyway, if you really want to find out all MDX functions supported, you can send MDSCHEMA_FUNCTIONS schema rowset request using either one of AS APIs - XMLA, ADOMD.NET, OLEDB, ADOMD etc and find out the full comprehensive list. So let's do it against AS2005 and check all the new functions which weren't there in AS2000.

New AS2005 functions

Aggregate( [, ] )

Well, this isn't new function, but there were significant changes around it. First, Aggregate works with Distinct Count measure, as well as with semiadditive measures - even when the set for Aggregate includes Time dimension. Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it. I.e. If the current measure is Ratio: Sales/Cost, and we are computing Aggregate({USA, Canada}), then instead of error, it will return Aggregate({USA, Canada},Sales)/Aggregate({USA, Canada},Cost).


This function is closely related to .Current, i.e. it can only be used with set alias while iterating over set (most commonly inside Generate). But instead of returning the tuple, it returns its current position in the set, i.e. it serves as loop counter of the Generate loop. One of the main motivations for creating this function was this interesting thread about implementing Pareto/ABC analysis in MDX (if you can read Russian). One simple and illustrative (although perhaps not very practical) example would be to reverse set in MDX:

Generate([Customer].[State Province].members as A, {A.Item(A.Count-A.CurrentOrdinal-1)})


This function works in tandem with connection string property CustomData, and returns its value. This setup is very useful for applications which control both cube design and access methods in order to manage application users and cube security without creating Windows users. Unfortunatelly CustomData property doesn't work from OLEDB in AS2005 RTM, so use of ADOMD.NET or XMLA is required. It will be fixed for SP1, and perhaps even as RTM QFE.

Exists( [, ] [, ])

This function perform manually what autoexist performs automatically - i.e. it does Exists against dimension table(s). The obvious difference from the CrossJoin function (which like the rest of MDX employs autoexist) is the ability to specify filter set for Exists. There is one more variant for this function, which accepts 3 parameters instead of 2. The third parameter is the name of the measure group. In this case, Exists will be executed against fact table of that measure group instead of against dimension table(s). This variant of the function most closely resembles deprecated NonEmptyCrossJoin function.


OK - Existsing is technically not a function. It is an operator, like +, -, * etc. Only it is not binary operator, it is unary operator - just like unary minus or NOT operators. So the proper way to use it is to say "Existing set". However, if parethesis are used around the set, it starts looking like a function "Existing (set)". Anyway, Existing operator is similar to the Exists function, but it uses as a filter set the current coordinate. The classic example for Existsing is to count number of customers who satisfy current slicing conditions:

Count(Existing Customers.[Name].Members)

Then, if calculated member with this expression is used in a query, which slices on USA - it will return the number of customers in USA.

KPIGoal, KPIStatus, KPITrend, KPIValue, KPIWeight

All those are KPI helper functions. Example of usage can be found here . One interesting note about these functions which is often overlooked, is that they return MDX Member object rather then numbers. I.e. it is possible to write the following query

SELECT {KPIValue("MyKPI"), KPIStatus("MyKPI")} on COLUMNS FROM cube

The curious reader will wonder what are these members that KPI functions return. Inspection of member unique name will show something like [Measures].[MyKPI Value]. These are actually hidden calculated measures which AS automatically creates behind the scenes when KPI is created. AS is smart enough to analyze the expression to check whether such measure (even if calculated) already exists, for example, if expression for the value of KPI is simply Measures.Sales - then it will be used to return from KPIValue function, and no hidden calculated measure will be created.


Another KPI helper function - it returns the MDX Member object, hopefully from the Time dimension. This is like customized Time's default member but the one which could be different for every KPI.

Leaves, Root

These two are interesting MDX functions. They are indended to be used only inside MDX Scripts to define the scope of the calculations (i.e. in the left-hand side of the assignments). Leaves function senses the current measure group, and adjusts the scope accordingly (since different measure groups can have different granularities, there is no single "cube leaves" granularity).


This is helper function also intended to be used inside MDX Scripts to limit the scopes to the measures from specified measure group.


Ability to associate strongly typed numeric value with attribute members is important property of UDM. In the model definition it is done by using MemberValue binding. Let's consider an example of Product dimension, where every product has a weight. We could define Weight attribute, but how can it be used in MDX - for example in order to find all the products lighter then 5 kg. In AS2000, it would've been written like this:

Filter(Product.[Product Name].Members, Val(Product.CurrentMember.Properties("Weight")) < 5)

In AS2005 the same would be achived much simpler (and more efficient)

Filter(Product.[Product Name].[Product Name].Members, Product.Weight.MemberValue < 5)

Note, that for this to work properly it would still require Weight to be related to Product Name (either directly or indirectly), but if [Product Name] is dimension key it is always true. Another, perhaps more elegant solution is to use abovementioned Exists function, i.e.

Exists(Product.[Product Name].[Product Name].Members, Filter(Product.Weight.Weight.Members, Product.Weight.MemberValue < 5))

This approach will work correctly and efficiently regardless whether Weight and [Product Name] are related attributes or not.

NonEmpty( [,])

NonEmpty function gives you the power of NON EMPTY clause, but inside MDX expressions, not just at the top level of SELECT statement. It completely supercedes deprecated function NonEmptyCrossJoin, and fixes all of its shortcomings - i.e. takes into account calculations, preserve duplicates etc. The syntax is also more streamlined and similar to Exists function, i.e. NonEmpty(set, filterset).


Unknown members are AS mechanism to deal with RI issues in the data warehouse. For more details - read this excellent whitepaper by T.K. Anand. .UnknownMember is somewhat similar to .DataMember, in a sense that it is another example of when MDX function is used as part of unique name for the member.


In MDX sets have always well defined order of tuples. Every MDX function which operates on the set specifies that order. Sometimes, however, order doesn't matter at all. In SQL, unless ORDER BY clause is used, the order of rows is not deterministic. Unorder(set) achives the same in MDX. It is used in order to get better performance by not spending time on proper ordering of the set. Note, that some MDX functions do it automatically, for example SUM(set) is equivalent to SUM(Unorder(set)). The best way to see this function in action, is to start AS profiler, and send SQL query to the cube. In AS2005 SQL queries are translated to MDX, and it is easy to see the MDX queries in a form of

SELECT NON EMPTY Unorder(L1.members*L2.members*...)

Internal functions

Now, there are some other MDX functions that can be seen being generated in AS Profiler. Those are internal MDX functions for internal operations, and they are not supposed to be used by anybody but AS itself. For example, during OLAP Mining Models processing, you can see ordering by function named DataId. Don't use these.

Bad/Deprecated functions

Some functions are supported by AS2005 for backward compatibility purposes, but either deprecated (which means that they could be removed in the future release) or simply bad to use. The worst offenders are listed below


With MDX Scripts and the procedural view of execution, the cube designer doesn't need to worry anymore about passes - it is all taken care of automatically by the system. CalculationCurrentPass therefore while nominally still produces correct results, these results will be pretty much useless.


Same comment as above. In general, CalculationPassValue was most commonly used in two following scenarios:

1) Go to the previous pass, i.e. CalculationPassValue(Measures.CurrentMember, -1, RELATIVE)
There is no need in this because of automatic recursion resolution performed by AS engine. For the practical example of such recursion resolution, see this insigtful whitepaper by Richard Tkachuk.

2) Go to some hardcoded pass, usually to pass 0 where no calculations exist, i.e. CalculationPassValue(Measures.CurrentMember, 0, ABSOLUTE)
This still can be done, but FREEZE statement makes it unnessesary. Also FREEZE statement is more poweful then this.


If UDM is designed correctly, then going from one attribute to another happen due to their relationships. LinkMember, on the other hand does this linkage by comparing keys, which while can model both many to one and one to one relationships, is still only a hack. Exists function can be used for one to many and many to many relationships.


UDM vision, is that the entire model is built as a whole in single UDM. Therefore, there should be no reason to look into another cube. Like in AS2000, performance of LookupCube could be an issue, and for both these reasons it should be avoided.


People who follow my blog/newsgroup postings, know that I dislike this function most. I am the one who invented near the end of AS2000, and while it solved some problems, it is very kludgy solution. I am really not proud of it. Luckily, in AS2005 there is no need for it, because most of the performance problems that it used to solve are solved natively in the engine, and for other cases there are NonEmpty and Exists functions.


Use of .Properties should be avoided as much as possible. Filtering by member properties should be rewritten by using CrossJoin (or Exists). Lookups should be rewritten by using related attributes and .MemberValue. There are very few reasons to use .Properties.

StrToMember, StrToSet etc

String manipulations are bad. Usually they were required to do conversions from the results of UDFs, but with AS2005 supporting reach MDX object model in stored procedures (server side ADOMD.NET) this is not a reason anymore. For more details on stored procedires and MDX object model see this blog .

Published Tuesday, October 11, 2005 3:57 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement