Does Excel Do MDX?

One of my primary interests at present is Business Intelligence (BI), using available tools to present meaningful information to the business that actually adds value.

I won’t go into what BI is, the quality of BI products available at present, or the how businesses perceive or fails to perceive BI, that may be a topic for another day, but rather in developing my skills and capabilities in this area. Excel is a wonderful medium for presenting this information, and the majority users that I know would rather use Excel than other tools.

Most people who are familiar with BI will have heard of OLAP cubes, a technology that allows manipulating and analyzing data from multiple perspectives, and provides fast analysis of data in a way that a relational database just cannot match. In Excel, it is very simple to build a pivot table that can plug into an OLAP cube, allowing retrieval of large, complex of data sets easily and quickly.

I have built many applications that utilise pivot tables against cubes, and have also started to build more analyses using the newly added cube functions in Excel. The basic format of a typical cube function, say CUBEVALUE, is

CUBEVALUE(connection, member_expression1,member_expression2…)

with an example formula being

=CUBEVALUE(“MDX Step-By-Step Chapter 3 Cube”,”[Product].[Category].[Bikes]”,”[Measures].[Reseller Sales Amount]”)

where a member_expression, such as “[Product].[Category].[Bikes]”,”[Measures].[Reseller Sales Amount]”)is a text string of a multidimensional expression (MDX) that evaluates to a member or tuple within the cube. This is not the most intuitive syntax I have come across. There are various ways to find these tuples, which we will not go into here but maybe a subject for a later post, but I felt it would be a good idea to get better acquainted with MDX. By getting a better understanding of MDX I fell I would be able to query cubes more effectively, from within SQL Server 2008 Management Studio (SMS), or even within Excel.

To this end, I bought myself SQL Server 2008 MDX Step by Step, a book from Microsoft Press, and the following is my view of this book (yes, I did pay for this book, it is not a free review copy :)).

One thing to explain here, especially for those of you who know this primarily as an Excel blog, is that this book is primarily aimed at developers working in Analysis Services and makes no mention of Excel that I can recall, and all the examples are shown within SMS.

Before I make any specific comments, I will state that I had used MDX prior to reading this book, but felt a bit at sea. It may that as this was my first book on the subject that it found me as a soft target, but I liked the book, and felt it was just about worth my outlay. The comments below should be taken with this overall view in mind.

The book starts gently enough, guiding you through an overview of BI and MDX, introducing the MDX Query Editor in SMS, and explaining the concept of tuple.

Chapter 3 was the first where any real MDX code was deployed, using a simplified cube in order to focus on the points being made. As well as tuples, it introduced attribute and user hierarchies, which are key cube fundamentals to understand.

Chapter 4 was the first where the material became more than just simple explanation and more detailed concepts were introduced. I think that in too many places the authors did a less than satisfactory job of explaining what was going was going on; too often things were stated without an explanation as to why, and the rationale for techniques was not explained. This is especially important in this chapter, the first where we meet these less obvious concepts and where that clear explanation is vital. For example,

– on p67 the Distinct function is introduced, and explains how it can be used to remove any duplicate rows (or columns) that the query might generate. The thought occurred to me that maybe you just add that to every set statement to make sure there are no duplicates, but of course I am sure it comes at a high cost in performance. Nothing was mentioned as to the pros and cons. Just stating what the function does should not be the objective of a book in my view, that is what help is or, it should look to guide you through when and where to use them, best practices, and so on

– when discussing MeasureGroupMeasures in chapter 4, most of the rows returned currency or quantity formatted cells, one returned non-formatted cells, but there was no explanation as to why. Figure 1 below shows the query in question, and Figure 2 shows the results with the cells in question highlighted.

Figure 1

Figure 2

The answer seems to be due to how the cells within the cube space are designed, the format of the cells is given here. By adding some code to the query to get the cell properties

Figure 3

and double-clicking one of the non-formatted cells we can see that the format is null

Figure 4

This is covered as a topic earlier in the book, but it could have been reiterated here in my view, to clarify this seeming anomaly, and to empathise that earlier description.

Quibbles aside, Chapter 4 introduced these key concepts in a logical fashion, and generally did a good job explaining them.

Expressions were introduced in Chapter 5, the ability to add calculated members to the already powerful cube. As the authors state, this adds a whole new dimension (no pun intended) to the cube, and they rightly give it a thorough explanation, 30 pages on a single topic as against the 31 pages of Chapter 4 which covers many topics within sets.

I won’t cover the rest of the chapters here, Chapters 1- 5 covers Part I of the book, MDX Fundamentals. Parts II and III cover MDX Functions and MDX Applications. I will leave you to explore these yourself if you are minded to buy this book, suffice to say that I found them informative, relevant, and appropriate. They are less obviously helpful to me from an Excel perspective, but will help me as I work deeper with cubes in the future.

One thing that I think is difficult, and this is not aimed at this book but is a comment about cubes generally, is that it is very difficult to envisage the data, and see more than what the query returns (without creating other queries) – n-dimensions is just too hard. But we have another excellent tool available to help us, one that I made extensive use of whilst reading this book; that is our good friend Excel. Using Excel and pivot tables I was able to get a much more familiar peek into the cube, one that allowed me to double-check what the authors were saying, and also to give me wider context that helped me extend their quoted queries to delve even deeper.

Giving just one example of what I mean by this, in Chapter 4 there is discussion on querying the members of a hierarchy or hierarchy level. One of the key concepts covered was the difference in querying the members of an attribute hierarchy at the leaf level as against all levels. The query of all levels is shown in Figure 5 below

Figure 5

which returns the data set shown in Figure 6.

Figure 6

This is difficult, at least to me, because there are various levels of data here. ‘Bike Wash – Dissolver’ is a product within the ‘Cleaners’ sub-category, which is within the ‘Accessories’ category, no clear demarcation in the dataset as returned. Figure 7 shows the same query results with some manual highlighting of the groups

Figure 7

By creating a simple pivot table in Excel, Figure 8, I was able to view this same data in a much more readable manner, which helped me to understand the points being made.

Figure 8

Here you can see that Helmets is a sub-category of Accessories very clearly, and of course you have the filtering options (which I used to restrict the calendar dates as per the set SELECTion in the MDX, and geography to the United States as in the MDX WHERE clause), and grouping.

I found creating simple pivots extremely useful in working through the examples.

 

In summary, I found this book well structured, and that it gave me a good understanding of MDX and how to use that within SQL Server 2008 Management Studio. As explained above, it does not explicitly cover using MDX within Excel or VBA, two areas of interest to me, but I gained more than enough information to be able to apply that to my existing Excel and VBA knowledge, and feel far better equipped having read this book.