Browse DevX
Sign up for e-mail newsletters from DevX


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

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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]

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