Chris Webb's BI Blog

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

Archive for July 2008

PASS Camp Germany 2008

leave a comment »

Are you looking for a public MDX course in Europe? I teach private MDX courses (I’m in the process of building a new website with all the details on, now I’ve left Solid Quality – drop me an email if you’re interested in one in the meantime) but I’ll also be teaching a 3-day public MDX course at PASS Camp Germany from the 2nd to the 4th of September this year:
http://www.sqlpasscamp.de/default.html

Although the website’s in German my session will be in English, and although the event is aimed at the German market I’m sure anyone from anywhere will be more than welcome!

UPDATE: here are the details in English:
http://www.sqlpasscamp.de/mail/news1a.html

Written by Chris Webb

July 4, 2008 at 5:13 pm

Posted in Events

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

Follow

Get every new post delivered to your Inbox.

Join 3,112 other followers