Lab 1: Introduction to MDX
Lesson 1: MDX and the Sample Application
Lesson 2: Basic MDX Syntax
This page left intentionally blank
Lesson 1: MDX and the Sample Application
This lesson will define MDX and learn its uses. Then it will discuss and create a simple query using the MDX sample application.
What You Will Learn
After completing this lesson, you will be able to:
· Define MDX
· List the MDX terms and the analogous terms in SQL
· Use the MDX Sample Application
· Describer the data structures in the FoodMart database
· Write a simple MDX statement
Before You Begin
Prerequisites
· Those who are familiar with the SQL language or an equivalent database language will have an advantage when learning MDX.
Lab Setup
· You must have a workstation with SQL Server OLAP services installed. The OLAP installation will setup the FoodMart database, which is used throughout this Lab.
MDX Defined
What is MDX?
The name MDX originated from Multi-Dimensional eXtensions. As this origin implies, MDX started as extensions to the SQL language. If you know the SQL language, you will have a good platform for learning MDX. But don’t be led to believe that it is SQL with a bit extra, it has a very different structure from SQL.
Is MDX like SQL?
Very simple MDX statements resemble SQL, they both take the format
SELECT …..
FROM ….
WHERE ….
That is where the similarity ends. However someone with SQL skills will have a good base for learning MDX. Both are data manipulation languages. |
Why MDX, why not SQL?
MDX is designed to facilitate queries on multidimensional cubes, while the SQL language is designed to facilitate queries on two-dimensional tables.
SQL and MDX Analogous Terms
|
Multidimensional Term |
Relational Analog |
|
Cube |
Table |
|
Level |
Column (string or discrete numeric) |
|
Dimension |
Several related columns or dimension table |
|
Measure |
Column (discrete or continuos numeric) |
|
Dimension member |
The value in the specific row and column corresponding to the given dimension level |
How does OLE DB fit in?
OLE DB is a data access API. Just as you can call relational database OLE DB providers with SQL requests, you can call OLAP OLE DB providers with MDX queries.
The MDX Sample application started out in life, as its name implies, a sample application to demonstrate how to call OLAP from a VB application. This application was the only interface that supported freeform MDX. Subsequently its importance rose. It is currently not an official part of SQL Server 7.0, but being the only MDX query editor it is extremely useful. Being a sample application, you get the source code for the MDX Sample with SQL Server. By default the sample application will be found at C:\Program Files\OLAP Services\Samples\MdxSample\. This is what the application looks like:
Notice that it has 4 panes. The top pane is where you can type in your MDX query; the bottom pane is where the results are displayed; the middle left pane is displaying the cube structure; and the middle right pane contains templates for MDX functions. This application is very useful in the way it supports drag and drop from the cube structure and function templates to the query pane.
The Sample Database
All examples in this lab are based on the Sales cube in the FoodMart sample database. This cube has been designed to facilitate the analysis of a chain of grocery stores and their promotions, customers and products. We have the following dimensions:
Sales Cube Dimensions
Key Points
Take a moment to review this information about the data cube.
- In particular note that some dimensions such as Customers have several levels.
- Some dimensions such as Gender and Marital Status have only one level. |
|
Dimension Name |
Level |
Description |
|
Customers |
Country, State or Province, City, Name |
Geographical hierarchy for registered customers of our stores |
|
Education Level |
Education Level |
Education level of customer, such as “Graduate Degree”, “High School Degree” |
|
Gender |
Gender |
Customer gender, “M” or “F” |
|
Marital Status |
Marital Status |
Customer marital status “S” or “M” |
|
Product |
Product Family, Product Department, Product Category, Product Subcategory, Brand Name, Product Name |
The products that are on sale in these stores. |
|
Promotion Media |
Media Type |
The media used for a promotion, such as Daily Paper, Radio, etc. |
|
Promotions |
Promotion Name |
Identifies promotion that triggered the sale |
|
Store |
Store Country, Store State, Store City, Store Name |
Geographical hierarchy for different stores in the chain (country, state, city) |
|
Store Size in SQFT |
Store Sqft |
Area occupied by store, in square feet. |
|
Store Type |
Store Type |
Type of store, such as “Deluxe Supermarket”, “Small Grocery” etc |
|
Time |
Years, Quarters, Months |
Time period the sale was made |
|
Yearly Income |
Yearly Income |
Income of customer |
Notice how some Dimensions, such as Customers, have multiple levels. This means that the dimension is hierarchical. Country is the highest level, each country will have one or more State Provinces; each State Province will have one or more cities; and each city will have one or more Names (customers).
Sales Cube Measures
|
Measure Name |
Description |
|
Unit Sales |
Number of units sold |
|
Store Cost |
Cost of goods sold |
|
Store Sales |
Value of Sales transactions |
|
Sales Count |
Number of Sales transactions |
|
Store Sales Net |
Value of Sales transactions less cost of goods sold |
|
Average Sale |
“Store Sales”/”Sale Count”. This is a Calculated Measure, which means that it is derived from other measures and does not need to be stored in the cube. |
Measures are the things you want to aggregate (count, sum, average, max, min etc.) When you come to writing MDX, it is useful to think of measures as like dimensions. In MDX whenever we reference a cell, we explicitly or implicitly specify which member of every dimension and measure we want as coordinates for that cell.
Let’s think about our first MDX query. The simplest MDX query has the following structure:
SELECT something ON COLUMNS
FROM cube
So far MDX syntax looks similar to SQL. This is where the similarity ends. MDX is a language designed for multidimensional cubes. Dimensions are the basic constructs, not tables.
In MDX, we can specify any number of dimensions in our resultset. We will call them axes to prevent the confusion with a cube’s dimensions. We can have zero, one, two, three…n. axes. All of the axes are symmetric. That means, whatever we can do on one, we can do on another. Typically MDX queries don’t have more than two dimensions in the resultset as we do not have many tools to display more than two-dimensional results.
Members
A member is an item in a dimension or measure. For example in the Gender dimension “M”, “F” and “All Gender” are all members.
Exercise 1
Using the MDX Sample Application to Write a Simple MDX Query
In this exercise, you will open the MDX sample program, create and execute a simple query.
Opening the MDX Sample Application
Let’s start the MDX Query Application.
1. From the Start menu select Programs, Microsoft SQL Server 7.0, OLAP Services, and the MDX Sample Application.
2. You will be prompted for the server and the provider. Enter the name of the server that has the OLAP Services and sample database installed. By default it is the Windows NT name of your machine. Leave the provider as MSOLAP and select OK. The MDX Sample Application will start.
3. Select the “FoodMart” database from the “DB” drop down list at the top of the screen.
4. Select the “Sales” cube from the “Cube” drop down list near the middle of the screen.
5. Select New from the File menu (or click on the “New Query File” button at the left of the toolbar).
We now have a fresh query window to start MDXing.
Your first MDX query
Your first MDX query will report on the number of units sold to male versus female customers.
1. Type the following query into the new query window.
SELECT
Gender.MEMBERS ON COLUMNS
FROM Sales
2. Run the query by clicking the green arrow on the toolbar (or click the F5 key).
Notice, how the result set also has a column headed “All Gender.” By default, MS OLAP will create an “All” level at the peak of the hierarchy. When you create dimensions in Plato, the “All” member is generated by default and it becomes the default member. This turns out to be very useful. In our query, we did not specify which member of the “Marital Status” dimension to use, so Plato used the default member “All”. By default this member has the format [All dimensionname], but if the dimension name is very large, it might be abbreviated to [All]. You may call the all member by any name. For example in the FoodMart database the all member of “Media Type” is “All Media”.
Now that you are familiar with the MDX Sample Application proceed to lesson 2 to study MDX syntax.
Lesson 2: Basic MDX Syntax
Now the fun starts:
We are now going to write some MDX queries. In some ways MDX is simpler than SQL. Plato has a lot of meta knowledge. That is, it knows about the relationships between the dimensions and members. You do not need to write INNER, LEFT/RIGHT/FULL OUTER JOIN etc. |
What You Will Learn
After completing this lesson, you will be able to:
· Write two dimensional MDX statements
· Use identifiers
· Use the CHILDREN syntax
· Apply a WHERE clause
· Enumerate the axes
Making More Complex Queries
Now we will explore some additional concepts.
Two Dimensional Queries
MDX can request resultsets with more than two dimensions. However, most tools have difficulty in presenting more than two dimensions. Hence, two-dimensional queries are the most common. Lets enhance the previous query by putting measures on rows.
1. Add a comma after COLUMNS and insert a new line with “Measures.MEMBERS ON ROWS”.
SELECT
Gender.MEMBERS ON COLUMNS,
Measures.MEMBERS ON ROWS
FROM Sales
Congratulations, you have written a two dimensional query.
Identifiers and Qualifiers
In the MDX language, identifiers can be delimited with square brackets.
2. Update the above query to use delimiters around the member names.
SELECT
[Gender].MEMBERS ON COLUMNS,
[Measures].MEMBERS ON ROWS
FROM Sales
Square brackets are always required when identifiers have spaces, are numbers or are also reserved words. MEMBERS is a reserved word, hence you should not enclose it in square brackets (unless you have a member called “MEMBERS”). Now that we know how to qualify identifiers we can query on any of the dimensions.
3. Update your query to report [Marital Status].MEMBERS ON COLUMNS and [Promotion Media].MEMBERS ON ROWS.
SELECT
[Marital Status].MEMBERS ON COLUMNS,
[Promotion Media].MEMBERS ON ROWS
FROM Sales
Set Identifiers
Curly brackets are used to denote sets. [Marital Status].MEMBERS and [Promotion Media].MEMBERS are sets and can be delimited by curly brackets. In the preceding query, Plato “knew” they were sets so the curly brackets were not necessary. However sets are not always evident to Plato. Hence it is good practice to write queries with curly braces.
4. Put curly braces around the set specifications in your query.
SELECT
{[Marital Status].MEMBERS} ON COLUMNS,
{[Promotion Media].MEMBERS} ON ROWS
FROM Sales
Children
For Your Information
Remember the chart on page 6. - The Customers dimension had several levels.
- Marital Status had only one level. |
Dimensions are often hierarchical. For example, our Customers dimension has 4 levels (Country, State or Province, City, Name). If we write a query using Customers.members on one of the axes, we are asking for the items at every level of the Customers dimension. That is all Countries, all States/Provinces, all Cities, and all Names. Perhaps we only want the members at one level. Children are very useful in limiting the output to the immediate level. For example, let’s report on the Customers dimension at the first (country) level.
5. Write a query that has [Marital Status].MEMBERS on columns and [Customers].CHILDREN on rows.
SELECT
{[Marital Status].MEMBERS} ON COLUMNS,
{[Customers].CHILDREN} ON ROWS
FROM Sales
Notice how the resultset only includes members of the first level of Customers. That is Country. Expand the Customers dimension on the cube browser pane (middle left) to view the levels in the Customers dimension. You might also notice in the results that we only have figures for USA. While Canada and Mexico are valid countries, there is no data to report on. You might say, that there could be stores in Canada and Mexico, but as yet there are no Sales.
We might want to drill down on USA and show the figures for states within the USA.
6. Use the CHILDREN keyword on the [Customers].[USA] member to show the USA states.
SELECT
{[Marital Status].MEMBERS} ON COLUMNS,
{[Customers].[USA].CHILDREN} ON ROWS
FROM Sales
This query asks for all the children members (all states) within USA. Notice how Plato knows about this hierarchy. We did not have to specify parent child joins as we would normally need to in SQL. We could further drill down on one of the states.
7. Ask for all the cities in WA by typing “.[WA]” after “[USA]” in your query.
SELECT
{[Marital Status].MEMBERS} ON COLUMNS,
{[Customers].[USA].[WA].CHILDREN} ON ROWS
FROM Sales
You can pivot the result set with a button on the far right of the toolbar. Look for the curved double-headed arrow.
The WHERE Clause
At first glance the WHERE clause appears similar to the corresponding clause in SQL. For example, we might want to report on Education Level by Marital Status, but only where customers are male.
8. Update your query by inserting a line at the end and type “WHERE Gender.[M]”
SELECT
{[Education Level].MEMBERS} ON COLUMNS,
{[Marital Status].CHILDREN} ON ROWS
FROM Sales
WHERE Gender.[M]
If we want to add further filters to the WHERE clause we enclose the whole WHERE clause in rounded brackets and dimension filters with commas. For example we might want to restrict the previous query to only those Sales where the sale was induced by a promotion media of “Radio”.
9. Add “,[Promotion Media].[Radio]” to the end of your WHERE clause and enclose the clause in rounded brackets.
SELECT
{[Education Level].MEMBERS} ON COLUMNS,
{[Marital Status].CHILDREN} ON ROWS
FROM Sales
WHERE (Gender.[M],[Promotion Media].[Radio])
We could continue to add filters to the WHERE clause, until we had one for each dimension. But we cannot create a query with a dimension on an axis (row or column) and in the WHERE clause. For example in the previous query, it would be illegal to put [Education Level].[Graduate Degree] on the where clause.
[Gender] and [Promotion Media] dimensions both have (the default) all level. Hence if we were to be particular about writing the WHERE clause we would have written it as:
WHERE (Gender.[All Gender].[M],
[Promotion Media].[All Media].[Radio])
[M] and [Radio] are not ambiguous in their respective dimensions, so it was not necessary to qualify them with their parent level. Indeed if the member is unambiguous in the cube, it is not necessary to qualify it at all. However, it is a good practice to qualify members.
There are many dimensions not specified in this query. For non-specified dimensions, it is useful to think of them as being specified with their default member, which is usually the [All] member.
The full measure
What if we want to measure something other than [Unit Sales]? The preceding queries have reported on the default measure (Unit Sales). It is the default measure because there is no [All] member and [Unit Sales] is the first member of Measures. If we want to report on another dimension, we would name it in the WHERE clause.
10. Modify the preceding query to report on Store Sales instead of Unit Sales by adding “,Measures.[Store Sales]” to your WHERE clause.
SELECT
{[Education Level].MEMBERS} ON COLUMNS,
{[Marital Status].CHILDREN} ON ROWS
FROM Sales
WHERE (Gender.[M],[Promotion Media].[Radio],
Measures.[Store Sales])
Review of Brackets, Braces and Parentheses
Let’s recap this area, as it is often an area of confusion.
· Square brackets [ ] are used to delimit member names.
· Curly brackets { } are used to define sets.
· Round brackets ( ) are used in functions and to delimit tuples. There will be examples of these soon.
A tuple is another word for a row in a table.
Review - Practicing the Basics
Now you know the basics of the MDX query language. Let’s practice using these elements before we move onto more sophisticated aspects of the language. The answers are at the bottom of the page. All examples use the Sales cube.
1. Write a query to show measures on columns and all of the [Promotion Media] members on rows.
2. Write a query to show the first level members (CHILDREN) of Product on columns and gender members on rows.
3. Write a query to show the first level members (CHILDREN) of [Product].[Drink] on columns and [Marital Status] members on rows. But only show figures for Gender [F].
4. Modify the WHERE clause so the report shows [Store Sales Net] figures.
Answers:
1. Suggested syntax:
SELECT
Measures.MEMBERS ON COLUMNS,
[Promotion Media].MEMBERS ON ROWS
FROM Sales
2. Suggested syntax:
SELECT
Product.CHILDREN ON COLUMNS,
[Gender].MEMBERS ON ROWS
FROM Sales
3. Suggested syntax:
SELECT
[Product].[Drink].CHILDREN ON COLUMNS,
[Marital Status].MEMBERS ON ROWS
FROM Sales
WHERE [Gender].[F]
This query shows figures for [Unit Sale] as it is the default measure.
4. Suggested syntax:
SELECT
Product.CHILDREN ON COLUMNS,
[Marital Status].MEMBERS ON ROWS
FROM Sales
WHERE ([Gender].[F], Measures.[Store Sales Net])
Enumerating Our Own Axes
So far we have only used the MEMBERS and CHILDREN collections of dimensions. Very often we want to be more precise than this. We might want to report on just two selected products, or we might want to select months October and November and the full year. Fortunately, in MDX we can specify exactly which members we want on an axis. We do this by enumerating our own set with declared tuples. For example, there are lots of promotions, but we only want to look at [Price Destroyers], [Price Slashers], and [All Promotions].
1. Enclose the above promotions within curly braces, which delimit the set, to enumerate your own row axis. Put {[Time].[1997].CHILDREN} on columns.
SELECT
{[Time].[1997].CHILDREN} ON COLUMNS,
{Promotions.[All Promotions].[Price Destroyers],
Promotions.[All Promotions].[Price Slashers],
Promotions.[All Promotions]} ON ROWS
FROM Sales
We have written the fully qualified member names with all levels qualified. Because the low level member names are not ambiguous, an equivalent query would be:
SELECT
{[Time].[1997].CHILDREN} ON COLUMNS,
{[Price Destroyers], [Price Slashers],
[All Promotions]} ON ROWS
FROM Sales
We can combine these enumerated axes with a WHERE clause and our query starts to get interesting.
2. Write a query to look at January, February, March and April of 1997 on columns, [Price Destroyers], [Price Slashers], [All Promotions] on rows, and filter for store Sales to male customers in the state [WA]. And we want to measure [Store Sales].
SELECT
{[Time].[1997].[Q1].[1],
[Time].[1997].[Q1].[2],
[Time].[1997].[Q1].[3],
[Time].[1997].[Q2].[4]} ON COLUMNS,
{[Price Destroyers],
[Price Slashers],
[All Promotions]} ON ROWS
FROM Sales
WHERE (Gender.[M],
Customers.[USA].[WA],
Measures.[Store Sales])
Let’s create our own tuples for the axis
In the previous section we enumerated our own axes from individual members. We can take that further by specifying dimension intersections on axes. For example we might want to compare Store Sales to female customers in WA compared with male customers in CA. And view this by quarters of the year 1997. The tuple females in WA can be specified as ([Customers].[USA].[WA],[Gender].[F]), likewise males in CA can be specified as ([Customers].[USA].[CA],[Gender].[M]).
3. Put these together to make a request to show [Store Sales] with 1997 quarters on columns, Female WA customers on one row and male CA customers on another row.
SELECT
{[Time].[1997].CHILDREN} ON COLUMNS,
{([Customers].[USA].[WA], [Gender].[F]),
([Customers].[USA].[CA], [Gender].[M])} ON ROWS
FROM Sales
WHERE (Measures.[Store Sales])
All members welcome
Whenever we specify a tuple, we are actually specifying an intersection of all dimensions. The dimensions that we do not specify have their default member included. You might see now why it is useful to have the [All] member as the default member.
There is no [All] member for measures. It does not make sense to aggregate the different measures. For measures, the default member is the first member. Some of your cubes may have dimensions without an [All] level. For example, Time dimensions often do not have [All] levels. Also a dimension with members [Forecast] and [Actual] might not have an [All] member. A default of either [Forecast] or [Actual] is probably more useful.
Review
Finally, let’s do an exercise to review the previous sections.
1. Write a query to report on store Sales; put the USA states on columns and all the combinations of gender and marital status on rows.
Answer:
SELECT
{[Customers].[All Customers].[USA].CHILDREN}
ON COLUMNS,
{([Gender].[M],[Marital Status].[S]),
([Gender].[M],[Marital Status].[M]),
([Gender].[F],[Marital Status].[S]),
([Gender].[F],[Marital Status].[M])} ON ROWS
FROM Sales
WHERE ([Store Sales])
The Use of Functions
At this stage you would be forgiven for thinking that MDX would be difficult for complex reports. Fortunately, it has the richness of many functions. We will introduce one here in our discussion of syntax.
The CROSSJOIN Function
As we said, MDX, like SQL, is a redundant language. That is, the same request, can be written in many different (redundant) ways. The previous query, for example, wanted all the possible join combinations between [gender] and [marital status] on rows. We wrote the MDX with the members hard coded. How about just asking Plato to join all the possible combinations using the CrossJoin function such as:
SELECT
{[Customers].[All Customers].[USA].CHILDREN}
ON COLUMNS,
{CROSSJOIN([Gender].CHILDREN,
[Marital Status].CHILDREN)} ON ROWS
FROM Sales
WHERE ([Store Sales])
Of course if we wanted to have totals by [gender] and [marital status] we could use MEMBERS in place of CHILDREN.
2. Modify the previous query to use Members for both gender and marital status.
Note: Using members for the customers dimension might not be such a good idea as that would involve loading all 10,000 customers in the MDX Sample Application.
SELECT
{[Customers].[All Customers].[USA].CHILDREN}
ON COLUMNS,
{CROSSJOIN([Gender].MEMBERS,
[Marital Status]. MEMBERS)} ON ROWS
FROM Sales
WHERE ([Store Sales])
Congratulations
You have completed this Introduction to MDX. You should now know what MDX is and how to use the language to write basic queries. If you would like to continue to learn more about the richness of this language, go on to Lab 2: MDX Functions and Calculated Members.