Catálogo de publicaciones - libros

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.

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.

This chapter is an introduction to using MDX to query OLAP cubes and we’re also using it to introduce you to MDX syntax in general.

Now that you’ve seen how MDX works in practice, this seems like a good time to explain the use of brackets and braces.

In Chapter 2 we said that MDX is typically used for querying OLAP cubes and also for expressions, so here we’ll start looking at creating and using MDX expressions. Expressions may take a tuple or set as a parameter and always return a value (even if it is a null); MDX doesn’t have any notion of ‘void’. Expressions are partial MDX statements and they have a host of uses such as defining calculated members, sets, or member properties.

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.

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.

Over the last few chapters you’ve taken several steps along (and up) the MDX learning curve and we’ve started to open the door to a great deal of functionality. In this chapter we’ll continue the progress by looking at moving averages. These are useful analytical tools which can be used to solve very common business problems.

It is a common requirement to want to extract a subset of the data in a cube depending on whether it meets certain criteria. For instance, you may want to identify any periods in which the sales of many products fell, compared with the sales for the previous period. What we want here is a ratio: we want to take a count of products whose sales fell (when compared to the last period) and divide that by the count of all products. The count of all products with falling sales is a subset of the count of all products and in order to resolve this, MDX has a Filter function which is used to identify subsets of data.

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.