Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Measure Expressions

with 14 comments

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.
 

Written by Chris Webb

June 30, 2005 at 1:32 pm

Posted in Analysis Services

14 Responses

Subscribe to comments with RSS.

  1. 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.

    Mosha

    July 1, 2005 at 7:57 am

  2. 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.

    Amir

    July 7, 2011 at 4:00 am

  3. [...] at least one dimension with the current measure group, and op is either the * or / operator. (See Chris Webb’s blog post for more [...]

  4. [...] at least one dimension with the current measure group, and op is either the * or / operator. (See Chris Webb’s blog postfor more [...]

  5. 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!

    Haytham Othman

    May 2, 2014 at 4:24 am

  6. Yes, exactly that

    Chris Webb

    May 3, 2014 at 6:49 pm

  7. 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

    sai ram

    May 21, 2014 at 11:19 am

    • 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?

      Chris Webb

      May 21, 2014 at 7:45 pm

  8. 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![Log]([Measures].[% MTD] + 1))) -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

    sai ram

    May 22, 2014 at 2:58 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: