Data Loading through Rules File

Now that we have created dimensions in our outline through Dimension Build Rules and we have set Account & Period as Dense dimension and rest of dimensions as Sparse.

So let’s make our data load process simpler to understand by loading data only into single block.

(I believe you still remember my earlier post which explain when/how block is created – so with that you will be able to load data only single block by selecting which block you wanted to load)

Here based on our outline we have selected a block as below.

And let’s try loading this data into Essbase through Rule File and our data file would be something like below.

Need not change load method (dimension build/data load) by default it will be data load method.

Now map each column of your data file to dimensions in outline.

Make number of lines to Skip as 1 ( as I have period names/header as first row)

Map data columns to specific members in outline(In case of multi fields of data).

For Ex: we are loading data for periods Jan,Feb,Mar,Apr,May,June  etc.., map those columns to those members “Jan”,”Feb”,”Mar”,”Apr”,”May” etc  respectively and rest of the fields with dimension name.

Let’s say I have defined rule file as below and what would happen if I try to load data through this rule file which I am able to validate successfully.

You can see I have mapped fields with specific member name

“FY14”,”Andhra Pradesh”,”Final”,”Actual” from each of the dimensions as they are the constant members across the whole data file.

And mapped account dimension members with “Accounts” as the account members would change at each row. And finally as I am loading data in periods I have mapped those fields with specific periods.

Save the rule file and try loading data .

So as we have same values being repeated at every row , I have made them declared at Header Definition and ignore those fields which has same values repeated at each row.

Options à Data Load Settings à Header Definition

This Header definition will act as a global parameters for your data source.

Every period there would be a data load that would be happened against same actual,version,year dimension members in that case you can make those members as constant or global members for all data loads by defining them in Header Definition as above.

Now rule file will look like below.

Validated successfully and save it again.

Now try to load data using this rule file after you have defined those members in Header Definition.

Ta Da J Able to load the data finally.

Now other way of creating rule file to load this data is Map each field with Dimension Name except for data field with those specific members. And Rule file will look like below

So except for Data column/field all other fields has to be mapped with Dimension Name and only data fields has to be mapped with specific members of dimension or with data field incase if we have only single data column.

Ex: JanàActualàFY14àUSàColaàFinal=100

Mapping would be like “Period”,”Scenario”,”Year”,”Entity”,”Product”,”Version”,*Data*

You can also define with specific members in this case as we have only 1 row.

“Final”,“FY14”,”Actual”,”Andhra Pradesh”,”Revenue1”,”Jan”,*Data* -- which would work but it will give warning as below in error file.

But data would have got loaded into system as it’s a warning we got.

So finally we should remember that always there will be only 1 data field property set and if you are loading data against a single member for list of accounts/members then that field should be set with that specific member.

Prepare your data file in such a way that each block is touched only once.(Data file we used for all periods is an example which shows i have touched and loaded data into only 1 block.)

Define/Place the dimensions in header definition if members in the field are repeating/same across whole data file. Except for data field do not map the field to specific members .