Chris Webb's BI Blog

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

Archive for October 2011

Analysis Services 2008 R2 Performance Guide Published

leave a comment »

Not much to say, really, apart from what’s in the title and the fact that if you are serious about SSAS you need to read it! You can get it here:
http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/10/10/analysis-services-2008-r2-performance-guide.aspx

Written by Chris Webb

October 11, 2011 at 8:50 pm

Posted in Analysis Services

SQL Server training in London

with 2 comments

Over the last few months I’ve been working on setting up a new business to provide advanced SQL Server training courses in London, similar to what’s available at the SQLBits training day but in a more traditional classroom environment, lasting several days and with practical exercises. I’m not quite ready to launch yet (watch this space – the website will be ready soon) but I do have two courses to promote right now. Both are run by guys from COEO, and both cost £990 plus VAT. Here are the details:

SQL Server Developer Workshop with Gavin Payne
1st – 2nd November 2011

A 2-day interactive workshop that drills down into new features, tools and best practices for developers working with SQL Server 2008 or 2008 R2.
More details and registration here.

SQL Server Internals and Troubleshooting Workshop with Christian Bolton
6th – 7th December 2011

The Advanced Troubleshooting Workshop for SQL Server 2005, 2008 and R2 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven methodical approach to problem solving. The workshop will enable attendees to tackle complex SQL Server problems with confidence.
More details and registration here.

I’ll have some more courses, including runs of my MDX and SSAS cube design courses, to announce soon…

Written by Chris Webb

October 10, 2011 at 9:07 pm

Posted in Events

Tagged with

Project Trinity

with one comment

I had an interesting chat with Gary Short at SQLBits last week about social network analysis, and he pointed out that Microsoft Research’s graph database, Project Trinity, was now available to download. You can find out more about it here:
http://research.microsoft.com/en-us/projects/trinity/default.aspx

Gary suggested that Trinity might, at some point in the future, follow F# in being a MS Research project that becomes a commercial project and to be honest I think he might be right. If Analysis Services is a database that is optimised for multidimensional data, why not add Trinity to the SQL Server stack as a specialised database for graph data? After all, social network analysis is only going to become more and more important in corporate BI in the future, and that’s just one use case for a graph database. And if Trinity becomes a product, why not include features of a tool like NodeXL in Project Crescent?

Written by Chris Webb

October 8, 2011 at 11:28 pm

Posted in BI

Tagged with

Excel subtotals when querying Multidimensional and Tabular models

with 4 comments

As I mentioned briefly in a recent post, the fact that Excel generates some pretty rubbish MDX for detail-level reports has been well documented by Richard Lees and Rui Quintino. The new Excel 2010 named set functionality allows you to work around these problems if you can write your own MDX, but let’s face it most people who are building Excel reports will not be able to do this so this problem can be a major headache and cause severe performance problems. One interesting point to note, however, is that Excel 2010 will generate slightly better MDX when querying a Tabular Model (and I’m including PowerPivot models here) compared to when it’s querying a Multidimensional Model (ie a SSAS cube). Take the following pivot table built using Excel 2010 against the Adventure Works cube running on SQL 2008 R2:

image

I’ve put the Internet Sales Amount measure on columns, the Calendar Year and Day Name hierarchies from the Date dimension on rows, and turned off all subtotals and grand totals. Here’s the MDX that Excel generates for this pivot table:

SELECT
NON EMPTY
CrossJoin(
Hierarchize(
{DrilldownLevel(
{[Date].[Calendar Year].[All Periods]},,,INCLUDE_CALC_MEMBERS)})
, Hierarchize(
{DrilldownLevel({[Date].[Day Name].[All Periods]},,,INCLUDE_CALC_MEMBERS)}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS 
FROM [Adventure Works]
WHERE ([Measures].[Internet Sales Amount])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

If you run this query you’ll see it returns 40 columns and that the first few columns contain the subtotals and grand totals that we specifically said we didn’t want, which can be the cause of performance problems:

image

However, if you create a PowerPivot model containing DimDate and FactInternetSales and build exactly the same pivot table, you’ll see a different pattern of MDX being produced. To enable easy comparison, I’ve taken the MDX that my PowerPivot model generated and changed the hierarchy names so it will work on the SSAS Adventure Works cube:

SELECT
NON EMPTY
Hierarchize(
DrilldownMember(
CrossJoin(
{[Date].[Calendar Year].[All],[Date].[Calendar Year].[Calendar Year].AllMembers}
, {([Date].[Day Name].[All])})
, [Date].[Calendar Year].[Calendar Year].AllMembers
, [Date].[Day Name]))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS 
FROM [Adventure Works]
WHERE ([Measures].[Internet Sales Amount])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

If you look at the results, you see that while the grand total is still being returned, the most of the unwanted subtotals are not and we only get 33 columns returned:

image

So if all other things were equal (and they’re not – the Tabular and Multidimensional engines are very different) then this MDX query has a big advantage over the first one because it’s doing much less work. Excel knows to use this new type of query by looking at the PREFERRED_QUERY_PATTERNS value returned by the MDSCHEMA_CUBES rowset; I’m told that the reason it isn’t used with Multidimensional models is that in many cases it could lead to worse, rather than better performance. This is another good reason to prefer Tabular models over Multidimensional models for detail-level reporting if you’re using Excel as a front-end.

An interesting side-note is that this new style of MDX is only possible in SSAS 2008 R2 because the DrillDownMember has got a new parameter called Target_Hierarchy, which allows you to specify which hierarchy you want to drill down on in a tuple (note there were some other changes with this type of function that I blogged about here). You can see the documentation here:
http://msdn.microsoft.com/en-us/library/ms145580(v=SQL.105).aspx

Here’s a quick example on Adventure Works. Consider the following query, where we’re drilling down on a tuple containing the all members from the Day Name and the Calendar Year hierarchies:

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

Here are the results:

image

You’ll see that only the Day Name hierarchy has been drilled down on, and this is (as far as I can see) because it’s the last hierarchy that appears in the tuple. However, with the new parameter, we can specify that we want the Calendar Year hierarchy drilled down on instead:

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

image

Thanks, as always, to Akshai, Marius, Greg Galloway, Teo Lachev and the usual string of SSAS obsessives for providing the background info for this post…

Written by Chris Webb

October 7, 2011 at 9:21 am

Posted in BISM, MDX, PowerPivot

SQLBits 9 Summary

with one comment

Well I’ve just recovered from yet another SQLBits, and for some reason (probably the obscene amount of beer consumed – I don’t even want to think what the bar bill will be like) it needed a lot more recovering from than previous events. That only goes to show how much I enjoyed it though…! Everything went well in the end; I think part of what makes SQLBits what it is is the slightly shambolic nature of it and nothing ever goes completely to plan, but we had no major disasters and attendance was comparable with Brighton. In my opinion we had some of the strongest content we’ve  had for a long while: I particularly enjoyed Kasper’s session on Crescent and Alex’s session on comparing PowerPivot and Qlikview, plus the sessions from Jen on mobile BI and Marco on how Tabular will change how we model data. If you weren’t there, we recorded all the sessions as usual and they’ve be available in the content section of the site in a few weeks.

I’d like to thank my fellow organisers, and everyone who helped out during the conference, for making it such a success. One of the questions I always get asked about SQLBits is “Why do you do it?”. It is a lot of effort, but in the end it’s great fun too – not only do you get to learn stuff, but it’s an excuse for everyone in the SQL Server community to get together and have a drink and a chat. I’m looking forward to a few weeks off though before we have to start planning the next one…

In the meantime, don’t forget about SQLRelay this week. I’ll be speaking in Hatfield tomorrow night and there are events taking place all round the country too. And if you’d like to stay in touch with what’s happening in the world of SQLBits, why not join the SQLBits LinkedIn and Facebook groups and see what people are saying on Twitter about us.

Written by Chris Webb

October 3, 2011 at 10:02 am

Posted in Events

Tagged with

Follow

Get every new post delivered to your Inbox.

Join 3,309 other followers