Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for December 2005

My session at the PASS European Conference 2006

with 3 comments

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.

Written by Chris Webb

December 8, 2005 at 1:50 pm

Posted in Events

SQL2005 Books Online December Update

with one comment

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.

Written by Chris Webb

December 7, 2005 at 11:47 am

Posted in On the internet

New articles from Chris Harrington

leave a comment »

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.

Written by Chris Webb

December 6, 2005 at 2:52 pm

Posted in On the internet

Parameterising by Measures in Reporting Services 2005

with 7 comments

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!

 
 
 

 

Written by Chris Webb

December 5, 2005 at 2:02 pm

Posted in Reporting Services

Dejan Sarka on AS2005 stored procedures

with one comment

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′.

Written by Chris Webb

December 5, 2005 at 1:28 pm

Posted in On the internet

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers