MDX Scripts article

I didn’t realise this was freely available until just now, but here’s an article I wrote for the April edition of ‘SQL Server Professional’ on MDX Scripts:
It’s adapted from the chapter I wrote on the same subject for ‘MDX Solutions’, although the big difference is that for this article I used Adventure Works for my demos whereas in the book I used a cube I built specifically for the purpose.

Good article on Microsoft’s BI strategy

…from Douglas McDowell, a colleague at Solid Quality Learning and someone who really knows his stuff:
Part 1 is noteworthy in that it’s the first time I’ve seen anyone write about what has been an open secret in the Microsoft BI community for some time – that MS are about to launch their own financial planning app.

The cost of Oracle BI

I’m a big fan of Mark Rittman’s blog on Oracle BI, so hopefully he’ll forgive me for highlighting this interesting (from a MS point of view) nugget in his most recent posting regarding the Oracle BI Suite:
One note of caution though is over price – from speaking to people, the per-CPU license fee for the whole BI Enterprise Edition stack, including the analytic server (which could remove the need for an Oracle database + OLAP Option), Answers, Delivers, Dashboards and so on, is an eye-watering $225k per CPU. Wow.
(see for the whole thing). However much Microsoft end up charging for the Proclarity software they’ve just bought, if as a customer you go with the Microsoft BI stack you are not going to pay anywhere near the price that Mark quotes for equivalent functionality.

More thoughts on stored procedures

