I know this blog has been rather light on useful content (as opposed to links) recently, and I’m sorry – it’s not that I’m not learning anything new, I am, but much of it is going into my chapters for MDX Solutions 2E rather than on here. I’ll try to get round to blogging about stuff like the HAVING clause in MDX and the MemberValue() function sometime soon if I can do so without giving away all the juicy information that you’ll want to buy the book for, and if Mosha (or even BOL) don’t beat me to it…
But here’s a cool cube design feature that doesn’t cause any conflict of interest for me – measure expessions. The easiest way to explain what they are is to explain one scenario where they’re useful. In AS2K, if you’ve tried to model currency conversion, you’re probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can’t then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house – they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define.
So what does a measure expression look like and where do you set it? In Visual Studio, in the cube tab of the cube editor, you’ll see the MeasureExpression property in the properties pane when you highlight a measure. Click on this and you get a text box where you can enter your measure expression. This has to be of the form
M1 op M2
where M1 is a measure from the same measure group as the measure you’re currently editing, M2 is a measure from a different measure group which shares at least one dimension with the current measure group, and op is either the * or / operator. Here’s an example:
[Measures].[Sales]*[Measures].[Currency Rate]
In previous builds AS has been very picky about having square brackets around the measure names and things like that, so if you’re trying this yourself make sure you copy the above example very closely. There are several other restrictions too, such as the fact that neither of the measures can be distinct count measures, so you can see that your options here are quite limited. That said for most scenarios they should offer all the flexibility you need to do the job.
How much faster are they than normal MDX, then? That’s the million dollar question, and I have to say that I don’t know yet. Even if I did I’m sure there would be some clause in the beta licence that stopped me from publishing performance data, and anyway it’s not fair to test performance on a product that hasn’t been released yet. Hopefully it will make a big enough improvement to make the headaches that accompanied this kind of requrement in AS2K a thing of the past. If anyone else out there has used this functionality and would care to share their experiences I’d be interested in hearing from them…
Measure expressions also allow you to do some really cool things with many-to-many dimensions too, but I’m going to save that for a future blog entry; this one’s already long enough.
UPDATE: No need to blog on measure expressions and m2m dimensions, actually, because Richard Tkachuk covers the subject in his white paper on m2m dimensions here.
Couple of comments:1. MemberValue is not a function MemberValue(…), but rather a property on a member – <member>.MemberValue, much like .Name, .Level etc2. I would challenge your statement that measure expressions are _much_ faster then equivalent MDX. Try to define the following MDX script:SCOPE (Leaves(), Measures.M1); This = Measures.M2 * Measures.M3;END SCOPE;And you will be amazed by how fast it will work.
I have a question: I have the case where one of the operands (the measures: [measures].[sales]) is calculated measure, how can I used it ? Knowing that this solution “Measure Expression” suites my need because I need to multiply then aggregate. Having an MDX directly wouldn’t help me since it will aggregate both measures then multiply them. I could use Sum (descendants ([dimension1].[hierarchy1].CurrentMember,, leaves), [Measures].[Sales]*[Measures].[Currency Rate]) in MDX but I have about 10 dimensions and I don’t know which dimension(s) the report designer will use in their report when using my measure. I am in a real trouble! Many thanks for your help in advance.
You can’t use calculated measures with measure expressions. However you can do what you want to do with a scoped assignment with MDX and it should perform quite well. Take a look at this post on currency conversion calculations (where you also need to multiply before aggregating, and can’t know in advance which dimensions will be used) for an example of how to do this: http://cwebbbi.wordpress.com/2011/01/12/tuning-the-currency-conversion-calculations-created-by-the-add-business-intelligence-wizard/
Thanks for this blog Chris
I was trying to create a calculated member which consists of “Three” operands
for example
[Measures].[OCOS Amount] * [Measures].[OCOS Toggle] / [Measures].[Currency Rate]
However it seems that SSAS refuses to accept more than two operands, any two combination of these operands together works but it just doesn’t work when I have three or more
Notice that the [Currency Rate] and [OCOS Toggle] are in two separate dimensions which are linked with many-to-many relationship to the main measure group where [OCOS Amount] resides.
Any thoughts on how to get this to work.
Thanks!
You can’t create a measure expression with three operands – Only two are allowed. You’ll have to use MDX instead.
When you say using MDX … do you mean using similar technique explained in the link below.
http://cwebbbi.wordpress.com/2011/01/12/tuning-the-currency-conversion-calculations-created-by-the-add-business-intelligence-wizard/
Thanks!
Yes, exactly that
Hi Chris,
We are currently facing issues with a calculated member which perfoms Aggregation by Multiplications using Logarithmic functions.
The business rule which should be implemented is something like this (X+1)*(Y+1)*(Z+1)-1 where x,y,z are %MTD values. This business rule is implemented in the cube using the below logarithm transformation:
exp(ln(x+1)+ln(Y+1)+ln(Z+1))-1. The calculations work fine and provide expected results when the %MTD values(X,Y,Z) are positive numbers. The issue arises when any of the %MTD value has a negative return values, as the Log functions cannot be applied on negative numbers.
http://sqlblog.com/blogs/mosha/archive/2006/12/18/how-to-aggregate-values-as-a-product-multiplication-in-mdx.aspx provides the detailed approach used for our calculated members.
Any help to fix this issue or workaround would save us as the these measures hold major impact on clients end reporting.
Thanks in Advance.
Ram
Maybe you could use the ABS() function to make all the values positive, then count the number of negative values there are and if that number is odd multiply the final result of your calculation by -1?
HI Chris,
Many thanks for sparing your precious time on answering all our queries.
The solution suggested by you looks absolutely correct in theory and worked out with my sample data when I checked it using Excel formulas. But I’m afraid I got no clue on how it can be implemented using MDX as I haven’t worked much on it. We are unable to work out on how we can count the number of negative values and identify if the count is odd or even.
Below is the code sample of our calculated member:
CREATE MEMBER CURRENTCUBE.[Measures].[%YTD]
AS
Exp(Sum(PeriodsToDate([LoadMonth].[Load Month].Levels(1)),
VBA)) -1 )
I know its too much to ask but it would be great if you can please help us out on the logic here?
Sorry to trouble you again on this but we are really short of time and MDX experience.
Regards,
Ram
I suggest you create a calculated measure that checks to see whether the value for each period is negative or not, and returns 1 if it is, 0 otherwise. Then sum up the results for this measure for each month and you have the number of negative values. You can then find if it is even by seeing if there is a remainder when you divide by 2 (something like the code here would help: http://social.technet.microsoft.com/wiki/contents/articles/18186.mod-function-in-mdx.aspx).