The Baker’s Dozen: 13 Tips for Querying OLAP Databases with MDX MDX is just like Transact-SQL, right? MDX is English-like and therefore easy to learn, right? Different software tools offer MDX generators and therefore I don’t need to learn MDX, right? MDX isn’t that popular, right? Well, just like the punch-line of the old Hertz car rental commercial, “Not exactly.” If your organization uses OLAP databases, you can add great value by knowing how to query OLAP databases using MDX queries. This article will cover common OLAP query requirements and MDX code solutions. What Is MDX and Why Should I Care? MDX stands for Multidimensional Expressions. You use it to query OLAP databases. In a nutshell, MDX is to OLAP databases as SQL queries are to relational databases. So OK-what are OLAP databases? OLAP stands for Online Analytical Processing. OLAP databases primarily consist of OLAP cubes, which store facts (i.e., “measures” such as sales, returns, etc.) and dimensions/dimension hierarchies. An OLAP database is often an aggregation of a relational database; as a result, you can write MDX queries to retrieve key calculations that measure company performance, often with less code than standard SQL. | " |
If you want to RANK a result set based on an order, first create an ordered set and then create a calculated member that applies the RANK function against the ordered set. This will usually provide maximum performance.
| " |
Microsoft created MDX in the late 1990’s. One of the architects for Microsoft Analysis Services, Mosha Pasumansky, invented the MDX language. (See the end of this article for recommended references.) Other vendors have since implemented MDX, such as Essbase by Hyperion Solutions (now owned by Oracle). However, Microsoft continues to add MDX extensions to new versions of Analysis Services. Because of the nature of OLAP databases, “power-users” can often write MDX code to retrieve data in far fewer lines of code than would be required using SQL. This is a segue into the role that OLAP databases and MDX play in the world of business intelligence. Over the last few years, Microsoft has made serious advancements in the areas of business intelligence and OLAP databases. Each year, more and more companies use BI and OLAP tools that support MDX. Here are some examples where MDX comes into play: - Creating Key Performance Indicators (KPIs).
- Building SQL Server Reporting Services reports against OLAP databases.
- Designing custom dashboard output in PerformancePoint Server (for SharePoint deployment).
If you are a SQL developer who is new to MDX, you may look at MDX syntax and conclude that the syntax is very similar. As it turns out, SQL and MDX are very different. Many tools that use MDX will offer designers that generate basic MDX syntax; however, you may still need to modify or customize the generated syntax to address specific requirements. What’s On the Menu? Recently, a CoDe Magazine subscriber approached me at a community event and asked me why I always include thirteen tips. I smiled and told him about the famous Saint Nicholas tale of the legendary Van Amsterdam Bakery. (You can read the full story of the Baker’s Dozen at http://www.aaronshep.com/rt/RTE09.html). The story symbolizes the providence of giving as much as you can. | " |
Use FILTER inside a CROSSJOIN-not the other way around.
| " |
So, here are the 13 tips on the menu for this issue: - Some basic MDX queries to learn MDX syntax.
- Sorting result sets with ORDER.
- Using WITH SET and WITH MEMBER to create sets and calculated members.
- Filtering result sets with WHERE and FILTER.
- Assigning a sequential ranking number to result sets with RANK.
- Retrieving TOP N results using TOPCOUNT, TOPSUM, and TOPPERCENT.
- Combining complex SET definitions with GENERATE.
- Using the HEAD and TAIL functions when you need to retrieve the top or bottom data from a result set.
- Prioritizing calculations using SOLVE_ORDER.
- Retrieve data by time period using a variety of period functions, such as ParallelPeriod and LastPeriod.
- Retrieving data in a hierarchy using DESCENDANT.
- Using the LOOKUP function to retrieve data from multiple OLAP cubes.
- The Baker’s Dozen Spotlight: Incorporating an MDX query into a SQL Server Reporting Services report, using parameters.
Before I begin, I want to make a special thanks to Mr. Deepak Gupta. Deepak is a SQL Server MVP who monitors many online forums, including the Microsoft public newsgroups. In writing this article, I needed to research some material and post some questions online, and Deepak was able to answer my questions. Deepak, you really know your stuff! The Data Source The code for this article uses the OLAP database for AdventureWorks, as shown in Figure 1. If you don’t have the AdventureWorks BI project you can download it from http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004. The AdventureWorks BI project contains many common attributes for an OLAP database. 
Figure 1: The AdventureWorks Cube.Tip 1: “MDX Looks Like T-SQL, Tastes Like T-SQL-but…” Here are some starter queries to help you get a feel for MDX. This first query retrieves sales amount by Customer State. Note the clauses ON COLUMNS, ON ROWS, and FROM [CubeName]. SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
[Customer].[State-Province].CHILDREN ON ROWS
FROM [Adventure Works]
Either from this MDX query, or from the next set of queries, you’ll discover some immediate differences between MDX and SQL: - You explicitly state the COLUMNS and ROWS that you want for the result set.
- You don’t issue JOIN statements.
- You will usually query from one OLAP cube. In rare instances, you may need to retrieve data from a second cube. Tip 13 covers this with the LOOKUPCUBE function.
When you view the results of the first MDX query, you’ll also notice that the result set contains a row for every state, even those with NULL results. You can use the NON EMPTY statement to filter out NULL results. -- Filter out NULL results
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY
[Customer].[State-Province].CHILDREN ON ROWS
FROM [Adventure Works]
Of course, many MDX queries will need to return multiple measures, broken out by multiple dimensions. To return sales and freight by Product Subcategory within state, you’ll need to wrap curly braces around the column names, and parentheses around the dimensions: -- Use {} to enclose multiple measures
-- Use () to enclose multiple dimensions
SELECT {
[Measures].[Internet Sales Amount],
[Measures].[Internet Freight Cost] } ON COLUMNS,
NON EMPTY
( [Customer].[State-Province].CHILDREN,
[Product].[Subcategory].CHILDREN ) ON ROWS
FROM [Adventure Works]
Tip 2: Can I Take Your “Order”? Most results sets need to be sorted in a particular order. The MDX equivalent of the SQL ORDER BY statement is the ORDER function. The ORDER function receives three parameters: the set definition to be sorted (i.e., show States and Subcategories down the left), the data to sort on (Sales Amount), and whether to sort in ascending or descending sequence. -- Use ORDER (will sort within customer)
SELECT [Measures].[Internet Sales Amount]
ON COLUMNS,
NON EMPTY
ORDER(
( [Customer].[State-Province].CHILDREN,
[Product].[Subcategory].CHILDREN ),
[Internet Sales Amount],DESC)
ON ROWS
FROM [Adventure Works]
This MDX query produces the following results: -- Result set is ordered by
-- Category sales within State
Alabama Tires and Tubes $37.29
Alberta Road Bikes $14,022.43
Alberta Touring Bikes $5,510.49
Alberta Mountain Bikes $2,294.99
Alberta Bike Racks $240.00
Arizona Mountain Bikes $2,071.42
Arizona Tires and Tubes $32.60
Notice one thing: The results are sorted by sales descending, within the state. In other words, the sort “respected” the hierarchy. However, you may want to rank all state/subcategories combinations, regardless of the hierarchy-in other words, you may want to “break” the hierarchy. MDX provides the BDESC (or BASC, for ascending), if you want the sort to break the hierarchy. | & | | 
By: Kevin S Goff Kevin S. Goff, a Microsoft SQL Server MVP, has been writing “The Baker’s Dozen” productivity series in CoDe Magazine since 2004. Kevin hosts a weekly webcast on SQL Server and Business Intelligence topics, at http://www.BDBIRadio.com.
Kevin has been an applications developer, architect, and technology mentor since 1987. Kevin has worked in many lines of business, including insurance, manufacturing, health care, consumer packaged goods, accounting and finance, advertising, and many others. Along the way, Kevin has won several awards in both the public and private sector for applications development. Additionally, Kevin has taught SQL Server, Business Intelligence, and Data Warehousing classes to hundreds of students over a period of five years and has built numerous custom courseware modules along the way.
Kevin has authored one book on reporting development and contributed chapters on MDX programming to a second book. He is currently developing a set of commercial video training courses for SQL Server and BI topics. He has been a SQL Server MVP since 2010 and was previously a .NET/C# MVP from 2005 to 2009.
Kevin is a frequent speaker at User Group and other community events (SQL Saturday, SharePoint Saturday, Code Camp) in the Mid-Atlantic region, and speaks occasionally at conferences. In 2012 Kevin worked with Microsoft TechNet Radio on a 13 week webcast series on new features in SQL Server 2012.
For more information, check out Kevin’s main site at http://www.KevinSGoff.net, or email him at kgoff@kevinsgoff.net
kgoff@commongroundsolutions.net | Fast Facts | | MDX is to OLAP databases as SQL queries are to relational databases. You use MDX to retrieve data from OLAP databases, and to define Key Performance Indicators (KPI) in SQL Server 2005 Analysis Services. As Microsoft continues to add new business intelligence functionality to SQL Server, MDX will continue to grow as an important part of the technology. | |
|