SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tools to make creating MDX much easier

For those of you who use MDX, I’m sure you share my opinion that MDX is a lot harder to master than SQL.  One trick I have learned is that instead of trying to write MDX from scratch, use one of the following tools to build the MDX for you:

ProClarity: A great front-end tool for doing ad-hoc reporting on a cube, as well as creating sophisticated, re-usable reports.  Unfortunately, Microsoft purchased the product in 2006 and no longer offers any upgrades.  PerformancePoint is supposed to be the replacement for ProClarity, but it’s not all there yet (a future blog will discuss this more).  Anyway, many companies still use this tool.  Think of it as being similar to Excel, but it’s easier to use and has more features.  The one cool feature it has it that after you create a report, there is a menu option called “View MDX Editor” that will show you the underlining MDX.  Instead of typing out a MDX statement from scratch, I will frequently use ProClarity to build the results I want, then click “View MDX Editor” and copy and paste the MDX to where I need it.  If you don’t already have ProClarity installed, the only way to get a copy now is through MSDN.  I have had power users who would use ProClarity to build a calculation to their specifications, then I would have them send me the MDX so I can add the calculation directly into the cube.  That saves me the time of having to try and understand the calculation they want and then write it in MDX.  Instead, the user does all the work.

OLAP PivotTable Extensions: This is an Excel 2007 and Excel 2010 add-in which extends the functionality of
PivotTables on Analysis Services cubes.  One of the features it adds is the ability to view the PivotTable MDX.  So it works in the same manner as ProClarity: build the report to your specifications, then view the MDX.  This option will be the better one if you don’t have ProClarity and/or your users are very comfortable with using PivotTables in Excel.

SSRS – If you use the SSAS designer within SSRS you can switch from design mode to see the generated MDX statement by clicking on the Design Mode icon in the toolbar and choosing Query mode.

Any of these tools will make your life much easier when it comes to having to create MDX statements.

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


No comments.

Leave a Comment

Please register or log in to leave a comment.