Metadata and Data Integration for Oracle EPM Cloud, Part 1
Today starts a series of blogs I am writing on metadata and data integration options for Oracle EPM Cloud implementations. The blogs will cover everything from Data Management, to Smart Push, to data exports using Business Rules. Also included will be descriptions of use cases and advanced topics such as drill-through to details loaded through Data. I will also point out which methods can be automated by using EPM Automate and which methods can be used by which applications – PBCS (Planning and Budgeting Cloud Service), EPBCS (Enterprise Planning and Budgeting Cloud Service) and FCCS (Financial Consolidation and Close Cloud Service).
Exporting and Importing Metadata
The first blog herein is on metadata exports and imports. Loading metadata is a prerequisite for loading data and any automation, so this seems like a good place to start. In the examples below, I am using the sample PBCS application called Vision. While my examples are for PBCS, they also apply to EPBCS and FCCS.
Editing Existing Metadata
This is not intended to describe all the properties and what they mean nor to describe all the options for adding members, moving members, etc., but it will point you in the right direction. You can manually edit existing metadata by navigating to “Create and Manage” > “Dimensions”. This looks very much like on premise Planning applications. I first selected the Product dimension, then drilled down on the hierarchy. To edit the member, click on the member you wish to edit and then click on the pencil. The various properties will be displayed. Make any changes that you need and then click the “Save” button.
Running a Refresh
Just like Planning on premise, after updating metadata, it is necessary to run a refresh to push the metadata from PBCS/EPBCS to the Essbase database. There are a few ways to do this.
To run a refresh, under “Actions” select “Refresh Database”.
Alternatively, you can select the “Refresh Database” icon.
Note that the above methods do not provide any options. The next method does. Navigate to “Overview” and under Actions, select “Refresh Database.”
Select “Create” and your options and then click the “Refresh Database” button. You can also save this as a job which can be used when automating processes with EPM Automate.
Editing Metadata with Smart View
You can also use the Excel Smart View add-in to edit metadata. This add-in allows editing of the metadata for both on premise and cloud implementations. (This will be a subject for another blog.)
Exporting Metadata
I’m discussing exporting metadata before importing as this is a great way to see what the format needs to be like when you import the metadata. With PBCS and EPBCS you can export the metadata in two ways:
- Export Metadata Job
- Artifact Snapshot (this is the subject of another blog)
Export Metadata Job
To export the metadata, navigate to “Application” > “Overview”. Click on “Dimensions”, then click on the “Export” button, then click the “Create” button. For the location, select “Outbox”. This will send the file to the Planning outbox and it can be downloaded to your computer from there. Select the “Product” dimension and the delimiter of “Other”. Enter a pipe (|) in the box to specify the delimiter. I prefer to use a pipe delimiter as formulas, for example, may contain commas and semicolons, so those are typically not good choices. Since we have selected the Outbox, we need to click the “Save as Job” button.
Give this job a name.
If you are running this as “Local” (i.e. to be sent directly to your computer), then you would have selected the “Export” button.
Click the “Save” button, then click the “Close” button. To run this job immediately, click the wheel and then select “Submit”.
Click the “Close” button, then navigate to “Application” > “Jobs” to view the completion status.
When the job is complete, navigate to “Application” > “Overview” and select “Inbox/Outbox Explorer” from the “Action” menu.
Select the file and then click the wheel and select “Download File”. You can choose to open the file or to save the file. If you choose to save the file, it will be downloaded into the Downloads folder on your computer. The .txt file will be within a .zip file.
To schedule this export metadata job, navigate to Application > Jobs. Click on the “Schedule Jobs” button. Under “What type of job is this?” select “Export Metadata”. Then make your other selections.
When you go to your “Downloads” folder, the file will be a text file within a zip file.
You can then copy this file elsewhere to edit it. Be careful what type of program you use for editing. Excel can corrupt the file, for example, by removing leading zeros. The file may also be too large to open using Notepad. Other text editors such as TextPad or Notepad++ can open larger files more efficiently.
Note that one difference between on premise and the Cloud is that the header for the Child (the first column) has the name of the dimension “Product” instead of “Child”.
When you are working with the metadata .txt files, note that the columns can be in any order and not all columns are required. For example, if you are building a Customer dimension and all the members add up the hierarchy so that all the aggregation operators are +, then it is not necessary to include the aggregation columns, as that is the default.
Import Metadata Job
The Import Metadata Job is very similar to running an export. First we will upload our file into the Inbox. Navigate to “Application” > “Overview” and under “Actions”, select “Inbox/Outbox Explorer”.
Click the “Upload” button and browse to your .txt file. You may wish to click on the “Overwrite” checkbox if you have uploaded the file before. Then click the “Upload File” button.
You will then see a successful completion message. Click the “OK” button.
You will then see your file listed in the Inbox. Click the “Close” button.
In order to import the file, we will first create the job. Navigate to “Application” > “Overview”. Click on the “Import” button and then click on the “Create” button.
For location, select “Inbox”. Type in the name of the text file next to the Product dimension. For the File Type select other and type in the pipe, which is our delimiter. If you wish to clear all the members in the dimension before loading the file, click that checkbox.
Then click the “Save as Job” button. Give the job a name. If you wish to refresh the database upon successful completion, check the checkbox. I am choosing that option here as it saves a step. Click the “Save” button.
To run the job, navigate to “Application” > “Jobs”. Click the “Schedule Jobs” button and select “Import Metadata” and “Run Now”. Click the “Next” button.
Select your job and click the “Next” button and the click the “Finish” button.
The job will then run and it will be in “Processing” state. Notice that there are two jobs in processing state – the import job and the refresh database job.
Click the “Refresh” button to update the status. When complete, the jobs will look like:
EPM Automate
Now that we have created an import job, EPM Automate can be used to automate the loading of files sourced from elsewhere, such as an ERP systems like Oracle Financials Cloud, Oracle-E Business Suite, SAP, etc. This will be the subject for a future blog.
Next Blog of the Series
The next blog of this series will be on using EPM Automate to import metadata. To receive the next blog, simply subscribe to my blog (see upper left hand column to enter your email). You will receive an email with the blog the day I release it! Stay tuned! Also, if there are any specific questions you have or items you would like to see covered in my blogs, just shoot me an email at deannasunde@gmail.com.
