Chris Webb's BI Blog

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

Archive for June 2011

It just works–but why?

with 9 comments

One of the things that often confuses people when they learn MDX is the way that certain queries seem to work without them understanding why. This is because MDX tries to be helpful – and I would say too helpful – in correcting your mistakes for you by applying functions to objects, and casting objects to other objects, without you knowing to avoid raising an error. This might seem like a useful thing to do but in the long run I think it stops people getting a proper grasp of how MDX actually works, and reinforces the habit of ‘hack the statement until it works’ that some people have.

Let’s look at a couple of examples. First of all, in a SELECT statement, the way we define what appears on rows or columns is to use an expression that returns a set object. Now look at the following query:

SELECT
[Measures].[Internet Sales Amount]
ON COLUMNS,
[Date].[Calendar].[Calendar Year].&[2003]
ON ROWS
FROM [Adventure Works]

It executes just fine, but what we’ve put on rows and columns is in each case just a member. SSAS is expecting a set but is casting each member to a set with that one member in it, ie

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

Now the problem with the former is that as soon as you want to put more than one member on an axis, eg

SELECT
[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]
ON COLUMNS,
[Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Calendar Year].&[2004]
ON ROWS
FROM [Adventure Works]

The query errors with one of MDX’s famously unhelpful error messages:

Parser: The statement dialect could not be resolved due to ambiguity.

In this case it now can’t tell whether this is a SQL query or an MDX query (and yes, you can query SSAS with SQL) and so it fails; you now need to add those braces that should have been there in the first place:

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

There are numerous other examples. This query returns all Calendar Years on rows:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
[Date].[Calendar].[Calendar Year]
ON ROWS
FROM [Adventure Works]

Even though, in the row axis definition, you’re supplying a level object: [Date].[Calendar].[Calendar Year] . What SSAS is doing here is secretly applying a function to the level object you’ve given it to get the set object that it actually needs, and that function is the .MEMBERS function. So what’s actually happening is this:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

However, when you supply a hierarchy object on rows instead:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
[Date].[Calendar]
ON ROWS
FROM [Adventure Works]

You only get the All Member returned, so we’re not getting the .MEMBERS function being applied here (if we were, we’d get every member from every level of the Calendar hierarchy). What we’re actually getting back is the default member – but from which function? It’s actually the .CURRENTMEMBER function, as far I can see, so what’s actually happening is this:

SELECT
{[Measures].[Internet Sales Amount]}
ON COLUMNS,
{[Date].[Calendar].CURRENTMEMBER}
ON ROWS
FROM [Adventure Works]

To prove this, if you run this query with the hierarchy object being used in a calculated measure:

WITH
MEMBER MEASURES.TEST AS
MEMBERTOSTR([Date].[Calendar])
SELECT
{[Measures].[Internet Sales Amount], MEASURES.TEST}
ON COLUMNS,
[Date].[Calendar].MEMBERS
ON ROWS
FROM [Adventure Works]

…you can see the output of the calculated measure varies by row:

image

Whereas if the function being applied here was, say, the .DEFAULTMEMBER function, we’d always get the default member being returned here.

I could go on, but the real point to make here is that relying on SSAS to fix your mistakes for you is a bad thing. Whenever I write MDX I always try to be as ‘correct’ as possible to help me (and whoever needs to maintain the code after me) understand exactly what’s happening as easily as possible; it’s as important as commenting the code properly in my opinion. And if you’re learning MDX, be curious: if something works but you don’t understand why, take some time to understand it; just think what problems you’ll have when something doesn’t work and you don’t understand why!

Written by Chris Webb

June 30, 2011 at 10:36 am

Posted in MDX

Public MDX Course–London–October 26 to 28 2011

with 6 comments

Following on from my first, sold-out public MDX course earlier this year, I’ve now organised another run in London from October 26th to 28th. After feedback from the previous course I’ve now expanded it to three days to allow even more time for practical exercises; the cost is a very reasonable (I think) £749 plus VAT. For a full agenda and to register, see:
http://www.regonline.co.uk/Register/Checkin.aspx?EventID=988368&trackingcode=CWB

Written by Chris Webb

June 28, 2011 at 10:08 pm

Posted in Events

SQLBits 9 – Liverpool, 29th September-1st October 2011

with one comment

In case you haven’t heard already, the dates and venue for the next SQLBits have been announced. SQLBits 9, “Query Across the Mersey”, will be taking place at the Adelphi Hotel in Liverpool from 29th September to 1st October. More details are, as always, available at http://www.sqlbits.com/.

Session submission is open, so if you’d like to speak (and we welcome new speakers) check out http://www.sqlbits.com/information/SessionSubmission.aspx. You can take a look at the sessions already submitted here: http://www.sqlbits.com/information/PublicSessions.aspx

Hope to see you there!

Written by Chris Webb

June 24, 2011 at 2:47 pm

Posted in Events

Tagged with

Denying access to an entire level with dimension security

with 16 comments

Most of the time when you’re using dimension security with SSAS, you’re slicing a hierarchy vertically: for example, on a Geography dimension you only want members of a given role to see just one Country on your Country hierarchy. Occasionally, though, you may want to slice a hierarchy vertically: on a Time hierarchy, you may want to allow members of a role to drill down from Year to Month, but not see Date level data. This is also possible; a customer recently asked me how to do it so I thought I’d write up the explanation since it’s very easy to do.

Let’s take the Calendar hierarchy on the Date dimension in Adventure Works as an example. When you browse it in the cube browser in BIDS, you’ll see a hierarchy that goes down from Year at the top to Date at the bottom:

image

Now, let’s create a role that stops users from drilling down beyond Month. Create a new role and go to the Dimension Data tab, and select the Date dimension on the Adventure Works cube (note not the Date dimension further up the list – that’s the Database dimension, we want the Cube dimension, the instance of the Date dimension inside the cube):

image

Then select the Date attribute hierarchy (the Date attribute hierarchy is used as the bottom level of the Calendar user hierarchy, so by securing it you’re also securing all user hierarchies that it appears in) and select the Deselect All Members radio button.

image

You can then deploy the project with this new role, and go to the Cube Browser tab, click the Change User button and select the new role in the Security Context dialog to test it:

image

You will then be able to see that you can no longer drill down below the Month level in either the metadata pane or the pivot table:

image

Written by Chris Webb

June 22, 2011 at 11:02 pm

Posted in Analysis Services

Tagged with

Interactive Information Visualization

leave a comment »

Here’s some more interesting work coming out of MS Research, this time in the area of data visualisation:

http://research.microsoft.com/en-us/news/headlines/visualizations-061511.aspx

I’d love the see this demo (why not record it and put it online somewhere…?) – iSketchVis in particular sounds cool. It’ll be interesting to see if any of this work makes it into Crescent any time in the future…

Written by Chris Webb

June 16, 2011 at 5:44 pm

Posted in Visualisation

Excel DataScope

with 3 comments

Jamie Thomson just tipped me off about something new and very interesting – one week after I had a moan about Microsoft doing nothing about Excel and the cloud, here comes Excel DataScope:
http://research.microsoft.com/en-us/projects/azure/datascope.aspx

Here’s the blurb from the site:

From the familiar interface of Microsoft Excel, Excel DataScope enables researchers to accelerate data-driven decision making. It offers data analytics, machine learning, and information visualization by using Windows Azure for data and compute-intensive tasks. Its powerful analysis techniques are applicable to any type of data, ranging from web analytics to survey, environmental, or social data.

There are yet more tantalising details in the video and the two pdfs here:

http://research.microsoft.com/apps/video/?id=149888
http://research.microsoft.com/en-us/projects/azure/exceldatascope_11_poster1.pdf
http://research.microsoft.com/en-us/projects/azure/exceldatascope_11_poster2.pdf

I’m currently trying to find out more about all this, but there’s clearly a  ton of cool stuff here:

  • You can use the Live Labs Pivot control for visualisation.
  • It does data mining in the cloud. Is this the successor to the old data mining addin? The functionality is clearly the same.
  • There’s a section on Map Reduce running on Windows Azure on one of the posters. Is this using Dryad?

Is this a first glimpse of a new cloud-based BI platform from Microsoft? Will SSAS in the cloud form part of it? Before we all get too excited (or at least I get too excited) it’s worth noting that this is coming from the eXtreme Computing Group and not the SQL Server team, it’s clearly aimed at scientific rather than business users, and is described as “an ongoing research and development project”, ie it is not a commercial product. The potential is obvious though, and I hope it becomes something significant.

Written by Chris Webb

June 13, 2011 at 8:31 pm

Posted in Cloud, Data Mining, Excel

Tagged with

BI User Group Evening–London, June 21st

with one comment

It’s been ages since I’ve organised a BI evening for the UK SQL Server User Group which is bad, I know, but I’ve finally got round to setting one up. It will be at Hitachi Consulting’s offices in London on June 21st; we have one speaker confirmed, Jason Thomas, who’ll be covered the use of spatial data in SSRS, and there will be one other speaker from Hitachi but I don’t have the final details on the session yet.

To register, go to:

http://sqlserverfaq.com/events/292/London-UG-meeting-on-BI-topics-including-spatial-data-support-other-topic-TBC.aspx

Hope to see you there!

Written by Chris Webb

June 8, 2011 at 1:45 pm

Posted in Events

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers