Wednesday, June 20, 2018

(Re)Introducing load rules in OAC

Rules define operations that Essbase performs on data values or on dimensions and members when it processes a data source. Use rules to map data values to an Essbase database or to map dimensions and members to an Essbase outline.

 In other words, rules file is the small out of the box ETL utility to extract, transform and load the data into Essbase. You can either build/edit dimension or load data into Essbase using these rules.

In this blog, we will convert the flat data into Essbase dimension called "Year". There is no need to create this empty dimension in the outline. Rules will take care of creating it too.

Rules are available in Database inspector under Scripts (Applications -> Expand - > Database -> Actions -> Inspect - > Scripts)

Click Create and choose Dimension Build (Regular). New browser tab will be opened to edit the rules.

 In the New Rule popup, lets name it "year", select a file for preview, uncheck the headers as there is no header row in our text file and click proceed.
The data is delimited by Tab here but it could be anything like comma, space, ...

 Rules editor will preview the data like below

The top toolbar will help us to define global properties, dimension properties and data source specifications.

 The toolbar above the grid will help us to define the field or column properties.

So in the global toolbar, lets create a dimension called "Year".  In the popup, you can use the left hand text box to type new dimension name or search existing. You can edit properties for the dimensions but for this use-case, lets keep the default.

Now, tag each column as generation of the "Year" dimension.

Save and Close.

In the database inspector, refresh to see the new rules. Cool thing is you can export the rules into JSON file. Select a rules file -> actions -> Export

Close the database inspector and go to jobs. To load data, you should upload the year.txt file in the database directory. (Files -> Applications -> <app> -> <DB> -> upload files )

In Jobs page , New -> Build Dimension
Select application, database, rules file and data file.

Click OK and wait for the jobs to complete.

Open outline and verify the dimensions. In database inspector, you can also verify the generation names. 

How cool is this?

In next few tutorials, we will talk about loading data and some advance concepts and also brand new data source based rules. Stay tuned...


  1. Is it possible to split the file and build two dimensions using 1 load rule?
    2018 will go to Year dimension. rest go to period dimension.

    1. Yes, it is possible. Add as many dimension as you want in the dimensions tab and tag the columns/fields appropriately.

    2. Thanks for the reply. Will try out