Developer Secrets Revealed: The Best Methods to Import Data to Oracle EPM Cloud
When loading data from a flat file to Oracle EPM Planning Cloud, you have two options: the native “Import Data” capability or the “Data Management (DM)” solution. Both options have their advantages and disadvantages, and this blog provides my recommendations on when to use which solution based on significant experience with Oracle EPM Cloud projects.
First, keep in mind we are talking strictly flat file data loads, since Import Data only works with these. If you need to load data directly from a source system, then Data Management is the solution.
Second, ask yourself if you need any of the extra functionality that Data Management provides. Import Data does one thing: it loads data. Data Management offers additional features, such as drill-through, which manipulates the data before it is loaded, as well as flexible file formats. If you need any of these things, then DM is your solution.
If you have a data file that is in the correct format for Import Data, does not need additional manipulation, and you do not need drill-through capabilities, either Import Data or Data Management is an option. To help choose which method to use, consider some pros and cons of the two methods.
Import Data Pros and Cons
1. Minimal configuration
Setting up an Import Data load is very easy, there are only six options to set and you are done:
Figure 1: The Import Data load easy-to-use solution.
2. Easy to run
Executing Import Data is also very easy! You can click on “Import” as soon as you configure the load, or you can save the configuration as a job and run it at later with a single click.
3. Data cannot load to the wrong period
Why this is even an issue is only apparent when we look at Data Management later, but for Import Data the data load is exactly as it appears in the file.
4. Errors are unambiguous
When a row of data fails to load, this throws an error. Again, why this is even an issue is only apparent when compared to Data Management.
1. Minimal log information on errors
The log messages are brief and often cryptic. Usually, you are given only an Essbase error code that you will need to research for more information. When using the Essbase file format, the load stops at the first error so you have no information on how many other errors may be in the file.
2. File format is rigid
Import Data only understands two file formats (Essbase and Planning) and the file contents must match the format exactly. There is no room for flexibility.
3. No data manipulation
Import data does one thing and one thing only: load data. The data in the file must be in a format suitable for Oracle EPM Planning Cloud or it will fail.
Data Management Pros and Cons
1. Flexible file format
Unlike Import Data, Data Management allows you to define your own file format. This is particularly useful when you are using a file that you have no control over and must adapt to.
2. Data manipulation
If you are given a file and the data isn’t quite what is needed, Data Management gives you the ability to map data into a form suitable to Oracle EPM Planning Cloud. Again, very useful when you are given a file and you must make it work.
3. Informative log
Data Management is the opposite of Import Data when it comes to log files. The information in the log files is so verbose that it could be seen as a con since there is so much to go through. But when there is an issue with a load, this is the kind of information you want to see.
1. Lots of configuration
Since Data Management is so flexible, there is much more configuration and setup before you can run a data load. There are multiple components to configure and many settings within each component. Sometimes there is more than one way to go about configuring the load and you must decide which way to go.
2. Must specify period to load every time
Whenever you run a load rule in Data Management you must specify the period(s) that are being loaded. It is possible to configure DM to read the period from each line of the data file or to specify a period in the file name, but you must still specify the period at run time. If the period specified at run time does not match the data in the file, data may load to the wrong period or it may not load at all.
3. Data can be loaded to wrong period
Since there are multiple configuration options, and because of #2 above, it is possible for data intended for one period to get loaded to a completely different period. There are ways to mitigate this possibility, and experienced users will know to avoid this, but it is very different than Import Data where data can only go where it is intended.
4. Load status can be deceiving
With Import Data, any data that does not load will generate an error. With Data Management, data that fails to load may show as an error or as a warning. Only by reading the (very verbose) log file, you can tell if warnings mean data wasn’t loaded. A load rule can complete with a warning status but none of the data is loaded (it seems like this should be an error condition).
Sometimes we must use Data Management because we need drill-through capability, the file format is not compatible with Import Data, or the data requires manipulation. But when we have the option of Import Data or Data Management, which do we choose?
I used to think Import Data was always the way to go because it is fast to set up and run, it loads the data exactly as it appears in the file without having to specify (or even know) what periods are being loaded, and it either succeeds or fails with no ambiguous warnings.
But, after using both Import Data and Data Management on multiple projects, I now appreciate the benefits of Data Management. I still use Import Data for a quick load when I know there will be no (or at least very few) kickouts. But for reliably loading data on an ongoing basis, I rely on Data Management, even when I do not need the advanced capabilities such as drill-through. Even though DM takes more effort, the detailed log file is worth it when something changes, and you need to troubleshoot the kickouts. Also, when data requirements change the ability to map data values in Data Management, this can be much easier and quicker than getting the source that is generating the file to change. The more difficult parts of Data Management are easy to deal with once you are aware of them, and the flexibility of DM has proven to be worth the extra effort.
Have more questions on Import Data versus Data Management? Leave us a comment below or at email us at firstname.lastname@example.org.