Month: April 2006

MDX Scripts article

Chris Webb3 Comments
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

Chris Webb1 Comment
…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

Chris WebbLeave a comment
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 http://www.rittman.net/archives/2006/04/tuesday_and_wednesday_at_colla.html 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

Chris Webb10 Comments
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 http://spaces.msn.com/cwebbbi/blog/cns!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 http://charliem.wordpress.com/2006/04/26/wild-card-mdx-searching/). 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 http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry and http://spaces.msn.com/cwebbbi/Blog/cns!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 http://groups.google.co.uk/group/comp.databases.olap/msg/75fb29730b96f23b 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 (http://www.gotdotnet.com/workspaces/docs/about.aspx), 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

Chris Webb6 Comments
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!?!?

Chris Webb2 Comments
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

Chris Webb2 Comments
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 http://www.bizintelligencepipeline.com/news/186100315) 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.