Data Management is a flexible tool within Oracle EPBCS that allows the user to load data into the application via a flat file or source system. The first component of data management is setting up the target application, which defines what cube the data is getting loaded to as well as the dimensions and target dimension classes.
When you create a standard local planning target app in EPBCS, the Years and Period dimensions automatically get a target dimension class of “Year” and “Period”, while most other dimensions will say “Generic”. With these settings, when you load the data file the period and year you specify in the load rule determine the year and period the data gets loaded to in the application. However, there are some caveats to this; What if you want to load 10 years of data at once from a file? What if you just want to run the file and it automatically pulls the year and period the data should be loaded to from the file itself?
Let’s say you have two types of files:
- File A has the periods in separate columns at the end of the file and has the year in the file name but is not present in the file itself (e.g. columns 15 – 26 in the file are the monthly data for 2020).
- File B has the 10 years of data with the year in column A and the period in column B.
Option 1: Create Two Target Apps to the Same Cube
Set up “Target App A” with the Years and Period dimensions having target dimension classes of “Year” and “Period” respectively. Set up another “Target App B” exactly the same except with Year and Period having target dimension class of “Generic”.
To load file A, use Target App A. Create the import format with the amount being driven off the period, then create the location, mappings, and data load rule as normal. The period and year you specify in the load rule determine where the data gets loaded. E.g. in the data load set the start period to Jan-20 and the end period to Dec-20, and it will correctly load all the data.
To load file B, use target app B. In the import format, you will now see Period and Year appear as Targets. Create the import format and data mappings for Year and Period as you would any other dimension, and then when you run the file the year and period you specify only affect where the data gets loaded to in the workbench, but has no bearing on where the data actually gets loaded to in the application. All 10 years of data in the file will be loaded to the year and periods in columns A and B of the file, the same way the data is loaded to the correct dimension member for all other dimensionalities.
NOTE: With this solution, should you decide to implement drill through capability, you may encounter some issues with it. The drill through may get confused with possibly different data table columns for the different dimensions due to the two target apps going to the same cube. Although separate target apps is an easier solution to loading the two different types of files, you will need to use option 2 below if you intend on using drill through capabilities.
Option 2: Use One Target App and Create Period Mappings
Set up the target app with the Years and Period dimensions having target dimension classes of “Year” and “Period” respectively.
To load file A, the process will the same as option 1. Create the import format with the amount being driven off the period, then create the location, mappings, and data load rule as normal. The period and year you specify in the load rule determine where the data gets loaded. E.g. in the data load set the start period to Jan-20 and the end period to Dec-20, and it will correctly load all the data.
To load file B, first in the import format you will have to add Year and Period by adding “Source Period Row” in the import format mapping. Then, you will need to create source mappings for the file. When the data load rule is created, make sure to set the period mapping type to “Explicit” and the calendar to the calendar name you specified when creating the source mappings as shown in the image below (in this case would be “Example”). Note that for the example file B, you would need to create source mappings for all 10 years and the 12 periods within each year.
Lastly, to load file B using this method you can only load one year at a time. If you try to run Jan-20 through Dec-29, it will only load Jan-20 through Dec-20, so you would need to reload this file 10 times to fully load all its data.
