May 032010

Recently I delivered a session at local conference organized by Microsoft Croatia – WinDays¹º. The title of the presentation was: “DAX vs MDX vs T-SQL”.

As its name suggests, the presentation is oriented towards SQL and BI developers (for change) instead of Excel power-users and compares DAX calculations with T-SQL and MDX expressions. The idea behind, besides improving my T-SQL skills and learning DAX, was to build a mental model of what DAX functions and their combinations actually do by finding their best analogies in other languages. Since there are more than a hundred DAX functions, I’ve concentrated primarily on those that use relations. On one hand, they are probably the most difficult group of functions in DAX. On another, they should be the closest to SQL and BI developers’ mindset.

If you’re wondering why T-SQL and MDX, the reason is simple. PowerPivot uses in-memory column-oriented relational database technology and was developed by SSAS team (together with Excel team). “Relational” means we have every reason to compare DAX with T-SQL. And since SSAS team was involved, MDX comes naturally. Hereby I wish to emphasize that the comparison shown in the presentation was based on imaginary or potential analogies in T-SQL and MDX and not the real implementations built into PowerPivot engine. In the same manner, the summary highlights showing strengths and weaknesses of each language is based on personal impressions. Regardless of that, I believe the presentation might be of great help for many.

The presentation (translated in English) is published on my blog including all the related files. You can find it here.

Looking forward to your feedback.

  3 Responses to “DAX vs. MDX vs. T-SQL”

  1. Nice slides. Did you make it through all in 50 minutes? Thanks for sharing the content.

  2. Thank you Dan.
    Almost, in 55 :-). It was scheduled as the last one for that day so I guess it wasn’t much of a problem.
    It was supposed to be a level 400 (highest) session (comes as the recommendation with MVP status, regarless of the potential capacity of the audience which btw. counted around 20 this time, throughout, as I’ve been told afterwards), so I envisioned it in the beginning as a fast paced one. And rehearsed if it can fit inside a given timeframe.
    Personally, I don’t like slow sessions, especially if they are not introductory type. We live in a fast-paced world, so why should the sessions we see or deliver be any different than that? If it made you think or you liked what you saw, there’s almost always a blog and a ppt on it so go ahead, watch it again as you would a movie, music video or similar content that runs in a given time frame. Slow content underestimates one’s intelligence depriving him of a wish to repeat the experience.
    PPT is a standard. For a long time now. Too long if you ask me. Designed to be more of a business meeting type of delivery, slow and static. True, you can add animation, incorporate a video or switch to demo, percentage of which is often asked for when applying for a conference. Why? Is it a new KPI for "boringness" of a session? Hopefully Flash-Silverlight/Director-Semblio or similar technologies will replace it in the future, at least partially. That would be my wish (and I can see Microsoft is following the trends :-)). But that’s another story. Let’s get back.
    The problem arose when I realized I can’t return in time and that I’ll have to do it remotely. So much screen flipping (from demo to ppt and back) would be too much in this situation and I’ve decided to put much of my demo in slides which made them grew a lot as you saw. I did have some demo though.
    Additional motif was that I don’t like when a ppt has only a few slides and a lot of demo in scripts that you just can’t experience right unless it comes as a video recording. And although my session was being recorded, both the ppt and the audio were in Croatian (naturally, the official language of the conference unless you’re a foreigner). Knowing it won’t be of any use to a lot of people (unless I make subtitles for it :-)), I’ve decided that whatever is important should go to ppt which I will eventually translate and publish here. Moreover, it’s faster to deliver to a wider audience this way. So, that’s it.

  3. […] The OLAP cube was an invention of the seventies, but not really used until mid-nineties. The demand from management of summary information with short response time made really at that time no other solution possible. You had to foresee  the needs and have most sums calculated in the night shift or on weekends, when the load on the server was moderate. With it came a new programming language – MDX, a rather tough piece to grasp. Still understood by few humans compared to SQL. Do we need MDX any more? Most problem for BI could be used by any. […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>