My first Essbase data load Rules file

In my previous blog, I showed how to load metadata into Essbase. Let's load data now.

You can load data with and without rules file. You need a rules file if the data source does not map perfectly to the database. If a data source contains all of the information required to load the data values in it into the database, you can load the data source directly in a free-form data load. To load a data value successfully, Essbase must encounter one member from each dimension before encountering the data value. If Essbase encounters a data value before a member of each dimension is specified, it stops loading the data source. 

💀 Please Note: If you are performing a data load without a rules file, when Essbase encounters an invalid member field, it stops the data load. Essbase loads all fields read before the invalid field into the database, resulting in a partial load of the data values.

To map perfectly, a data source must contain all of the following and nothing else:

• One or more valid members from each dimension. A member name must be enclosed in quotation marks if it contains any of the following: Spaces, numeric characters, dashes, plus signs, ampersands.
• One or more valid data values. If the data source contains blank fields for data values, replace the blank fields with #MI or #MISSING. Otherwise, the data values may not load correctly.
• Valid delimiters.

An incorrectly formatted data source will not load. You can edit the data source using a text editor and fix the problem. If you must perform many edits (such as moving several fields and records), consider using a rules file to load the data source.

Let's see how to load data with a rules file.

First, I have created a data load file. It's a pipe delimited text file that looks like below:

Let's create the rules file now. To create a rules file, login to EAS console, go to the required application, right click on Database and then select Create and the click on Rules file (Rules are files are specific to database):

Alternatively, you 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:

Now I will open the data file that I want to load. Go to File then Open data file.

I browsed and selected my data file. 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.

Click Options then Data source properties.

Go to the Delimiter tab and specify pipe (|) as the delimiter. Under Delimiter, choose the radio-button for Custom and enter the pipe symbol.

Click on OK. Now you will see proper formatting of your data file.

One very important thing here in creating the Rules file is that if we look at the above screenshot, we can see that Row1 has the heading. Therefore, we want to tell Essbase to load data only starting Row2, and it can be achieved by skipping the first two lines during data load. Click Options then Data source properties.

Go to the Header tab. Make sure Number of lines to skip is set to 1. 

Click on OK.

Remember: Since we are creating Rules file to load data, make sure to click the Data load fields icon.

During the Rules file creation process, we can choose to show or hide the RAW data. This step is optional and for convenience. If you want to hide RAW data, go to View then Raw data.

Next, we need to associate the outline. Click Options then Associate outline.

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

Next is assigning Field Properties. This is where we define which field corresponds to what dimension.

Highlight Field1 and then go to Field then Properties.

Click the Data Load Properties tab.

Since my first field in the data file is Version, I will double click on the Version dimension. It will look like below:

Click on Next >>.

My second field in the file is Scenario, so I will double click on Scenario.

Follow this process to define the fields. When it comes to the fields that correspond to Jan, Feb, Mar, etc, we can expand the Period dimension and choose the right members to correspond to the months.

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

Now go to Options then Data Load Settings.

Under Data Load Values, we can choose to Overwrite existing values or Add to existing values or Subtract from existing values. In this case, I will choose the Overwrite existing values option.

Click on OK.

Now, let's validate the Rule to ensure it does not have any error. Go to Options then Validate.

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

Now, save the Rules file. Go to File and then Save.

Now right click on the database and click on Load data:

Browse and select the data and the rule files:

Click on OK.

You should get a Success message like this:

Your data is loaded.

You can check the same from SmartView.