Title： MDX（2）
Publisher： Friend Lv
Lesson 1: MDX Functions
function set within the MDX language. These can be particularly useful for character string manipulation.
MDX also has a set of built-in functions that are specifically suited to manipulation of sets. The MDX Sample Application is friendly in the way that it provides templates for these functions on the right hand side of the middle pane. Let’s try some.
1. Write a query to report on Store Sales with [Education Level] on columns and [Promotions] on rows.
SELECT
{[Education Level].MEMBERS} ON COLUMNS,
{Promotions.MEMBERS} on rows
FROM Sales
WHERE ([Store Sales])
Notice there are many empty rows. We want to ignore the empty rows. This is simple enough, we just add “non empty” at the beginning of the row specification.
2. Type “NON EMPTY” at the beginning of the rows row.
SELECT
{[Education Level].MEMBERS} ON COLUMNS,
NON EMPTY {Promotions.MEMBERS} on rows
FROM Sales
WHERE ([Store Sales])
The ORDER Function
The next task is to sequence the rows in ascending order by value of [All Education Level].
3. Add in an ORDER function around the Promotions.MEMBERS set, using [Education Level].[All Education Level] descending as the sequence. Note; you can find a template for the ORDER function on the middle right pane of the MDX Sample Application. Just drag and drop it onto your query pane to set up the template. If you require help on any of the functions, use the OLAP Services online Product Documentation. This help file called MSDSSBOL.chm is (by default) placed in the C:\Program Files\OLAP Services\Help directory. You can access it from the Start menu by selecting Programs, Microsoft SQL Server 7.0, OLAP Services, and Product Documentation.[1]
SELECT
{[Education Level].MEMBERS} ON COLUMNS,
NON EMPTY ORDER(
{Promotions.MEMBERS},
[Education Level].[All Education Level],
DESC
) ON ROWS
FROM Sales
WHERE ([Store Sales])
In contrast to an SQL request, the MDX request treats columns and rows the same (symmetrically). They are both axes. Hence we can sequence columns in the same way that we sequence rows.
4. Add another Order function in to order columns by [All Promotions] in ascending sequence.
SELECT
ORDER(
{[Education Level].MEMBERS},
[Promotions].[All Promotions],
ASC) ON COLUMNS,
NON EMPTY ORDER(
{Promotions.MEMBERS},
[Education Level].[All Education Level],
DESC) ON rows
FROM Sales
WHERE ([Store Sales])
Now we are just starting to see things in MDX that are very difficult in Transact-SQL. Two order statements in one query.
The TopCount and Descendants Functions
Let’s find the 10 most profitable customers for December 1997. We could use the ORDER function and just read the first 10 rows, but there is a TopCount function that does exactly what we want. We will also need to use the Descendants function because the Customers dimension has 4 levels and we only want the customers level, which is at level four. I.e. Descendants([Customers], [Name]) will retrieve all the members of the [Customers] dimension at the [Name] level. Putting these two functions together we can ask for the top 10 customers.
5. Write a query with measures on columns and the top 10 customers for December 1997 on rows, measured by [Store Sales Net].
SELECT
MEASURES.MEMBERS ON COLUMNS,
TopCount(Descendants([Customers],
[Name]),10,
MEASURES.[Store Sales Net]
) ON ROWS
FROM Sales
WHERE ([Time].[1997].[Q4].[12])
Let’s assume that we are only interested in [Store Sales Net] and we would like to put [Product Family] on columns.
6. Modify the preceding query to measure [Store Sales Net] and put Product Families (children of Product) on columns.
SELECT
[Product].CHILDREN ON COLUMNS,
TopCount(Descendants([Customers],[Name]),
10,
MEASURES.[Store Sales Net]
) ON ROWS
FROM Sales
WHERE ([Time].[1997].[Q4].[12],
MEASURES.[Store Sales Net])
Now we are getting close to the format we want. The last request from our financial controller is to include a column for all products.
7. This is quite simple, just include the [Product].[All Products] tuple on the column specification.
SELECT
{[Product].CHILDREN,
[Product].[All Products]} ON COLUMNS,
TopCount(Descendants([Customers],[Name]),
10,
MEASURES.[Store Sales Net]) ON ROWS
FROM Sales
WHERE ([Time].[1997].[Q4].[12], MEASURES.[Store Sales Net])
The BottomCount Function
We now want to find the store cities that are contributing least to our profitability.
8. Use the BottomCount function to list the 5 worst cities in terms of [Store Sales Net] for all time. Let’s show all measures on columns, including calculated members. Filter out those cities that have had no [Store Sales]. We will discuss the AddCalcuulatedMembers function in a few minutes.
SELECT
AddCalculatedMembers(MEASURES.MEMBERS) ON COLUMNS,
BottomCount(
Filter(
{Descendants([Store], [Store City])
},
MEASURES.[Store Sales]<>0
),
5,
MEASURES.[Store Sales Net]
) ON ROWS
FROM Sales
Order by BASC
Our Financial Controller wants this list of stores, but he wants it in alphabetical order of the [Store Name].
9. Add in an Order function for the rows using Store.CurrentMember.Name as the sequence field and sequence in BASC order. The B in BASC signifies that sequencing is to Break out of the hierarchical sequence. If you use ASC, OLAP will sequence the members within their hierarchies, cities within states.
SELECT
AddCalculatedMembers(MEASURES.MEMBERS) ON COLUMNS,
Order(
BottomCount(
Filter(
{Descendants([Store],[Store City])},
MEASURES.[Store Sales]<>0),
5,
MEASURES.[Store Sales Net]),
Store.CurrentMember.Name ,BASC)
ON ROWS
FROM Sales
MDX or Transact-SQL
Take a moment to think how you would write the above query using Transact-SQL. It would be difficult in a single query. A pragmatic approach might be to use a stored procedure that begins by creating a temp table with a record for each city and its Store Sales Net. Insert the records in ascending sequence of Store Sales Net. The insert statement would have a GROUP BY …. HAVING clause to group by city and to filter out cities with no sales. It would then be a matter of selecting the first 5 records. Consider which statement is more elegant; MDX or SQL? I am not a great fan of procedural code to do data manipulation, so I would go with the MDX. But I can appreciate some developers would prefer a stored procedure. Now consider which statement would execute faster. Typically the MDX statement would be orders of magnitude faster. But don’t trust what I say. Try it out for yourself.
TopPercent
TopPercent (and BottomPercent) are similar to TopCount and BottomCount, but as the name implies they are getting the top (or bottom) n percent.
1. Let’s write a query to get the first percentile of customers for the 4th quarter of 1997 in terms of Profit. Order the customers by name and put cities (non-empty) on columns.
SELECT
NON EMPTY Descendants([Store], [Store City])
ON COLUMNS,
Order(
TopPercent(
{Descendants([Customers],[Name])},
1,
MEASURES.[Profit]),
Customers.CurrentMember.Name ,BASC)
ON ROWS
FROM Sales
WHERE ([Time].[1997].[Q4],[Profit])
That’s great, but now our financial controller wants the same top percentile of customers, but she wants [Promotional Media] on columns.
2. Add these three extra features;
§ First column is to hold customer profit total
§ The [Promotional Media] headings are to be in alphabetical sequence
§ Do not show empty columns.
SELECT
NON EMPTY
{
([Promotion Media].[All Media]),
(Order
(Descendants
(
[Promotion Media], [Media Type]
),
[Promotion Media].CurrentMember.Name,
BASC
)
)
}
ON COLUMNS,
Order
(TopPercent
(
{Descendants([Customers],[Name]) },
1, MEASURES.[Profit]
),
Customers.CurrentMember.Name,
BASC
)
ON ROWS
FROM Sales
WHERE ([Time].[1997].[Q4],[Profit])
Again, it is worth considering how one would write such a query in SQL. It would certainly involve multiple SQL statements and would be very difficult to read.
Lesson 2: Calculated members
What You Will Learn
After completing this lesson, you will be able to:
· Write your own calculated members
Using Calculated Members
You might be excused for thinking that you had learnt the essentials of MDX. It is already very powerful with the symmetric enumerated dimensions and rich function list. However, there is one more important facet of MDX. That is the ability to dynamically create members in the query. For example, you may not be surprised to know that the measure “Store Sales Net” is not physically held in the cube. The cube just has a formula, and calculates it on the fly from [Store Sales] – [Store Cost]. We can create similar calculated members in the query.
Basic Syntax
The syntax for calculated members is to precede the SELECT statement with:
WITH MEMBER parent1.name1 AS ‘expression1’
[MEMBER parent2.name2 AS ‘expression2’ … ]
Here, “parent” refers to the parent of the new calculated member “name”.
Let’s add in a calculated member that will report on sales/items, which will be the average cost of an item. For this we need the syntax:
WITH MEMBER MEASURES.[Average Item Cost] AS '[Measures].[Store Sales]/[Measures].[Unit Sales]'
1. Add this calculated member into a query that uses it on columns and product members on rows.
WITH
MEMBER MEASURES.[Average Item Cost]
AS '[Measures].[Store Sales]/[Measures].[Unit Sales]'
SELECT
{MEASURES.[Average Item Cost]} ON COLUMNS,
{PRODUCT.MEMBERS} ON ROWS
FROM Sales
Just like any other member, we can specify that this member in the WHERE clause.
2. For example, write a query to report on the [Average Item Cost] with [Store Type] on the rows and [Product] on the columns.
WITH
MEMBER MEASURES.[Average Item Cost]
AS '[Measures].[Store Sales]/[Measures].[Unit Sales]'
SELECT
{[Store Type].MEMBERS} ON COLUMNS,
{PRODUCT.MEMBERS} ON ROWS
FROM Sales
WHERE MEASURES.[Average Item Cost]
Note
You may have noticed that the preceding query was not as fast as some of the earlier queries. This is not due to the computation required for the calculated member. It does require some additional processing, but the slower response is mainly due to the large number of cells that are returned. There are about 1560 products in our database. Our query asks for all 1560 products plus all the higher level members.
3. Now restrict the number of products to only Walrus wines. I.e., we want children of “[Product].[All products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].”
WITH
MEMBER MEASURES.[Average Item Cost]
AS '[Measures].[Store Sales]/[Measures].[Unit Sales]'
SELECT
{[Store Type].MEMBERS} ON COLUMNS,
{[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Walrus].CHILDREN} ON ROWS
FROM Sales
WHERE MEASURES.[Average Item Cost]
Now let’s assume that our financial controller has asked us to report on sales in the four quarters of 1997. She is particularly interested in the growth of the last quarter and would like to isolate Walrus wines.
4. Start by writing a query that will show us store sales for the quarters of 1997 on columns and the various Walrus wines on rows.
SELECT
{([Time].[1997].CHILDREN)} ON COLUMNS,
{[Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine]
.[Wine].[Walrus].CHILDREN} ON ROWS
FROM Sales
WHERE MEASURES.[STORE SALES]
The next task is to add a column that will provide us growth for the fourth quarter. Our financial controller has provided us with the formula: Q4/SUM(Q1,Q2,Q3).
5. Let’s create that as a calculated member [Time].[1997].[Q4]/
AVG({[Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997]
.[Q3]}). Add this calculated member to the query.
WITH
MEMBER [Time].[1997].[Q4 Growth] AS
'[Time].[1997].[Q4]/AVG({[Time].[1997].[Q1],[Time]
.[1997].[Q2],[Time].[1997].[Q3]})'
SELECT
{([Time].[1997].CHILDREN),
([Time].[1997].[Q4 Growth])}
ON COLUMNS,
{[Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine]
.[Wine].[Walrus].CHILDREN} ON ROWS
FROM Sales
WHERE MEASURES.[STORE SALES]
We are half way there, we need to add a row that reports on all wines excluding Walrus. We can do that, it is simply another calculated member of wine minus Walrus wine like:
[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine]
-[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Walrus]
6. Add this into your query.
WITH
MEMBER [Time].[1997].[Q4 Growth]
AS '[Time].[1997].[Q4]/AVG({[Time].[1997].[Q1],
[Time].[1997].[Q2],[Time].[1997].[Q3]})'
MEMBER [Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine].[Wine]
.[Excluding Walrus]
AS '[Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine].[Wine]
-[Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus]'
SELECT
{([Time].[1997].CHILDREN),([Time].[1997].[Q4 Growth])}
ON COLUMNS,
{[Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine]
.[Wine].[Walrus].CHILDREN,
[Product].[All products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Excluding Walrus]} ON ROWS
FROM Sales
Solve Order
This is exactly the report desired by our financial controller. However, we were fortunate with this query. If you look closely, it is not clear how Plato should calculate the bottom right cell. Should it calculate [Q4 Growth] before [Excluding Walrus] or the other way round. It makes a big difference.
7. Let’s explicitly tell Plato which way to do it with the SOLVEORDER clause. The default SOLVEORDER is 0. Give [Q4 Growth] a SOLVEORDER of 0 (the default) and [Excluding Walrus] a SOLVEORDER of 1.
WITH
MEMBER [Time].[1997].[Q4 Growth]
AS '[Time].[1997].[Q4]/AVG
({[Time].[1997].[Q1],
[Time].[1997].[Q2],[Time].[1997].[Q3]}
)’,
SOLVEORDER = 0
MEMBER [Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Excluding Walrus]
AS '[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine]
-[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Walrus]’,
SOLVEORDER = 1
SELECT
{([Time].[1997].CHILDREN),([Time].[1997].[Q4 Growth])}
ON COLUMNS,
{[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Walrus].CHILDREN,
[Product].[All products].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Excluding Walrus]}
ON ROWS
FROM Sales
WHERE MEASURES.[STORE SALES]
Note
Notice the bottom right cell has changed. This is because Plato has calculated it by getting [Q4 Growth] for [Wine] and subtracted [Q4 Growth] for [Walrus].
What we want Plato to do is to get [Excluding Walrus] figures for the quarters and then calculate [Q4 Growth] figures. When SOLVEORDER is important, you should not leave it to chance. It is good practice to explicitly define SOLVEORDER. Rather like “ORDER BY” in SQL. You may know the current database optimizer will use a certain index and the data will be sorted. However, if you do not explicitly declare order, you cannot be sure that you will always receive it the way you want.
8. Let’s complete the query with explicitly declared SOLVEORDER.
WITH
MEMBER [Time].[1997].[Q4 Growth] AS
'[Time].[1997].[Q4]/AVG({[Time].[1997].[Q1],
[Time].[1997].[Q2],[Time].[1997].[Q3]}) ',
SOLVEORDER = 1
MEMBER [Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine].[Wine]
.[Excluding Walrus] AS '[Product].[All products]
.[Drink].[Alcoholic Beverages].[Beer and Wine]
.[Wine]
-[Product].[All products].[Drink]
.[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Walrus]',
SOLVEORDER = 0
SELECT
{([Time].[1997].CHILDREN),([Time].[1997].[Q4 Growth])}
ON COLUMNS,
{[Product].[All products].[Drink]
.[Alcoholic Beverages].[Beer and Wine].[Wine]
.[Walrus].CHILDREN, [Product].[All products]
.[Drink].[Alcoholic Beverages].[Beer and Wine]
.[Wine].[Excluding Walrus]}
ON ROWS
FROM Sales
WHERE MEASURES.[STORE SALES]
Lesson 3: Set Functions
What You Will Learn
After completing this lesson, you will be able to:
· Use MDX Set functions
Using Set Functions
Our marketing manager has asked us to report on sales numbers as absolute numbers and percentages of the parent. Let’s create a MEMBER for the percentage of sales the current member is of its parent using CURRENTMEMBER and PARENT properties. I.e. ([Store].CurrentMember, Measures.[Store Sales]) / ([Store].CurrentMember.Parent, Measures.[Store Sales]). Member properties such as CurrentMember are very useful, as they allow us to write generic calculated members. Notice, that we have explicitly defined the Measure in the tuple. We would not need to explicitly define the Measure if the one we wanted was the default Measure or if we defined the one we wanted in the WHERE clause. As we noted earlier, MDX is a redundant language. There are many ways of writing the same request.
1. Let’s use this calculated member to put [Store Sales] and [PercentSales] on columns and USA states on rows.
WITH
MEMBER MEASURES.PercentSales AS '(Store.CurrentMember,
[Store Sales]) / (Store.CurrentMember.Parent,
[Store Sales])'
SELECT
{[Store Sales], PercentSales } ON COLUMNS,
{Store.[All Stores].[USA].CHILDREN} ON ROWS
FROM Sales
The Format_String Property
We have the data we want, but not the format. How do we tell Plato that PercentSales should have a format of “%#.00”? Fortunately it is very simple, we just append “, FORMAT_STRING = ‘#.00%’” on the MEMBER clause.
2. Add formatting to the calculated member PercentSales.
WITH
MEMBER MEASURES.PercentSales AS '(Store.CurrentMember,
[Store Sales]) / (Store.CurrentMember.Parent,
[Store Sales])', FORMAT_STRING = ‘#.00%’
SELECT
{ [Store Sales], PercentSales } ON COLUMNS,
{Store.[All Stores].[USA].CHILDREN} ON ROWS
FROM Sales
Now the format is just as the Marketing Manager wanted. Notice the PercentSales Member uses CurrentMember and Parent properties, so we can change the Store level and everything will work fine.
3. Change the query to report on all cities in WA.
WITH
MEMBER MEASURES.PercentSales AS '(Store.CurrentMember,
[Store Sales]) / (Store.CurrentMember.Parent,
[Store Sales])', FORMAT_STRING = '#.00%'
SELECT
{ [Store Sales], PercentSales } ON COLUMNS,
{Store.[All Stores].[USA].[WA].CHILDREN} ON ROWS
FROM Sales
4. Change the query again to show all stores in Seattle
WITH
MEMBER MEASURES.PercentSales AS '(Store.CurrentMember,
[Store Sales]) / (Store.CurrentMember.Parent,
[Store Sales])', FORMAT_STRING = '#.00%'
SELECT
{ [Store Sales], PercentSales } ON COLUMNS,
{Store.[All Stores].[USA].[WA].[Seattle].CHILDREN} ON ROWS
FROM Sales
Notice there is only one store in Seattle.
The PrevMember Property
Another useful member property is PrevMember. It is particularly useful in time series dimensions. For example, if we wanted to report on the sales for each month and the percent growth on the preceding month we could use the following expression. “([Store Sales]) / ([Store Sales], Time.PrevMember) “.
5. Use this expression to report Months on columns and [Store Sales] and [PercentGrowth] on rows.
WITH
MEMBER MEASURES.PercentGrowth AS '([Store Sales])
/ ([Store Sales], Time.PrevMember)',
FORMAT_STRING = '#.00%'
SELECT
{Descendants([Time],[Month])} ON COLUMNS,
{ [Store Sales], PercentGrowth } ON ROWS
FROM Sales
The IIf Function
This gives us the report we want. Well almost, the first cell has a divide by zero, because there were zero sales for December 1996. We can resolve this by using VBA's "Immediate If" function IIf.
6. Let’s add a check for an empty cell and return a percentage of 100 (or whatever you prefer) in place of a divide by zero.
WITH
MEMBER MEASURES.PercentGrowth AS
'IIf(IsEmpty(Time.PrevMember),1,
([Store Sales]) / ([Store Sales], Time.PrevMember))',
FORMAT_STRING = '#.00%'
SELECT
{Descendants([Time],[Month])} ON COLUMNS,
{ [Store Sales], PercentGrowth } ON ROWS
FROM Sales
Lesson 4: Basic Numeric Functions
What You Will Learn
After completing this lesson, you will be able to:
· Use MDX numeric functions
Using Basic Numeric Functions
For a complete list of the numeric functions check the Product Documentation file selecting Functions, MDX in the index. Some common functions include Avg, Count, IIf, Max, Median, Min, and Sum.
The Avg Function
Let’s assume we want to report on average sales of stores over the 12 months of 1997. One way of doing this would be to write a calculated member summing up all 12 months and dividing by 12. However, if a store opened in July, this would skew its average figure. Fortunately the numeric function AVG will determine average from non empty cells. For a store that opened in July, this would involve adding 6 numbers and dividing by 6. Let’s try it out with a report that will provide us with the top 10 stores for monthly average beer sales to female customers in 1997.
1. Start with a query that will report all stores on rows and average beer sales for months in the year 1997 on columns. Don’t forget to filter by females and beer.
WITH
MEMBER Time.1997MonthAvg AS
'Avg({Descendants([Time].[1997],[Month])})'
SELECT
{[Time].[1997],[Time].[1997MonthAvg]} ON COLUMNS,
{Descendants([Store],[Store Name]) } ON ROWS
FROM Sales
WHERE
(Gender.[F], MEASURES.[Store Sales],
Product.[Drink].[Alcoholic Beverages]
.[Beer and wine].[Beer])
2. Now add a function to return only the top 10.
WITH
MEMBER Time.1997MonthAvg AS
'Avg({Descendants([Time].[1997],[Month])})'
SELECT
{[Time].[1997],[Time].[1997MonthAvg]} ON COLUMNS,
TopCount({Descendants([Store],[Store Name]) },
10, [Time].[1997MonthAvg]) ON ROWS
FROM Sales
WHERE
(Gender.[F], MEASURES.[Store Sales], Product.[Drink]
.[Alcoholic Beverages].[Beer and wine].[Beer])
The Count Function
Now just to double check, let’s add in a column that will report how many months are included in the average. We can use the Count function with our Store set. In this query we will use EXCLUDEEMPTY, an optional flag of the COUNT function.
3. Add in a 1997MonthCnt member Count({Descendants([Time].[1997],[Month])}, EXCLUDEEMPTY)'.
Your statement now reads:
WITH
MEMBER Time.1997MonthAvg AS
'Avg({Descendants([Time].[1997],[Month])})'
MEMBER Time.1997MonthCnt AS
'Count({Descendants([Time].[1997],[Month])}, EXCLUDEEMPTY)'
SELECT
{[Time].[1997],[Time].[1997MonthAvg],
[Time].[1997MonthCnt]} ON COLUMNS,
TopCount({Descendants([Store],[Store Name]) },
10, [Time].[1997MonthAvg]) ON ROWS
FROM Sales
WHERE
(Gender.[F], MEASURES.[Store Sales], Product.[Drink]
.[Alcoholic Beverages].[Beer and wine].[Beer])
Lesson 5: Set Functions
What You Will Learn
After completing this lesson, you will be able to:
· Enumerate your own sets
Using Set Functions
For a complete list of the set functions check the Product Documentation file selecting Functions, MDX in the index. Some common functions that have not yet been introduced include Distinct, Except, Intersect, and Union.
Manipulating Sets
So far we have seen how the MDX language places sets on axes, in some ways we can view “set” as synonymous to “axis”. We don’t have to use dimensions, we can create our own axes using tuples. For example, on columns we might want sales of drink to females and sales of food to males. We can do this with the following statement:
SELECT
{
([Gender].[F], [Product].[Drink]),
([Gender].[M], [Product].[Food])
}
ON COLUMNS
FROM Sales
We can add another tuple to this axis (columns), but it must match the tuples that are there already. For example, we could add:
([Gender].[All Gender],
[Product].[Drink])
or
([Gender].[F],
[Product].[Drink].[Alcoholic Beverages]
.[Beer and Wine].[Wine].[Walrus]
.[Walrus Chardonnay]).
But we could not add:
([Product].[Drink],
[Gender].[All Gender])
It is in the wrong sequence
Nor could we add:
([Gender].[All Gender],
[Product].[Drink],
[Marital Status].[M])
It has an extra dimension.
We could in effect add this last tuple if we add
[Marital Status].[All Marital Status]
to the end of the other tuples.
1. Write an MDX query that has three tuples declared on columns; one for sales of drinks to females; one for sales of food to males; and another for sales of drinks to married customers.
SELECT
{
([Gender].[F], [Product].[Drink],
[Marital Status].[All Marital Status]),
( [Gender].[M], [Product].[Food],
[Marital Status].[All Marital Status]),
( [Gender].[All Gender], [Product].[Drink],
[Marital Status].[M])
}
ON COLUMNS
FROM Sales
WHERE MEASURES.[Store Sales]
Union and Intersect Functions
Now let’s look at set functions such as Union and Intersect. Suppose we want rows to contain results for USA, each state in USA, and each city in WA. We could write this as:
{[Store].[USA], [Store].[USA].CHILDREN,
[Store].[USA].[WA].CHILDREN}
An Equivalent set using the UNION function would be:
UNION(
{[USA]},
UNION(
{[USA].CHILDREN},
{[USA].[WA].CHILDREN}
)
)
The Except Function
A related function is Except. This can be thought of as a subtraction. You start with a set and remove some tuples. For example, we might like to report on all promotions except [No Promotion].
Write a query to report all the measures on columns with all promotions on rows except [No Promotion]. Just to make it easier to check, put an ORDER function on [Promotions].CurrentMember.Name on the row specification.
SELECT
MEASURES.MEMBERS ON COLUMNS,
ORDER(
{EXCEPT(
{[Promotions].CHILDREN},{Promotions.[No Promotion]}
)
},
[Promotions].CurrentMember.Name,
BASC) ON ROWS
FROM Sales
Lesson 6: More Calculated Members
What You Will Learn
After completing this lesson, you will be able to:
· Request all measures, including calculated members
· Use the Filter function
Using Calculated Members
In the previous query, the observant MDX hack will have noticed that there are only five columns, although the MDX Sample Application suggests there are seven measures. What happened to the other two measures? Members [Unit Sales] and [Sales Average] are calculated members and are not enumerated when we request Measures.MEMBERS. We can specify them explicitly, or if we want them in the Members collection, we use the AddCalculatedMembers function. For example:
AddCalculatedMembers(Measures.MEMBERS)
1. Use this function to alter the preceding query to include physical and calculated measures.
SELECT
AddCalculatedMembers(MEASURES.MEMBERS) ON COLUMNS,
ORDER(
{EXCEPT(
{[Promotions].CHILDREN}, {Promotions.[No Promotion]}
)
},
[Promotions].CurrentMember.Name, BASC
) ON ROWS
FROM Sales
Filters
A very useful function in MDX is Filter. As the name implies, it allows you to filter tuples out of an axis. The syntax for Filter is:
Filter(set, filter condition)
For example, let’s look at the top 10 customers by profit contribution, which I define as [Store Sales Net]/MEASURES.[Store Sales], but filter out those customers that have spent less that $20. The Filter syntax will look like
Filter({DESCENDANTS(CUSTOMERS,[Name])},
MEASURES.[Store Sales] > 20)
2. Write the MDX query to return the top 10 customers (on rows) by profit contribution that have spent at least $20. Put all real and calculated measures on columns.
WITH
MEMBER MEASURES.PercentContribution AS
'MEASURES.[Store Sales Net]/MEASURES.[Store Sales]',
FORMAT_STRING = '#.00%'
SELECT
AddCalculatedMembers(MEASURES.MEMBERS) ON COLUMNS,
TopCount(
{Filter({DESCENDANTS(CUSTOMERS,[Name])},
MEASURES.[Store Sales] > 20)}, 10,
MEASURES.[PercentContribution]
) ON ROWS
FROM Sales
Don’t leave me with that empty feeling
Often we are only interested in non-empty tuples. The following query returns a row for every store, even ones that have no sales:
SELECT
MEASURES.MEMBERS ON COLUMNS,
{DESCENDANTS(Store,[Store Name])} ON ROWS
FROM Sales
WHERE [Customers].[USA].[CA]
3. Add “NON EMPTY” to the beginning of the row specification to request only those stores that have had sales.
SELECT
AddCalculatedMembers(MEASURES.MEMBERS) ON COLUMNS,
NON EMPTY {DESCENDANTS(Store,[Store Name])} ON ROWS
FROM Sales
WHERE [Customers].[USA].[CA]
Pull your OR in
Many people who learn MDX from an SQL base, at some point ask ‘how do you put an “OR” in the MDX WHERE clause?’ This is a bit like a car driver looking at a yacht cockpit and saying, “where is the brake pedal?” I will try and explain with a few examples. Let’s take an SQL statement such as:
SELECT * FROM Promotions
WHERE Promotion = “Bag Stuffers”
OR Promotion = “Big Time Discounts”
What this query is really asking for is two rows, one for “Bag Stuffers” and one for “Big Time Discounts”. With MDX we can simple enumerate the row dimension with these two tuples.
4. Write a query with {(Promotions.[Bag Stuffers]),
(Promotions.[Big Time Discounts])} on rows and measures on columns.
SELECT
{[Measures].MEMBERS} ON COLUMNS,
{(Promotions.[Bag Stuffers]),
(Promotions.[Big Time Discounts])} ON ROWS
FROM Sales
5. Now let’s try an SQL Query that is using the OR clause to filter records that will be aggregated. Suppose we want to see a row for each gender, and we want to include sales that are for “Bag Stuffers” or “Big Time Discounts”. Ie
SELECT Gender, SUM(“Store Sales”), SUM(“Store Costs”)
FROM Sales
WHERE Promotion = “Bag Stuffers”
OR Promotion = “Big Time Discounts”
GROUP BY Gender
We could write a comparable query in MDX with a calculated member. It would look like:
MEMBER Promotions.[Stuffers and Big Timers] AS 'Promotions.[Bag Stuffers] +
Promotions.[Big Time Discounts]'
6. Write the complete query with gender on rows and [Store Sales] and [Store Costs] on columns.
WITH
MEMBER Promotions.[Stuffers and Big Timers] AS
'Promotions.[Bag Stuffers]
+ Promotions.[Big Time Discounts]'
select
{[Measures].[Store Sales], MEASURES.[Store Cost]}
ON COLUMNS,
Gender.MEMBERS ON ROWS
from Sales
WHERE Promotions.[Stuffers and Big Timers]
Congratulations
You have completed the MDX Walk-In Labs. Remember, MDX can perform very sophisticated queries. Like SQL, it is almost always more appropriate (and possible) to write your request in one query. I would encourage you to build good MDX skills so that whatever the request, you will be able to write an elegant looking query that will get the result set quickly. In the OLAP space the developers that make a difference will be ones that are intimate with MDX.
--------------------------------------------------------------------------------
[1] Tip: You might find the MDX Sample Application templates very useful for functions that you understand. For functions that you need help on, go to OLAP online Product Documentation.
