Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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.


No comments.

Leave a Comment

Please register or log in to leave a comment.