With the increasing use of self-service BI tools, data exploration tools, and data discovery tools, end users have more options to analyze data coming directly from any data source. The other day I was asked why people should choose MDX (and constructing a cube) for analysis when there are so many other options. Here are some scenarios where MDX is advantageous, especially in Excel but also in other tools. This is a good starting point for evaluating MDX as an analytic solution.
|Capability||MDX (ODBO/XMLA)||ODBC/JDBC (usually SQL)|
|Large data volumes||Data stays on the server and the server calculates the summary values.||In Excel, the data is imported and stored in the workbook. Excel does the summary calculations, at performance expense.|
|Ease of use for BI client users||Point and click interface to connect to model, get summary values, slice and dice data.||End user must know SQL very well to join tables and perform anything other than basic summary analysis.|
|Reusable data model||One data model can be built and used with many different BI clients (Excel, Tableau, SAP BusinessObjects, Cognos, Microstrategy, etc). MDX calculations in the model can be reused.||The calculation logic must be repeated in every BI client connecting to the server.|
|Analytic capabilities||MDX language has shortcuts for common analytic capabilities such as time-based calculations. Top 10 and other sets can be shown automatically. Can drill to details after viewing summary information.||Optimized for table and row operations, not for analytics. Calculations that are easy in MDX can be hard in SQL.|