PBCS/EPBCS Data Import Options

Importing data directly into PBCS can be done in two ways.  There is a planning format and an Essbase format.  Yes, it can be loaded through Data Management, but this is focused on explaining the direct import process, the formats, and the benefits to each.

Planning File Format

What Is The Planning File Format

Per Oracle, if data is exported to a file in the Planning format, you can use Notepad to change the database name in the file; for example, ASOCube, and then import the data file into the aggregate storage outline provided all dimensions exist.

This is fine and dandy if you are importing data exported from Planning.  Normally, there is a source that is giving the data to PBCS and a format has to be provided to that source.  The format is basically a delimited file with some quotes around a group of the fields.  Technically, there are 3 pieces of the file.

1. One dimension has to be in the rows.
2. One dimension has to be in the columns.
3. The third section is the POV, or the remaining dimensions.

It looks like this.

Account,Jan,Feb,Mar,"POV",Plan Type Name
10020,40.10,50.60, 70.20,"Actual,Final,Company_10,FY18",Finance

Why Use The Planning Format?

There are some benefits and drawbacks of using this format.

• Data loaded to smart list accounts can be loaded as the actual smart list label. This is great for applications where the data is provided in that format.  WFP data often provides data in the smart list value, like Part Time/Full Time.
• When there are errors, the load continues through the end of the file.
• The job console shows the number of records processed, the number of records that couldn’t be loaded, and the first x lines of errors with line numbers.  I will warn you that I have seen the console show all records loaded and nothing loaded.  I have also seen no rejected records when the file didn’t load completely.  So, when you are developing loads, I would recommend validating carefully and looking at the block counts to ensure the messaging in the console is correct.
• The biggest drawback to loading the planning format is that it loads through Planning and because of some of the benefits, it is slower to load. This normally isn’t a huge issue because the loads are finished within a few minutes.
• The format is a little odd and can sometimes be problematic to produce.  Certain dimensions are required in the row and column headers.  This is often challenging.  For instance, most applications require the months to be in the row or column areas.  Since they can’t be put in the POV, more than two months have to have multiple columns.  If the source can’t produce this easily, a file for each month has to be created.

Essbase File Format

Why Use The Essbase Format?

Loading directly to Essbase is faster and often times easier to produce the needed format.  It loads directly to Essbase so none of the mapping done when loaded through Planning (like smart lists) happens.  This is often a huge headache because the mapping has to be done in the source or in some middle ETL layer (assuming DM is not being used).  Another issue with using this method is that when a record rejects, nothing more is loaded.  The job console also does not show the records processed.

What Is The Essbase File Format

Per Oracle, (exported by an on-premises product). These files can be formatted in either Cell list or Column list format. Cell list format specifies a single cell value for each record, whereas Column list format specifies multiple cell values for each record. For example, if you specify Time on the column, you might have the values Jan, Feb, Mar, and so forth on the column, with 12 values for each record (that is, one for each month). Conversely, the Cell list equivalent would contain 12 records, one for each month. For information on exporting files from Essbase, see the on-premises Oracle Essbase Administration Services Online Help.

That doesn’t tell us much.  The format of this loading methodology is almost identical with the Planning format.  The thought process is a little different.  This file will hold a column for every dimension but has no header line.  Each dimension is preceded and terminated by quotes and delimited by a tab.  All the dimensions are listed first, followed by the value of that intersection.  The planning format shown above will look like this

"10020"       "Actual"      "Final"       "Company_10" "FY18" "Jan"  40.10
"10020"       "Actual"      "Final"       "Company_10" "FY18" "Feb"  50.60
"10020"       "Actual"      "Final"       "Company_10" "FY18" "Mar"  70.20

Last Call

Neither of these formats/methods are complicated, but the documentation is lacking when explaining exactly what they are.  There is little mention to the pros/cons.  Hopefully sharing this will fill in the gaps that exist in the documentation.