Format MDX & DAX

Format MDX & DAX

  • Comments 16

Have a MDX query generated by Excel or any other client and want to understand what it does?  Try http://formatmdx.azurewebsites.net - a simple tool that formats MDX & DAX queries. There are few options that affect result of formatting.

Feel free to drop a comment to this post with suggestions or bugs or anything else.

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • Not bad, but I prefer Mosha's online tool at this stage : http://mdx.mosha.com

    I'm sure it will evolve nicely though.

    Regards,

    J

  • Hi Jason,

    Nick's tool, however, does about the same formatting (with a few custom options), plus it lets you format DAX as well...

    Would be good to hear, though, what features do you like more from the old one (online version of MDX Studio)

  • Hi Cristian (and Nick),

    I agree with Jason:

    - indentation is only partially implemented (only for members and sets in the WITH part and subselect but not for the other parts of the query - slicer, axis, ...)

    - no support for double qoutes (" instead of ' when defining a string)

    - you preserve the WITH part with nothing in it (test "WITH SELECT FROM d", although this is not exactly the formatting issue but since you also check other syntaxes, maybe you should fix this one too because it makes a lot sense to have a format and syntax checker in one tool and you already went half that way)

    - ...

    On the positive side, it supports DAX which is great.

    However, there's also place for improvements:

    - brackets are placed in separate lines (use "sum(a)" for example, doesn't look nice when sliced in three lines)

    - indentation of function and their arguments in general (I'd prefer you take advantage of the parsing tree (you do use it, right?) and wrap nodes properly (in new lines) because DAX is all about cascading expressions -> when not formatted nicely it's hard to read -> see how bad this turns out:

    "IF (a,

           CALCULATE (SUM (b), c = d)

         - CALCULATE (SUM (b), c = -d),

       BLANK ()

    )", the node I'm referring to is the - operator. This might be helpful for MDX too, haven't tested yet.

    )

    General advice - use higher contrast between foreground and the background, such as a white background or similar. Editors are usually white, not grey (although I understand why you want a different background color).

    Here's another free tip - provide the "Like"/"Don't like" buttons (with optional comment line) and keep the history of the queries, both entered and formatted. Save the results in the QueryLog table (:-)) with other useful options, apply analytics on it and bingo - you'll have a UsageBasedFormatRecommender. That way you'll know which features of the tool you should tune in the future. Not everyone will give you feedback, but they might click on a button. And this will give you a sense of direction what you should do about this tool to make it better ;-).

    From the user perspective, with a lot more config options (and perhaps a cookie to store them) this tool could become great.

    Hope it helps,

    Tomislav

  • Thanks for the feedback, Tomislav. I will respond to some of your comments, for the others I need to see examples, so I will be working on Line/Not like buttons.

    As for strings defined in double quotes - they are supported, but colored yellow, so might not be visible.  I will change their color as well as the background color.

    'brackets are placed in separate lines ' - feature requested by some developers who debug query by commenting it's parts, I am not how it is useful for other people

    As for IF - it is a bug.  IF is highlighted blue, it means it is recognized as a keyword, however this is a function. The code does indentation for functions, but does not do them for keywords.  Calculate() has the same problem.

    I plan to have the update to be ready by the end of the next week.

  • I fixed the problems mentioned above, except Like functionality.  Will do Like later.

  • Would be nice if this would work with regular Excel formulas as well.

  • Hello,

    You link seems to be dead.

    Could you please forward the correct one, I'll be realy interested to check this out.

    Thanks!

  • Link dead - would love to take a look..

  • The link is dead. Please fix it or provide an alternate link.

    Thanks!

  • It is back to live now.  Sorry that it took so long to bring it back.

  • >Would be nice if this would work with regular Excel formulas as well.

    Currently I am using parser that works only for MDX and DAX.  There is no easy way to include Excel formulas.  The best way it to start another project from scratch, specify separate grammars for MDX, DAX and Excel and do formatting on the top of this.

  • Hi,

    Your link is dead again. Could you please share the active link.

  • I don't suppose you can open source this? I would like to share it with our users, but its availability is too unreliable. If you open source it, we can host it on our corporate network.

  • The site is moved to more reliable hosting.  New address is: formatmdx.azurewebsites.net

  • Nick, thanks a bunch for providing a more reliable server.

Page 1 of 2 (16 items) 12