The ability to create multiple databases in Oracle Cloud Enterprise PCM (EPCM) is one of the most exciting and practical functionalities that was introduced. This functionality allows us to have cubes dedicated to a specific purpose (say for calculation and reporting), enhances security, and allows for the separation of duties by cube.
Enterprise PCM applications are deployed with two cubes by default, PCM_CLC (the “calculation” cube) and PCM_REP (the “reporting” cube). Utilizing these cubes properly is an essential aspect of managing your application’s performance, especially in applications with large data sets. For applications that produce a significant amount of post-allocation output, it is essential to keep the calculation cube (PCM_CLC) lean, with only the data required for the current calculation present and the historical data sets in the reporting application (PCM_REP). The obvious question is, how do you move data between the calculation and reporting cube?
There are several ways to move data between the calculation and reporting database, but which method you use will largely depend on the size of the data set you are looking to utilize and the method of execution. Data Maps are an excellent option for smaller data sets as they can be embedded into data forms and are extremely easy to set up. However, Data Maps don’t work well in large applications with a significant amount of data; likewise, utilizing Data Exchange was previously impossible in these scenarios. Fortunately, in the November 2022 patch, Oracle solved this dilemma by enabling Quick Mode Data Synchronization for ASO databases. This blog will give a quick introduction to how to use this functionality.
Step by Step on how to use Quick Mode to Populate the Reporting Cube
Quick Mode isn’t anything new to EPCM, it has been available since the initial release, but previous to the November 2022 patch release, we could not utilize this functionality for importing data into an ASO database. We have detailed how to use Quick Mode to export level 0 data from EPCM, but now you can follow the below steps to use it to populate your reporting database.
Step 1 – Ensure the Required Target Applications Exist
All of the steps in this tutorial will be done in Data Exchange. Our first task is to ensure that we have the calculation (PCM_CLC) and reporting (PCM_REP) databases created as applications in Data Exchange. These applications should be created by default, but it’s worth double-checking as we cannot proceed with any other steps if they are not.
To do this, navigate to the Data Exchange UI (1), click the “Actions” button (2), and then “Applications” (3):
Next, ensure you have target applications configured for the calculation database (PCM_CLC) and Reporting database (PCM_REP):
In addition to the above, ensure you have configured your Period and Category Mappings. Without them, you will not be able to Filter your exports to a specific POV.
Step 2 - Create your Data Integration Job
Now that we have confirmed our source and target applications, we can configure the Data Integration Job. To configure our data integration between the Calculation and Reporting Databases using Quick Mode, we are going to navigate to the Data Exchange UI (1) and click the “+” button (2):
In the next screen, give your Integration a Name (1), Description (2), Location Name (3), ensure “Quick Mode” has been enabled (4), select the Calculation Database as the “Source” (5), the Reporting Database as the “Target” (6), the relevant Category (Scenario) (7) and then the “Save and Continue” button (8):
Next, we will set up the Dimension mappings. This will be a straightforward activity since our Calculation and Reporting databases will likely have the same dimension configuration. In this case, all we will need to do is make sure dimensions selections are the same for the Calculation Database (PCM_CLC) and the Reporting Database (PCM_REP) (2):
Once you have mapped all dimensions, click the “Save and Continue” button (3).
In the next screen, we could configure any member mappings, but because we are using Quick Mode, we aren’t going to configure any member mappings and will click the “Save and Continue” button (1):
Next, we can configure our Filters. This is important as we often want to send a subset of data from the Calculation database to the Reporting database (for example, a specific POV). To do this, we will ensure the “Filter” tab is selected (1), then click the “+” button (2) for each dimension you would like to set a Filter for. In my case, I configure a Filter for the POV dimensions (Years, Period, Scenario, and Version) and then populate them with a default selection (3):
Now we can set any required Options. To do this, ensure the “Options” tab is selected (1) and set any critical options. In my case, I ensured that I had the correct Category chosen (2), that the “Data Extract Option” was set to “Level 0 Data” (3) and that “No” was selected for “Member name may contain comma” option (4):
Finally, we can set our target clear region by clicking the “Clear Region” tab (1), clicking the “+” button for each dimension you would like to add to the clear region (2), and then making the relevant dimension selections (3):
The above Step is essential because we want to clear out the region of data in the Reporting Database before loading the data from the Calculation Database to prevent any data issues.
Finally, we can click the “Save” button to save the integration (4) and close it.
Step 3 – Execute Integration Job
Now that we have configured our integration, we can execute the Data Integration Job. To do this, we are going to navigate to the Data Exchange UI (1) and click the “Run” button (2):
In the “Options” tab, ensure the Mode is “Replace”, and in the “Filters” tab, ensure that you have the appropriate POV selection:
When ready, hit the “Run” button at the bottom of the pop-up. Your integration job will now be executed. Depending on the size of your data, the job may take a few minutes to complete successfully.
If you are running a clear as part of your data integration job, you may need to create a valid data intersection in the Reporting database for the clear to run successfully.
Conclusion
Up to the November 2022 patch release, customers who had large data sets struggled to move data from the EPCM Calculation Database (PCM_CLC) to the Reporting Database (PCM_REP). With the ability to leverage Quick Mode for data integration jobs between ASO Databases, moving data between our EPCM databases is easy to set up and efficient.
Be sure to try it out; it might be a game-changer for you.
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.