Category Archives: 11531

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.

Keeping It Clear

It is not my intention for EDDD to be another ‘Excel Tips & Tricks’ blog, there are plenty of good examples of these already, but I saw someone struggling in a demonstration recently so I am thinking that this technique is not as well know as I had presumed, nor as it should be. Therefore I shall share it with you all.

Firstly, I hope we all agree that the Pivot table Field List in Excel 2007 is a big improvement over earlier versions. However, sometimes the cost of a nice facility is that comes at the cost of a lot of real estate, the field list is quite big. And it is rooted to the right of the Excel window. Or is it?

Actually, you can tear it off, move it and resize it.

Move the cursor towards the top of the field list until the cursor changes to  a cross with arrows at all points



At this point, you can tear off the field list using the mouse left button, and drag it off to wherever you want it on the screen, and resize it to whatever size is good for you.

This is so much easier than scrolling the data and pivots around so that the field list does not obscure the point being made.

To restore it to its original position, just double-click in the title bar.

The Glory of Pivots

Surely we all know how useful Pivot Tables are in Excel by now? They are great for knocking up a filterable, drillable list of the data, but they can be much more.

For instance, it is realtively simple to create a Top 10 (or top n) list from your data in a pivot table. To my mind, this sort of table should form the basis of all dashboards, far better than those pointless guages, pie charts and stacked bar charts (but we digress!).

Anyway, as I said, a top 10 list is simple to do. Here are the instructions for excel 2003.

We will start with a simple table of data like so



which we create a pivot table from, the result looking like this.



Then we double click on the item we want to rank (or we wcould right-click and select Field Settings), Company in this case, which presents the following dialog



From here we select the Advanced button, and get the following dialog



 


Here it is a simply a matter of choosing whether the data is ascending or descending, clicking the On option of Top 10 AUtoshow, and spinning through the number to be ranked for our choice.

The results of the top 10 would then look like this




Simple, quick and effective, what more can we ask? Of course, you need to refresh the pivot whenever the data changes, but that can be automated.