Brian Welcker on Analysis Services/Reporting Services integration

I’ve been quite vocal in my disapproval of the way that Analysis Services and Reporting Services integration has been handled over the last few months, so it’s only fair to publicise a posting on Brian Welcker’s blog which is I guess aimed at answering people like me:
 
I don’t accept some of the points he makes, though, and hopefully by the time you read this the comment I submitted will have gone through moderation and be visible.
 
However I do think the fact that the problem has been addressed in this way highlights the openness of Microsoft’s development teams and the positive effect that their willingness to blog, post on newsgroups etc has on customer satisfaction. Even though I’m still not satisfied with the functionality in question after reading Brian’s post, the feeling that I’m able to express my grievances and have them heard is makes me happier than if I thought no-one at all was listening.

Andy Hayler’s Blog

Anyone who was interested in last week’s Nigel Pendse podcast should probably check out this blog I’ve just discovered, by Andy Hayler of Kalido:
His take on the whole ‘BI for the Masses’ thing (http://andyhayler.blogspot.com/2005/12/does-bi-stand-for-business-indigestion.html#links) had me nodding my head.

My session at the PASS European Conference 2006

I mentioned the 2006 PASS European Conference a few months ago, but I thought I’d publicise the fact that I’ve been confirmed as a speaker (I don’t know whether that will sell any more tickets, or indeed keep people away):
I’ll be covering my new pet subject of MDX Scripting.
 
Nice to see Thomas Pagel and Markus Sprenger confirmed too; I’m sure there will be plenty of other good speakers appearing on that list in the near future. If anyone reading this is planning to attend, let me know and we can get a party together for dinner and drinks on the Thursday night.

SQL2005 Books Online December Update

SQL2005 Books Online was pretty awful at RTM, and I’ve just seen that MS have released an updated version already. You can get it here:
It’s something of a stealth release – I only heard about it while browsing Channel 9, not from any of the other SQL Server-related rss feeds I read. You’d think they’d publicise this kind of thing better.
 
Browsing through, though, I can see that it still contains several errors – see, for example, the CALCULATE statement where various features are documented that were dropped from the beta ages ago. But in this case I only have myself to blame: a few years ago I met a guy who worked on SQL BOL and he pointed out the ‘send feedback’ feature that’s available on every page – if you see a mistake then you can use this feature to send an email directly to the BOL team and presumably they’ll try to fix it before the next release. Needless to say when I first saw this particular entry I didn’t bother to send any feedback, but I have now and will make a special effort to do the same whenever I see any other mistakes.

New articles from Chris Harrington

Chris Harrington’s site has been quiet for a while, but he’s just mailed me to let me know about some new articles he’s posted up:
 
The last two are the really interesting ones – he’s doing some cool stuff with XMLA here.

Parameterising by Measures in Reporting Services 2005

If you’ve been reading this blog for a while, you probably know that I’m not the greatest fan of the way that support for MDX has been implemented in RS2005. Anyway, here’s a little tip that might soften the pain…
 
In RS2005 you are always forced to put the Measures dimension on the columns axis in your queries if you’re using a data source of type ‘Microsoft SQL Server Analysis Services’ (you can still get the glorious AS/RS2K experience if you use an OLEDB connection instead along with the AS OLEDB Provider). While this is irritating at the best of times, it does have one serious consequence: how can you parameterise a query by Measures? It doesn’t look like the RS dev team thought this would be a valid scenario, but in fact in my experience there are plenty of times where you do want to do it. It is possible though, and here’s a solution:
 
First of all, you need to create a query that will give you a list of measures on rows. The following AdventureWorks example shows how to do this:
 

WITH
SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)
MEMBER MEASURES.MeasureUniqueName AS
MEASURES.ALLMEMBERS.ITEM(
RANK(
[Date].[Date].CURRENTMEMBER, MYSET
)-1
).UNIQUENAME
MEMBER MEASURES.MeasureDisplayName AS
MEASURES.ALLMEMBERS.ITEM(
RANK(
[Date].[Date].CURRENTMEMBER, MYSET
)-1
).NAME

SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,
MYSET ON 1
FROM [Adventure Works]

It relies on there being a level on a hierarchy somewhere in your cube that has more members on it than there are measures, so it’s a bit of a hack, but this isn’t a problem most of the time and is the easiest way of solving the problem in MDX. You just create a set with the same number of members in as you have measures, put that on rows in your query, and then using calculated measures return the name/unique name of the measure which has the same rank in the set Measures.AllMembers as the currentmember on rows (Date.Date in this case) has in that set.
 
After you’ve created a new report parameter tied to this resultset you can create the query you want to parameterise. Once again the need to have measures on columns needs to be worked around – this time you need to create a calculated measure in your WITH clause, put that on columns, and then parameterise the definition of that calculated measure. Here’s an example:
WITH
MEMBER MEASURES.SELECTEDMEASURE AS STRTOMEMBER(@MyMeasure, CONSTRAINED) SELECT NON EMPTY { MEASURES.SELECTEDMEASURE} ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE
 
Before this will work though, you need to declare the query parameter used by this query. To do this, click on the ‘query parameters’ button in the toolbar above where your MDX appears (it looks like an @ symbol overlaid on a table) and then fill in the following values:
  • Parameter as the name of your parameter, without the leading @. So in our example it would be MyMeasure
  • Dimension needs to be left blank – the Measures dimension doesn’t appear on the dropdown list
  • Hierarchy needs to be blank too
  • Multiple Values needs to be left unchecked
  • Default must be entered, but you can use an expression such as MEASURES.DEFAULTMEMBER

Thanks go to my colleague Tony for working this last bit out.

You can then bind this query to a table in your report and select different measures to slice by. Hope this helps!

 
 
 

 

Dejan Sarka on AS2005 stored procedures

Interesting set of posts from Dejan Sarka on writing stored procedures for AS2005:
 
The focus is on data mining but the code also includes some useful AMO examples too. I’ve seen plenty of stored proc examples for data mining but very few for the olap side of AS2005 for some reason. My colleague Jon Axon has been promising me a guest post for this blog on the subject but won’t finish his writeup until this ng question is answered; in the meantime and in the absence of any useful information in BOL (no surprise there), there are very few other AS2005 stored proc examples out there. Mosha did a brief blog entry and several books on my book list deal with it too, such as ‘Data Mining with SQL Server 2005’, ‘MDX Solutions’ 2E and  ‘Applied Microsoft Analysis Services 2005’.

Podcast #1: Nigel Pendse

A while ago I decided that it would be cool to jump on the podcasting bandwagon, and here’s my first attempt. I was very lucky to get Nigel Pendse to chat with me about the impact that SQL2005/AS2005 will have on the BI market as a whole; if you don’t know who Nigel is he’s the man behind the OLAP Report and the OLAP Survey, both of which are great sources of competitive intelligence if you’re a software vendor or consultancy, and also a very popular speaker at conferences and seminars. He has a lot of interesting and intelligent observations to make in my opinion.

 

Anyway, here’s where to get the podcast (it’s about 25 minutes long and in mp3 form):

http://www.ourmedia.org/node/108782

Apologies for the rather amateurish production…

 

If you think this is a worthwhile undertaking, I’ll start thinking of people to ask to interview for future podcasts. Let me know what you think!

 

Excel 12 BI first impressions

After my gushing posts about Excel 12 BI a month or so ago, I was co-opted onto the Office 12 beta program with the invitation to blog all I want about it. So… having downloaded the beta bits and installed them, here’s the first in a series of posts on my experiences with Excel 12. I suppose this makes me a willing tool of the evil Microsoft hype machine (maybe if I blogged about games consoles, exotic holidays or stock tips people would try to bribe me with something better than beta testing) but hey, I’m sure you’ve already worked out that I sold my soul to billg several years back!
 
