The WorkerThread Blog

we know stuff so you don't have to

Archive for August, 2011

Book Review- MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook

Posted by workerthread on August 31, 2011

There has been a big buzz around all of the new Business Intelligence features coming along with the next release of SQL Server, currently codenamed Denali.   This release is going to have a huge number of new features, not least of all two different versions of the Analysis Services engine – the original multidimensional OLAP one and a new Tabular mode one.   Marco Russo has a brief but useful explanation of this in his blog post here.

As Marco says, the new Tabular mode engine is going to make great use of Data Analysis Expressions or DAX.  Learning DAX will be something all BI analysts and developers working with the Microsoft set of tools will want to do and his PowerPivot book is a good place to start.  

So you may ask, why am I reviewing a book on MDX, which is the multi-dimensional expression language used with the older multidimensional model?  Well, as exciting as all of the upcoming technology is, I think MDX is going to be around for a good while yet.  We don’t yet have a release date for Denali and many of the new features are going to need a lot of planning, particularly if you want to use the new reporting features such as Crescent along with SharePoint.  In the meantime, there is an opportunity to make use of the current SQL Server release to build very effective BI solutions with Analysis Services which will still work well once Denali is available. 

The book’s author, Tomislav Piasevoli is a SQL Server Analysis Services MVP based in Croatia.   He has a lot of real-world experience with Analysis Services, cube development and MDX and that certainly comes through in this book.  All of the cookbook “recipes” are well documented with a full set of downloadable examples.  And the technical reviewers for the book are among the most experienced in the world, including Marco Russo, Chris Webb and Greg Galloway.  This certainly shows through in the sample code – I’ve tried out a good few of these already and all work as described which unfortunately isn’t the case with some other books out there.  All examples use the good old AdventureWorks database samples in their 2008 R2 guise.  So its possible to work through everything in the book by installing the trial version of SQL Server, available as a free download, or maybe purchasing the developer edition which has all the features of SQL Server Enterprise.

As the title suggests, this is a “cookbook” covering lots of MDX solutions or “recipes”.  Tomislav’s technique is to lay these out as in a standard format: Getting ready, which sets up the initial query for the data you will use; How to do it which shows you a worked example with MDX code; How it works, describing what the code is doing and finally There’s more which will point you to other examples of how the technique might be used.

Each chapter of the book covers a specific set of problems or challenges which you might encounter when developing a multi-dimensional Business Intelligence solution and provides appropriate solutions.   You can take a look at the areas covered in the table of contents over at PackPub.  I haven’t tried out everything covered in the book – in the same way as it’s unlikely you would cook every dish in a real cookbook unless you are Julie Powell and you want a book/film deal.  Here are some of the topics I’ve been impressed with so far –

Working with Time – lots of examples on adding time intelligence to your cubes and MDX queries along with several examples of calculating today’s date.  As Tomislav says, this is one of the most frequently asked questions in online searches and forums.

Concise Reporting – How to use a set to make your analytical queries more compact and concise.  Examples here cover topics like isolating best or worst members, displaying important members plus “others” and “totals” rows and finding exceptions.

This isn’t a book for anyone completely new to MDX but is definitely an excellent resource if you have already started to work with it want good real-world advice.   MDX isn’t an easy language to master and I think regardless of your experience level you will find something of value here.   Well worth buying.   And if you are completely new to MDX I would suggest starting with the Stairway to MDX series on SQLServerCentral.com (registration required).

If you are a Kindle user the book is available from Amazon and you can download a sample from there to get a better feel for how the cookbook and its recipes are structured.

Technorati Tags: ,,

Posted in Business Intelligence, SQL Server | Comments Off on Book Review- MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook