Parse ASO export to columns

I developed MDX Query Tool in order to facilitate extracting small amounts of data from ASO databases using MDX queries. Sometimes, however, the volume of data you are working with exceeds the MDX query limit. You may be able to craft an optimized report script to work around this limitation. Other times the window required by your service-level agreement with clients is simply shorter than the time it takes to run the mdx query. Chances are report scripts are not going to be much help in this situation. Judging by the 151 posts on Network54 related to ASO exports in column format, many others have also come to this conclusion.

Now we know that running a level-0 export from an ASO cube is extremely fast. For example, I've been able to export cubes with 10GB dat files in less than a minute. The problem is that the output is in a format optimized for loading back into Essbase, not for querying or grepping. Having run into this roadblock recently, I've created a utility to parse this format into the same format as a BSO level-0 column export.

ASO level-0 export format (default is tab-delimited)

"Account_A" "Account_B" "Account_C" "Account_D"
"Jan" "Actual" "CurrentVersion" "FY09" "Entity_A" "Dept_A" "LC_01" "PR_00" "PJ_00" "ICP_000" 94678.7
"Dept_B" -2538.48

Output from tool (also tab-delimited)

"Account_A" "Jan" "Actual" "CurrentVersion" "FY09" "Entity_A" "Dept_A" "LC_01" "PR_00" "PJ_00" "ICP_000" 94678.7
"Account_A" "Jan" "Actual" "CurrentVersion" "FY09" "Entity_A" "Dept_B" "LC_01" "PR_00" "PJ_00" "ICP_000" -2538.48

Click here to download the Parse ASO export to columns tool.



Follow these steps to use the tool:

1. After downloading the zip file, just unzip it and double-click on the ASOColumnarExport.jar file to launch the GUI.
2. As you can see in the screenshot above, you will need to select the file location to which you've already saved the exported ASO database. I can make this step part of the tool, but for now I wanted it to be as flexible as possible. If you'd like this as an option, let me know.
3. Make sure the Application (e.g. ASOsamp) and Database (e.g. Basic) match the export file that you selected. Otherwise the process will error out.
4. Fill in information for the other fields as necessary.
5. Click the "Parse to columns" button to run the process. A status bar will be display the parser's progress and will notify you when the process is completed.

If enough people find this tool useful, I will create a version that can be run from the command line/batch files/shell scripts.