Product Review: ASO Native Export Format into Column Format with cubeSavvy Utilities

Sometimes a new functionality is introduced, but an important feature is missing to fully utilize its potential. Likewise, in the Essbase Aggregate Storage Option (ASO) we have a database export function but limited to the native format of the export file. As you might know, Essbase is super-fast at exporting all data and it can also handle this native format very well, but one cannot easily use this data elsewhere. To solve this issue, Harry Gates has built a solution called “ASO Export Parser”; let’s take a deeper look at this function of cubeSavvy Utilities.

What is the problem with native format? Isn’t ASO the reporting side of Essbase and thus the final destination of the data? And, why do you want to store ASO data in a different database when it is already exists in the best database on earth? Valid questions, but Oracle Essbase databases are usually part of a larger IT infrastructure. ASO databases might be more on the reporting side, but they accept data input from end-user tools and can have calculations.

We need a way to export and also process this data for other purposes. Data can’t be locked in ASO. We might need to transfer data into a relational or Essbase database. It certainly will become necessary after data input by users and running calculations. Data input should only be done once, even if this data is used in different places, therefore we need a process to get it out in an easy and reliable way.

Calculations often have substitution variables with a time reference. There might also be a certain order in which they are executed. Redoing these calculations takes time and can be error-prone.

A data load seldom comes alone. Each data load should be preceded with a CLEAR DATA on the load area, but things can go wrong in this process or the data deliveries can be incorrect, thus a (partial) restore of the backup might be needed. We need a way to store the backup data in a format so it can be retrieved easily as a partial restore. This requires a usable format of the Essbase export file.

As you might know from experience, nothing pulls the data faster from an Oracle Essbase cube than a data export. It is a stable and dependable way to get the data from Essbase, but you get everything because you can’t set a filter, and with ASO you get it in native format. The export options only enable “Level 0 data blocks” as shown below in Figure 1.

cube1.jpg

Figure 1: Export Database for ASO

So what is this native format, and why is it a problem?
The native data format isn’t the best for loading into a relational table, Excel, or anything else. The structure of the ASOSamp application is shown in Figure 2. Members of one dimension are brought into the data columns. In this example, there are five members in this dimension. The maximum data value is 5, but when the last of the columns (here “Returns”) has no data, there are only four data values and the last is blank.

Other dimension members are in the rows. Line 2 always has one member from each dimension, except the one in the columns. Then, when a member from a dimension changes to its prior reference, this member name is shown in the row. Those that do not change are not repeated.

This format makes the file so difficult to handle in a structural way because the repeated members depend on the data and not on the metadata.

cube2.jpg

Figure 2. File in native format

Imagine you want to translate the example above to a structural file? What needs to be done to get from the native format into a column format? This is shown in Figure 3. You see the red member names were added to make the structure complete. In the original file, the data is in five different columns for the members “Original Price,” “Price Paid,” “Units,” “Transactions” and “Returns.” Also, these are brought into the rows. In the original file, in row 4 the member “Photo Printers” and the member “14010” are listed. This is reflected in Figure 4 in rows 9 until 12 where the member “Camcorders” will be replaced with “Photo Printers” and the member "017589" with "14010.” These are colored in black as changed elements.

cube3.jpg
Figure 3: In column format converted file. The red-marked member names have been added to the structure.

A file in column format repeats every member in each line, so one line is a complete record with one member from each dimension, followed by the data members. Figure 4 shows the column format which has been created manually from the native format.

cube4.jpg

Figure 4. File in column format

How can cubeSavvy Utilities help here?
cubeSavvy has the functionality to convert the native format into a column format.
The cubeSavvy Utilities can be downloaded as a trial version at http://www.cubesavvy.com/. This utilities box version 3.0 contains the “Outline XML Parser,” “MDX Query,” “File Filter,” and of course the “ASO Export Parser.” These were developed by Harry Gates, a talented programmer and Essbase fan.

This ASO Export Parser is written in Java and references the Outline with the Essbase Java API. It takes the export file in native format and adds the missing member names. It will add the red-colored member names in each row as displayed in Figure 3. The program needs to keep track of which member comes from which dimension and what member should be added at this position. When the last line of the native export file is done the converted file is written to a file location, where it can be picked up for further processing. In this way, the utility converts the native format into a column format.

What were my experiences with the ASO Export Parser?
I used the ASOSamp application for testing, because it has a normal database in “the field”; you will recognize the screen shots and you might be able to validate my findings.

The GUI
The cubeSavvy Utilities can be launched by a double-click of the cubeSavvyUtilities.jar. The user interface opens as shown in Figure 5, and you can give the connection details. Once the login is successful, all tabs of the different utilities are enabled.

cube5.jpg

Figure 5: GUI before login

The ASO Export Parser is the second tab and when selecting it, the layout shown in Figure 6 is shown. There are two different selections possible –  the “Parse” and the “Member Filter” sub-tab.

The Parse page can be used to select the application and database. In this dropdown list, you will see only ASO applications, which is a helpful feature. Under “ASO Export file,” select the export file in native format. The browse button next to “Output file” gives the location and file name of the converted file. Here, you can specify a file name and if it does not exist, it will be created. I like this because it is useful when you want to give your files a date reference in the name. The conversion can be started with the button “Parse to columns.”

cube6.jpg

Figure 6: ASO Export Parser

The second sub-tab is “Member Filter.” In this tab, you can specify a filter on multiple dimensions. This is a great functionality because the export data brings all Level 0 data into the export file and no filter can be applied there. In other words, this “ASO Export Parser” utility can split the export file into separate data slices. This becomes very useful when you need the export as a backup and you know all data input, for example, is in the scenario “Adjustments.” Another use-case is that you load your data incremental for three months. You will be able to select only the time periods you need.

 As can be seen in Figure 7, the selections are <CHILDRENOF, <OFSAMEGENERATION, <ONSAMELEVEL, <DESCENDANTSOF, <ALLSIBLINGSOF and <LSIBLINGOF (when applied to Feb, it brings Jan). Unfortunately, the member itself is missing, but I hope it might be added soon into a new release.

cube7.jpg

Figure 7: Member Filter

The automation
One of my personal views is: If you cannot automate it, you shouldn't use it. Thus, the automation part was an important part of my testing. I can tell you here, the ASO Export Parser passed the test.

The Java program can be started from the command line with a config file (see Figure 8). Unfortunately, in this version it is not possible to use variables in the config file or tell the utilities to use a specific config file. Thus, if you have different files to be converted, you will need to have different config files prepared. In the conversion process of multiple files, you copy the config file, do the conversion, and then copy the next file and do that conversion, etc.

cube8.jpg

Figure 8: Config file with automation details. See also rows 38 until 42.

cube9.jpg

Figure 9: Batch window after running the conversion

Also, a long-running test, with 200 exports in sequence, was giving a good and consistent results. The test with error trapping, triggered with stopping the ASO application, was done correctly.

The filter setting is a bit different from the GUI, but an example is given in the default config file, which is shipped with the download.

The performance
The data in a native format file is stored quite efficiently. Therefore, the size of the column format file is considerably larger. In the case of the ASOSamp application and the data set I used, the factor is about 20 larger. So, 1 MB of native format generates 20 MB of column format.

ASO is normally used with a lot of data, thus, the process should be fast, and otherwise it is not usable in normal circumstances.

I modified the original data set of ASOSamp to get some insight into how the ASO Export Parser will work with a larger data set. In Table 1, you can see the results from the performance test.

I modified the original data set of ASOSamp to get some insight into how the ASO Export Parser will work with a larger data set. In Table 1, you can see the results from the performance test.

table1.jpg

Table 1: Conversion performance

As you can see, the performance is very good. Also, larger files are converted very quickly. This test was done on a vmware image running quite low on resources, but the 5GB was written fast. To give you an impression, an input data file in native format of more than 200MB means 40 million cells in ASO.

Also, when filtering the data file in the conversion step, the performance is good. This is shown in Table 2.

table2.jpg

Table 2: Conversion performance with filter

Conclusions
Harry Gates has done a great job, and his distant relative Bill would be proud of him, if he knew. The ASO Export Parser fills an important gap in ASO cube functionality.

The utilities are in further development with feedback from many interested in the community. I had a look at version 3.0, which I believe is ready to be integrated in production processes. It is stable and fast in doing what it should do. I can recommend downloading the software and giving it a try yourself.

Recent Stories
Announcing the 2019 ODTUG Innovation Award Nominees

New Oracle Professional Scholarship Applications Are Now Open

ODTUG April 2019 News