Mosha Pasumansky

Snapshot data analysis

The MDX functions covered in the last two chapters are more than enough to get you started on your glittering career solving OLAP cube problems (or meeting OLAP cube challenges, if you prefer). In this chapter we’ll look at some business problems that are common to inventory systems and, of course, we’ll also cover how to cure them. They happen to be well suited to inventory type problems but, of course, they have many wider applications. (The sub-text here is “Even if you don’t happen to work with inventories, these functions are still worth getting to know!”)

Member properties and dimension security

You may recall that we touched briefly upon member properties back in Chapter 1. Now we’re going to take a further look at them and see how the value of these properties can be maximized using MDX.

Further advanced data modeling techniques

We’re still using the Budget cube and in the last chapter we successfully dealt with three problems using Custom Order, Custom Rollup and Custom Members. However, this cube continues to have some problems and, by a remarkable stroke of good fortune, resolving those problems enables us to introduce you to some further MDX-related topics.

Readme.doc — definitions you need to know

We used a sample set of data and a sample cube in order to produce the screen shots that appear in this chapter. However, the cube was created just to provide the screen shots and has very little merit as a real cube so we haven’t, therefore, included it on the CD-ROM.

Navigating the hierarchy

We talked in the last chapter about relative referencing of cells in a cube. CurrentMember is very useful as a dynamic “you are here” identifier for a particular cell, both when used on its own and in conjunction with other functions such as PrevMember, Lag and Lead.

Setting the default member

Up to this point we have written expressions that only reference one, two or maybe three dimensions. Behind the scenes MDX is actually generating an expression that applies to all the dimensions. In the background the system takes the default member of each dimension and appends it onto the expression. The default member for a dimension is usually at the All level, and the All level typically has only one member — All. So for the Product dimension the default member is All Products; you could say that Al1 Products is the default default member.

Advanced data modeling — Custom Order, Custom Rollup, Custom Members

The topics covered in this and the next chapter all come under the general heading of issues which typically need to be addressed in a financial application, so that is how we’ve illustrated them. However, once again, we want to stress that this is simply a convenient way of illustrating them — the topics can be applied in a host of different applications.

Recursion in MDX

The term ‘recursion’ is used to describe something that references itself. My favorite definition of recursion is found in the joke.

More MDX in general

The first edition of this book (18 chapters long) was written as an easy introduction to MDX. Assuming we succeeded in this task, by now you should be over that initial steep learning curve. So, when we came to add this extra material for the second edition we felt that it was appropriate to change gear a little and look at some more advanced topics. Recursion and NonEmptyCrossJoin came pretty much top of the list of advanced topics that people find challenging, so we decided to cover those.

How MDX is used

MDX is the key to unlocking all of the advanced capabilities of Analysis Services, so once you go beyond building basic cubes and want to add business logic to the cube, you really start to need MDX.