MDX Query Tool - XMLA Edition

Response to the MDX Query Tool has been overwhelmingly positive. My sincere thanks for the emails and comments. They keep me motivated to continue making useful tools. There's nothing quite so discouraging as releasing a tool, only to have no one use it.

Several people, however, experienced the phenomenon of JAR hell, caused by the version of the ess_es_server.jar file in the lib directory. The version here has to exactly match the version of Provider Services running on the server from which you want to retrieve data. I recommended that you find the version on your server and copy it to the MDXQuery/lib folder. This felt like a hack, however, so I started thinking about ways to eliminate this requirement.

Like most Essbase developers, in the back of my mind I knew that Essbase supported XMLA, but had never really thought about using it - until now. The main appeal at first was that no Essbase-specific jars are needed. However, as the Wikipedia article on XMLA states, "XML for Analysis (abbreviated as XMLA) is an industry standard for data access in analytical systems, such as OLAP and data mining." It's used by other vendors (Microsoft, Pentaho, SAS) in other OLAP products besides Essbase (MS Analysis Services, Pentaho/Mondrian - open-source, Jedox - open-source). I don't have any of these products installed, but theoretically at least, you could point this version of the MDX Query Tool to them and it should work. Please let me know in the comments if you do this and whether or not it works.

Just as with the original MDX Query Tool, you can download the MDX Query Tool - XMLA Edition from my Google drive, no Google account needed. After unzipping, there are two files to launch: MDXQueryXMLA.bat (for Windows) and MDXQueryXMLA.sh (for the superior operating systems). As a zip file cannot maintain file permissions, you'll need to 'chmod a+x MDXQueryXMLA.sh' on *NIX and Mac OS X. This tool has been incorporated into cubeSavvy Utilities with improved functionality. You can download it here.

After launching the MDXQueryXMLA.bat|sh file, you'll need to input the appropriate parameters for your environment, as seen below. The sharp-eyed will notice that there are no longer fields to enter Application and Cube information. This is not necessary through XMLA, since the MDX expression already contains this information in the FROM statement. For this reason alone I like it better than the Essbase Java API version that uses IEssOpMdxQuery.

As before, 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.

Happy MDX querying!