Via Amyn Rajan of Simba Technologies, I see that IBM's Cubing Services OLAP tool now supports OLEDB for OLAP:
http://blogs.simba.com/simba_technologies_ceo_co/2008/05/ibm-cubing-serv.html

Another boost for the MDX language then! It's the lure of Excel compatibility that is driving all this of course, but it also opens the way for other MDX-friendly client tools as well.

Meanwhile MDX support is also proceeding in other, less direct ways. I mentioned before here that I hoped some of the new breed of data warehouse appliance vendors would start to support MDX as well and in a way it's starting to happen: Vertica and Pentaho announced a partnership a few months back and I've already heard of one instance of a company using Mondrian on top of Vertica. Hopefully other vendors will begin to realise that raw query performance is not much use unless matched with a language that allows you to easily express the queries and calculations you need.

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...

I usually avoid using cell security like the plague for the good reason that it absolutely kills query performance. But sometimes there's no alternative but to use it and I'm working on one such project right now. However I've found a new gotcha: the behaviour of cell security changed between AS2005 SP1 and SP2 and in my opinion it didn't change for the better. The short explanation is that when you are using just Read permissions in cell security, if you have MDX Script assignments at a lower level of granularity you'll find that the higher level members whose values are affected by the assignments have Read Contingent permissions applied to them, even though according to the MDX expression I used in for the Read permissions these values should not be secured.

Here's the repro I've got for Adventure Works using AS2005 SP2:

• In AW, comment out everything in the MDX Script apart from the Calculate statement
• Add the following calculations to the Script:
  CREATE MEMBER CURRENTCUBE.MEASURES.[Show Value] AS
  IIF(
  [Date].[Calendar].CURRENTMEMBER IS
  [Date].[Calendar].[Month].&[2004]&[1],
  FALSE, TRUE);
  
  CREATE MEMBER CURRENTCUBE.MEASURES.[Show Internet Sales Amount] AS [Measures].[Internet Sales Amount];
• As an Administrator you see the following results for a query in the cube browser:
  
• Now create a role with the following Read permissions in the cell security tab:
  IIF(
  [Date].[Calendar].CURRENTMEMBER IS
  [Date].[Calendar].[Month].&[2004]&[1]
  AND
  [Measures].CURRENTMEMBER IS [Measures].[Internet Sales Amount],
  FALSE, TRUE)
• Display the same query in the browser when connecting through this new role, and as expected you see the following:
  
• Now add the following assignment to the MDX Script:
  ([Date].[Calendar].[Month].&[2004]&[8])=[Date].[Calendar].[Month].&[2004]&[7];
• And refresh the query, so you see this:
  
• The big change is that the value for CY2004 and the All Member are now secured too. So we’re now in the situation where we’ve made an assignment that displays a value that wasn’t secured anyway, but we now can’t see the CY2004 value even though the expression in the Read permissions returns true. If you were to change the assignment so it refers to the value for January 2004, as follows:
  ([Date].[Calendar].[Month].&[2004]&[8])=[Date].[Calendar].[Month].&[2004]&[1];
  You can see the January value either while looking at August or the ‘Show Internet Sales Amount’ calculated measure, as I would expect, because neither are aggregated from anything and the expression for the Read permission always returns True for them:
  

For my customer, who is migrating from AS2005 SP1 direct to Katmai, this is potentially a major problem. And to be honest as my repro shows I don't think this new behaviour makes any sense at all anyway - if I wanted to use Read Contingent permissions I'd have used Read Contingent permissions!

I've just come across SqlSpec from Elasoft (http://www.elsasoft.org/) a tool that can create documentation for Analysis Services databases. You can see some sample output here:
http://www.elsasoft.org/samples.htm

I've downloaded it and given it a quick try, and it seems to do the job pretty well. This is the second documentation tool for SSAS that I'm aware of, the other being BI Documenter. Which one is better? Well I guess it depends on what you want to do since the two tools support different data sources and have slightly different features (http://www.elsasoft.org/compare.htm vs http://www.bidocumenter.com/Public/Features.aspx). You can see sample output for BI Documenter here:
http://www.bidocumenter.com/Public/SampleOutput.aspx

You pays your money, you takes your choice...