In the first installment of the OneCloud Did You Know blog series, we're going to discuss OneCloud’s high-speed extraction and conversion capabilities for Oracle Profitability and Cost Management to an open and standard format that can be shared with many other applications as the following diagram depicts.
PCMCS is powered by Oracle Essbase Aggregate Storage (ASO). Essbase is the engine that powers much of the Oracle EPM Cloud offering including those for Financial Close, Tax Reporting, Financial Planning, and Profitability and Cost Management. Oracle Profitability and Cost Management (PCMCS) can be used for a variety of business needs including Operational Profitability, Fully Allocated Financial Statements (Thin Ledger) as well as Expense, Regulatory and Shared Service Allocations. Often the results generated by PCM need to be shared to upstream or downstream systems; however, this can present a challenge because PCM leverages ASO for its data storage.
Data extracts from a PCM application are a challenge due to the two mechanisms that are available to export data from PCM. The first extract option is query-based. A PCM query allows more control over the format of the file that is generated but queries have a 5 million cell (not row) limit. This means that if a query is going to return more than 5 million cells (including blank/missing cells), then the query simply will not execute.
With a 5 million cell limit, it might require dozens, hundreds or even thousands of queries that extract different multidimensional combinations. This is problematic as it has an impact on extraction performance and requires custom code. This is certainly not impossible to develop, but far from ideal and should not be the focus of any Oracle EPM implementation.
The second option available is a level 0 data extract. The ASO level 0 export format is unwieldy. While the extract is delimited, the layout of each record is variable. Some rows will have more columns than other rows. This makes sharing ASO data extracts with other systems, particularly those that expect a consistent delimited format (i.e.tabular), incredibly difficult. A sample level 0 export is below. Notice how the number of columns vary by row.
Sample ASO Format
The ASO level 0 export has two primary limitations. The first is that there is no ability to export a subset of the level 0 data. The second limitation is the format of the extract. Because the record layout is inconsistent it is troublesome to load to even another Essbase cube without complete metadata synchronization. Relational or analytic reporting systems such as Power BI or Snowflake expect tabular data and simply cannot accept the raw ASO export format. Like the query option above, custom code can be developed but introduces technical and maintenance challenges that can drive up the implementation and on-going production costs.
To address these challenges, OneCloud has recently added new capabilities to its platform to provide a performant, cost-effective and maintainable solution to integrate source Essbase ASO cubes with any upstream or downstream application. Once the data is available in an open and consistent format, OneCloud’s built-in and lightning-fast transformation features can reshape the data to load to a target system.
OneCloud’s transformation features include the ability to filter rows and columns, find & replace, pre-aggregate and blend (lookup) multiple datasets. Learn more about OneCloud’s transformation by visiting the Transformation QuickStart Guide.
Since seeing is often believing, let’s walk through the configuration of a OneCloud Chain that will extract data from a source Oracle PCMCS application and provide a clean tabular output that can be shared with other systems in your ecosystem.
Step 1: High-Speed Data Extract
To export source data from PCMCS, OneCloud has the Export Query Results command. The setup is easy whereby only a few parameters are needed to specify the intermediate file name and select the option to focus the extract on level-0 data. The second screenshot illustrates the download of PCMCS data after the first Command is complete.
OneCloud Command #1
OneCloud Command #2
Step 2: Extract Source Metadata
As data and metadata go hand-in-hand, it is necessary to have the source metadata of the PCMCS application. The following two screenshots illustrate this is another easy OneCloud configuration to specify the preconfigured snapshot name and download what is referred to as an “LCM export”.
OneCloud Command #3
OneCloud Command #4
Step 3: ASO Format Conversion
After the intermediate PCMCS files have been created and downloaded, the content needs to be converted to a tabular format that can be transformed and shared. Like the prior configurations, the set up is easy and only requires two parameters for the ASO Export File and the LCM Export File.
The resulting OneCloud Chain is configured as follows. It is simple and easy to understand, highly performant, and utilizes no custom code. To make updates, an authorized user can simply double click any Command and change the configuration.
The performance will always vary from application to application, but we are happy to share some benchmarks.
If we were to compare the results of trial 2 above to using a custom-coded query option we might see the following extrapolated performance comparison.
In subsequent posts, we will explore how the extracted PCMCS data can be subsequently filtered and/or mapped using OneCloud’s advanced transformation capabilities. Be sure to subscribe to our blog and follow us on social media to get alerts for new posts and updates on exciting new functionality and solution highlights.
Lastly, we’d love to hear from you. If you like to see OneCloud offer more solutions like the highlighted here, please drop us a line at email@example.com.