MDX Query Tool

I really like MDX. Its syntax and power make it an invaluable tool in any Essbase professional's tool-kit. However, Hyperion/Oracle has not made a very user-friendly way to output MDX queries. You can use MaxL to output the results, but then you have to parse all the extraneous bits to make the result useful. You can use EAS to view the results of your MDX query, but there's no way to copy the data table to use somewhere else.

Therefore, I put together a GUI utility that uses the Essbase Java API to output the result of an MDX query to a table that can then be saved to a file. The file is tab-delimited, so it can easily be imported to Excel. The tool is ever so aptly named the MDX Query Tool. Hey, I'm an Essbase geek, not a marketing guru! If anyone else has a better name, I'm willing to rebrand the tool posthaste.

You can download the MDX Query Tool from my Google drive, no Google account needed. As you can see, there are two files to launch: MDXQuery.bat (for Windows) and MDXQuery.sh (for the superior operating systems). As a zip file cannot maintain file permissions, you'll need to 'chmod a+x MDXQuery.sh' on *NIX and Mac OS X. Please use cubeSavvy Utilities, which has improved functionality for displaying MDX properties. You can download it here.

Once unzipped, you'll see a lib directory. The ess_es_server.jar file included with the MDX Query Tool is for version 11.1.2.3. If you're on a different version, you'll need to find the ess_es_server.jar in your install and copy it to the lib directory. For example, it resides in ORACLE_HOME/common/EssbaseJavaAPI/11.1.2.0/lib in my 11.1.2.3 installation.

After launching the MDXQuery.bat|sh file, you'll need to input the appropriate parameters for your environment, as seen below:

Clicking on the "Save to file" button at the bottom will open a dialog box that will allow you to choose a file path/name for the file. You can then open this file, copy the contents, and paste directly to Excel. If there is demand, I will add the ability to copy the results directly from the table to the clipboard. Just let me know in the comments.

I hope this tool makes it easier for you to work with MDX queries.