Home > MDX > Is SSAS Too Lenient on MDX Syntax?

Is SSAS Too Lenient on MDX Syntax?

I started my career in IT as a Visual Basic application programmer. VB had the wonderful/horrible data type called a Variant. Variant data types were great in that you could store anything in a Variant. Variants were horrible because you could store anything as a Variant. Because you could store anything in a Variant, you never knew what you had. As I matured as a programmer, I grew to hate the Variant data type. I grew to hate scripting. I grew to love strongly typed languages. Applications written in strongly-typed languages are much easier to maintain, much easier to understand, and are much less likely to incur run-time errors that would have been discovered when the application was initially compiled.

Well, you may be asking yourself, what does this have to do with MDX? When it comes to interpreting and understanding an MDX statement, it just seems all functions and parameters don’t care much what you pass them. Everything’s a Variant and the parser will do it’s best job at artificial intelligence to interpret what you want it to return. And it just seems to me that building the parser in this way makes MDX much harder to understand rather than easier to construct.

For example, there was a recent post on the SSAS forum that inspired me to write this blog. Not so much because of the blog post itself. But because of the root cause of the problem. The issue posted was why was the following query acceptable,

SELECT    {
        [Measures].[Internet Sales Amount]
    } ON COLUMNS,
    {
        [Customer].[Customer Geography].[City].&[Berkshire]&[ENG]
        * [Product].[Product Categories].[Category].Members
        * [Date].[Calendar].[Calendar Year].[CY 2008]
    } ON ROWS
FROM    [Adventure Works]

but when you switch the [Product].[Product Categories].[Category].Members and [Date].[Calendar].[Calendar Year].[CY 2008] as below

SELECT    {
        [Measures].[Internet Sales Amount]
    } ON COLUMNS,
    {
        [Customer].[Customer Geography].[City].&[Berkshire]&[ENG]
        * [Date].[Calendar].[Calendar Year].[CY 2008]
        * [Product].[Product Categories].[Category].Members
    } ON ROWS
FROM    [Adventure Works]
execution of the query results in an the following error: Query (5, 3) The * function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

Why would should a small change make such a big difference? The question that probably should be asked is not so much why the second construct generates an error but more why the first construct does not. The CROSSJOIN function takes two sets as parameters. In the first construct, the first two parameters to the CROSSJOIN function are a member, [Customer].[Customer Geography].[City].&[Berkshire]&[ENG], and a set [Product].[Product Categories].[Category].Members. In this case, the parser decides since one of the parameters is a set, you really meant to execute a Crossjoin so it implicitly converts the member passed as the initial parameter to a set. In the second construct, the parser sees that both parameters passed to the Crossjoin are Members and neither is a set. In this case, it chokes like a cat with a hairball. If the first parameter is fully qualified as a set, { [Customer].[Customer Geography].[City].&[Berkshire]&[ENG] }, or the second parameter is qualified as a set, { [Date].[Calendar].[Calendar Year].[CY 2008] }, then the error is resolved.

Just seems to me is that when a function requires a set as a parameter, you should have to qualify the parameter as a set. When the function requires a tuple, you need to qualify it as a tuple. And when you need to pass a member, none of this passing a hierarchy and the default member is assumed. Just my opinion but these features in the language make MDX less tractable rather than more.

About these ads
Categories: MDX
  1. October 4, 2011 at 3:35 AM

    I completely agree with you here… I’ve always thought that behaviour like this made MDX harder to learn.

  2. April 15, 2013 at 10:25 PM

    This site was… how do I say it? Relevant!! Finally I’ve found something that helped me. Appreciate it!

  3. April 30, 2013 at 4:58 AM

    Nice post. I learn something new and challenging on websites I stumbleupon on a daily basis.
    It will always be helpful to read through content from
    other writers and use something from other web
    sites.

  4. May 27, 2013 at 5:26 AM

    Woah! I’m really enjoying the template/theme of this site. It’s
    simple, yet effective. A lot of times it’s very difficult to get that “perfect balance” between user friendliness and appearance. I must say that you’ve done a superb job
    with this. Additionally, the blog loads very fast for me
    on Chrome. Exceptional Blog!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: