My first metadata build rule in Essbase

In my previous blog, I explained the detailed steps to create a Planning application in EPM 11.2.2. In this blog, I will explore the steps to build metadata.

Building dimensions is the process of loading dimensions and members to an Essbase database outline by using a data source and a rules file. You can also use Outline Editor to add dimensions and members manually.

🔎 What is s Rules file?

• 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. 
• Rules are stored in rules files. A rules file defines which build method to use, whether data values or members are sorted or are in random order, and how to transform data values or members before loading them. It is best to create a separate rules file for each dimension.
• Essbase reads the data values or members in the data source, changes them based on the rules in the rules file, and loads the changed data values into the database and the changed members into the outline. Essbase does not change the data source. You can reuse a rules file with any data source that requires the same set of rules.

Let’s start building dimensions through rules. I will build Account dimension through Generation Reference Method (top-down approach). For more details on generations, please check here.

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):

Once you click on Create > Rules file, you will get below screen:

By default Data loading option would be selected , but as we are building dimension, select Dimension build fields.

I prepared a data source file (as shown below), created in terms of generations. It's a simple text file with pipe delimited fields. 

Lets continue creating the rules file. Open the above data file.

When you browse and open your data source file, you will see the content of your data source file as below:

By default, delimiter will be Tab. I will need to change it to pipe (|).

Go to Options and Data source properties.

Under Delimeter tab, I will choose Custom and mention pipe as my delimeter.

When you click OK, you will see your data source gets separated with columns as below:

For me, I already have the Accounts dimension created. If you don’t have Accounts dimension in the outline, you have to define the dimension as you are creating through rules file.

Go to Options and then Dimension build settings:

Go to Dimension Definition.

You will get below dimension build setting window. Select Rules file and give dimension name which you are building.

In my case, Accounts dimension is already there, hence its listed under Outline as shown below. We need to double click it to make as chosen dimension which we are building. It will look like below:

Click on OK.

Now that we have defined the dimension that we are building, let's define the method of dimension building.

Go to Options and then Dimension build settings:

Go to Dimension Build Settings tab and double click dimension that you are going to build and select Use generation references as Build method.

Click on OK.

Once this is done, we need to set the field properties of each field so that Essbase knows what they mean. Highlight each column (Field) as below and set their properties. Select the first field and then click on Properties.

You will get Field properties dialogue box. Go to Dimension Build Properties tab. Double click on Account dimension under Dimension.

Double click on Generation under Type and give the number as 1.

Click Next and select Generation and set the subsequent generation number for other remaining fields.

Once those selections are done, click on OK. It will look something like below:

In this case, as we are building Accounts dimension through header definition option and we have already defined that, we need not consider the first field (which is dimension name). We need to ignore this field by selecting the Field then Properties.

Then Dimension Build Properties tab and select Ignore field during dimension build.

Click OK.

The first field will be grayed out. It will look something like below:

We don’t need the first row (Gen1,Gen2,Gen3,Gen4) as it is not part of the outline requirement. We need to get rid of it. There is a feature of skipping lines in rules file which is available under Options then Data Source Properties.

Then Header tab. Give Number of lines to skip as 1 since we wish to skip only the 1st line.

Time to validate our Rules file. Go to Options then Validate.

You should get a positive message:

Save the Rules file.

Create the dimension with your rule file by below process. Open your outline in edit mode. Then go to Outline then Update Outline.

Select Type as Data Files. Then browse and select the data and the rules file.

Click on OK.

You will get a confirmation once the loading is complete.

Don’t forget to Save your outline.

Review your outline.

I have shown you one method of building metadata. You can do the same by the below methods as well:

• Level References
• Parent Child References

Please note: If you have an Essbase application, then you can load metadata like this. However if it's a Planning application, then you need to load metadata through Planning and then refresh to Essbase.

Next blog will be on loading data to Essbase.