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. 

Essbase data load and the rules files

We can load data in an Essbase database/cube in several ways using the Rules files.

• 1         MAXL script to load data.
• 2        ESSCMD script to load data.
• 3        EAS to load data.

In the examples that I have provided below to load data using Rules Files, some of the names assumed are as follows:

• Application Name: nirmal
• Database name: khoonks
• Username and password: admin and password
• Name of source file: sample_load.txt
• Rules file name: LdData

Loading data using MAXL script

We can run the MAXL script either in interactive mode or File Input mode. 

In this example, we will use the File Input mode in regards to both MAXL and ESSCMD scripts where we create a (MAXL or ESSCMD) file and run it each time we want to load data. 

These can be scheduled as well which makes it easier in the real-life environment.

In order to create a new MAXL file –

• Open a blank text editor, such as Notepad or TextPad or whatever you are used to.
• Make sure that the file has the following content (at least for this example).
• Save it as Load_sample.MXL or Load_Sample.msh.

##### Content of the file for reference #####

set LOGFILE=myscript$\\logs\\Sample_load.log;

set DATAFOLDER=datafolder$\\data_folder;

set ERRORFOLDER=myscripts$\\error_folder;

spool on to "\\$COMPUTERNAME\\$LOGFILE";

/* Login onto the Server  */

login admin password on $COMPUTERNAME;

import database "nirmal"."khoonks" data from local text data_file "\\\\$COMPUTERNAME\\$DATAFOLDER\\sample_load.txt" using server rules_file 'LdData' On Error write to "\\\\$COMPUTERNAME\\$ERRORFOLDER\\sample_load_error.err";

logout;

spool off;

exit;

Basically, what the script does is – Login to Essbase and load data from file “sample_load.txt” using the rules file “LdData” to the database “khoonks” which is in application “nirmal.” Moreover, there are other things such as creating log files and error files, etc.

Now, everytime we run the Load_Sample.msh or mxl file, we load the data in just one click.

In order to run the Load_Sample.msh/mxl file, right-click the file name -> Send To -> ESSMSH -> Click.

Loading data using ESSCMD script.

We can create an ESSCMD file and name it Load_Sample.txt and run it by right-clicking and Send To ESSCMD. It is the same way as we did the MAXL file, except for the file extension and content inside the file. 

We can also open a COMMAND PROMPT and type in –

ESSCMD E:\myscriptFolder\Load_Sample.txt

In case we are running MAXL script from the command prompt, we can type in 

ESSMSH E:\myscriptFolder\Load_Sample.msh

The content of the ESSCMD file can be as follows:

:Create an output log for the Esscmd

OUTPUT 1 "\\Servername\myscripts$\logs\sample_load.txt";

:Login into the Essbase Server

LOGIN "EssbaseServerName" "admin" "password" "nirmal" "khoonks";

:Loads data to khoonks database

IMPORT 3 "\\Servername\myscripts$\datafilefolder\sample_load.txt.txt" 4 "Y" 2 "LdData" "N" "\\Servername\myscripts$\errorfolder\load_error.err";

:Logs out of application

LOGOUT;

:Ends log recording

Output 3;

:Exits Esscmd

EXIT;

The admin console (EAS) to load data.

This is the graphical way of loading data in an Essbase database.

On the EAS enterprise view, expand the branch so that you can see the database where you are loading data.

Right-click the database -> Load Data

The Data Load screen pops up.

Click “Find Data File” to choose the source data file. Browse through either the “Essbase Server” or “File System” to find the data file. Click OK.

Click “Find Rules File” to choose the Rules file that you would like to use to load data. Click OK after you find the Rules file.

Now if you look at the screenshot below, there are other things that you can check, for example, “Abort on Error,” “Error File” etc. Check them as per your requirement.

Click OK.

Playing with Hyperion Planning data form Menus

In the context of Planning data forms, MENUs can be defined as short-cuts of links by using which we can jump quickly to open a URL, business rule, data form, or approval. When we are in a data form, for example, we can right-click and choose a menu item to open another data form to enter data or simply get more information about the data, or launch a calculation, or go to another scenario or version, or simply open a URL – as per the requirement of the scene.

As administrators, we can create MENUs and incorporate them in data forms so that they can be used by a simple right-click.

Provided that you have already opened a Planning application in Workspace –

GOTO Administration -> Manage -> Menus

CREATE

Enter the name of the Menu. I have named it “Sample_Overtime_Detail.”

OK

Now the menu “Sample_Overtime_Detail” is available in the list of Menus that I have in the environment/application.

Click EDIT.

Click the “Add Child” tab.

Enter the following

Menu Item:

Label: What name users see when they right-click.

Type: You can choose from the drop down – URL, data form, business rule, manage approval, menu header, previous form.

Required Parameter: Choose from the drop down.

Under the Properties section:

Choose the folder and name of target data form. In the following example, I have chosen a data form called Update Overtime under the folder Detail Forms.

SAVE

Now, you will see this

To this menu item, which is labeled #1, we can add child or sibling, if need be. However, we will just use this one in this case.

Now that we have created this MENU, we can use it in a data form so that right-clicking it would take the user to the “Update Overtime” data form.

Adding a MENU in a data form

One thing to be noted here is that we created a MENU clicking which would take user directly to a data form named “Update Overtime.” This is what we have pointed to in the above step of creating the MENU “Sample Overtime Detail”

In this step, we will add the menu to a form named “Overtime” so that when the Overtime data form is open, user can right click the menu and go directly to “Update Overtime” data form.

GOTO Administration -> Manage -> Data Forms and Ad Hoc Grids

You will see a list of Data forms.

Check the one named “Overtime” and click EDIT

Click the “Other Options” tab.

On the “Context Menus” section towards the end of the page, you will see a list of MENUS. Choose the one that you need; in this case “Sample_Overtime_Detail” and click the “ADD” arrow.

Now you will see the “Sample_Overtime_Detail” move to the right.

Click SAVE and then Finish.

Now to see how the Menu works as a link, open the “Overtime” data form.

Right-click and you will see “This will take you to details form.” The reason you see “this will take you …” in this case is because while creating the MENU, we had typed in the sentence in the LABEL section. If you would like something else, you can type whatever makes sense or according to company policy, naming convention, etc.

Click “This will take you to details form” and it will take you directly to the “Update Overtime” form.

Creating batches and bursting them in EPM Workspace

In this tutorial, we will create Batches in EPM Workspace which we can schedule to burst as needed. I have divided the tutorial in two parts.

1.       Creating batches.

2.       Scheduling the batches to run

Creating batches

Provided you are logged in to Workspace as an ADMIN user.

File -> New -> Document

Choose the RADIO-BUTTON “Batch Reports for Scheduling.” NEXT (buttom right)

Choose the Financial Reporting Documents (reports) that you would like to include in the batch on the left hand side. You can use SHIFT or CTRL keys to select multiple documents.

When you are done selecting, click the blue arrow (>). The selected reports will move to the “SELECTED ITEMS” window on the right. In this example, there are only Financial reports that are included, however, we can included Books as well. If you would like to include books, click on the “TYPE” drop-down in the above screen and you can choose “Financial Reporting Book.”

Click FINISH. 

SAVE. You can do File -> Save or Save As OR Click the SAVE button.

Scheduling batch(es) to run

Go to the folder where you have the batch file (SAMPLE_BATCH file), in this example. Double-click it.

Edit – Schedule Batch

The following box pops up. Click NEXT

Click NEXT again.

Type in Logon credentials. Click OK

On the following screen, under “Frequency” drop-down, select “Perform Now” or choose the one as per your requirement. Fill other fields accordingly. NEXT.

In the following window, under Destination, you can choose the folder where you would like to output the reports after the burst. If you want to give a path, check the “In another folder” radio button and browse through the folder.

You see the “FILE PERMISSION” tab on the top right corner which can be used to give permission regarding which users are able to view the reports. “EXPORT AS….” Can be chosen as to what types of files you want to create during the burst. Click NEXT.

The following is the last screen that you will see during the process where you can click FINISH in order to finish the process.

In this section, there are fields that you can fill out, such as “If successful, email details to” “in unsuccessful, email details to” etc. You can use these fields as per necessity or your job or organization requirements for notification purposes.

Hyperion Planning Audit Trail

Planning Audit Trail

By turning audit trail on in Hyperion Planning, we, as Planning Administrators, can keep changes to Planning metadata, business rules, data forms, users, workflow, access permissions, etc.

You can take the following steps to turn Planning audit trail on:

Log on to Planning Workspace and open a Planning application.

With planning application open, GOTO Administration > Application > Reports

Click the AUDITING tab.

 Select the artifacts for which you have to turn the audit trail on.

SAVE SELECTIONS

You will see the confirmation pop up regarding “AUDITING OPTION SAVED.”

Click the “X” on the pop up to close it.

At this point, you have completed turning the Audit trail on for the Planning application.

Now, each time there is any change on the artifact that you have turned the Audit trail on for, it can be traced. Planning records all such changes in the HSP_AUDIT_RECORDS table in the relational database. So after turning the audit trail on, if you want to track any changes, you can simply log in to your relational database, such as Oracle or SQL Server or whatever you have and check the HSP_AUDIT_RECORDS table.

This is a sample Oracle database, where I have a look at the HSP_AUDIT_RECORDS table.

Patches and a tattered cloth - my point of view

I remember distinctly when, as a kid while playing I would tear my clothes and my mother would say she would “patch” it up or would tell the local tailor to do so if the cloth had bigger tear. I think we all have worn those patched clothes at some point. As I grew up, Rock and then Grunge music influence us so much that those “patches” became fashion. I remember buying jeans that were all tattered or had a lot of holes on them. If they did not have holes, we would make some ourselves to make ourselves look cool. That is how “patches” had a huge role in fashion and in our growing up.

In fact, patches never seem to go away from our lives, especially if one is working in the field of Information Technology. Let’s take, for example, the life cycle of a software or even the whole operating system. Companies spend millions of dollars in creating software or system and even billions. In the case of Windows 8 operating system, for example, Microsoft has spend around 1.5 billion dollars only for Marketing purposes. So, if we add the overall cost associated to the Operating system, it comes to several billions. Anyways, despite the billions companies spend in software production and the time they spend in building, testing, and quality assurance of the systems, they are not perfect; they are not without bugs or issues because several issues come to the foreground only after consumers use them in the real environment. That is where the patches come into play.

Google Chromecast HDMI Streaming Media Player

After users start using software or operating system of any kind they start finding bugs and errors not found by the company’s testers during the development of the system. They start complaining about the bugs which will act as input for the software development company to counter the bug and they come up with a patch to fix the bug, which they give a nice word or phrase so as to make the consumer think it is something good to have, for example Service Pack 1, Service Pack 2, Visual C++ Redistributable for Visual Studio 2012 Update 4, etc. They sound pretty cool right. Most of them are but simply patches that are meant to patch the tear on your software or operating system. In fact, if you work in the field of IT, you will find the server team applying patches to the Operating system almost every month and/or during major upgrades as such. Now let’s face the EPM patches, which I consider the grand mother of all pathes that at least I know of.

EPM or Enterprise Performance Management is a business intelligence Analytical enterprise system that belongs to Oracle. I use EPM version 11.1.2.1, and there are so many patches that can be attributed to this and other version of EPM that the Windows patches can be considered child’s play. Recently, I had to apply a patch to fix a hole, if you will, in the system, and guess how many patches I have been applying as a part of that supposedly single patch.

EPM version 11.1.2.1 works fine with version 7 of Internet Explorer, however, it is not compatible with IE version 9 – meaning, some of the things work, some may work, and most important things will not work. Some issues, for example, are the missing Hyperion Planning tool bar, disappearance of Business Rules, toggle between Explorer not working etc. Upon, checking with Oracle, they recommended applying a patch 14464109 which would fix the Planning issue in IE9. We were so happy that we would finally resolve the issue by simply applying the patch.

I logged in to the Oracle Support page, searched and finally found (for those of you who have not been to Oracle Support page and other Oracle page, it is like a maze where you would lose yourself trying to find the item you are looking for) the required patch. Happily, I downloaded the .zip file, moved it to the Planning server, unzipped it, and before moving it to the OPatch folder, which is the folder in the EPM Middleware home where we are supposed unzip the patch (or move it already unzipped) before running the script to run the patch, I went through to the READ ME, and then to my horror found out that the patch 14464109 is not easy and on its own. It had several brothers-and-sisters patches that had to be applied before it could be run, some Oracle patches and some Microsoft patches.

So, the READ ME said “You must have applied Microsoft VC 2005 SP1 ATL redistributable pack” before we could even think about applying the EPM patches. And apart from this MS patch, we had to apply the following patch:

      Essbase Administration Services 11.1.2.1.103 Patch 13904635

      Essbase Administration Services 11.1.2.1.103 Patch 13904640

      Oracle Hyperion Shared Services 11.1.2.1.600 Patch 14142678

      Oracle Hyperion Enterprise Performance Management Workspace 11.1.2.1.600 Patch 14119724

Now we are talking about six patches when we thought it was simply one. Moreover, they had to be applied in sequence as some were prerequisites to the others. So the sequence would be:

Microsoft VC 2005 SP1 ATL redistributable pack

14119724

      14142678

13904635

      13904640

      14464109

Now that we had all the files unzipped in place, we started applying those patches in sequence – one at a time, and after we applied the last but one, ie, the 13904640, we found out that the EAS stopped working.

I asked about this issue in OracleForum and the King of the Forum, by whom I mean John Goodwin, suggested me applying one more patch – the Essbase Run Time Client patch # 13904628. Even though, we wanted to follow him as we know he is simply amazing when it comes to EPM (Thanks John), we wanted to make it official so, we opened a ticket with Oracle Support for any suggestion from their side. And guess how many days it took for us to get the same response – 10 freaking days.

Now that we applied the patch, it is working again. Anyways, my point is that if the EPM or any other information technology system comes brand new, however, as times goes on, it gets holes and tears in the form of bugs or errors in order to fix which they come with patches. The patches fix some issues but they come up with more holes, ultimately having some many holes and patches to fix them that they, if we had to visualize would look like a tattered jeans full of holes and patches.