I had a good time at the PASS European conference last week – had a few drinks, met a lot of people, and learnt a lot too. One of the sponsors of the event was a new company who have a product for Reporting Services that I hadn’t heard of:
What their product does is offer a user-friendly way of creating Reporting Services reports. At this point you’re probably saying, hold on – isn’t that what Report Builder does? Well, yes, and even though in their marketing materials they go to great lengths to say that they aren’t competing with Report Builder (a great example of the ‘big lie’ theory applied) anyone with half a brain can see that they are. Which, of course, might be a big problem if Report Builder didn’t suck… well, maybe that’s harsh, but I’ve yet to meet anyone who actually likes it. Its big problem is its confusing UI, which is in turn linked to RB’s ability to create queries which span multiple tables – it’s not easy to convey table relationships, which fields it makes sense to display next to each other etc. Bob SQL Reporting Services blog has some good detail on this:
Having seen some demos of it – although not actually used it – RSInteract has a slick AJAX-y UI, quite smart-looking and clear, so I’d put it that down in its favour. It achieves this through not being anywhere near as ambitious as Report Builder – you can only write reports which are based on one table, view or stored procedure as I understand it. On one hand this is quite limiting, and it does push work back to the IT guys to make sure all the data you want is available in that table/view/sp, but if you genuinely do want a tool that non-technical users can use then I think that’s a necessary sacrifice because it removes a lot of complexity.
One downer, at least from my point of view, is that I understand that their Analysis Services support isn’t ready yet, although it’s coming soon. Overall though, definitely worth checking out.

Configuring and Connecting Analysis Services 2005 Webcast

Excel 12 BI Video on Channel 9

Even though I’m sure many of you subscribe to the Excel 12 blog (a masterclass in how to disseminate technical information in a product’s beta phase – other product teams in MS would do well to follow its example) I thought I’d relay the news that a new Excel 12 BI video is available on Channel 9:
I’ve not watched it yet but it should be interesting. I also noticed today that a whole bunch of introductory BI videos are available here:
Again, I’ve only fast forwarded one or two of these so I can’t comment, but they’re worth a look for the creepy female android voice-over alone. It made me think of 2001 and what a conversation with speech-enabled BI system might be like:

Microstrategy Can Now Generate MDX

I saw this announcement a while ago and somehow didn’t blog about it, but it’s an important one: Microstrategy can now generate MDX as well as SQL, and this means it can connect to OLAP servers such as Analysis Services and Essbase (at least those were the only ones mentioned – what about other platforms that support MDX? I wonder if they’ve been tested). See the press release here:
This can only be a good thing for the MDX language and XMLA as a standard, although from Microstrategy’s point of view it’s probably intended to encourage shops with a mixture of OLAP tools in place using anything other than Microstrategy (such as Office 12, perhaps?) as the client tool for all of them.

MDX Solutions Sample Chapter

You can now download chapter one of the second edition of ‘MDX Solutions’ (which I’m a co-author of), along with the table of contents and the index, here:
The book itself is coming very soon – place your order now! Although there are several other good Analysis Services 2005 books out there now, this is the only one which covers the new MDX features and functions in depth.
UPDATE: the sample chapter has been changed to chapter 6, which is rather more meaty in terms of content. And I believe that the book is now published! George at least has a copy, and I guess I’ll be getting one soon. Hurrah!

Who’s going to PASS Europe next week?

I’ll be there… if you’d like to meet up for a drink or a meal one evening then drop me a line. I’d also like to try to record a podcast while I’m there too, just me talking to anyone who thinks they’ve got anything interesting to say about Analysis Services or BI in general, so if there are any volunteers then please make yourself known!
While we’re on the subject of podcasts, I need to let off some steam – I’ve had two attempts at recording one with Mosha and on both occasions managed to not record the very interesting conversations we’ve had. Arggh! Does anyone know a good tool for recording Skype conversations that isn’t Powergramo (I’m obviously not alone in having problems with it)?

AddCalculatedMembers bug

I learn a lot from reading posts and answering questions on newsgroups and online forums, and here’s a good example:
Basically there’s a bug in the AddCalculatedMembers function which means that it brings back calculated members which it shouldn’t actually bring back. Since AddCalculatedMembers isn’t the most widely used function you’d think it wouldn’t have much impact, but one area that it might cause a problem is when client tools issue MDX queries to get metadata for display purposes – a fairly widely used technique. So, for example, in AdventureWorks if you added the following calculated member definition to the cube’s MDX Script and then deployed:
CREATE MEMBER CURRENTCUBE.[Product].[Product Categories].[Category].&[4].[BugTest]
AS 999, VISIBLE = 1 ;
You’d expect the calculated member to appear only underneath the Category ‘Accessories’ when you browsed the Product Categories hierarchy in the treeview in BIDS. However, what actually happens is that the calculated member appears underneath every category in the hierarchy and not just the one you wanted, which is pretty irritating, and this doesn’t just happen in BIDS – it happens in SQLMS, the Microsoft Excel Addin, and even Proclarity (reportedly), although it doesn’t happen in Office 12 beta or Panorama.
Running a Profiler trace while using SQLMS reveals what the problem is. When expanding ‘Bikes’ in the treeview it shows the following MDX being run:


SUBSET( ADDCALCULATEDMEMBERS( [Product].[Product Categories].[Category].&[1].CHILDREN ), 0, 501 )


{} ON 1 FROM [Adventure Works]

If you actually run this query in SQLMS you’ll see that AddCalculatedMembers is indeed returning the [BugTest] calculated member incorrectly. Hopefully this is on the list to be fixed in SP1…

URL Encoding in MDX

Been a bit busy this week, so only a short entry but hopefully a useful one. Continuing my series on undocumented new features in AS2005, I present the URLESCAPEFRAGMENT function which performs url encoding on a string. Here’s an example of how it works:

AS URLESCAPEFRAGMENT("[Geography].[City].&[Abingdon]&[ENG]")
FROM [Adventure Works]

It returns the following result set:
Why on earth do you want to do this though? Well I came across a problem last year when I was creating actions that created dynamic urls. I wanted to embed a unique name inside my url and realised that all those square brackets and ampersands needed to be replaced with escape characters, and couldn’t find an easy way of doing this (the only option seemed to be to do some custom coding). However the guys on the dev team were way ahead of me and as soon as I asked about this problem, they told me about this function.
%d bloggers like this: