devxlogo

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

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

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.

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), commands if you want the sort to break the hierarchy.

Tip 3: To Sir, “With” Love
One of my favorite movies is “To Sir, With Love”. Well, one of my favorite MDX language features also begins with WITH.

The first few MDX queries I discussed in this article retrieved existing measures. However, many queries need to return calculations based on existing measures (for example, per unit calculations, percentages, etc.) You do this in standard SQL syntax with something like (Sales / Units AS SalesPerUnit). In MDX, you do this by creating a calculated member, using the WITH MEMBER statement, and then by using the calculated member in the query. Note that you can format the display of the calculated member with the FORMAT_STRING statement:

   WITH MEMBER [Measures].[GrossProfitPerUnit] AS     [Measures].[Internet Gross Profit] /        [Measures].[Internet Order Quantity],   FORMAT_STRING = '$0.00'  -- Result set   SELECT {      [Measures].[Internet Gross Profit],      [Measures].[Internet Order Quantity],      [Measures].[GrossProfitPerUnit] ON COLUMNS,   NON EMPTY      ([Customer].[State-Province].CHILDREN,       [Product].[Subcategory].CHILDREN)   ON ROWS   FROM [Adventure Works]

If you need to create multiple calculated members, you can use the following syntax:

   WITH MEMBER       [Measures].[GrossProfitPerUnit] AS      [Measures].[Internet Gross Profit] /         [Measures].[Internet Order Quantity],   FORMAT_STRING = '$0.00'   MEMBER       [Measures].[NetProfitPerUnit] AS      [Measures].[Internet Sales Amount] /         [Measures].[Internet Order Quantity],   FORMAT_STRING = '$0.00'   SELECT…

You can also use the WITH statement to build a customer SET definition. A SET is essentially a collection (usually an ordered collection) of members (i.e., TUPLES) from one or more hierarchies. The code below defines a SET called OrderedSales?you can abstract out the Order logic into the SET definition, and then refer to the SET in the SELECT statement:

   WITH SET [OrderedSales] AS   ORDER(        ([Customer].[State-Province].CHILDREN,         [Product].[Subcategory].CHILDREN ),         [Internet Sales Amount],BDESC)   SELECT [Measures].[Internet Sales Amount]   ON COLUMNS,   NON EMPTY      [OrderedSales] on rows   FROM [Adventure Works]   

Many MDX queries use calculated members and sets together. Suppose you want to sort on a calculated member. You would create the calculated member first and then create a SET with an ORDER function that uses the calculated member:

   WITH MEMBER       [Measures].[GrossProfitPerUnit] AS      [Measures].[Internet Gross Profit] /         [Measures].[Internet Order Quantity],   FORMAT_STRING = '$0.00'   SET [OrderedSales] AS   ORDER(        ([Customer].[State-Province].CHILDREN,         [Product].[Subcategory].CHILDREN ),         [GrossProfitPerUnit],BDESC)   SELECT {[Measures].[Internet Sales Amount],           [GrossProfitPerUnit]}     ON COLUMNS,   NON EMPTY  [OrderedSales] on rows   FROM [Adventure Works] 

Tip 4: “Filter, Wherefore Art Thou?”
So how do you use MDX to filter based on specific conditions? For example, you may want to filter on all products where the gross profit per unit is greater than $500.00, where the product contains the word ‘BIKE’ in the description.

Use FILTER inside a CROSSJOIN?not the other way around.

MDX provides a FILTER function that takes on two parameters: the SET definition, and the filter expression. Here is an example:

   SET [FilteredOrderedSales] AS FILTER(   ORDER(        ([Customer].[State-Province].CHILDREN,         [Product].[Subcategory].CHILDREN ),         [GrossProfitPerUnit],BDESC),         [GrossProfitPerUnit] > 500   AND INSTR(         [Product].[SubCategory].            CURRENTMEMBER.Name,'BIKE') > 0)

Notice that the code uses the INSTR function and must evaluate each member using the CurrentMember.Name convention.

Tip 5: Name, “RANK”, and Serial Number
SQL Server 2005 provides a terrific new function to RANK result sets (i.e., the new ROW_NUMBER OVER syntax). MDX provides functionality to RANK result sets as well, with the RANK function.

Suppose you want to rank sales by product, according the sales amount descending (so that the top-selling product is ranked #1, etc.) In the simplest form, the RANK function takes on two parameters: which member you are ranking on (SubCategory.CurrentMember) and the ordered set against which each subcategory CurrentMember is evaluated:

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.
   WITH SET [SalesRank] AS   ORDER(       [Product].[Subcategory].CHILDREN ,      [Measures].[Internet Sales Amount], BDESC )   MEMBER [Measures].[SalesRank] AS       RANK([Product].[SubCategory].CurrentMember,          [SalesRank])   SELECT       {[Measures].[SalesRank],        [Measures].[Internet Sales Amount]} on 0,       [SalesRank] on 1   FROM [Adventure Works] 

That covers a basic example. But suppose you want to rank product sales within customers? Or suppose you want to rank product sales across all products and customers? Tip 7 will cover this in more detail.

Tip 6: Can You “TOP” This?
You can easily perform the equivalent of a SELECT TOP 10 by using the TOPCOUNT function. TOPCOUNT takes three parameters: the set definition, the number, and the measure on which to base the top order logic:

   -- Get the top 10 states based on sales   SELECT        [Measures].[Internet Sales Amount] on 0,      TOPCOUNT( [Customer].[State-Province].CHILDREN,      10,[Internet Sales Amount]) ON 1   FROM [Adventure Works]

If you want to retrieve the states that make up the top ten percent of sales, you can use the TOPPERCENT function:

   -- Get the States that make up    -- the top 10% of total sales   SELECT       [Measures].[Internet Sales Amount] on 0,     TOPERCENT( [Customer].[State-Province].CHILDREN,     10,[Internet Sales Amount]) ON 1   FROM [Adventure Works]

Finally, if you want to retrieve the states that make up the top ten million dollars in sales, you can use the TOPSUM function:

   -- Get the States that represent    -- the top 10 million dollars   SELECT        [Measures].[Internet Sales Amount] on 0,      TOPSUM( [Customer].[State-Province].CHILDREN,      10000000,[Internet Sales Amount]) ON 1   FROM [Adventure Works]

Tip 7: Never Too Late to “Generate”
One of the more complicated MDX functions is Generate. You will usually use it when you need to combine sets of data. For instance, suppose you want to retrieve the top five states by sales, and within each of those ten states, you want to know the top five product categories. Here is the full query you would use:

   SELECT [Measures].[Internet Sales Amount]    ON COLUMNS,     Generate (         TOPCOUNT (          [Customer].[State-Province].children,5 ,          [Internet Sales Amount]),           ({[Customer].[State-Province].CurrentMember},            Topcount([Product].[SubCategory].children,10,           [Internet Sales Amount] )),ALL ) on rows   FROM [Adventure Works]

You may be saying, “Ouch! That looks nasty!” Well, if you break it down into smaller pieces, you’ll see that it really isn’t that bad.

First, you need to establish the top five states by sales, which you learned how to do back in Tip 6:

    TOPCOUNT (       [Customer].[State-Province].children,5,       [Internet Sales Amount]), 

That command will essentially return one set, with the top five states. Your MDX query needs to take each state and perform a second query for the top ten products for each state:

     ({ [Customer].[State-Province].CurrentMember},        Topcount([Product].[SubCategory].children, 10,        [Internet Sales Amount] ))

The second query uses the CurrentMember of the State dimension and “applies” it to the second TOPCOUNT call to retrieve the top five products for each specific state. Note that by wrapping parenthesis around the entire code block, you are creating a second set of results.

If your query requirements involve combining multiple sets, start thinking about using the Generate function.

So you have a set of five states, and a second set that will return ten products for each state CurrentMember. You finally combine them with the Generate function, which will return the final result of each relevant state/product combination.

One final thing: the order of the result set will be the states with the highest aggregate sales amount, and products by sales amount within each state. That may be fine, or you may want the result set in the natural or original hierarchical order (state name and product subcategory name). You may be tempted to insert some ORDER statements to force the order you wish. Fortunately, there is a much easier way! You can use the Hierarchize function to force the results into the original hierarchical order. Simply by wrapping the entire ON ROW contents inside Hierarchize, you can force the result set order:

   select [Measures].[Internet Sales Amount]    ON columns,      Hierarchize (          Generate (             TOPCOUNT (...

Are you on overload yet? Well I hope not, because there’s one more important use of Generate that further reinforces the value of this function. Back in Tip 5, I presented a basic RANK function. But you may want to know how to rank sales by state/product category, independent of the hierarchy. Once again, you’ll use the Generate function in the same way you used it to perform multiple TOPCOUNT queries. The code below provides a full example. Once again, breaking down the query will help:

   -- First, create a Set    WITH SET [SalesRankSet] AS      GENERATE( [Customer].[State-Province].Children,   ORDER (      NONEMPTY(         ([Customer].[State-Province].CurrentMember,          [Product].[Subcategory].Children) ,          { [Measures].[Internet Sales Amount] } ),          [Measures].[Internet Sales Amount], BDESC ))   -- Next, create a calculated member    MEMBER [Measures].[CategoryRank] AS      RANK(          ([Customer].[State-Province].CurrentMember,           [Product].[Subcategory].CurrentMember),           EXISTS(              [SalesRankSet],               {[Customer].[State-Province].CurrentMember}) )   SELECT      {[Measures].[Internet Sales Amount],        [Measures].[CategoryRank]} ON 0,       [SalesRankSet]  ON 1   FROM [Adventure Works] 

First, you’ll create a SET by using Generate to combine States with an ordered set of State/Product Subcategory combinations (sorted by dollar amount, descending):

   WITH SET [SalesRankSet] AS      GENERATE( [Customer].[State-Province].Children,   ORDER (      NONEMPTY(        ([Customer].[State-Province].CurrentMember,        [Product].[Subcategory].Children) ,        { [Measures].[Internet Sales Amount]}),        [Measures].[Internet Sales Amount], BDESC ))

Next, you’ll create a calculated member to determine the ranking of each state/product subcategory combination. You’ll call the RANK function and pass two parameters. The first parameter will be each CurrentMember combination of State and SubCategory (placed in parenthesis to form a single expression). The second parameter will be the SET returned from the MDX Exists function, which will return a set of data from the SalesRankSet from above, based on each state CurrentMember:

   MEMBER [Measures].[CategoryRank] AS     RANK(      ( [Customer].[State-Province].CurrentMember,        [Product].[Subcategory].CurrentMember),     EXISTS([SalesRankSet],       {[Customer].[State-Province].CurrentMember}))

Finally, you can simply reference the CategoryRank member and the SalesRankSet set:

   SELECT      {[Measures].[Internet Sales Amount],    [Measures].[CategoryRank]} ON 0,      [SalesRankSet]  ON 1   FROM [Adventure Works] 

Tip 8: “Heads or Tails”
While not as common, sometimes you may want to retrieve the first row(s), or last row(s) from an ordered set. For example, in a list of top ten states by Sales, you may want to retrieve the first three. You can use the HEAD function, as follows:

   -- From the list of top ten states,    -- grab the first three   SELECT        [Measures].[Internet Sales Amount] on 0,      HEAD(        TOPCOUNT( [Customer].[State-Province].CHILDREN,        10,[Internet Sales Amount]),3) ON 1   FROM [Adventure Works]

If you want to retrieve the ninth and tenth rows from the same list of top ten states by Sales, you can use the TAIL function, as follows:

   -- From the list of top 10 states, grab the 10th   SELECT        [Measures].[Internet Sales Amount] on 0,      TAIL(        TOPCOUNT( [Customer].[State-Province].CHILDREN,        10,[Internet Sales Amount]),10) ON 1   FROM [Adventure Works]

Both functions take on two parameters: the set to read and the number of rows to ultimately retrieve.

Tip 9: Time, Time, Time?Functions to Deal with Time Periods
Suppose you have a report that reads an OLAP database and reports on the last four quarters of available data. You certainly don’t want to hard-wire the date range into the query. But how do you write an MDX query to determine the last four quarters of available data, at any one time?

You can specify the LastChild member of a Quarterly date dimension to retrieve the last date, and then use the LastPeriods function to retrieve the last four (or eight, etc.) quarters going backwards from the LastChild:

   SELECT [Measures].[Internet Sales Amount]   ON COLUMNS,      { LastPeriods (         4, [Date].[Calendar Quarter].Lastchild )}    ON ROWS   FROM [adventure works]

If you want to retrieve data for the last four months, going back from February 2004:

   SELECT [Measures].[Internet Sales Amount]     ON COLUMNS,      { LastPeriods ( 4,         [Date].[Calendar].[Month].[February 2004]  )}    ON ROWS   FROM [adventure works]

Finally, if you want to retrieve data from a prior parallel period (for example, same month from a year ago, same quarter from a year ago), you can use the ParallelPeriod function:

   -- Retrieve sales for the month that is    -- one year prior to Feb 2004     -- (would be Feb 2003)    SELECT [Measures].[Internet Sales Amount]   ON COLUMNS,     {ParallelPeriod (       [Date].[Calendar].[Month],     12,     [Date].[Calendar].[Month].[February 2004]  )}   ON ROWS   FROM [Adventure Works]

Note that ParallelPeriod receives three parameters: the level expression (which could be a month, a quarter, etc.), the number of periods to go backwards in time (12 months, 4 quarters, etc.), and then the starting point. ParallelPeriod is particularly valuable for trend analysis.

Tip 10: Maintaining Order with SOLVE_ORDER
Up until now, you haven’t needed to tell MDX queries to calculate results in any specific sequence (except when you explicitly defined a set, and then created a calculated member that accessed the set). But there may be times where you are calculating variances in both dollar amounts and percentages, and therefore need to calculate the dollar variances before calculating percent variances.

You can use the MDX SOLVE_ORDER statement to specify the order in which calculations occur. For example, suppose you need to display Sales Amount, Freight Cost, and Freight Per Unit, for the third quarter and fourth quarter, and then for the difference between the two quarters. You’ll need to calculate the Freight Per Unit first for each of the two quarters first, before calculating the Freight Per Unit variance between the two quarters:

   WITH MEMBER [Measures].[FreightPerUnit] AS       [Measures].[Internet Freight Cost] /        [Measures].[Internet Order Quantity],        FORMAT_STRING = '$0.00', SOLVE_ORDER = 0     MEMBER [date].[Fiscal].[Q3 to Q4Growth] AS     [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2004] -      [Date].[Fiscal].[Fiscal Quarter].[Q3 FY 2004],   SOLVE_ORDER = 1   SELECT    {[Internet Sales Amount],[Internet Freight Cost],      [FreightPerUnit] } on columns,     {[Date].[Fiscal].[Fiscal Quarter].[Q3 FY 2004],       [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2004],       [Date].[fiscal].[Q3 to Q4Growth] } on rows   FROM [Adventure Works]

Tip 11: The Family Descendants
MDX contains a DESCENDANTS function that returns some or all descendants of a member at a specific level. You can use this function whenever you need to retrieve (for example) sales data for multiple levels of a specific hierarchy.

Suppose you want to retrieve (in a single result set) sales data for a year, and then for each hierarchical representation of the year (Semester, Quarter, Month, and Day), you can use the DESCENDANTS function as follows:

   SELECT  [measures].[Internet sales amount]    ON COLUMNS,   DESCENDANTS (         [Date].[Calendar].[Calendar Year].[CY 2003],      [Date].[CALENDAR].[DATE] ,   SELF_AND_BEFORE )    ON ROWS   FROM [adventure works]

Note the use of the SELF_AND_BEFORE parameter, which returns all the descendants of the Calendar Year hierarchy. If you don’t specify this second parameter, the query will return data for the lowest level only (i.e., daily sales).

In other usage, suppose you want to retrieve sales data for the Year, Semester, Quarter, and Month (in other words, everything but daily sales). You can use the SELF_AND_BEFORE parameter against the Calendar Month level:

   SELECT  [measures].[Internet sales amount]   ON COLUMNS,   DESCENDANTS (         [Date].[Calendar].[Calendar Year].[CY 2003],      [Date].[cALENDAR].[Month] ,   SELF_AND_BEFORE )    ON ROWS   FROM [adventure works]

Besides SELF_AND_BEFORE, there are other options you can use (such as SELF, AFTER, BEFORE, and more?check SQL Server Books Online for details.

Tip 12: “Look Up, Look Around”
Once in a great while, you may find a situation where you need to query data from multiple cubes. This is a somewhat controversial subject, as some people view this as a design issue with the cubes. However, online questions on this subject indicate that some developers certainly need to be able to do this.

As I stated earlier, you can only specify one cube in the FROM clause. However, MDX provides the LOOKUPCUBE function: you simply construct a query string and pass it as a parameter.

For example, suppose you have a cube of labor data and you’ve constructed a basic MDX query to retrieve hours worked and total labor dollars by quarter. Now you need to add material purchase amount by quarter to the result?and the material purchases belong in a different OLAP cube. Here is a query sample to accomplish this:

   with member [PurchaseAmt] AS        LOOKUPCUBE("Project Materials Cube",       "([Measures].[Purchase Amount],[Fy Qtr].["          + [FY Qtr].CurrentMember.Name + "])" )   -- We need to construct a string that looks   --  exactly like this…    --  ( [Measures].[Purchase Amount] ,    [Fy Qtr].[2005 Q1])   select { [Measures].[HoursWorked],             [Measures].[Total Labor],             [PurchaseAmt]}    on columns,     [Fy Qtr].children on rows   from [Project Labor Cube]

Tip 13: The Baker’s Dozen Spotlight?Using MDX in an SSRS2005 Report
Now that you’ve seen plenty of MDX examples, I’m now going to show an example of how to use MDX in a reporting situation. Tip 7 shows an example of a result set for the Top five sales states, and within those states, the top ten product subcategories sold. Suppose the end user wants to change either or both parameters before seeing the results on a report. How would you accomplish this?

I’ll assume that you already know how to create a new SQL Server Reporting Services 2005 report project, and that you know how to create a data source connection to Analysis Services and to the AdventureWorks DW OLAP database.

Be VERY careful when using design tools that generate MDX code and allow you to edit the results. If you return to design mode, the designer tool will overwrite your custom changes.

The key point here is to incorporate the MDX query into the SSRS2005 DataSet designer, and to add the two parameters (you’ll call them TopStateCount and TopProductCount.). Here are the steps:

  1. Enter the code from Tip 7 into the MDX query editor (in the SSRS DataSet designer), as shown in Figure 2.
  2. Note that in Figure 2, I’ve highlighted the Query Parameters button. Click on that button and add two parameters for the State Count and Product SubCategory Count, and also set default values (see Figure 3).
  3. Go back to the MDX query in Figure 2, and replace the literals of 5 and 10 with @TopStateCount and @TopProductCount, respectively. Then click on the Execute Query button in the toolbar (the icon with the explanation point), and make sure the query executes correctly.
  4. Design the report in the report layout designer (I added a table from the SSRS report toolbox, created a report group on State, dragged and dropped the columns from the report DataSet, and then added a subtotal for sales by state).
  5. Note that once you added the Query Parameters in Step 3, SSRS2005 automatically adds report parameters for you (see Figure 4). You can modify the prompt as needed.
  6. Finally, you can preview the report. Note that SSRS2005 prompts for the two parameters, and then displays the report (see Figure 5).
?
Figure 2. The SSRS2005 MDX Query Editor.
?
Figure 3. Enter MDX Query Parameters for the SSRS report.

?
Figure 4. SSRS automatically creates report parameters.
?
Figure 5. The SSRS report, where the user can set the top count parameters.

Sneak Preview of MDX 2008
By the time you read this, Microsoft SQL Server 2008 (“Katmai”) should be just around the corner. Here are some of the new MDX capabilities in SQL Server 2008:

  • New CREATE KPI and DROP KPI statements that let you move KPI definitions into a common calculation area.
  • Dynamic named sets for improved performance.
  • Ability to change calculated members on the fly.

Resources:
Again, you can find the entire source code for this article on my web site.

You can find the MDX function reference for Microsoft SQL Server 2005 Analysis Services here.

Both Mosha Pasumansky and Chris Webb maintain terrific MDX blogs.

You can also find the answers to many MDX and Analysis Services questions on MSDN at. Another good online forum is the newsgroup microsoft.public.sqlserver.msolap, which you can find on the Microsoft public news server (news.microsoft.com).

Finally, I also recommend two books. “Fast Track to MDX,” by Mark Whitehorn, Robert Zare, and Mosha Pasumansky is a good introductory book. “MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase,” by George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, and Francesco Civardi, is a more intermediate/advanced book with a large amount of sample MDX code.

Coming Up Next, Creating Business Intelligence Dashboards
This article is a prelude to my next article, where I present 13 tips for building business intelligence (BI) dashboards using Microsoft’s newest BI product offering, PerformancePoint Server. I’ll show you how to leverage MDX query techniques from this article so that you can build and customize effective and attractive dashboards using SharePoint, Microsoft Excel Services, and PerformancePoint Server.

Closing Thoughts
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas after the fact? Well, I’m the king of thinking of things afterwards. Fortunately, that’s the type of thing that makes blogs valuable. Check my blog for follow-up tips and notes on Baker’s Dozen articles?and maybe a few additional treats!

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist