Chris Webb's BI Blog

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

Dynamically generating session calculated members in code with Analysis Services 2008

with 3 comments

One problem I come across from time to time is the need to be able to group members on a dimension into buckets, for example if you have a Customer dimension you may want to group your Customers together by age group and run a query that shows sales for the 0-9 age group, the 10-19 age group and so on. If you know what buckets you want then you can simply build this into your dimension in advance by creating another attribute; but what if your users want to be able to change the definition of the buckets themselves from query to query – perhaps they wanted to 5 year age groups rather than 10? I described a bit of a hack you could use on AS2K to do this a while ago, but it doesn’t work any more on AS2005 or AS2008 because it uses the unsupported CreatePropertySet function and in any case it was pretty nasty; but the only alternative is to be able to have your client tool dynamically create large numbers of calculated members (one for each bucket) with the appropriate definition and then use these calculated members in your query.

However with Analysis Services 2008 there’s now another possibility – you can create a stored procedure that can create calculated members within the current session. The "Analysis Services Personalization Extensions" sample shows off a lot of the new possibilities for server-side coding and Michel Caradec blogged about them a while ago too, but neither show how to create calculated members so I thought I’d put an example up here.

The following class generates the calculated members needed to draw a Lorenz curve (as always please excuse the code – I’m no great C# coder and I just threw it together to make the point):

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdServer;

namespace testdc
{
    public class testdc
    {
        public static void Lorenz(string cubeName, int incr, string setToGroup, string numericValue)
        {
            AdomdCommand cmd = new AdomdCommand();
            try
            {
                //drop set
                cmd.CommandText = "drop set [" + cubeName + "].[Lorenz]";
                cmd.ExecuteNonQuery();
            }
            catch
            {
                //if the set isn't there we'll get an error we can ignore
            }
            StringBuilder s = new StringBuilder();
            s.Append("{");
            //create the calculated member for the total of the set
            //try to drop the member first, in case it exists
            try
            {
                cmd.CommandText = "drop member [" + cubeName + "].measures.LorenzTotal";
                cmd.ExecuteNonQuery();
            }
            catch
            {
                //if the member isn't there we'll get an error we can ignore
            }
            cmd.CommandText = "create member [" + cubeName + "].measures.LorenzTotal ";
            cmd.CommandText += "as aggregate(" + setToGroup + " , " + numericValue + ")";
            cmd.CommandText += ", visible=false;";
            cmd.ExecuteNonQuery();
            //create the calculated members for the Lorenz curve
            for (int i = incr; i <= 100; i += incr)
            {
                //try to drop the member first, in case it exists
                try
                {
                    cmd.CommandText = "drop member [" + cubeName + "].measures.Lorenz";
                    cmd.CommandText += i.ToString();
                    cmd.ExecuteNonQuery();
                }
                catch 
                {
                    //if the member isn't there we'll get an error we can ignore
                }
                cmd.CommandText = "create member [" + cubeName + "].measures.Lorenz"; 
                cmd.CommandText +=  i.ToString();
                cmd.CommandText += " as aggregate(bottomcount(" + setToGroup ;
                cmd.CommandText += ", count(" + setToGroup + ") * " + i.ToString() + "/100 , ";
                cmd.CommandText += numericValue + "), " + numericValue + ")/measures.LorenzTotal";
                cmd.CommandText += ", visible=false, format_string='percent';";
                cmd.ExecuteNonQuery();
                s.Append("measures.Lorenz" + i.ToString() + ",");    
            }
            s.Append("{}}");
            //Create set containing all the calculated members
            cmd.CommandText = "create hidden set [" + cubeName + "].[Lorenz] as " + s.ToString();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
    }
}

You need to call the as follows:

call
testdc!Lorenz(
//The name of the cube you’re using
"Adventure Works",
//The % increment for each grouping member
//For example, 5 will result in 100/5=20 members
5,
//The set to group
"[Customer].[Customer].[Customer].Members",
//The measure to use
"[Measures].[Internet Sales Amount]")

When it executes it creates a number of calculated members (the exact number depends on the second parameter) and a named set called [Lorenz] with them all in. Having done that you can run a query and request all the members in the named set:

select [Lorenz] on 0 from [adventure works]

You can call the sproc many times in the same session with different parameters and it should always work, although I’ll admit it could do a better job of dropping old calculated members. It would also have been able to create a function that did much the same thing but which returned a set containing all these calculated members, so you could call it direct from your query and not have to call the sproc beforehand, but I couldn’t get that to work unfortunately.

Written by Chris Webb

July 3, 2008 at 6:25 pm

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. > It would also have been able to create a function that did much the same thing but which returned a set containing all these calculated members, so you could call it direct from your query and not have to call the sproc beforehand, but I couldn\’t get that to work unfortunately
     
    This should never work, and I hope that AS has plugged all the holes and won\’t let you do this. Changing session state from within sproc call is very dangerous. It\’s probably OK when sproc is called through "CALL" syntax, but imagine that you run MDX query, and in the middle of execution it starts modifying session state. The result could be unpredictable (anywhere from crash, deadlock, wrong results etc).

    Mosha

    July 3, 2008 at 10:45 pm

  2. Yes, I can see why it would be dangerous. But I still see the need for something like CreatePropertySet, to create a number of calculated members dynamically for the purposes of grouping. Maybe something to add to my wish list for the next release…

    Chris

    July 4, 2008 at 9:21 am

  3. trying several news on the blog they have arrived here Indeed very
    made this blog optimal explanation thanks in order to have published
    it of all on hand we Faithfully yours Absolut-Desing

    absolutforyou

    July 8, 2008 at 10:18 am


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,144 other followers

%d bloggers like this: