Chris Webb's BI Blog

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

Measure Expressions

with 6 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

6 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 [...]


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 2,867 other followers

%d bloggers like this: