One of the latest and greatest features of Oracle Cloud PCM is the ability to extract, filter, and transform level 0 data from the database quickly and efficiently through a simple EPM Automate or REST API command.
However, keen observers would notice that the same command we use in PCM is not available to us in Enterprise PCM. So, how can we export level 0 in Enterprise PCM? This blog will show you how!
The Short Answer:
For those who want the abbreviated answer, we use Data Exchange and leverage the "EPM Data File" target application type and the "Quick Mode" functionality!
The reason why the "exportQueryResults" command (which is what we call in EPM Automate and through the REST API in PCM) is not available in Enterprise PCM is that the common EPM Platform already had the functionality to export level 0 data to a columnar format and having a consistent experience across the different business processes makes sense.
How Do We Export Level 0 Data in EPCM?
If you have ever used the EPM Data File functionality, you would have most likely stopped reading BUT in case you haven't, let's have a quick crash course on how to get it to work:
Step 1 - Setup the EPM Data File Target Application:
In this tutorial, we will be using Data Exchange, and our first step is configuring all our target applications. We will show how to set up the EPM Data File target application, but you will need to ensure you have, at a minimum, the calculation cube (PCM_CLC) target application configured as well.
In the Data Exchange UI (1), click on the "Actions" (2) button and click "Applications" (3):
Click the "Add" (+) button towards the top left of the UI. In the pop-up screen, make sure the following selections are made and click the "OK" button:
You will now see the "EPM Data File" application in your Applications screen. Click on the "EPM Data File" text and navigate to the "Options" (1) tab, where you will now be able to set your export file name (2):
In this blog, I am not going to go into how to configure Period Mappings and Category Mappings but keep in mind you will have to configure those to go onto the next step.
Step 2 - Create your Data Integration Job:
Now we are ready to create the Data Integration job that we will be able to execute to export the level 0 data in columnar format. The configuration is reasonably straightforward, as we don't need any mappings.
First, we are going to give our job a name (1), in my case, it was "DL-PCM_CLC-Lev0-Exp", a Description (2) if desired, and a Location Name (3) and ensure "Quick Mode" (4) has been enabled. Next, select your PCM_CLC application from the drop-down under "Source" (5) and EPM Data File from the drop-down under "Target" (6), choose your appropriate Scenario from the Category (7) drop-down and click "Continue" or Save (8):
You can click "Continue" through the "Map Dimensions" and "Map Members" tabs, as we don't require any dimension or member mappings for this exercise.
Finally, in the "Options" tab, add any appropriate filters such as Year, Period, or Version to the "Filters" tab and ensure "Level 0 Data" has been selected from the "Data Extract Option" in the "Options" tab:
Click "Save" to save the job.
Step 3 - Execute the Data Integration Job:
Execute the job by clicking the "Run" button next to the newly created job. In the pop-up screen, the mode can be left to "Replace," (1) and the user will be able to make any appropriate Filter Updates before clicking the "Run" button (2):
You can now wait for the job to complete or click the "Continue Offline" button if you would like to continue with other tasks while it runs in the background.
Step 4 - View the Job Status in the Process Details:
If you did select "Continue Offline" and need to come back later to see if the job was successful, you can navigate to the Data Exchange screen and click "Actions" and then "Process Details".
Once you have confirmed the job was executed successfully, you can navigate to your Inbox/Outbox Explorer in the "Overview" screen, where the file will be available.
Conclusion:
Exporting, filtering, and transforming level 0 data into a columnar format has become essential functionality in any Profitability solution where our post-allocated results can be substantial. The functionality allows us to get a large amount of data out of Enterprise PCM that downstream applications can utilize very efficiently.
Unlike the exportqueryresults command in PCM, the above data integration job can be kicked off by the appropriate end user, which gives greater flexibility and useability. Like PCM, the task can be automated through EPM. Automate the REST API if required too.
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.