Reporting Services, MDX and Aggregated Values

Undoubtedly the best time to find out about an undocumented change in functionality in a product is halfway through a demo of said functionality to a large group of people. This happened to me last week: I was teaching my MDX course (written on the last CTP of SP2) and had just finished my speech on all the rubbish aspects of Reporting Services/Analysis Services integration and was trying to show how Reporting Services automatically filtered out all but the lowest level of granularity of data from an MDX query (see Teo Lachev’s post here: http://prologika.com/CS/blogs/blog/archive/2006/02/08/853.aspx and Reed Jacobsen’s posts here: http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/08/07/22359.aspx and here: http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/08/04/22346.aspx for details) when I found that it wasn’t doing it any more. Later on I emailed Teo to ask if he knew anything about this change – he didn’t but he asked the RS dev team and they gave him some details (he then blogged about it here: http://prologika.com/CS/blogs/blog/archive/2007/06/10/aggregate-rows-and-sp2.aspx). So, a small victory for the people – Reporting Services now no longer tries to force its own aggregation functionality on you and you always see the full results of your query, unless you’re already using the RS Aggregate function in your reports. This is apparently in response to customer demand. Just be sure to check any existing RS/AS reports you’ve got in production to make sure they’re not displaying extra rows now!

I’m now hopeful that the message will get through about the other stupid restrictions that RS places on AS data sources, such as only being able to put the measures dimension on columns. I’ve not kept up with the changes in RS2008 as much as I should (Teo again has a good overview of what’s coming here: http://prologika.com/CS/blogs/blog/archive/2007/06/09/teched-2007-us-memoirs.aspx) but I’ve not heard that these restrictions will be lifted. The problem is of course that RS expects to do all the aggregation of data itself, but the RS dev team don’t seem to understand that if I’m using AS then I will have designed all my aggregation business logic into my cubes and dimensions and I couldn’t care less about what RS can do in this area (for an example of their mindset, see the somewhat patronising comments on this posting on Connect from last year: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125414).

While we’re talking about Katmai I might as well mention why I’ve not blogged about the new AS features in it – it’s because there aren’t all that many. You’ve probably already read Mosha’s post about the attribute relationship designer (http://sqlblog.com/blogs/mosha/archive/2007/06/07/katmai-june-ctp-attribute-relationships-tab.aspx); Vidas Matelis has covered the other cube design wizard changes here: http://www.ssas-info.com/VidasMatelisBlog/?p=22#more-22. More features will come in later CTPS but overall it’s really going to be all about performance improvements and manageability, and while I know a bit about some of the planned features it’s too early to blog about them because it’s too early to say what’s going to actually get included. Suffice to say that for the serious AS developer there are going to be some very welcome improvements but there won’t be anything that excites the marketing people.

Microsoft buys Stratature

Microsoft have been on a bit of a buying spree this week: after the Dundas deal they’ve now gone and bought the MDM vendor Stratature. Take a look at Stratature’s site for more details:
Jamie Thomson has some extra information here:
 
Yet another step closer to Microsoft having a complete BI stack…

SQL2008 Webcasts

Via Euan Garden, here are some upcoming webcasts on new SQL2008 (Katmai) functionality:
…including one on new dimension design features in AS.

Katmai Public CTP / Dundas Deal

I’m not at TechEd so this is second-hand news, but two big announcements: first of all, the first public CTP of Katmai is available for download on Connect; secondly, and more interestingly, a deal has been done where Microsoft has licensed Dundas’ data visualisation components. Russell Christopher has the best breakdown of this deal I’ve seen so far here:
He’s right in wondering whether the OLAP component is part of this deal – that’s the big question from the AS point of view.
 
UPDATE: see Russell’s comment below – the OLAP components (http://www.dundas.com/Products/Chart/NET/OLAP/index.aspx) are part of the deal! For me, the obvious use is as a replacement for OWC, not just instead BI Development Studio but as a free component that anyone developing a thick/thin client for Analysis Services can use. Next question: does licensing the components mean that Microsoft can now give them away for free like this? Or can it only use them embedded in its own products?

Generating Excel 2007 workbooks linked to SSAS using SSRS and OfficeWriter

Here’s the second of my (paid-for) articles for the folks at Softartisans:

http://officewriter.softartisans.com/officewriter-410.aspx

This time they wanted me to write something about using OfficeWriter with Analysis Services, and after a bit of thinking I came up with the idea of trying to generate Excel workbooks using OfficeWriter and Reporting Services that use the new Excel 2007 cube functions so that they have live links back to Analysis Services. After a lot of sweat I got it to work, and I have to thank Excel MVP Charley Kyd (the guy behind the Excel BI site http://www.exceluser.com/) for giving me the solution to the problem of OfficeWriter writing values to the cell Value rather than Formula property. Hopefully this is the sort of thing that could be made easier when/if OfficeWriter gets integrated with SSRS in Katmai.

Book Review: The Rational Guide to Business Scorecard Manager 2005, by Nick Barclay and Adrian Downes

I’ve got an admission to make: I’ve never really done anything seriously with Business Scorecard Manager (perhaps European businesses are less interested in formal methodologies like the Balanced Scorecard?), so when Nick Barclay asked me if I’d like a free copy of his new book about it I was very interested to see it so I could get up to speed. Having been a long-time reader of his blog (http://nickbarclay.blogspot.com/) and also having recently started reading his co-author Adrian Downes’ blog (http://adriandownes.blogspot.com/), I had high expectations since both are among the best Microsoft BI-related blogs out there and I wasn’t disappointed.

BSM2005 isn’t a massive topic, and as such is an ideal fit for the Rational Guide series where books are limited to 224 pages in length to make sure they’re as concise as possible. In a world where most IT books seem to be doorstop-sized this is a welcome move, and although Nick and Adrian have sidestepped this constraint by making a couple of extra chapters download-only the book is very to-the-point without descending to Books Online territory. The technical side of things is handled with great clarity and there’s also a healthy amount of discussion of the theory of performance management too. The text is clearly laid out with a lot of illustrations and all processes are broken up into numbered steps making it all very readable.

The one obvious (and probably unfair, but it’s what everyone will be thinking) criticism that can be made about this book is that it’s about BSM2005 rather than the forthcoming PerformancePoint. Hopefully the authors are planning to update it as soon as PerformancePoint gets released, whenever that will be – it seems like the entire Microsoft BI world has been holding its breathe waiting for PerformancePoint for so long we’re in danger of turning blue and fainting. Apart from that, if you’re about to start a project involving BSM2005 I can’t imagine a better resource to have on your desk.

You can buy it from Amazon UK here.

Intelligencia OLAP Controls

Andrew Wiles announced yesterday the availability of a beta version of his new MDX query builder component, ‘Intelligencia OLAP Controls’:
I had a quick demo of it this morning and I was very impressed. It has some really quite clever ideas in it such as the spreadsheet-based calculation functionality, and while it doesn’t do absolutely everything I’d like (it is still a beta, after all) it does an awful lot and Andrew is very open to feedback for what needs to be added. If you’re interested in checking it out you can download it here:
 
The product is aimed at ISVs, in-house developers and consultants who want to incorporate MDX querying functionality in their own products. I’ll be blunt: the company that really should be looking at this, and perhaps licensing it for use in Katmai and/or future versions of Office, is Microsoft. The control’s Office 2007 look-and-feel gives a tantalising glimpse of what a power user would want to see in Excel when connecting to AS and it puts the Reporting Services MDX query builder to shame.

Want to work for Microsoft?

The AS user education team are having a hard time trying to recruit someone who knows MDX. Is this something you, dear reader, would be interested in? Here’s the job description I got from Neil Orint:
 

Do you have a background in Analysis Services and MDX? Are you looking to put your mark on the next version of SQL Server content deliverables? Want to work on a writing team where your development team counterparts are as passionate about your content as you are? If so, the Analysis Services User Education team is looking for an experienced technical writer to assist us in delivering top-notch customer facing technical documentation.

The successful candidate will have a strong background in technical writing, a working knowledge of MDX and OLAP; possess solid project management and planning skills and a passion for learning new technologies. Strong communication skills are a must. As a member of our team you can expect opportunities to:
· Help define and execute upon content strategies and prioritizes
· Listen, analyze and respond to customer feedback
· Learn the entire spectrum of Microsoft’s business intelligence offering – Integration Services, Analysis Services, Reporting Services, Office and more.
Qualifications
· A practical understanding of MDX and OLAP.
· A history of developing assistance content for end users in a variety of delivery formats
· The ability to learn new tools, technologies, and processes quickly and independently

Please contact: neilor@microsoft.com

 
Please email Neil on the address given if you want to discuss it further.

Batch Reporting With SSIS and OfficeWriter

I’m a long-standing fan of OfficeWriter, the tool that’s just been licensed by Microsoft for possible inclusion in Katmai Reporting Services, and recently I was engaged by Softartisans to write a few articles for their website (yes, that means I was paid). Here’s the first of them, on how to create a batch reporting solution using their components and Integration Services:
 
Seems a bit of a weird thing to want to do when you can do the same thing in Reporting Services, but as I say in the article there are some advantages for using Integration Services for this task. I’m going to write another article next week on using OfficeWriter with Excel 2007 and Analysis Services which I’ve got some fun ideas for…

Dataupia

Following on from the Teradata post, I was just wondering how well the newly announced Dataupia (their website is rubbish at explaining what the product is – and I can’t even pronounce the name – but see this article for background: http://www.channelregister.co.uk/2007/05/15/dataupia_databases/) would work with Analysis Services, if you were using Dataupia underneath SQL Server and using AS in ROLAP or HOLAP mode. Could make for an interesting story for scalability if it does what it says on the box.