My first pleasant surprise came with the installation – it was freakishly fast. Having wasted hours installing various versions of SQL2005 on my machine over the last year or so, Office 12 seemed to install in about 5 minutes. Opening Excel it wasn’t hard to get to grips with the new interface and I could appreciate the benefits: it’s a lot more visual, and not having to make your way through several levels of nested menu items does make it faster to use.
 
Creating a connection to a cube was pretty easy, much better than the wizard of previous versions, and I created a pivot table. Then came my first disappointment. As with the pivot tables we’re all familiar with, Measures are treated as a special case and can only be dragged into a ‘Values’ region on the pivot table rather than put on Rows or Columns directly. After a bit of searching I came across a property which let me move my measures to where I wanted them, but I really don’t understand why this is so difficult; no other AS client tool has this problem. Similarly, when selecting members you’re still only given the option to view a hierarchy as a treeview starting at the All Member and then drilling down to the members below; most, if not all other AS client tools (including SQL Management Studio and BI Dev Studio) also give you the option to see a treeview consisting of the levels of each hierarchy, so you can either select a whole level or expand it and select members from it. Admittedly in AS2005 the fact that you’ll have lots of single level attribute hierarchies in your cube makes this slightly less of an inconvenience, but it’s still pretty irritating, and a bit of a step backwards from the functionality offered in Microsoft’s existing Excel addin.
 
However, once I’d run my first query there was a whole load of good new functionality to enjoy. For example, under Field Settings/Show Data As, you can switch between showing the actual values of your measure and various calculated values such as percentage of column – meeting a very common requirement and one which MDX can’t handle well, as this recent newsgroup thread shows. Displaying member property values is handled nicely, and the filtering/sorting functionality available on member names, member property values and measure values is very good indeed – possibly better than in any other AS client tool I’ve come across – although I didn’t seem to be able to filter on the values displayed using ‘Show Data As’, only the real values. Then there’s the formatting functionality, already well covered in the Excel 12 blog and again very good indeed. Query performance seemed ok, in fact better than some other client tools on my test cube, and browsing hierarchies with large numbers of members was not a problem.
 
Like other Excel addins for AS, you can also convert your pivot tables into a set of formulas which return member names and values. I don’t have an installation of the existing Excel addin handy, but from what I remember there are several important improvements here: you can now use formulas to construct the parameters you pass into these functions, eg such as =CUBEMEMBER("localhost MyDB","[Product].[MyUserHierarchy].[Category].&[" & H12 & "]"); there are also some new functions which allow you to declare named sets using whatever MDX you like and then pick members out of them, so that these members can then be passed into the other functions.
 
Obviously this being beta 1 there were some things that didn’t work. I don’t know whether it was because I had to install Excel on a Windows 2003 box and work via Remote Desktop Connection, but the charts looked like they’d been generated on a ZX Spectrum. There were also rather interesting Group/Ungroup buttons which didn’t work… I wonder if they are for creating custom groups of members? Overall though, the build seemed stable and the BI features worked well.
 
What next? I need to download and install the documentation (!) and Excel Services so I can check out how easy it is to create BI dashboards for the intranet. This post was only intended to convey my first impressions so as I learn more I’ll make sure I blog about it, and if I’ve said anything so far which is wrong or inaccurate (which is certainly possible) I’ll be happy to issue a correction. If anyone has any BI-related Excel client features they’d like me to check out then please leave a comment and I’ll do my best to oblige. I’m impressed with what I’ve seen so far; maybe the BI community can exert a bit of people power and lobby to get the less good features changed, so that Excel 12 fulfills its promise.
 

Do you know anyone Scottish?

Here’s something fun to show them:
  1. Open SQL Server Management Studio
  2. Connect to the AdventureWorks cube
  3. Run the following query:
    select [Measures].[Internet Sales Amount]
    on 0,
    [Geography].[Geography].[State-Province].[England].children
    on 1
    from [Adventure Works]
  4. Scroll down the result set and point out that according to AdventureWorks, Scotland is a city in England.