RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX

MDX is a SQL-like query language used to retrieve data from OLAP databases and to define Key Performance Indicators (KPI) in SQL Server 2005 Analysis Services.

DX 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. MDX is to OLAP databases as SQL queries are to relational databases. 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.

One of the architects for Microsoft Analysis Services, Mosha Pasumansky, invented the MDX language in the late 1990's. (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.

If you work with OLAP data, if you build reports with SSRS 2005, or if you need to build dashboards with SharePoint and PerformancePoint Server, or you just want to do serious work in business intelligence (BI) solutions, then you need to make MDX part of your vocabulary.
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 online.) The story symbolizes the providence of giving as much as you can.

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 DESCENDANTS.
  • Using the LOOKUP function to retrieve data from multiple OLAP cubes.
And finally, the Baker's Dozen Spotlight:

  • Incorporating an MDX query into a SQL Server Reporting Services report, using parameters.
Figure 1: The AdventureWorks Cube.
Author's Note: 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. You can download the AdventureWorks BI project if you don't have it already. The AdventureWorks BI project contains many common attributes for an OLAP database. You can find the entire source code for this article on my web site.

Editor's Note: This article was first published in the January/February 2008 issue of CoDe Magazine, and is reprinted here by permission.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date