Contact us FR

Profitability Tech Tips – New Functionality – Level 0 Export of Cube Data in Columnar Format

Published December 3 2021
Back to insights

I am happy to say we have some good news to share with some new, and very exciting, functionality coming to Oracle Cloud PCM in the latest patch this week! Oracle has obviously been working hard at providing us with PCM enhancements lately, and the one we are getting in the 21.12 patch is a BIG ONE:

Blog 1

Blog 2

So what’s the big deal? Let’s find out!

What Exactly Does This New Functionality Do?

Since the inception of Aggregated Storage Option (ASO), there has never been an efficient method for extracting large data volumes out of the Essbase Database. Of course, in the traditional ASO world, this was never a significant roadblock as ASO applications were used almost exclusively as Reporting applications. However, with the introduction of the Management Reporting Profitability Type, where data is stored and calculated in an ASO database, we started to see challenges with extracting large data volumes for Reporting and downstream applications.

So how did we do this in the past? We had two options:

1. Extract Data using MDX

  • Extracting data through MDX is the most common method to extract data from an ASO Essbase application and is the mechanism used by both Cloud Data Management (CDM) and the Export Query functionality.
  • Although it can be efficient for small volumes of data, the limitation of only being able to export 5 million rows of data (which sounds like a lot but is a fraction of what a PCM application can contain) and performance limitations means that you have to create A LOT of exports for even a moderately sized PCM application.

2. Extract the Level 0 Export and Convert it to Columnar Format

  • We have always had the ability to extract the level 0 data from an Essbase ASO Database, both on-premise and the cloud, but there is a catch. While the export itself is extremely efficient in terms of its size, it cannot be ingested by any relational-based tool (even excel), and we cannot filter it in any way. So even if we have the level 0 extract from a PCM Database, how do we use it?
  • Well, to use the Level 0 Extract, you need a utility that can at a minimum convert the extract to a columnar format and also provide filtering mechanisms if possible. This is what the Alithya ASO Export Utility has provided to clients for a number of years, and it works great, but you have to extract the level 0 data, download it, run it through the ASO Export Utility and then upload it to your reporting application.

Now wouldn’t it be nice for Oracle to provide us the best of both worlds and give us the ability to run fast small extracts using MDX but have some kind of functionality that allows us to export extremely large volumes of data that can be filtered and consumed by other reporting applications? Well, this is what the new functionality does, the new EPM Automate and REST API commands allows us to extract the level 0 data and converts it to a columnar format with filtering capabilities!

So How Do I Use The New Functionality?

As with a lot of new functionality, Oracle has released the ability to execute the new functionality through both EPM Automate and the REST API. In this blog, I am going to focus on EPM Automate but reach out to me if you would like to know more about using the REST API.

Oracle has actually made it really easy for us to use this new functionality and has leveraged the existing “exportQueryResults” command. To use the new functionality:

  1. Login to EPMAutomate with a local Administrator username and password
  2. Execute the exportQueryResults command with appropriate parameters
  3. Once the export has been completed, either download the export .zip file from the profitoutbox or utilize the “copyfromInstance” command to transport the .txt file to the desired location.
  4. Logout of EPMAutomate

If you are trying to export and convert the level 0 export your command will look something like this:

epmautomate exportQueryResults BksML30 fileName="ExportLevel0-Data"

exportOnlyLevel0Flg=true dataFormat="COLUMNAR" memberFilters:"{"Year":

["2016"],"Period":["November","December"]}" includeHeader=true

roundingPrecision=3 keepDuplicateMemberFormat=false

 

For those who are familiar with the exportQueryResults command the above will look very similar but to make it a little easier to identify the differences I have highlighted the commands that are unique to the new functionality. You can get more details about the command and its parameters online in the Working with EPMAutomate for Oracle Enterprise Performance Management reference guide.

 

The level 0 export to column format functionality will provide exports in two formats; in a .zip file which can be downloaded from the cloud and used in any on-premise or other reporting applications and a .txt file which can be utilized when moving data between Oracle instances.

 

Closing

The new functionality to export level 0 data in a columnar format while applying a filter mechanism is an absolute game-changer for clients who have extremely large data models. In the past, clients who have had partners with extensive experience with PCM, or have gone to third-party vendors, have been able to implement solutions external to Oracle Cloud to export and transform the level 0 extract in order to meaningful use the post allocated results from PCM applications but it came at the cost of an increase in solution complexity and maintenance. The introduction of this functionality means that Oracle now provides a way for their clients to export these large data volumes in an extremely efficient manner and not introduce additional complexity in their solution.

 

 

 

For comments, questions, or suggestions for future topics, please reach out to us at infosolutions@alithya.com.  Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.