Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
MDX Queries
Discussion of data warehousing and business intelligence wouldn’t be complete without a brief overview of MDX queries. MDX is a language for querying Analysis Services cubes. Unfortunately, MDX is poorly documented and has complicated grammar. However, if you wish to build your own front-end DW tools or even customize views produced by third-party tools, you must learn MDX. Writing very simple MDX queries is easy; in fact, such queries resemble SQL to a degree. The MDX shown in Figure 6 returns 1998 sales by country.
SELECT {Customer.[customer country] .members} on rows,
{measures.[total sale] } on columns
FROM sales
WHERE ([time].[1998] )
)
Figure 6 Total sales by country in 1998.
However, don’t expect to see much similarity between SQL and MDX once you get past the basic queries. For example, let’s see what it would take to report an average of all sales on [customer city] level if you also wanted to report the total sales within each U.S. city. Generating such a report in Transact-SQL is a 20-second task for any reasonable programmer; however, in MDX it is quite a challenge. A query would look like what is shown in Figure 7.
WITH SET blah AS ’{DESCENDANTS(customer. usa , 1)}’
SET rowset AS ’HIERARCHIZE({ blah , GENERATE( blah , {ANCESTOR(customer.CurrentMember, 1) })})’
MEMBER measures. average AS ’avg(
INTERSECT( blah , DESCENDANTS(customer.CurrentMember, customer. customer city )), measures. total sale ) ’
SELECT {measures. average } ON COLUMNS,
{ rowset } ON ROWS
FROM sales
)
Figure 7 Average sales in the US and total sales in each city.
I’m not an opponent of MDX; on the contrary, I believe MDX is an extremely powerful language, but due to lack of good documentation and a rather limited supply of MDX books it is difficult to master. Indeed, there are very few programmers who can write advanced MDX. The good news is that many front-end tools (including Excel and ProClarity) will write MDX for you. The not-so-good news is that MDX written by such tools isn’t always what you need on your reports. In a nutshell, if your business users would like the functionality that isn’t supported by tools such as Cognos or ProClarity, they should expect to spend top dollar on an MDX resource (and good luck finding a good one).