Creating a new measure that only sums data in specific part of cube

SQL Server Analysis Services

Jeff Roedel


I have a cube with about 6 dimensions but only two are important right now. My main fact table is called MRSTATS. The dimension tables are Dim_Client#, and Dim_Test. I would like a measure called HospVol that only aggregates rows where MRSTATS.[Client#]=2. Then I need a measure called HospRVU that multiplies HospVol Measure by Dim_test.RVU. How do I got about configuring this

Let me know if you need more information or if i'm going at this the wrong way. These values will be used in an SSRS report. Thank you very much.

Top

Re: Creating a new measure that only sums data in specific part of cube

Bryan C. Smith


I'm a little unclear on how you intend to set up your query, so my example may not be quite what you are looking for. Anyway, here is a sample query for AdventureWorks. I've broken down the calculations to make this easier to read:

Code Snippet

with member [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[List Price] as

IIF(

[Product].[List Price].CurrentMember.MemberValue=0,

NULL,

[Product].[List Price].CurrentMember.MemberValue)

member [Measures].[Bikes Sold] as

[Measures].[Reseller Sales Amount Bikes] /

[Measures].[List Price], format="#,#"

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[List Price],

[Measures].[Bikes Sold]

} on 0,

NON EMPTY [Product].[Product].[Product].Members on 1

from [Adventure Works]






Top

Re: Creating a new measure that only sums data in specific part of cube

Jeff Roedel

To clarify things a little bit this is for labratory testing and Dim_Section is the section of the lab where the test was performed, Dim_Test is a table of the different tests and their various billing codes. Dim_Client# is a table of different Clients who we do testing for.

Alright the rows in my table in SSRS is going to be based on members of my Dim_Section Table and then my Dim_Test table and the columns are going to be attributes of the Dim_Test table as well as a few measure groups.

The only measure I have now is Volume which is a sum of the my item_qty field. I want a measure called HospVol which is the summation of the item_qty when the client# = 2 and all so a OtherVol for rows where client# !=2

I have done no work with MDX query text just the BI SSAS interface in SQL2005.



Top

Re: Creating a new measure that only sums data in specific part of cube

Bryan C. Smith

Take a look at this code. The main thing to look at are the expressions at the top. Don't get too concerned with rows and columns just yet. SSRS will "flatten" your queries so that you establish the structure in the report. Main thing is to get the formulas right for you needs.

I wrote these samples against the AdventureWorks sample SSAS OLAP database. I'd recommend working with that database a bit to get comfortable with these concepts before applying them to your cubes.

Good Luck,
Bryan

Code Snippet

with member [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[Reseller Sales Amount Not Bikes] as

AGGREGATE(

EXCEPT(

[Product].[Category].[Category].Members,

[Product].[Category].[Category].[Bikes]

),

[Measures].[Reseller Sales Amount]

)

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[Reseller Sales Amount Not Bikes]

} on 0,

NON EMPTY [Date].[Calendar].[Calendar Year].Members on 1

from [Adventure Works]





Top

Re: Creating a new measure that only sums data in specific part of cube

Jeff Roedel

Is there a way to set up my intended measure inside Business Intelligence SSAS without using MDX

Otherwise could you give me a little insight on what the MDX code is actually used for Is the MDX code used in building a cube and if so where can i edit the MDX code of the cube. Or is MDX used to run queries against the cube

This is sort of what I get out of the above query: It sets up two new measures based on one existing measure. I dont know what 'on 0' means at the end of the select statement. The query uses the three measures and aggregates each for each year in the database. (Again, i dont know what 'on 1' means) and then [Adventure Works] is probably the cube name

Could you direct me to a webpage about MDX

Thanks for your help and patience.


Top

Re: Creating a new measure that only sums data in specific part of cube

Bryan C. Smith

So, MDX is used for two things: defining expressions and assembling cell sets. The WITH MEMBER portions at the top of the sample are the expression approach. The SELECT ... FROM portion of the sample are the cell set construction part.

The SELECT stuff is only used when building queries. In cube design, you won't take advantage of this stuff. So, if the "on 0" and "on 1" stuff don't make a lot of sense, that's OK for now.

The WITH MEMBER stuff is the foundation for the MDX you would embedded in your cube as calculated members. Take a look at Books Online for topic "CREATE MEMBER statement".

I'm not aware of any books that cover MDX really deep. You may want to browse some of the books on SSAS as they should all cover calculated members and some basic MDX queries.