I know I promised more content on stored procedures a few months ago, but, well, you know… Once I found out that you couldn’t actually run an MDX query from them (unless you used the ADOMD.Net client library and opened a connection from within the sproc, which seems a pretty silly thing to do) and can’t do stuff like dynamically create calculated members or named sets with them, then I realised I couldn’t implement any of my cool ideas.
Anyway, I have been thinking about them again quite a lot recently. For example, I had some contact with Mark Mrachek about this post on his blog about drillthough:
I had already been contacted by someone having the same problem, and thinking some more about we came up with a possible solution using an action which calls an AS sproc similar to the one I posted a while ago to find the currentmember on every dimension (see!7B84B0F2C239489A!586.entry), and which in turn passes the keys of each member through to a SQL sproc to do the drillthrough. Mark has promised to blog about the full solution when he’s had time to implement it.
Similarly, today on Charlie Maitland’s blog he talks about how to filter dimension members using wildcards (see It seems to me that this would be a prime candidate for a sproc – there is a lot of string functionality in .NET that could be very useful in MDX; another example would be the way that you couldn’t use the VBA REPLACE function in MDX either.
Finally, there are some things which are very complex in MDX which could be simplified no end if they were put into a sproc. Two examples would be the discussion on this blog last year about tuning YTD-style calculations (see!1pi7ETChsJ1un_2s41jm9Iyg!107.entry and!1pi7ETChsJ1un_2s41jm9Iyg!111.entry) and problems such as finding the count of members on an attribute which have the same first letter in their name as the currentmember on that attribute (see for how to do it).
So, to get to the point, I was thinking that solving individual problems and posting code up on this blog was not the best way to go. I’m not the world’s greatest .NET coder by any means, and rather than just being able to see the code it would be much better if there was one dll which people could download to get at all this useful stuff. Surely it would be much better if everyone who was interested could collaborate on producing this dll, perhaps using something like a gotdotnet community (, so it would be much easier to add functionality and fix bugs. What does everyone think about this? Is there someone out there with a solid coding background who would be willing to help?

Reporting Services and Server Aggregates

Recently I was contacted by Peter Koller from Norway, asking me about some bizarre behaviour he’d seen with calculated members disappearing from query resultsets in Reporting Services. I had a suspicion about why it was happening and came up with a workaround, but asked him to post it as a bug which he duly did:
Now much as I’m tempted, I’m not going to go off on another rant about the fundamental flaws in the way support for Analysis Services is implemented in Reporting Services. I’m going to seize on the glimmer of hope contained in the following sentence:
For a future release and maybe service pack, we are considering adding an explicit switch that allows treating server aggregate rows as "detail rows".
What, let Reporting Services actually display the results of your MDX query without adulteration? Sounds like a dangerously sane idea! I’d like to propose some community action (I’m currently in France so I must have become infected with Gallic militancy): can anyone who agrees with me that this feature should be in the next service pack leave a comment at the above link? Hopefully if a few comments get posted then it’ll help persuade the RS team to do something.

Hyperion and Microsoft to collaborate on BI!?!?

OK, I have no idea what’s going on here… Hyperion have announced a series of initiatives to allow their BI tools to be used with Microsoft’s. Of course it’s presented as them being responsive to customer opinion, but you can’t help wonder if there’s a hidden agenda too. Is this some kind of anti-Oracle gesture?

Google OneBox

Google OneBox is mentioned a lot on the blogs today:
The idea of integrating your BI systems with Enterprise Search seems quite topical (see also what Cognos are up to and I’m sure it would be very easy to integrate Analysis Services and Reporting Services in the same way. But I can’t help but think that this idea ranks along with English Query in the nifty-but-of-no-practical-use stakes. If you’re a consumer of BI reports or tools, are you really going to want to access them via a search interface? I think not.

Handling Relative Time Periods

I recently had an interesting exchange with Michael Barrett Jensen and Thomas Pagel on the MSDN Forum about the best way to handle the problem of relative time periods, ie letting the user select a member called something like ‘current month’, which always returns the most recent month’s data (see and please ignore the posts in the middle of the thread where I talk rubbish about when sets are evaluated). Now for some scenarios it’s a good idea to use named sets to hold the ‘current month’ or the last five months or whatever you want – if you have a client tool which handles sets well, then the advantage of this is that it works as a kind of dynamic selection and you get set of members you want each time. However not all front ends (eg Excel) support sets, they can be a difficult concept for some users to understand, and they are a bit inflexible. So as an alternative here’s the outline of a method for building a relative time dimension to add to your cube – it’s more of a sketch than a robust solution, so I’d be interested in hearing feedback from anyone who tries to implement it.
Anyway, the first thing I did was create a new table in Adventure Works DW from which I built my Relative Time dimension. For the purposes of this exercise, I only created two columns/attributes: Relative Year and Relative Quarter. Relative Year had two members, Current Year and Current Year-1, and Relative Quarter had eight members, Current Quarter to Current Quarter-7. I then added this to the Adventure Works cube, but did not make any changes on the Dimension Usage tab, so it had no relationship with any measure group. I then added the following code onto the end of the MDX Script:

//Relative Time

([Relative Time].[Relative Quarter].[Relative Quarter].Members, [Measures].[Internet Sales Amount]) =

([Measures].[Internet Sales Amount],


([Date].[Calendar].[Calendar Quarter].members,


([Relative Time].[Relative Quarter].currentmember, [Relative Time].[Relative Quarter].[Relative Quarter].members)




([Relative Time].[Relative Year].[Relative Year].Members, [Measures].[Internet Sales Amount]) =

([Measures].[Internet Sales Amount],

tail([Date].[Calendar].[Calendar Year].members,


([Relative Time].[Relative Year].currentmember, [Relative Time].[Relative Year].[Relative Year].members)




What this does is overwrite the values displayed in the cube for the measure [Internet Sales Amount] and everything below the All Members of the two attributes on Relative Time – Current Year shows the value of the last member on the [Date].[Calendar].[Calendar Year] level, and so on. You now have a Relative Time dimension in your cube that users can use in their reports, but which has no impact on aggregations or processing. You can change the scope of the assignments to cover all the measures in your cube fairly easily.
The advantages of physically building the dimension, rather than using calculated members to do the same thing, are that a) you get drill down from Relative Year to Relative Quarter and b) your MDX Script doesn’t get cluttered with (potentially) hundreds of calculated members.
As I said, this is really just a proof of concept. I’m not 100% satisfied with the way this works (for example I’d like to see it return something nicer than repeated values when you crossjoin Relative Year and Calendar Year, and the Root on Relative Time doesn’t show sensible values unless you have as many relative periods as actual periods), but I thought it was worth posting in case anyone was interested.
%d bloggers like this: