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/2010/2013 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.
We can also add mosha pasumansky mdx Tool for ease of MDX !! 🙂
Good point Vishal. I mention in another post how I use Mosha’s tool to clean up and validate my MDX. A must have!
Great !! Its good to read that post Too !!
Hi James,
I am new to MDX. I hope you can help me with a calculation of [Prev Month MTD]
My query is like below. It works fine for most time, but It does not work when the current date is, say, 12/31/2011 because there is not Novermber 31. Should the ParallelPeriod function handle it gracefully?
I am using SQL 2012. Is it a bug? Would you please tell me what is the right way to do it?
with
member [measures].[prv mon] as
sum(
MTD(
ParallelPeriod( [Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember)
),
[Measures].[sales amount USD]
)
select {[measures].[prv mon]} on 0,
{
[Business Unit].[Business Unit].[Division].members
} on 1
from [Invoice History]
where [Date].[Date].&[20111231]
Thanks
HUa