Solving business problems using Analysis Services
2000, sessions 1 - 3 (Richard Tkachuk and Rob Zare)
MDX (Multi-Dimensional eXpression language) is a Microsoft language that has been described as the OLAP equivalent of
SQL. It is defined as OLE DB for OLAP. The general syntax for a query initially looks hauntingly familiar:
SELECT
{ [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS,
{ [Time].[1997], [Time].[1998] } ON ROWS
FROM Sales
WHERE ( [Store].[USA].[CA] )
Again, just like SQL, MDX can be used for much more than querying, indeed it can be used for:
For example, calculated members make use of the WITH keyword. A calculated member which shows, for example, the value in the Packages measure increased by 17.5%, can be created using:
WITH MEMBER [Measures].[PackagesVAT] AS
'[Measures].[Packages] * 1.175'
MDX for querying is typically generated by a tool - Excel, Proclarity and so on, but, anyone who is interested in solving business problems will benefit from a knowledge of MDX; given its diverse uses. Despite the similarities outlined so far, the two languages differ in one very important way. Hardwired into SQL is the concept that the relative position of the data is immaterial. SQL has no ability to manipulate rows of data in sequence. There is no way for accessing the 'previous' or 'next' row; in other words, SQL can only process data as sets. In direct contrast, many operations in MDX refer explicitly to positional information. For example, to calculate how Sales for the current period compare with Sales from the previous period:
(Time.CurrentMember, Measures.CurrentMember) =
(Time.CurrentMember, Measures.Sales) -
(Time.CurrentMember.PrevMember, Measures.Sales)
(Note
the use of CurrentMember.PrevMember to identify the ‘same’ value from
the previous period.)
There is also a range of time-related functions in MDX which can be used to good effect. For example:
Sum(YTD(Time.CurrentMember), Sales)
YTD (Year To Date) neatly answers the question “What have Sales been since the beginning of the year?” Finally the concepts of parents, children, descendants and so
on were introduced. These simply refer to hierarchy positioning so that, in a normal time hierarchy for example, the expression:
Time.[2000].FirstChild
returns Q1, whereas
Time.[2000].Children
returns Q1, Q2, Q3 and Q4.
---
Next session
|
Session 1
Session 2
Session 3
Performance and scalability tips
|
