If you write direct MDX queries using SSAS 2005/2008 as a PowerPivot data source, it is better if you prepare your MDX query by using another editor. For example, write and test your MDX query by using SQL Server Management Studio and then copy & paste your query into the user interface provided by the Table Import Wizard. The remaining part of this post is dedicated to an explanation of the reasons for that.
The Table Import Wizard immediately shows the MDX Statement page and Design dialog box can be called by clicking on the Design… button, as you can see in Figure 1.
Figure 1 – Table Import Wizard dialog box
For Excel user it would be better if Design dialog box would appear immediately, especially whenever a query has still not defined, like in case shown in Figure 11.
Now, let’s consider the user experience when an MDX query is defined. First of all, there are two user interfaces to define the MDX query. You can see an MDX statement in Figure 2 (which is the Edit Table Properties window that appears to change the MDX query for an already existing table in PowerPivot).
Figure 2 – MDX query in Edit Table Properties
The other user interface is the one available in the Design dialog box as you can see in Figure 3. In this case, you have a better editor specific for MDX, with a drag & drop user interface that can be used also to get a minimal help for the syntax of MDX functions.
Figure 3 – MDX Query Editor in Table Import Wizard
Both editors have neither syntax highlighting (which is available in SQL Server Management Studio) nor IntelliSense (which is available in a tool like MDX Studio). Thus, an Excel user will hardly use one of these editors and probably will simply copy and paste some MDX query written by the IT department. Also for an IT Pro these editors are not so useful and he will develop MDX queries by using another tool. An improvement here would be to eliminate the simple text editor that is shown in Figure 2 to avoid confusion, providing it only if the cube metadata are not accessible (otherwise the editor in Figure 3 probably will not work).
Finally, there is another issue related to the formatting of the MDX query. It seems that if the original MDX query contains TAB characters, they are replaced by space when the PowerPivot window is closed and opened again (it always make this change when Excel is closed and then opened again). In Figure 4 you can see what happened to query shown in Figure 2 after Excel has been closed and then opened again: this changes in formatting limit the readability of more complex MDX queries.
Figure 4 – MDX query lost TAB formatting after Excel workbook is closed
The suggestion for future versions is to keep formatting or replacing TAB with spaces whenever the query is imported (using copy & paste) the first time.