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:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=248868&SiteID=1
 
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:
 

SELECT

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

DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0,

{} 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…

Nigel Pendse is doing his own podcasts

I must have given him the bug – you can listen to his thoughts on data visualisation here:
 
For those of you who are wondering when my next podcast is coming, I can reveal that it will be recorded on Wednesday night and be available by the end of the week.

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:
 

WITH MEMBER MEASURES.TEST
AS URLESCAPEFRAGMENT("[Geography].[City].&[Abingdon]&[ENG]")
SELECT MEASURES.TEST ON 0
FROM [Adventure Works]

 
It returns the following result set:
TEST
%5bGeography%5d.%5bCity%5d.%26%5bAbingdon%5d%26%5bENG%5d
 
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.

BI Pathfinder

Yet another beta test invitation arrived the other day, for an intriguing new tool called BI Pathfinder:
 
To quote the website’s FAQ:
BI Pathfinder is a methodology based software tool providing a structured approach to specifying and documenting BI Reporting systems.
 
If you’re interested in becoming a beta tester too, send a mail to beta@bipathfinder.com.
 

ascmd tool beta

I’ve just noticed Dave Wickert’s post on the following thread on the Analysis Services MSDN forum, and thought I’d flag it up:
 
If you’ve ever wanted to be able to execute MDX or XMLA from the command line then you might want to sign up to be a beta tester for the tool he’s co-developing. Here’s a summary from the original post:
With it you can execute either an XMLA script or an MDX query. Input and output can come from either the command-line or files. It is called ‘ascmd’ and is patterned after sqlcmd’s syntax and capabilities. Optionally you can as it to capture trace events  issued on the session (like SQL Profiler does). It runs over both tcp/ip connections and http access and it supports the new AS2K5 multi-instancing also.
 
UPDATE: ascmd is now available in the April SQL2005 samples download, available here:

MDX Limericks

Since last summer’s ‘OLAP Jokes’ entry was the most popular (in terms of links to it) entry I’ve made on this blog so far, I’ve been wracking my brains for a suitable follow-up. During my lunchtime browsing today I came across this posting on a certain Phil Factor’s blog:
…and thought that anything those SQL guys can do, us MDX-ers can do better. So I’d like to announce an MDX limericks competition. No prizes apart from the admiration and respect of the entire Analysis Services community, I’m afraid, but who needs prizes when faced with a noble challenge such as this?
 

Here are the rules:

  • Each entry must be an MDX query executable against any cube/perspective in the Adventure Works database, and must take the form of a limerick (see http://en.wikipedia.org/wiki/Limerick_(poetry for details)
  • The query must return at least one cell which contains a value other than null
  • Entries should be posted as comments to this blog entry
  • Extra points will be awarded for wit and creativity

It’s actually pretty difficult. Here’s my first attempt:

WITH MEMBER [Date].[Day Name].[TODAY]
AS 1 + [Date].[Day Name].[THURSDAY]
SELECT [Average Rate]
ON 0, [Date].[Date]
ON 1 FROM [Direct Sales] WHERE([MAY])

As an aside, this brings up the vexed question of how you should pronounce MDX. In my limerick I’m not pronouncing the punctuation so it reads:

WITH MEMBER DATE DAY NAME TODAY,
AS ONE PLUS DATE DAY NAME THURSDAY,
SELECT AVERAGE RATE,
ON ZERO, DATE DATE,
ON ONE FROM DIRECT SALES WHERE MAY.

However I’m prepared to be flexible on this point.

So come on all of you (and especially you Jon, as I know you can never resist something like this) and get composing!

 

UPDATE: I’ve decided two rule changes, to make things easier. First I’ll allow queries from Foodmart 2000 for those of you on AS2K; second I’ll allow statements other than SELECT statements (eg CREATE SET, UPDATE CUBE etc) so long as they execute without errors.

 

The StrTo functions and CONSTRAINED

If you’ve been using Reporting Services 2005 to generate MDX you’ll have probably noticed that the StrTo<Something> family of functions (StrToSet, StrToMember, StrToTuple, StrToValue) have gained an extra, optional parameter – the CONSTRAINED flag. It’s not mentioned in Books Online and since someone has been asking about it on the newsgroup I thought it would be worth a quick post explaining what it does.
 
Basically it’s a security feature for middle-tier scenarios to stop MDX injection attacks. Running parameterised MDX queries in RS is probably going to be the most common example of when you’d want to use it, but I guess anywhere where you are generating dynamic MDX in code you’re running the risk of an evil user trying to pass you a stored procedure call (or something worse) instead of the member name or key you were expecting. By putting the CONSTRAINED flag in your query you automatically restrict what the StrTo<Something> functions can accept as their first parameter:
  • StrToMember can only take a string containing a member name
  • StrToSet can only take an explicitly defined set, ie a list of member names or tuples in curly brackets
  • StrToTuple can only take an explicitly defined tuple, ie a list of member names in brackets
  • StrToValue can only take a constant value
What’s not allowed with CONSTRAINED is an MDX expression which evaluates to either a Member, Set, Tuple or Value. Here’s an Adventure Works query which illustrates this:
 

WITH

MEMBER MEASURES.STRTOSET_TEST1 AS COUNT(STRTOSET("{[Measures].[Internet Order Quantity]}"))

MEMBER

MEASURES.STRTOSET_TEST2 AS COUNT(STRTOSET("MEASURES.MEMBERS"))

MEMBER

MEASURES.STRTOSET_TEST3 AS COUNT(STRTOSET("{[Measures].[Internet Order Quantity]}", CONSTRAINED))

MEMBER

MEASURES.STRTOSET_TEST4 AS COUNT(STRTOSET("MEASURES.MEMBERS", CONSTRAINED))

SELECT

{MEASURES.STRTOSET_TEST1, MEASURES.STRTOSET_TEST2, MEASURES.STRTOSET_TEST3, MEASURES.STRTOSET_TEST4} ON 0

FROM

[ADVENTURE WORKS]

 
The final calculated member here produces an error because "MEASURES.MEMBERS" is an expression which evaluates to a set and is not an explicitly defined set like the ones in the first or third calculated members.
 
UPDATE: Jon makes a good point in the comments…