Chris Webb's BI Blog

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

Archive for August 2010

SQLBits Sessions

leave a comment »

OK, I’m back from holiday now and catching up with my blogging backlog. First of all, I need to mention that the SQLBits agenda has now been published and I’ll be doing two sessions: one on DAX and one on common SSAS design mistakes. You can see the agenda and more about my sessions here:

http://www.sqlbits.com/information/Agenda.aspx

It’s a truly stellar lineup this time; as Simon says, we have 20 MVPs and 4 former MVPs speaking. It’s also a much more international conference too – looking at the registration stats we’ve got people coming from Denmark, France, Germany, India, Ireland, Namibia, Poland, Portugal, Sweden, Switzerland, the Netherlands and the US as well as the UK.

Don’t forget also I’m doing a full day seminar on performance tuning SSAS on the SQLBits training day too:

http://www.sqlbits.com/information/TrainingDay.aspx

Written by Chris Webb

August 29, 2010 at 3:32 pm

Posted in Events

Teradata does MDX

with one comment

Another major BI vendor supports MDX: the Teradata OLAP Connector is an OLEDB for OLAP Provider for Teradata’s new ROLAP tool (for more information search for the Teradata Business Intelligence Optimizer). Here’s the press release:
http://www.prnewswire.com/news-releases/teradata-olap-connector-enriches-direct-excel-connectivity-enhancing-enterprise-and-real-time-analytics-101075004.html

It’s a boost for MDX as a cross-platform language; I wouldn’t be surprised if other vendors did something similar.

UPDATE: I thought I detected the hand of Simba at work here:
http://www.simba.com/news/Simba-Builds-Custom-ROLAP-BI-Solution-for-Teradata.htm

Written by Chris Webb

August 21, 2010 at 11:38 pm

Posted in MDX

Dryad goes commercial?

with one comment

I’m in the middle of my summer holiday at the moment, hence the relative silence here, but I just noticed this article by Mary Jo Foley suggesting that Microsoft might be turning Dryad into a commercial product next year and couldn’t resist posting it:
http://www.zdnet.com/blog/microsoft/microsoft-research-parallel-programming-project-set-to-go-commercial-in-2011/7161

There was a flurry of interest in it a few years ago but I thought all had gone quiet; maybe this is this the source of the rumours that Microsoft/Hadoop rumours too? I also notice that SQL Server/SSIS has disappeared from the ‘software stack’ diagram; is there going to be any tie-in with the rest of Microsoft’s BI efforts?

Written by Chris Webb

August 19, 2010 at 11:01 pm

Posted in BI

Order of Nested SCOPE Statements

with 2 comments

Funny how you can work with a product for years and years and still discover new things, isn’t it? I was writing some scoped assignments on a cube the other day and found that when you’re using nested SCOPE statements, the order that you put those SCOPE statements in the MDX Script is significant, contrary to what I had believed.

Consider a simple date dimension with the following attributes and attribute relationships:

image

Now, if we add the following MDX to the script, to scope on every member (including the All Member) on the date dimension:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;

SCOPE(MEASURES.DEMO);
    SCOPE([Order Date].[Date].MEMBERS);
        THIS = 2;
    END SCOPE;
END SCOPE;

You’ll see that it has changed the values of the DEMO measure for the whole of the date dimension from 1 to 2:

image

Now, if we add a second nested SCOPE on all the members of the Month level of the Month attribute (ie so not including the All Member on Month) as follows:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
    SCOPE([Order Date].[Date].MEMBERS);
        SCOPE([Order Date].[Month].[Month].MEMBERS);
            THIS = 2;
        END SCOPE;
    END SCOPE;
END SCOPE;

You’ll see that it now only changes month values, and nothing else:

image

This set of assignments gives the same result as the following, where there is no assignment on Date at all:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
    SCOPE([Order Date].[Month].[Month].MEMBERS);
        THIS = 2;
    END SCOPE;
END SCOPE;

However, if you reverse the order of the two SCOPEs you get a different result. So:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
    SCOPE([Order Date].[Month].[Month].MEMBERS);
        SCOPE([Order Date].[Date].MEMBERS);

            THIS = 2;
        END SCOPE;
    END SCOPE;
END SCOPE;

When you scope on all the members of Month except the All Member first, then all the members of Date including the All Member, the scope covers all dates and months:

image

Crossjoining these two sets in the same SCOPE has the same effect:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
    SCOPE([Order Date].[Month].[Month].MEMBERS,[Order Date].[Date].MEMBERS);
        THIS = 2;
    END SCOPE;
END SCOPE;

What’s happening is that where you have two nested SCOPE statements using sets of members from different attribute hierarchies on the same dimension, the attribute relationships that exist between those attributes become significant. Without any SCOPE statements then the ‘current’ scope context in the MDX Script is the entire cube, and then each successive SCOPE overwrites the previous context to create a new context and that process of overwriting context is not commutative. This is called attribute overwrite; I’ve wrestled with it before and frankly it makes my head hurt, but you can find out more about it here.

I suppose, therefore, that it would be a good idea when you’re scoping on sets containing members from multiple attributes from the same dimension to crossjoin those sets together and use a single SCOPE, to avoid any potential confusion. While I can just about rationalise this behaviour I can’t guarantee I’d be able to predict how attribute overwrite worked on a real dimension with multiple nested SCOPEs… Remember, of course if you have multiple nested SCOPEs using sets of members from hierarchies on different dimensions then the order of nesting doesn’t matter because there’s no attribute overwrite going on.

Thanks to Tomislav and Akshai for their help in understanding this problem.

Written by Chris Webb

August 3, 2010 at 10:20 pm

Posted in MDX

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers