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.
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.
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.
If your query requirements involve combining multiple sets, start thinking about using the GENERATE function.
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, Where for 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, where the product contains the word ‘BIKE’ in the description.
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.
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.
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.
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 on three parameters: the set definition, the number, and the measure on which the top order logic should be based.
-- 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.
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. Listing 1 provides a full example. Once again, breaking down the query will help.
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 return 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 ParallellPeriod 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 moths, 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 of the 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 of 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.
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:
Sneak Preview of MDX 2008
By the time you read this, Microsoft SQL Server 2008 (“Katmai”) should be just around the corner. Here is a list of some of the new MDX capabilities in SQL Server 2008:
- New CREATE KPI and DROP KPI statements that allow you to move KPI definitions into a common calculation area.
- Dynamic named sets for improved performance.
- Ability to change calculated members on the fly.
Resources:
You can find the MDX function reference for Microsoft SQL Server 2005 Analysis Services at: http://technet.microsoft.com/en-us/library/ms145970.aspx
I recommend two good blogs: Mosha Pasumansky maintains a terrific MDX blog at http://www.sqljunkies.com/weblog/mosha. Chris Webb also has a great blog at http://cwebbbi.spaces.live.com.
You can also find the answers to many MDX and Analysis Services questions on MSDN at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1. Another good online forum is the newsgroup microsoft.public.sqlserver.msolap, which you can find on the Microsoft public news server (news.microsoft.com).
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 (www.TheBakersDozen.net) for follow-up tips and notes on Baker’s Dozen articles…and maybe a few additional treats!
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.
Listing 1: Example of a RANK within a GROUP
-- 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]
