By Thomas Thuresson
December 21st, 2017
A couple of new data load abilities in Cloud Data Management (and in FDMEE 220.127.116.11 PSU 210) proved very useful at a recent EPBCS migration project. One of the data files we needed to load had two years of rolling actuals data and the other had multiple columns of data for multiple periods. Luckily a couple of new features made very easy to directly load these files without having to go back to the data team and ask for data files by period or files with multiple period data columns.
Multi-Period Data Loads
For the first requirement of loading the two-year rolling data file, we used the new ability to set up the Import Format to read the Year and Period from the source file by adding two source columns to the import format, one for the period and one for the year.
In addition to this, you also need to set up an Explicit Period Mapping to map the periods in the file.
And refer to this explicit map in the data load rule.
Multi-Column Data Loads
The other file we needed to load contained multiple columns of numeric and non-numeric data for multiple periods and the new feature to load multi-column data files came in handy.
In this example, the Driver Expression Type was defined to read column 5 through 9 and pick up the Account dimension member names from the file header.
We then mapped these header names to the corresponding member names in the Account Mapping. Alternatively, you could pick the member names directly using the member(s) lookup.
For the non-numeric data, we decided to load these using a separate import format with the “Multi-Column – All Data Types” file type instead of loading all data with one import format. Our data set had a lot of numeric zeros and utilizing the default suppression of zeros in the numeric import format made for a high performing data load. Using the All Data Types format for numeric data was very slow in our case as we would have had to first import and then Ignore (using the Data dimension map) all numeric zeros.
In addition to the multi-column setup for this data load, we also used the multi-period setup described in the first example to load multiple years of data in one go, also fully automated using EPMAutomate.