Even with the extraordinary popularity of Cloud Services, many clients still need to figure out how to get their hands on their data sets store into these services.
There is no exception with Oracle Planning and Budgeting Cloud Service (PBCS). Even with the reporting capabilities offered on this cloud service, there is still the need to extract data for further use.
Learning through experimentation, I identified three methods that can be used on PCBS to extract data. There may be more, but I am focused only on the methods that can be executed from a command line and can be scheduled to run without user interaction. The three methods are:
- Use the data export PCBS functionality
- Use the data management module (FDMEE)
- Use a Business Rule.
Each method has their advantages and disadvantages; I’ll enumerate the steps for each of them.
PCBS Data Export Functionality:
This is the quick and dirty option to extract data from PCBS. It can be found under the PBCS simplified interface.
Select your Planning application
Click on the Overview button, and then select the Cubes tab.
On the Actions button/menu, you will find an Export Data option
The Export Data window will allow creating and saving your export definitions. This would be useful when executing using the EPM Automate command. Click the Create button.
When creating an export, you will need to add dimension selections on all three sections (Row, Column, and Point of View) Row and Column sections only allow adding one dense dimension per section. In this case, our Account and Period dimensions are set as dense. The Point of View section will allow filtering the data set with the members needed on the export.
At this point, it is possible to execute the data export via EPM Automate. To login to your instance via EPM automate, you will need to issue the login command first:
epmautomate.bat login yourusername yourpassword https://yourcloudurl.pbcs.us1.oraclecloud.com yourdomain
The EPM Automate command to execute a data export is:
epmautomate.bat exportdata zDataExportTest003 [Filename.zip]
The filename parameter is optional, if omitted, the command will create a file with the same name as the data export.
Once it is finished, the file can be downloaded using the epmautomate downloadfile command
epmautomate.bat downloadfile zDataExportTest003.zip
The data file will have a format similar to the Hyperion Planning Data Export. In this case, the Account members will show as rows and the Period members will show as columns. The Point of View and Plan Type will be located to the right of the data values:
In the second blog post of this three-part series, I will cover how the data management module (FDMEE) can be used to extract data from PBCS.
