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.