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]
Previous Page
