Essbase data load Rules file - how to create

In Essbase, we use rules to map data values to an Essbase database or map dimensions and members to Essbase outline. Rules are stored in rules files and have extension .RUL.

We use Rules Files to define which build method to use during dimension build, how to transform data values before loading them as the source file may not exactly be in the format Essbase understands, what to load and what to avoid loading, etc.

This is what the Rules Files branch would look like in EAS Enterprise View.

We can follow the following process to build Rules files to load data in Essbase. We can use Rules to build dimension as well. The process of creating Rules files to load data and build dimension are almost the same except for some differences in the process.

In the picture of EAS enterprise view illustrated above, we already see the Rules Files branch. In this case we can simply right-click on the “Rules Files” and -> “Create Rules File”

In case, it is the first time we are creating a Rules File or if we do not see the Rules Files branch in the Enterprise View, we can use the “File -> Editors -> Data Prep Editor” option to open up the Data Prep Editor.

This is what the Data Prep Editor looks like

File -> Open Data File

Open the data file that you want to load or a sample file that is a replication of the load file.

In this example, I have chosen a .TXT file named “sample_load_file.txt” that I have on my desktop.

This is what it looks like. The top section is RAW data whereas as the bottom section has everything in Field1. So, we will work on this file so that we can create a Rule that tells Essbase what and how to load the data.

One very important thing here in creating the Rules file is that if we look at the bottom section of the above illustration, we can see that Row1 is blank, and Row2 has the heading. Therefore, we want to tell Essbase to load data only starting Row3, and it can be achieved by skipping the first two lines during data load.

Click “Options -> Data Source Properties

Under Delimiter, choose the RADIO-BUTTON for Custom and enter the Pipe Symbol.

Click the “Header” tab.

Make sure “Number of lines to skip” is set to “2.” OK

This is what we will see now.

During the Rules file creation process, we can choose to “show” or “hide” the RAW data. So, this step is optional. If you want to hide RAW data – View -> Raw Data.

Since we are creating Rules file to load data, make sure to click the “Data Load Fields” icon.

Associate Outline: Options -> Associate Outline

Choose the outline with which the Rules is to be associated with.

Assigning Field Properties: This is where we define which field corresponds to what dimension.

Highlight Field1, and “Field -> Properties”

Click the “Data Load Properties” tab.

Double click “Versions” as the first column is “Versions” dimension. This is what it looks like.

Next -> “Scenarios”

Next -> “Center”

Follow this process to define the Fields.

When it comes to the fields that correspond to Jan, Feb, Mar, etc, we can expand the “Time Periods” dimension and choose the right members to correspond to the months.

After defining the last field, we can click OK to have those field properties set.

Now, Options -> Data Load Settings.

Under “Data Values” we can choose to “Overwrite existing values” or “Add to existing values” or “Subtract from existing values” – whatever we would like to do. In this case, we will choose the “Overwrite existing values” option. OK

Now, let us validate the Rule to ensure it does not have any error.

Options -> Validate

The follow pop-up notifies that there are no errors.

Now we can save the Rules file.

File -> Save

Essbase gives the option of saving the file either in the Essbase server or File System.

Make sure the .RUL files is not more than 8 letters long.

Save the .RUL file as per organization guidelines or whatever makes sense.

Now that we have created the data load RULES file, we can use this file each time we want to load similar type of data to the database.

To learn more about loading data to Essbase cube using Rules file, you can visit my blog about Essbase Data Load 
You can also visit Oracle's Rules files page to learn more about Rules Files.