Hi all! Before jumping straight to ODI, let us take a moment to talk about how our database design will look like to be the “middle tier” between our sources and PBCS. We will split our design in 4 parts:
- Sources: Can be any kind of source (Oracle, SQL Server, Teradata, File system, FTP…)
- Stage Area: Where we’ll work the data
- DW: Will have all the information in the right format that PBCS expects
- File System: The data/metadata from the DW will be exported in the right format/layout and then zipped
While source and stage areas are common across several architectures out there, we need to take a moment to talk about the DW layer, where we have the DW table which will contain the data that we want to load to PBCS and a Metadata validation table. Before we can load data into PBCS we need to make sure that there is no invalid member on the file, and therefore we create a job to export metadata from PBCS in the first place. All existing metadata will be stored in a metadata table with the follow format:
Generally, we build this table to be partitioned by App_Name or Plan_Type, so we can retrieve its information in a faster manner, but it all depends on the size and the number of your applications. With this approach, we may validate all data in the DW tables against the Metadata table and remove/fix it before sending it to PBCS.
The “auxiliary” tool that we will use to send/extract data between our DW and PBCS is EPM Automate. The EPM Automate utility will act as a bridge between ODI and PBCS as it enables Service Administrators to automate many repeatable tasks including (but not limited to) the following:
- Import and export metadata and data
- Refresh the application
- Run business rules
- Upload files, list files and delete files from PBCS
- Copy data from one database to another
- Run a Data Management batch rule
- Export and import application and artifact snapshots
- Import pre-mapped balance data into Oracle Account Reconciliation Cloud
- Import currency rates, pre-mapped transactions, and profiles into Oracle Account Reconciliation Cloud
- Copy profiles to a period to initiate the reconciliation process
- Deploy the calculation cube of an Oracle Hyperion Profitability and Cost Management Cloud application
- Clear, copy, and delete Point of Views in Profitability and Cost Management applications
- Export and import template in Profitability and Cost Management applications
- Replay Oracle Smart View for Office load on a service instance to enable performance testing under heavy load
Next post we will show one example of how to Load Data using EPM Automate with ODI. Stay tunned!