Why use MDX?

Question

After 3 months of trying to learn MDX, I can now see why some people refer to it as Mind Destroying eXpressions! In my 20 years of programming (school, university, work), I have successfully learnt some tough languages (Assembler, Eiffel, Prolog, C++) but, for me, none have been as incomprehensible as MDX.

My market reasearch of MDX demand here in the UK (using two very respectable job sites) showed negligible demand for MDX. I have therefore decided not to pour any more time into MDX for our DW/BI solution. I reasoned that the job market confirms my conclusion about the applicablility of MDX. We have been using SQL and "Utility" tables very effectively to add value to the "raw" data in our data warehouse and delivered lots of successful BI to our business.

So, my specific questions are:

1.What will we be missing out on by not using MDX?
2.Even if the SQL is thougher to write (which hasn't been so far), I would like to know for completness what MDX gives you that you can not do in your ETL or Data Warehouse (using SQL)?
3.Is there a tool that helps generate MDX for you? At the moment, I am relying on Excel (connected to our Cube): I create the report in a work book and then use the OLAP PivotTable Extension tool to capture the MDX Excel is sending to SSAS. The only problem is that the MDX output in Management Studio looks completely different to that in Excel.

---

Amir

Answer 1

Hi,

You never see any explicit demand for a MDX-expert but you may see requests for a Analysis Services expert or an open source Pentao expert. There are several other OLAP products that use MDX as the query language.

A few years ago it was much harder to write good TSQL for Time Intellignce like running totals, comparing different months from different years to each other and so on. Coding time intelligence in MDX was much easier than doing it in TSQL.

Today with the latest extensions to TSQL in SQL Server 2012 with the Windows Functions this gap has been reduced. You also have DAX-expressions in Power Pivot and BISM Tabular.

There is no graphical tool that helps you with building the MDX queries but plenty of clients/browsers that abstracts that for you.

BR

Thomas Ivarsson

Answer 2

Thanks Thomas - very useful reply.

Our Business people would riot if we took away their Cubes (i.e. Analysis Services). But, we have been able to do what you refer to as "Time Intelligence" in the (backend) Data Warehouse, using SQL, and expose the value-add data in the Cubes. In other words, and as you say, we have used SQL instead of MDX.

I am not up to speed with SQL Server 2012 but from what you say, and a quick web search, it sounds like BISM is the beginning of the end for MDX (?)

---

Amir

Answer 3

Hi Thomas

Can you please provide the names of clients/browsers that abstract MDX queries?

---

Amir

Answer 4

Hi,

Maybe I was unclear but Excel, like you mentioned is one. Their is no Management Studio add in that I know of that can help you with MDX.

BR

Thomas Ivarsson

Answer 5

Hi Amir,

I think that you should be aware of the Multidimensional Expressions(MDX) is the query language that you use to work with and retrieve multidimensional data in Microsoft SQL Server Analysis Services (SSAS). We can use MDX query to perform the following things:

• Return data to a client application from a SQL Server Analysis Services cube.
• Format query results.
• Perform cube design tasks, including the definition of calculated members, named sets, scoped assignments, and key performance indicators (KPIs).
• Perform administrative tasks, including dimension and cell security.

Here are some video and article for your reference:
Introducing SQL Server 2008 R2 Analysis Services MDX Query Development: http://channel9.msdn.com/Blogs/rdoherty/Demo-Introducing-SQL-Server-2008-R2-Analysis-Services-MDX-Query-Development
SQL Server Analysis Services 2005/2008 Articles: http://www.ssas-info.com/analysis-services-articles

SQL Server 2012 introudce the BI Semantic Model which is a rich, scalable, and flexible model. You can refer to the following architecture:

A good book is Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model for your reference:
http://www.amazon.com/Microsoft-Server-2012-Analysis-Services/dp/0735658188
Microsoft SQL Server 2012 Business Intelligence Semantic Model (BISM): Multidimensional vs Tabular: http://channel9.msdn.com/Events/TechEd/NewZealand/TechEd-New-Zealand-2012/DBI306

Regards,

---

Bin Long

TechNet Community Support

Answer 6

I have the same feeling as Amir. I tried to transfer the analysis and reporting platform in my department to SSAS, but after a few months of learning and effort, I realized that I should not put more time on SSAS.  The reason is that although MDX is very powerful, very few end-user tools well support MDX. You might say Excel or SSRS can use MDX to connect SSAS cubes and present and analyze data, but this support is very limited. Excel can use Pivot Table to manipulate the cube and analyze the data; however if you have a MDX query in hand, it is not so easy to pull your required data from cube(this is ridiculous). If you use SSRS to present the data, the data from MDX will be flattened first and then use SSRS' facilities to group, format, and present.  so why cubes and MDX? You spent a lot of time to develop dimensions and cubes but finally found out there were no advantages. You can't expect end-use to use BIDS or SSMS to run MDX query, right? 

Answer 7

marser2010, I think you have a point with MS own support with its client and report tools.

I think that MS have mainstream solutions that will suit most users quite good but we always have these challenging scenarios that are not covered well by MS today.

There are many client tools on the market that works, in different ways, with SSAS, and that can perhaps cover the special scenarios as well.

Have a look here; http://www.ssas-info.com/analysis-services-client-tools-frontend

There is no single client or report tool that will cover all aspects of advanced end user requirements but with nice thing with SSAS is that you are not tied to Excel or SSRS as front ends.

Thomas Ivarsson

Answer 8

Hi Amir,

I have read most of the replys on this thread and have done allot of research.  I was hoping I could write an MDX query and present that to end users through an Excel Pivot table.  But as far as I can tell you can't.  Not even in Excel 2010.  You can use query or power pivot to return queries, but the result is a falt spreadsheet not a pivot table. 

I was hoping I could write mdx queries that emulate some SSRS reports I developed and present them through Excel to my end users.  The difference between a Pivot table that emaulates a report and an SSRS report being that the end user could modify like a pivot table and then drill in the detail, but I do not think this can be done.

Everything you can do with MDX, can be done much easier using BIDS.  If you want to return data to Excel, VBA and SQL are much easier.

I do not see any point in learning MDX from a developers point of view.  It does nothing for me or those I write reports for.

If I am wrong, please correct me!!!!!!!!!!!

Mike

Answer 9

SSAS is incredibly powerful and valuable when properly designed and deployed.  It is a user-friendly extension of a comprehensive data warehouse program.

In my organization, prior to implementing a full-blown SSAS solution, users in each department would pull data from various systems and integrate them in Access or Excel.  Each department relied on various data queries to build desktop reporting solutions to manage their business.

Now, users can very easily interract with over 100 measures across dozens of dimensions using nothing but Excel, with incredibly fast response times.  Want to see Net Sales by Product Department by Month with Prior Year data and Variance to Prior Year?  It's point and click.  Want to then drill across the same measures and see by Region or Store?  Point and click.  Want to see it by Cashier and Hour?  Point and click.  Want to then filter by Customer Type (Loyalty, Commerial, Non-Loyalty)?  You guessed it, point and click!

This is not about generating reports (though it's very useful for that, as well).  This is about getting the information into the hands of the people who need it in a timely and flexible manner.  Once a properly designed SSAS solution gets embedded in an organization the users will not only embrace it, they will start sounding like Chalton Heston... "From my cold, dead hands..."

Answer 10

The question is not the value of SSAS, It is the value of MDX.  What in your response above, what advantage did you gain from MDX.  All your advantages are about a cube.

Answer 11

See the other thread... MDX is very valuable in the cube design, as well as in report generation.

Answer 12

I have come to the conclusion that if you are presenting cube data via excel, the only reason to learn mdx is createing calculated measures.  All other tasks can be done manually, intuitively and faster and without learning MDX.

If my BI enviorment is SSRS reports (that process in an exceptable time) and Excel pivot tables, there is no reason for mdx except calculated measures.  Which I can figure out with out studying MDX.

Answer 13

Really?  I've found SSAS to be open.  You've got Tableau, Microstrategy, Business Objects (4.1 & above), Cognos, Excel, Oracle OBIEE... That's about 90% of the BI market there...