Making it Easier to Write and Test MDX Queries with Arc

As we explained in a previous article, MDX is becoming more and more important in the IBM Planning Analytics / TM1 community and it is one of the key skills that any TM1 developer needs to have in order to make the most of the software.

Arc includes a dedicated feature for editing and working with MDX queries, and it is called the MDX Lab.  The MDX Lab is a feature for both Slice and Arc and in this article, we will walk through several new innovative features introduced by The MDX Lab.

Arc supports code formatting for Processes, Rules, and MDX. In the MDX Lab, just copy any MDX query, and then click the Format Code button and you will see your query formatted with the correct case and spaces.  In addition, you can fully customise this feature by adding your own best practices, allowing you to enhance your queries in a very efficient way.

Troubleshooting a long MDX query might be cumbersome. With the MDX Lab, you can run a selected part of the query rather than the entire thing. This makes it easier to test your full query by testing each section one by one – so you can identify any potential problems with more precision.

To do that, just select a part of the MDX query and click the Execute button:

With the MDX Lab, you don’t need to remember all the various MDX functions.  The MDX Lab includes the full list of MDX functions that you can use, and you can get to them through a simple drop-down menu. Just press Ctrl+Space to scroll through all the MDX functions:

Inside the list of functions, you can find many snippets that will make writing an MDX query a breeze. After clicking on Ctrl+Space, you can use the @ symbol to show only the snippets. For example, if you need to get the list of elements filtered by level, you can just use the snippet called @Hierarchy Filter by Level:

If you are new to MDX, Arc includes many features to help you build MDX queries without having to code them from scratch.  You can read more about it here.

• Pulse transforms cold promotion with TM1

• Code formatting for TM1

• Building Active Forms with Hierarchies