本帖最后由 demo 于 2011-5-4 01:44 编辑
First exercises.
Well, we have learnedthe basic MDX constructions. We know how to construct axes in different ways,we know how to slice. Even though it was relatively simple, it already givesyou ability to answer many interesting questions. So, take a challenge, and tryto answer the following questions:
1. Buildthe MDX query which shows sales of products over the years. 2. Buildthe MDX query which shows sales of products to customers in 1996. 3. Buildthe MDX query which compares the numberof printers shipped in Asia against Europe over the years.
Tuples
We mentioned earlier that axes can contain members or tuples,and promised to explain about tuples later. Well, it's time to fulfill that promise.Consider the following example: You want to see sales of computers and printers in Europe and Asia over years 1996and 1997. Well, we can do that by writing an MDXquery with three axes:
SELECT
{ Continent.[Europe], Continent.[Asia] } ON AXIS(0),
{ Product.[Computers], Product.[Printers] } ON AXIS(1),
{ Years.[1996], Years.[1997] } ON AXIS(2)
FROM Sales
This will work, but the result of this query is not a two-dimensional table, but three-dimensional cube. This might be fine, if this query was used internally by data mining application. But what if our boss asked you to report these numbers. Howyou are going to print this three-dimensional thing? The solution would be to rewrite this query in such a way, thateven though it will encapsulate three dimensions, it will still contain twoaxes. We want the query which willproduce the following result:
|
1996
|
1997
| Europe |
Computers
|
|
|
Printers
|
|
| Asia |
Computers
|
|
|
Printers
|
|
|
One special thing about this table is that its rows axiscontains two dimensions. The dimensionProducts is nested into dimensionRegions, such that we produced all fourcombinations between {Europe, Asia} and {Computers, Printers}. Every such a combination is called tuple in MDX. So the tuple is a combination ofdimension members, coming from different dimensions. Syntax for specifying a tuple in MDX is
( member-of-dim1, member-of-dim2, …,member-of-dimk )
Now, don’t you have some kind of déjà vu here? Doesn’t it remind you something you’ve already seen in this book ? Yeah, sure. We havehad exactly the same construction in WHERE clause. As a matter of fact,official syntax of MDX specification defines tuple to follow WHERE clause. We are already familiar with tuples. We used them in the WHERE clause. We just didn’tcall them tuples. A tuple can contain only one member. Unlike axis, tuples cannot be empty. However, you have to clearlydistinguish between member and tuple containing only one member.
member is not equivalent to ( member )
We’ll talk more about member and their properties later.Now, let’s write our MDX query to use tuples.
SELECT
{ Year.[1996], Year.[1997] } ON COLUMNS,
{
( Continent.Europe, Product.Computers ),
( Continent.Europe, Product.Printers ),
( Continent.Asia, Product.Computers ),
( Continent.Asia, Product.Printers )
} ON ROWS
FROM Sales
It is important to remember, that just like the case when we put the same dimension into the axis members, when we construct an axis from tuples, all these tuples have to be of the same structure. It means, all tuples have to contain the same number of members, and members on the same positions have to be from the same position. For example the following tuples pairs don’t have same structure:
( Europe, Computers ) and ( Europe, Computers, Sales )
( Europe, Computers ) and ( Europe, Sales )
( Europe, Computers ) and ( Computers, Europe )
Well, thoughtful reader may say now: “Everything is fine, but in this example we build the axis which contains only four tuples, and it was relatively easy to enumerate them. What if you want to see all products against all continents against all years. Now what ? MDX has an answer for this problem. MDX has a lot of built-in functionswhich allow to manipulate with axes and produce new axes. We’ll discuss thesefunction in one of the following chapters.
Calculated members
So far we saw the various ways to query the existing information inside the cube. However almost any OLAP application needs the ability to perform some calculations over the existing data. Let’s take classic example. We have predefined measures Cost and Sales whichdescribe how much did the product cost to us and for how much did we sell it.The next natural thing that one wants to ask is, well, what was our profit outof it. If the cube creator included measure Profit inside the cube – weare safe. However, what we are gonna do if such a measure doesn’t exist ?Luckily, MDX allows us to extend the cube by defining new members todimensions, which values are calculated out of existing ones. The syntax forcalculated members is to put the following construction in front of SELECTstatement:
WITH MEMBER parent.name AS 'expression'
Here, “parent” refers to the parent of the new calculated member “name”. Since dimension is organized as a tree, when we add new member,we should specify its position inside this tree. Fast-thinking reader will immediately point out to the fact, that not all dimensions are organized as classic trees, but rather as multiroot trees. Indeed, even in our example, we wanted to add new member to the measures dimension, and measures don’t havecommon root. Well, in such a situation, the dimension itself serves as virtual root. I.e. dimension name will be specified as parent. So, let’s go ahead and create calculated member Profit in the dimension Measures.
WITH MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost'
As we just discussed, the parent of the new member is measures dimension itself, the name is Profit, and the expression is prettystraight-forward: Subtract your costs from your sales, and you’ll get theprofit. Let’s put the whole query now, which allows to see the profits ofproducts along years:
WITH
MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost'
SELECT Products.MEMEBERS ON COLUMNS, Year.MEMEBERS ON ROWS
FROM Sales
WHERE ( Measures.Profit )
As you can see, the calculated members is treated in exactly same manner as normal members. They can be used anywhere, where the normal member can be used, i.e. in axis definition, in WHERE condition, you can even define calculated members using the other calculated members. As example, let’s add to the calculated member Profit another calculated member – ProfitPercent,which will exhibit the profit as percent of the costs. To calculate it, we’lltake the profit and divide it by cost.
WITH
MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost'
MEMBER Measures.ProfitPercent AS 'Measures.Profit / Measures.Cost', FORMAT_STRING = '#.#%'
SELECT
{ Measures.Profit, Measures.ProfitPercent } ON COLUMNS
FROM Sales
We see several interesting things in this example. First is the fact, that to define calculated member ProfitPercent we used anothercalculated member. Second is the FORMAT_STRING statement right after thedefinition of ProfitPercent expression. As a matter of fact, MDX allowsyou to specify several properties of calculated member. The expression is one,probably most important one (and the only mandatory one). However there areother. One of the most useful is FORMAT_STRING. It allows you to specify how doyou want to get the result cell value formatted for the purposes of nicedisplay. While this property has nothing to do with semantics of MDX, we feelit is still quite important for OLAP applications, so we included it into thisbook. MDX standard doesn’t define how this format strings should look like.Developers of Microsoft MDX implementation have chosen to use familiar VBformat standard. Since ProfitPercent is a fraction which meaning ispercentage, we used here #.#% string which automatically converts fraction topercent (i.e. multiplies by 100).
Well, after these examples, you may get wrong feeling, thatcalculated members are used only in measures dimension. Of course, this is nottrue. Suppose we want to compare the performance of the company between year1997 and 1998 and we want to see the changes. One way to do it is to build aquery which has an { [1997], [1998] } axis, and always look into the pair ofnumbers for every measure. Another way, though, is to define calculated memberin the level Year, parallel to 1997 and 1998, which will hold thedifference between them. I.e.
WITH MEMBER Time.[97 to 98] AS 'Time.[1998] – Time.[1997]'
Now if we follow that by statement
SELECT
{ Time.[97 to 98] } ON COLUMNS,
Measures.MEMBERS ON ROWS
FROM Sales
Note, that the row of Sales will show the differencein sales (likely positive), and the row of Cost will show difference incost (hopefully negative). Member [97 to 98] belongs to the time dimension andbehaves exactly as any other member in the time dimension. If we want to seedifference between months December and October of 1997, we’ll create calculatedmember under member [1997], i.e.
WITH MEMBER Time.[1997].[Dec To Oct] AS 'Time.[1997].[12] – Time.[1997].[10]'
We’ll see many other interesting examples in followingchapters as we advance in studying powerful MDX functions and not just simplemath. Now, we would like to go a little bit back and recall ourfirst example with Profit. Suppose we want to see how profit has beenchanged from 97 to 98. Well, the natural thing to write will be
WITH
MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost'
MEMBER Time.[97 to 98] AS 'Time.[1998] – Time.[1997]'
SELECT
{ Measures.Sales, Measures.Cost, Measures.Profit } ON COLUMNS,
{ Time.[1997], Time.[1998], Time.[97 to 98] } ON ROWS
FROM Sales
We’ll get 3x3 matrix:
|
Sales
|
Cost
|
Profit
| 1997 |
300
|
220
|
80
| 1998 |
350
|
210
|
140
| 97 to 98 |
50
|
-10
|
60
|
It looks OK for the first glance, but if we look deeperwe’ll see a certain problem here. There is no doubt about what we get in everycell of this grid, except for the low right corner. The cell in the low rightcorner is in the intersection of two calculated members: Profit and [97 to 98].So what is the meaning of this cell: Is it grow of the profit between 97 and98, or is it difference between relative sales and relative cost. In thissimple example it doesn’t really matters, you can look into it one way oranother, and the result will be still the same. But let’s imagine, that [97 to98] was defined as
WITH MEMBER Time.[97 to 98] AS 'Time.[1998] / Time.[1997]'
Now it is clear, that the decision of what formula isapplied to this cell really matters. Indeed to we want to calculate
([97 to 98],[Sales]) – ([97 to 98],[Cost])
or
([1998], [Profit]) / ([1997], [Profit])
Well, the MDX parser cannot read your mind. Sometimes youwill want to do former, sometimes later. That’s why MDX allows to specifyexplicitly what do you want. For every calculated member you define, you canspecify optional property SOLVE_ORDER This property allows you to control which calculated member formula will beapplied if you have conflict on the cell, just like in the example above. Thehigher value you assign to SOLVE_ORDER property, the higher priority of this member. I.e. when several formulaspretend to be calculated for single cell, the one with highest priority ischosen. (As a matter of fact, the author of this book had very strong argumentswith other spec writers to name this property SOLVE_PRIORITY rather than SOLVE_ORDER, becauseit is much easier to understand it that way). Well, let’s get back to ourexample. Since we want to see difference between profits, we’ll give to the [97to 98] calculated member higher priority. The standard says, that if you omit SOLVE_ORDER property,the default value will be zero. So, it’ll be enough to say
WITH
MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost'
MEMBER Time.[97 to 98] AS 'Time.[1998] / Time.[1997]', FORMAT_STRING = '#.00%', SOLVE_ORDER = 1
The interesting question is what will happen if bothcalculated members have the same solve order property. Well, MDX standardspecifies, that than the outcome is implementation dependent, and generallyunpredictable. To summarize: It has been long, but very important chapter.Calculated members are extremely useful in any OLAP application, and we’ll seethem a lot in following chapters.
Members and related functions
As you know by now, in MDX we operate in the multidimensional hierarchized space. I.e. we have many dimensions, and everydimension has hierarchical structure (MDX allows to same dimension to have morethan one hierarchical structure, but we’ll talk about it later). Obviously weneed set of functions to navigate in this hierarchical multidimensional space.By defining the query’s axes, you specify lower-dimensionality subspace of yourquery (optionally sliced by WHEREcondition). You also want to have functions to navigate in this subspace,knowing where you are, moving to the right direction etc. This chapter isdedicated to this type of functions.If you have dimension member, you may apply several methods on it: Parent, NextMember, PrevMember, FirstSibling, LastSibling, FirstChild, LastChild etc. Theoutcome will be another dimension member with obvious relationship to originalone. For example the writing [WA].Parent is equivalent to write [USA]. Of course nobodywill use [WA].Parent instead of [USA], and we’ll see muchmore useful application of Parent in a moment. Often, you want to know thecoordinates of the cell by some specific dimensions inside the query’ssubspace. This can be achieved by function
<dimension name>.CurrentMember
<level name>.CurrentMember
Now, we can use CurrentMember and Parent to solve thefollowing problem: Suppose we want to calculate the percentage of the sales inevery city relative to its state. To calculate this percentage, we’ll dividethe sales in the city by sales in its state. And how will we deduct the stateby city ? Of course by using Parent !
WITH
MEMBER Measures.PercentageSales AS '(Regions.CurrentMember, Sales) / (Regions.CurrentMember.Parent, Sales)' ,FORMAT_STRING = '#.00%'
SELECT
{ Sales, PercentageSales } ON COLUMNS,
Regions.Cities.MEMBERS ON ROWS
FROM Sales
This query effectively allows to see the required percentage.
While the functions Parent, FirstChild, LastChild operate inhierarchy in vertical manner, i.e. applying such a function changes level, functions PrevMember, NextMember, FirstSibling, LastSibling move you horizontally, inside the same level. PrevMember returnsthe member preceding to the given one in the level.MEMBERS collection, possibly crossinghierarchy lines. Example:
[Aug].PrevMember returns [Jul]. Both members belong to [Qtr3]
[Oct].PrevMember returns [Sep]. We crossed the hierarchy changing parent from [Qtr 4] to [Qtr 3].
Let’s see interesting example in using PrevMember. We wantto define new calculated member – Sales Growth, which will show the growth ordecline in the sales compared with previous month, quarter or year. Naturally,the formula definition will look like:
WITH MEMBER Measures.[Sales Growth] AS '(Sales) – (Sales, Time.PrevMember)'
Now, we can use this definition in the query like that:
SELECT
{ [Sales], [Sales Growth] } ON COLUMNS,
Month.MEMBERS ON ROWS
FROM Sales
Another example will be to see breakdown of sales for different products and how did these sales grew for every product in the last month.
SELECT
{ [Sales], [Sales Growth] } ON COLUMNS,
Product.MEMBERS ON ROWS
FROM Sales
When we’ll talk about set functions, we’ll see many interesting things to do with this query, like filtering only those products, for which sales declined, or for which we had unusual growth. In the calculated members properties chapter we’ll see techniques of implementing what is known as exceptions coloring or ‘traffic-lights’. I.e. we will be able to change presentation color of negative sales growth to red, and those that beat expectations to green. And all this only using MDX.
http://www.mosha.com/msolap/articles/MDXForEveryone.htm
|