How to use your existing ODI on premise to seamlessly integrate PBCS (Part 6: Database Design and EPM Automate)


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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: