For those that don’t know; there are several ways of loading data to PBCS.
- SmartView “Submit Data”
- Administration > Import and Export > Import Data from File
- Simplified Interface
- Data Management (FDMEE in the cloud)
For both Import Data from File and the Simplified Interface, the file formats are exactly the same. There are 2 types of files that can be loaded to PBCS: Native Essbase and Planning format.
*Note, going forward, although the Import Data from File option is available in this release; going forward, the Simplified Interface will be the suggested and supported method of loading data.
Native Essbase Format
Native Essbase format is a pretty simple file, without a header, that has all dimensions in a different column of the file, and the final column is the data column. The Native Essbase format is great for GL loads and other data that has all the columns defined; and the metadata members match exactly what is in PBCS (aka, no mapping required).
If you do have member names in PBCS that have spaces, double-quotes are required. If not; they aren’t required but I recommend using them.
Loading Native Essbase File via Simplified Interface
Within the Simplified Interface, go to Console, and then in the Actions dropdown, select Import Data.
Within this interface, select “Create” to create a new data interface. You can either do a one-time manual load, or set up a saved job to automate later with EPM Automate.
To load data manually, select Local location, and Essbase for the Source Type. For the Plan Type, select the database to load the data (valid for multi-database Planning applications). Finally, path out to the Source File on your desktop or server. Press “Import” to load the data. Selecting “Last Import” will show any errors; or you can go into Tools > Job Console within the PBCS application to view any load details.
To set up an automated job to load a file, select the Planning Inbox as the Location. In order for a job to be created, you must install EPM Automate on the server where the data file is currently located. Select “Planning Inbox” as the Location, and then Essbase as the format, and select the valid Plan Type. In the Source File dialog box, type the name of the file that will be used in the job. It’s important that this is the exact name of the file.
You’d press “Save as Job” in the upper right hand corner, and you’d see this as a saved job on the Import Data home screen. From here, a .bat script would be created on the server to log into EPM Automate, upload the file from the server to the Planning Inbox, and then load the file into the PBCS application via the job you just created.
Loading Native Essbase File with Import Data from File
When loading in Native Essbase format through Import Data from File, select the PlanType to load to and browse to the file.
Notice there is an option to select a date format; but remember from my post last week – this isn’t working properly via the Import Data from File interface. Also, from speaking to the Oracle Support, there isn’t any plans on making it work in the near future; since the Simplified Interface will be the supported and suggested way to load data in the future.
The Planning formatted file is a little more complex. The first column is your “Dimension to Import” column – aka, what is the unique dimension to which you are loading. In my example, it’s the Employee dimension and is filled with Employee number. The “Driver Dimension” is the dimension that falls across the columns. This can be 1 column of data, or as many as you need.
The Driver Dimension can be “Period” and allow you to load 12 months of data at once (put Period name in each column; “Jan”, “Feb”, etc). Or, like my example, the different Accounts that need to be loaded for Employee planning.
The Point-of-View column is key – it is the remaining dimension members (level-0 of course) that are necessary to load the intersection of data. This is the tricky part – because the Planning formatted file must be a .csv, the Point-of-View column must be surrounded by double quotations in order for it to be opened properly by PBCS. You can’t see them in my example, because it is being opened in Excel. If you were to open the file in textpad, double-quotes would be visible around the column.
The last column is “Data Load Cube Name,” which defines the Plan Type to load to in the application. This is where it is defined for the Planning format; instead of the Native Essbase format where it must be defined in the interface.
Loading Planning Formatted File via Simplified Interface
Again, from the Simplified Interface Console, go to Actions > Import Data. Press Create to create the new integration. If you’ve uploaded your file to the Inbox via EPM Automate, you can reference your file in the interface. If not, you’ll have to browse and upload the file.
Remember, if you want to save this as a recurring (automated) job like the Essbase format, you’ll have to set up a process on your server with EPM Automate to move the data file from your server to the cloud inbox. Otherwise this is a manual process.
Loading Planning Formatted File via Import Data from File
When loading the Planning Format via Import Data from File, it varies slightly from the Essbase load options.
Select the Data Import Format to be Planning. Depending on your formatted file, the Dimension to Import will be the dimension of your first column in the file. In my example before, it is Employee.
Browse to find the file to import. For the Driver Member Specification, this will be the column headers of your data column. If you are doing a multi-period file, you can select the Period members with the selection box. In this example, we are uploading Accounts that are Annual Salary and Headcount.
You can test the load by pressing “Validate” – and then “Run” to kick off the data load.
The “View Status” button will show any kickouts or issues after the data is loaded.