The best MDX learning resource I've found is this class http://www.hitachiconsulting.com/page.cfm ID=trainingHandsOnMDXQueries. The schedule for the class is available at http://www.hitachiconsulting.com/page.cfm ID=trainingSchedule and you would just need to click the email link at the bottom of the page to get the details on how to register. BTW, I need to disclose I work for Hitachi Consulting, the company providing this course.

Good luck,
Bryan





Top

Re: Creating a new measure that only sums data in specific part of cube

Jeff Roedel

So I think i've found the direction I need to go with this. I think I need to be making a caluculated member to do the aggregations I need. So this is the MDX I have right now based on the example you provided and some templates I found.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[HospVol]

AS AGGREGATE

(

EXCEPT

(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]

),

[Measures].[Item Qty]

)

FORMAT_STRING = "Standard";

The error I'm getting right now is "Error 1 Query (4, 1) Parser: The syntax for 'CREATE' is incorrect. 0 0"

How does this look to you Thank you so much for your assistance.


Top

Re: Creating a new measure that only sums data in specific part of cube

Jeff Roedel

For anyone else looking for MDX documentation, here it is:

http://msdn2.microsoft.com/en-us/library/ms145506(SQL.90).aspx


Top

Re: Creating a new measure that only sums data in specific part of cube

Bryan C. Smith

It looks like in the EXCEPT function you are trying to return all the members of your Dim Client dimension's Dim Client hierarchy except the member with a key of 2. Is that correct

By executing the MEMBERS function against the hierarchy without specifiying the level, you will get the ALL member in your list. So, I would recommend using [Dim Client #].[Dim Client#].[Dim Client#].Members instead to return just the leaf level members and not the ALL level member.

Regarding the error, I don't know exactly what's going on with that. I would recommend creating the calculated member using the form- view of the Cube Designer's Calculations tab. This will help you get the overall syntax correct.

In the form view, you will need to give the calculated member a name, [HospVol]. (Be sure to include the brackets.)

You will need to identify it's parent hierarchy. In this case, select MEASURES.

Next, enter your expression: AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Item Qty])

Then, select your format string and visibility.

Finally, set the Non-Empty Behavior. This one is a little confusing but basically you identify a measure that when the measure is empty, the calculation is not performed. For this calculation, you can probably just ignore that.

Once you've set up the calculated member, just deploy your cube (you don't need to reprocess if the cube is already processed).

Good luck,
Bryan





Top

Re: Creating a new measure that only sums data in specific part of cube

Jeff Roedel

Hmm, I was going for nothing but Client# 2 so i'll have to look at that. It turns out that i'm going to need the except client# 2 so this one is still useful. I actually am using the form in the cube designer so thats probably why it was giving me the error with the create member.

I put in your expression and it evaluated but the aggregations are wrong because the values are higher than that of the total volume. I have confirmed that [Volume] has correct values so it must be a problem with the calculated member.

Here is my exact expression

Code Snippet

AGGREGATE(

EXCEPT(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

Could it be a problem with how is aggregating That should be based on [Volume] though correct

If it clears up anything with the Client# dimension, there are to attributes, [Dim_Client#] and [Org Client]. There's not exactly much of a heirarchy going on.

Would the aggregation of just Client# 2 be:

Code Snippet

AGGREGATE(

FILTER(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

This calculation takes a long time and returns blank cells. Maybe FILTER is the wrong keyword.

Am I going to run into any problems creating calculated members inside a non-measure dimension that multiply an measure by an attribute of the dimension

Aside from this stuff, I think i'm pretty much ready to take this on by myself. Thanks for all your help


Top

Re: Creating a new measure that only sums data in specific part of cube

Bryan C. Smith

Your expression needs to build a set of members and then cross join it to the measure of interest. This will generate a set of measure values associated with each member. That set can then be aggregated into a single value.

So, in the case where you want to get the set of all members that are NOT client key 2, the EXCEPT expression will give you that. If you want the set of just client 2, then just ask for that one member. Here is the set definition for each of these:

Code Snippet

EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2])

Code Snippet

[Dim Client#].[Dim Client#].&[2]

So, we have a set of members. Now we need to cross join this to the measure of interest, [Measures].[Volumes]. This will give us one measure value for each member in the set. If we have just one member in the set, SSAS can return just that one value. If we have multiple members, we need to aggregate those values to get a single, returnable value.

Code Snippet
AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Volumes])

Code Snippet
([Dim Client#].[Dim Client#].&[2],[Measures].[Volumes])

As for the syntax problems, I'd kinda need to play with the cube to figure that one out. That's why we often use the WITH MEMBER syntax on a SELECT statement to get these things defined before moving them into the cube.

Anyway, hope that helps. Good luck.

Bryan





Top