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.

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!

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.

Planning Calc Manager Rules - performance monitor

Sometimes I get comments from users that "Planning performance seems slow". The first thing I do in such instances is run the SQL code below to determine if there's an issue with Calc Manager rules running too long. Both the Oracle and SQL Server versions below calculate the "average time in seconds" (avg_time_seconds) and the "maximum time in seconds" (max_time_seconds) for each Calc Manager rule, producing a table similar to the following:

job_name	num_times_run	avg_time_seconds	max_time_seconds
AllocateBudget	45	25	50
AllocateForecast	15	28	32

Oracle:

select job_name, count(job_name) as num_times_run, round(avg(abs(extract(second from elapsed) + extract(minute from elapsed)*60 + extract(hour from elapsed)*60*60 + extract(day from elapsed)*24*60*60)),0) as avg_time_seconds, round(max(abs(extract(second from elapsed) + extract(minute from elapsed)*60 + extract(hour from elapsed)*60*60 + extract(day from elapsed)*24*60*60)),0) as max_time_seconds
from (
select user_id, job_name, start_time, end_time, (end_time - start_time) elapsed;
from hsp_job_status
where parent_job_id is null
and end_time is not null
and start_time > to_char(sysdate-7)
)
group by job_name
order by num_times_run desc

SQL Server:

select job_name, count(job_name) as num_times_run, round(avg(abs(DATEPART(second, elapsed) + datepart(minute, elapsed) + datepart(hour, elapsed) + datepart(day, elapsed))),0) as avg_time_seconds, round(max(abs(DATEPART(second, elapsed) + DATEPART(minute, elapsed) + DATEPART(hour, elapsed) + DATEPART(DAY, elapsed))),0) as max_time_seconds
from (
select user_id, job_name, start_time, end_time, datediff(ss,start_time,END_TIME) as elapsed 
from [dbo].[HSP_JOB_STATUS]
where parent_job_id is null
and end_time is not null
and start_time > GETDATE()-7
) as ps
group by job_name
order by num_times_run desc

Essbase runtime subvars in 11.1.2.3

The Oracle blog has a good explanation of the new runtime subvars functionality available starting in 11.1.2.3. At the end of that post they vaguely hint that it's possible to access this feature from the APIs. So I did a little research into adding the ability to pass runtime subvars from a grid in cubeSavvyLite to a calc script.

First, let's start with a simple calc script in Sample.Basic (named calcSubs):

SET UPDATECALC off;
SET RUNTIMESUBVARS { 
Currmonth = "Jun";
Currlocation = "Florida";
Currproduct="100";
};

FIX(&Currmonth, &Currlocation)
&Currproduct;
ENDFIX

I'd like to point out here that the new runtimesubvars command is very finicky. "SET RUNTIMESUBVARS" (in all capital letters, as above) works, but "SET runtimesubvars" will not even validate.

The Scala code (cubeSavvyLite is written in Scala, not Java) to call the "calcSubs" calc script and pass in the runtime subvars is:

val ess: IEssbase = IEssbase.Home.create(IEssbase.JAPI_VERSION)
val dom: IEssDomain = ess.signOn("admin", "password", false, null, "http://apsServer:portNumber/aps/JAPI")
val olapSvr: IEssOlapServer = dom.getOlapServer("serverName")
olapSvr.connect()
val cube: IEssCube = olapSvr.getApplication("Sample").getCube("Basic")
cube.calcFileWithRunTimeSubVars(
     false, "calcSubs", "Currmonth=\"Feb\";Currlocation=\"Texas\";Currproduct=\"200\";"
)
olapSvr.disconnect()

Looking at the Sample.Basic log, we see the following entry:
Calculating [ Product(200)] with fixed members [Year(Feb); Market(Texas)]

Runtime subvars will be coming soon to cubeSavvyLite. And they will allow you to pass in Page, Row, and Column members from the grid. The grid, in turn, is created from your report script definition.

I'm also looking at making the runtime subvars available in pre-11.1.2.3 versions, as they're really just simple find-and-replaces in the calc script. Please let me know if that's something you'd be interested in having available.