• First of all, you connect to the cube that your query runs against
• Then you enter your query in the text box at the top of the screen and hit ‘Run Query’
• This then starts the following process:
  • The tool reads the cube’s MDX Script and splits it up into its constituent statements, storing them in an array
  • It executes a Clear Cache command to ensure that all queries are run on a cold cache
  • It executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
  • For each statement in the array of statements from the MDX Script, it then:
    1. Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
    2. Runs the query you entered in the textbox
    3. Stores how long the query took to run, plus other interesting metrics
  • Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

As a result you get to see how much each calculation has contributed to the overall time taken by the query; when you see a big jump upwards in the query time graph (that isn’t followed by a big fall subsequently – which could happen with scoped assignments) then at that point in the MDX Script there’s an expensive calculation.

As you’ll see if you download the source code it is in a very basic state at the moment, but it works most of the time for me and has come in very handy on a number of occasions. There are a lot of changes and improvements that I’d like to make (such as recording the total number of cells returned by the query, the total number of non-empty cells and the number of Query Subcube events at each step) and if anybody out there has other suggestions or would like to sign up as a developer I’d be only too happy to hear from you.

I also need to thank Greg Galloway for making some invaluable contributions to the code and for fixing at least one critical bug for me. This is probably also a good point to mention another project on Codeplex that Greg and Darren Gosbell have been working on over the last few months, BIDS Helper:
http://www.codeplex.com/bidshelper

… which contains some really useful extra functionality for people working with AS in BI Development Studio.

I’m always happy to hear from readers of this blog (you can find my contact details at http://www.crossjoin.co.uk/contact.html) and happy to take requests for subjects to blog about. For example I got an email from Joseph Boschert the other week asking if I could post something about OR queries in MDX and since this is a subject that comes up on a fairly regular basis and can be quite confusing I agreed.

The simplest kind of OR query is where you want to display or slice by two members on the same hierarchy. For example in Adventure Works if I wanted to see Internet Sales where the year was 2003 or 2004 then I could write a query something like this:

SELECT [Measures].[Internet Sales Amount] ON 0,
{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]}
ON 1
FROM [Adventure Works]

and if I wanted to see an aggregated value I could either put the set containing 2003 and 2004 in the WHERE clause, as follows:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE({[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]})

or in a subselect:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM (SELECT
{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]} ON 0
FROM [Adventure Works]
)

Both queries should return the same result, although of course the WHERE clause and subselects are not equivalent (see Mosha’s posting here for an explanation of how they differ). For the sake of simplicity I’ll stick to using WHERE clauses for the rest of this post.

Things become a little more complex when you want to do an OR over two different hierarchies on the same dimension because the default behaviour here in MDX is to AND. For example, what if I was interested in seeing the value of Sales made in 2003 or on a Friday? The following query gives me the value of Sales made in 2003 and on a Friday:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE([Date].[Calendar Year].&[2003],[Date].[Day Name].&[6])

How do I get the OR? Well, the answer is this:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Date].[Day Name].[All Periods])
,
([Date].[Calendar Year].[All Periods],[Date].[Day Name].&[6])})

…but let’s take some time to understand why this works. The set in the WHERE clause contains two tuples, the first representing all Day Names in 2003 and the second representing Fridays in all Calendar Years. This is clearly what we want to pass into the OR, but isn’t there a danger of double counting here? Actually no because the WHERE clause is pretty clever. If you were to write the following query you’d see a different, incorrect, higher value:

WITH MEMBER MEASURES.DOUBLECOUNT AS
([Date].[Calendar Year].&[2003],[Date].[Day Name].[All Periods],[Measures].[Internet Sales Amount])
+
([Date].[Calendar Year].[All Periods],[Date].[Day Name].&[6],[Measures].[Internet Sales Amount])
SELECT MEASURES.DOUBLECOUNT ON 0
FROM [Adventure Works]

…because by summing the tuples together we’re adding the value of Sales on Fridays in 2003 twice, ie double-counting it. Luckily the WHERE clause doesn’t do a simple sum though, it looks at the area described in the set and doesn’t count any overlapping cells twice. You can visualise this area by running this query:

SELECT {} ON 0,
UNION(
CROSSJOIN({[Date].[Calendar Year].&[2003]},[Date].[Day Name].[Day Name].MEMBERS)
,
CROSSJOIN([Date].[Calendar Year].[Calendar Year].MEMBERS,{[Date].[Day Name].&[6]})
)
ON 1
FROM [Adventure Works]

It’s basically a set of tuples that contains all days in 2003 and Fridays in all other years. Incidentally, if we needed to create a calculated member that didn’t double-count we’d need to sum up this same set as follows:

WITH MEMBER MEASURES.NOTDOUBLECOUNTED AS
AGGREGATE(
UNION(
CROSSJOIN({[Date].[Calendar Year].&[2003]},[Date].[Day Name].[Day Name].MEMBERS)
,
CROSSJOIN([Date].[Calendar Year].[Calendar Year].MEMBERS,{[Date].[Day Name].&[6]})
)
, [Measures].[Internet Sales Amount])
SELECT {MEASURES.NOTDOUBLECOUNTED} ON 0
FROM [Adventure Works]

You could also use a query something like this:

WITH MEMBER MEASURES.NOTDOUBLECOUNTED AS
([Date].[Calendar Year].&[2003],[Date].[Day Name].[All Periods],[Measures].[Internet Sales Amount])
+
([Date].[Calendar Year].[All Periods],[Date].[Day Name].&[6],[Measures].[Internet Sales Amount])
–
([Date].[Calendar Year].&[2003], [Date].[Day Name].&[6],[Measures].[Internet Sales Amount])
SELECT MEASURES.NOTDOUBLECOUNTED ON 0
FROM [Adventure Works]

…which explicitly subtracts the tuple containing the double-counted value and may be slightly faster, but makes the potentially dangerous assumption that everything is additive.

The last scenario we need to mention is ORing across hierarchies from dimensions, for example if I wanted to see Sales where the transaction was either in 2003 or to a Customer in the US. Again, thankfully, the WHERE clause ‘just works’ (the example of two hierarchies on the same dimension doesn’t do what it does because of auto-exist) so that the following query doesn’t double-count:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Customer].[Country].[All Customers])
,
([Date].[Calendar Year].[All Periods],[Customer].[Country].&[United States])})

Before I finish, I should also mention that the Analysis Services Stored Procedure Project has a very useful sproc that makes it very easy to generate the kind of asymmetric sets of tuples we’ve been dealing with here, written by Darren Gosbell:
http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=AsymmetricSet&referringTitle=Home