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.

Seeing that Marco Russo has released his book "Programming Microsoft LINQ" reminded me of a conversation I had with him a while ago about something I've heard various people ask about



over the last year - will there be a LINQ to MDX?

Before we go on, I should state that it's my opinion that there isn't a big enough market out there for anyone (Microsoft or a third party) to justify spending time developing LINQ to MDX. That's not to say that I wouldn't want to see it - I would - just that I doubt anyone much would use it. MDX remains too much of a niche language, and off-the-shelf tools work well most of the time so there's less need to write custom MDX-generation code. As far as I know Microsoft isn't planning on developing LINQ to MDX and I'd be surprised if it ever did, so this will remain a theoretical discussion.

But for the sake of argument if you were to implement LINQ to MDX the main problem you'd have to tackle would be the same one you have with using MDX in Reporting Services and Integration Services: MDX can't guarantee fixed column names for any given query. However I had an idea on how to avoid this, and that is to think in terms of LINQ to MDX sets rather than LINQ to MDX queries. So for example if you take the following SQL query on a dimension table:

Select Year, Quarter, Month
From TimeDim
Where Year=2008 and (Month=March or Month=April)

that would then translate easily into the following MDX set expression:

{([TimeDim].[Year].[2008], [TimeDim].[Quarter].[Q3], [TimeDim].[Month].[March]), ([TimeDim].[Year].[2008], [TimeDim].[Quarter].[Q3], [TimeDim].[Month].[April])}

MDX sets have to be made up of tuples containing the same dimensionality, and if you think of a set's dimensionality in terms of columns in a SQL SELECT statement then you can see how that might map onto LINQ concepts. Instead of using LINQ to create an MDX SELECT statement directly, you'd use LINQ to create MDX sets and then pass all of these sets into another function which would then run the query using the sets created as axes.

Since I'm no LINQ expert this was the point where I dropped a mail to Marco to ask him his opinion; he thought it was feasible and even came up with an idea of what the code might look like in C#:

var timeSet = from period in cubeBudget.TimeDim.Members
            where period.Year == 2008
                    && (period.Level == TimeDim.Level.Year
                        || period.Month == "March"
                        || period.Month == "April" )
              select period;

Var measures = from measure in cubeBudget.Measures
               Where new string[] { "Sales", "Quantity", "Price" }.Contains( measure.Name )
               select measure;

Var query = from measure in measures
            from period in timeSet
            select new { period.Name, measure.Name, measure.Value };

var cellset = from cell in cubeBudget
              where cell.Columns( measures )
                    && cell.Rows( timeSet )
              select cell;

In the code above, timeset would define the set of members on the Period dimension you wanted to use and measures would return the set of measures. Then you could use them in two ways: query would return a flattened rowset and cellset would return a cellset. But this all seems very convoluted and probably just as confusing to the average developer as raw MDX.

Another alternative approach would ignore MDX altogether and query Analysis Services using SQL directly (see http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!751.entry for more details on this topic), although I'm sure you'd run into the limitations of the SQL that is supported very quickly and in any case you lose all of the flexibility and functionality of the MDX language when you do this.

Maybe if we're looking for a way to programmatically generate MDX then LINQ isn't the way to do it. It's something olap4j is working towards

and they have taken an approach that is much more in tune with the multidimensional nature of MDX. One of the 'open issues' that caught my eye in the section of the olap4j spec that deals with this functionality is the question "Is this API at the right level, or is it too close to MDX?", meaning I guess that it would be all too easy to come up with an interface that is just as complex as MDX itself. Where would you draw the line between ease-of-use and functionality? Is it the MDX language that is confusing for people, or the multidimensional concepts (concepts that any interface would have to reflect) that underpin it? Can you abstract MDX to an interface to make it easier to use? I wish I knew more Java so I could test drive olap4j - is there anyone reading this who is using it? As always, I'd be interested to hear anyone's thoughts on this matter so please leave some comments...

One of the cool new features of the 3.5 SP1 Microsoft AJAX Library is a history framework, which adds support for backwards and forwards navigation using history points. History points can be added any time, and contain a JavaScript dictionary that helps you serialize and deserialize the state of the application or control.

To enable history, the ScriptManager must be set to support history:

<asp:ScriptManager ID="ScriptManager1" runat="server" EnableHistory="true" />

To add a history point, simply add a JavaScript dictionary object and a title for the history point:

var state = { message: "Hello World!" };
Sys.Application.addHistoryPoint(state, "Welcome");

To handle the navigate event which is fired by Sys.Application upon client back button usage, create a handler:

function onHistoryNavigate(sender, eventArgs) {
    if (eventArgs

== null) {  // Adds intellisense to the method body
        eventArgs = new Sys.HistoryEventArgs();
        throw Error.invalidOperation('Expected HistoryEventArgs');
    }
    var state = eventArgs.get_state();
    // Do something with the state object here!
}

Finally, you'll need to add the handler to Sys.Application. Note that because navigate is an event, multiple consumers can add a handler to the event:

Sys.Application.add_navigate(onHistoryNavigate);

In a control, you'd want to call remove_navigate to clear your event handler.

Finally, the following sample page demonstrates the use of history through the creation of 3 history points. As each history point is added, the navigate event is raised (synchronously) causing the final state of the page to have the 3rd history point loaded. The user can then navigate back and forth between the 3 languages of Hello World.

<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>A simple history demo, from the book Service Oriented AJAX Applications (MS Press 2008)</title>
    <script

type="text/javascript">
        function pageLoad() {
            Sys.Application.add_navigate(onHistoryNavigate);
           
            var state = { message: "Hello World!" };
            Sys.Application.addHistoryPoint(state, "Welcome");

            var italianState = { message: "Ciao mondo!" };
            Sys.Application.addHistoryPoint(italianState, "Benvenuto");

            var frenchState = { message: "Bonjour monde!" };
            Sys.Application.addHistoryPoint(frenchState, "Bienvenue");
        }

        function onHistoryNavigate(sender, eventArgs) {
            if (eventArgs == null) {
                eventArgs = new Sys.HistoryEventArgs();
                throw Error.invalidOperation('Expected HistoryEventArgs');
            }
            var state = eventArgs.get_state();
            if (state.message) {
                $get('Greeting').innerHTML = state.message;
            }
        }   
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" EnableHistory="true" />
        <div id="Greeting"></div>
    </form>
</body>
</html>

With just a few lines of code, you can utilize the AJAX Library's history support in your production applications using similar techniques. You'll want to serialize the state of your control in the state parameter, and you'll also want to ensure that the

history you're handling is your own and not another AJAX control's.

Also note that this is new functionality in the 3.5 SP1 release-- the server control syntax will cause a compilation error in earlier frameworks. Finally, a shameless plug: I've got more details on history management as well as the rest of the AJAX library in my new book, available later this year: Developing Service-Oriented AJAX Applications (Microsoft Press 2008).