OBIEE 12c: The Essbase Acceleration Wizard & Loading Essbase – Part 2
This is the second part of a blog looking at using the OBIEE’s Essbase BI Acceleration Wizard, but using it in a way that enables automated data loads into the Essbase cube to be built to keep the data in the cube up to date and in-line with the data source.
In the first of this blog, found here: http://www.redstk.com/obiee-12c-the-essbase-bi-acceleration-wizard-loading-essbase/ , I covered how to combine the repository configuration with using the Acceleration wizard to create the Essbase cube and make it usable for reporting. In this second part I will show how a data load process can be scripted.
At the end of the first part we had an Essbase cube called Essbase_Sales, visible in the operating system (Linux in my case) in the following directory:
So, let’s take a quick look at what’s actually in this cube directory. Those of you with Essbase knowledge should recognise this.
Essbase cubes are actually defined as Applications and Databases, with each Application having one or more databases, depending on the type of application. For cubes built with the Acceleration wizard there is one database per application, but the database name is different to the application name.
The application name is this case is Essbase_Sales, what we called it in the Acceleration Wizard, however the database is called RevenueFacts – the wizard names this after the name of the fact table in the repository from which the measures were selected.
So in this directory we have three application files, Essbase_Sales.apb, .app and .msx. The .msx file is a message file which can be viewed and is occasionally useful for debugging purposes. The apb and app files are binary and not viewable.
The metadata and default directories contain files storing the data and internal details of the database. Files in neither are directly viewable, but the default directory is useful – if it contains no files then you know there is no data in the cube.
The temp and log directories do what they say, store temporary and log files. Again, these are internal files and not directly viewable.
Lastly, we have the database directory. As mentioned above, the name of this is dependent on the name of the fact table sourcing the measures, in my case it’s called RevenueFacts. Let’s take a look in here.
These are the files that define the database and some include the code required to perform data loads. Only the two XML files are viewable, the rest are all binary. The two XML files contain the details from the Acceleration wizard when the cube was built and deployed and contain the details to re-load and re-deploy the cube manually via the Acceleration wizard.
The .rul files (known as rule files) contain the specific information required to load data into the cube. There are two types of rule files, data and dimensions. Dimensions rule files load dimension details (dimension members, hierarchies, attributes) into the cube and Data rules files obviously load the data.
The rules files are all named after the Dimension or Fact table they are loading. So in the above directory listing, CustomerDim.rul, ProductDim.rul, OfficeDim_OfficeDim_H0.rul and TimeDim_TimeDim_H0.rul all load dimension details into the cube. Office and Time have multiple hierarchies in my data warehouse, so the H0 reference is to indicate which hierarchy.
Measures.rul loads an additional dimension. Essbase stores the name of each Measure as a dimension too. In my warehouse these are fields on the fact table, so the Measure dimension can be thought of as the field names. Measures is the one dimension we cannot load – it was defined when the cube was deployed and cannot be changed without re-deploying.
RevenueFacts.rul loads the actual data into the cube.
These will we use shortly.
The other files and directories are internal files and under OBIEE 12c we have no direct access to them. If you were using a standard Essbase implementation you would have the client tools to allow you to administer and configure these files to a high degree, but these have been removed under OBIEE.
So we have access to the rule files to allow us to load data into our cube, but how to we do that without these client tools? The answer is MAXL. This is the scripting language of Essbase that provides command level access to the cubes and their components and its full implementation is included with Essbase under OBIEE 12c.
I’m not going to cover all of MAXL in this blog, there is way too much! I’m going to cover the commands required to perform a data load, the following URL links to the technical reference for Essbase, which includes MAXL:
MAXL is a operating system command level tool that is initiated via a script – startMAXL.sh (.bat in windows). This can be found in the <obiee_home>/user_projects/domains/bi/bitools/bin directory.
As it’s a bit of a pain to keep referring to the directory every time I need to start MAXL so I usually copy the startMAXL.sh to a working directory, into which I will also create the additional script files needed to automate the load process.
Note that there is a second, different, version of startMAXL.sh under <obiee_home>obiee/bi/products/Essbase/EssbaseServer/bin. Do not use this one. It looks like the original full Essbase version which hasn’t been modified to work properly with the cut-down Essbase implementation under OBIEE.
You can just run startMAXL.sh (by running ./startMAXL.sh) to directly access the command level:
The first task upon starting MAXL is always to login and you so this with the OBIEE administrator user-id and password – usually weblogic. The format of the login command is:
Login userid identified by password;
Note that all MAXL statements must end with a semi-colon.
The command exit; will close down the MAXL session.
If you can start up MAXL and login successfully then you are ready to continue.
The startMAXL.sh script can accept a filename as a parameter, so we can create a text file containing a list of commands to execute. startMAXL.sh can be called from another script, so we have all we need to automate the running of MAXL commands. But lets not get too far ahead of ourselves just yet.
When loading data into an Essbase cube it is important to remember you need to load the dimensions as a well as the data. If the latest data set contains records for a new product, you need that new product loaded into the cube before you load the data. The data rules file will reject any records against dimension members it does not recognise.
To load dimension and data into a cube you use the MAXL command ‘import’ The format is slightly different for Dimensions and Data. Here is the command to load the first dimension, Customer:
import database Essbase_Sales.RevenueFacts dimensions connect as 'weblogic' identified by 'Password1' using rules_file '/u01/app/obiee/user_projects/domains/bi/bidata/components/essbase/app/Essbase_Sales/RevenueFacts/CustomerDim.rul' preserve all data on error append to 'RevenueFacts_CustomerDim.err';
The first line starts with the import command the continues to say we are going to import dimension details into the database Essbase_Sales.RevenueFacts.
The second line starts with the connection details, remember it’s extracting the data from the OBIEE repository, not directly from the data warehouse or any other data source. The ‘using rules_file’ parameter is then followed by the full path to the rules file for the customer dimension.
The final line has two parameters – ‘preserve all data’ simply means don’t discard the data before starting the load and the ‘on error append to’ parameter specifies the name of a file to write any error messages to – always a good idea!
The final line is finished with the semi-colon.
We can then create a text file to hold our MAXL commands, which I’m going to call load_RevenueFacts.mxl:
Now we can replicate this import command for the other dimensions in our cube, simply changing the name of the rules file and error file (you can use the same error file for all dimensions, but I prefer to keep them separate – makes it easier to identify exactly which import command had issues).
Now we need the import command for the data. Here is the command I’m using:
import database Essbase_Sales.RevenueFacts data connect as 'weblogic' identified by 'Password1' using rules_file '/u01/app/obiee/user_projects/domains/bi/bidata/components/essbase/app/Essbase_Sales/RevenueFacts/RevenueFacts.rul' on error append to 'RevenueFacts.err';
Similar to the dimension import – the first line contains the word data rather than dimension, the second line points to the data rules file, RevenueFacts.rul and the last line does not need the ‘preserve all data’ parameter.
So, my whole MAXL script file to load all dimensions and data looks like this:
Before we can run this load script, there are a couple of additional issues to address. Firstly, the Essbase cubes created by the Acceleration wizard are in Unicode mode. There is an advanced option to turn this off, but I wouldn’t recommend playing with this unless you are experienced with Essbase. What this means is that we need to run a utility to ‘mark’ our script file as UTF8 format. To do this we need to call a utility called ESSUTF8, which is located in the Essbase bin directory. Before running this we also need to set an environment variable called ESSBASEPATH. Substitute your install paths and run the following commands:
export ESSBASEPATH=/u01/app/obiee/bi/products/Essbase/EssbaseServer export LD_LIBRARY_PATH=/u01/app/obiee/bi/products/Essbase/EssbaseServer/bin /u01/app/obiee/bi/products/Essbase/EssbaseServer/bin/ESSUTF8 -b -c load_RevenueFacts.mxl
What this does is add a few special characters to the start of the file:
Secondly there is a configuration error with OBIEE which means that a java library file required to load Essbase cubes cannot be found. This generates the following error:
java.lang.NoClassDefFoundError: Could not initialize class oracle.epm.jagent.logging.LoggerHelper
The resolution is to add the path of the required file (jagent.jar) to the LD_LIBRARY_PATH environment variable. You will need to perform a search to locate this file, in my case it is in the directory /u01/app/obiee/user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/ESSBASE/4ezykt/APP-INF/lib
Use the export command to set the variable (use set under windows):
Note: if your LD_LIBRARY_PATH variable is already set, then add this path to it rather than replacing it.
We are now ready to run the load script:
You will see logging being displayed on the screen as this runs. Rather than include all of it here, I’ll show the output for one of the dimensions and the data load.
My customer dimension load looks like this:
OK/INFO - 1053012 - Object [RevenueFacts] is locked by user [weblogic]. OK/INFO - 1053012 - Object  is locked by user [weblogic]. OK/INFO - 1021085 - UTF16 to UTF8 conversion is forced for fetching records using ODBC! ConvertUTF16toUTF8 option is set in [esssql.cfg]. OK/INFO - 1021003 - Connection String for [OBI_BUSINESS_MODEL] is generated. OK/INFO - 1021041 - Connection String is [DSN=AnalyticsWeb;UID=...;PWD=...;]. OK/INFO - 1021006 - SELECT Statement [SELECT_BUSINESS_MODEL "Sales Data_Essbase"."Customer"."Customer ID", "Sales Data_Essbase"."Customer"."ADDRESS_KEY", "Sales Data_Essbase"."Customer"."Birth Date", "Sales Data_Essbase"."Customer"."CRDT_RATE", "Sales Data_Essbase"."Customer"."FST_CONTACT_DT", "Sales Data_Essbase"."Customer"."Gender", "Sales Data_Essbase"."Customer"."INCOME_LVL", "Sales Data_Essbase"."Customer"."LST_ORDER_DT", "Sales Data_Essbase"."Customer"."LTV", "Sales Data_Essbase"."Customer"."Marital Status", "Sales Data_Essbase"."Custom] is generated. OK/INFO - 1021043 - Connection has been established. OK/INFO - 1021044 - Starting to execute query. OK/INFO - 1021045 - Finished executing query, and started to fetch records. OK/INFO - 1021000 - Connection With SQL Database Server is Established. OK/INFO - 1021047 - Finished fetching data. OK/INFO - 1021002 - SQL Connection is Freed. OK/INFO - 1007132 - Building Dimensions Elapsed Time : [4.16] seconds. OK/INFO - 1053013 - Object  unlocked by user [weblogic]. OK/INFO - 1019017 - Reading Parameters For Database [Drxxxxxx]. OK/INFO - 1025029 - The IMPLIED_SHARE setting in essbase.cfg is ON. OK/INFO - 1025035 - The implied share setting for the database is forced to OFF. OK/INFO - 1007043 - Declared Dimension Sizes = [5 1002 41 42 2520 1001 608 21 657 3 10 221 966 5 1001 101 6 528 8 5 2 6 12 9 2 ]. OK/INFO - 1007042 - Actual Dimension Sizes = [4 1002 41 42 2520 1001 608 21 657 3 10 221 966 5 1001 101 6 528 8 5 2 6 12 9 1 ]. OK/INFO - 1200551 - Allocated TRIGMAXMEMSIZE:  Bytes. . OK/INFO - 1019010 - Writing Application Definition For [Essbase_Sales]. OK/INFO - 1019011 - Writing Database Definition For [RevenueFacts]. OK/INFO - 1019022 - Writing Database Mapping For [Essbase_Sales]. OK/INFO - 1007120 - Loading New Outline for Database [RevenueFacts] Succeeded. OK/INFO - 1024033 - Missing Database Config File [/u01/app/obiee/user_projects/domains/bi/bidata/components/essbase/app/Essbase_Sales/RevenueFacts/RevenueFacts.cfg], Query logging disabled. OK/INFO - 1007067 - Total Restructure Elapsed Time : [0.61] seconds. OK/INFO - 1053013 - Object [RevenueFacts] unlocked by user [weblogic]. OK/INFO - 1241113 - Database import completed ['Essbase_Sales'.'RevenueFacts'].
Seven lines in you can see the Select command that was sent to the BI Server, a few lines latyer you can see it took all of 4.16 seconds to load the dimension and at the end the dimension import was completed with no errors.
My data load log looks like this:
OK/INFO - 1053012 - Object  is locked by user [weblogic]. OK/INFO - 1019061 - Reading Rule SQL Information For Database [RevenueFacts]. OK/INFO - 1019025 - Reading Rules From Rule Object For Database [RevenueFacts]. OK/INFO - 1021085 - UTF16 to UTF8 conversion is forced for fetching records using ODBC! ConvertUTF16toUTF8 option is set in [esssql.cfg]. OK/INFO - 1021003 - Connection String for [OBI_BUSINESS_MODEL] is generated. OK/INFO - 1021041 - Connection String is [DSN=AnalyticsWeb;UID=...;PWD=...;]. OK/INFO - 1021006 - SELECT Statement [SELECT_BUSINESS_MODEL "Sales Data_Essbase"."Customer"."Customer ID", "Sales Data_Essbase"."Office"."OFFICE_KEY", "Sales Data_Essbase"."Product"."PROD_KEY", "Sales Data_Essbase"."Time"."Date", "Sales Data_Essbase"."Revenue Facts"."Fixed Costs", "Sales Data_Essbase"."Revenue Facts"."Variable Costs", "Sales Data_Essbase"."Revenue Facts"."Revenue", "Sales Data_Essbase"."Revenue Facts"."Units" FROM "Sales Data_Essbase"."Revenue Facts", "Sales Data_Essbase"."Customer", "Sales Data_Essbase"."Product", "Sales Dat] is generated. OK/INFO - 1021043 - Connection has been established. OK/INFO - 1021044 - Starting to execute query. OK/INFO - 1021045 - Finished executing query, and started to fetch records. OK/INFO - 1021000 - Connection With SQL Database Server is Established. OK/INFO - 1003040 - Parallel dataload enabled:  block prepare threads,  block write threads.. OK/INFO - 1021047 - Finished fetching data. OK/INFO - 1021002 - SQL Connection is Freed. OK/INFO - 1003052 - Data Load Elapsed Time for [SQL] : [3.47] seconds. OK/INFO - 1053013 - Object  unlocked by user [weblogic]. OK/INFO - 1241113 - Database import completed ['Essbase_Sales'.'RevenueFacts'].
Again, you can see the Select statement and the load took 3.47 seconds and there were no errors. Obviously, I’m using a very small cube in this example, your cube will take longer depending on just how much data there is and the size & number of the dimensions.
Now that we have a working load script we can embed this within a new script file (or bat file in windows) and schedule it to run at an appropriate time & frequency. A simple script file would look like this:
As this is a standard Linux script you are free to enhance this with logging and any other scripting standards you require. To log the output of the startMAXL.sh command simply add a redirect on the end. Here is an example script with logging:
logfile="/u01/app/scripts/logs/RevenueFacts_Load_"$(date +%y%m%d)_$(date +%H%M%S)".log" export LD_LIBRARY_PATH=/u01/app/obiee/user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/ESSBASE/4ezykt/APP-INF/lib echo "Starting RevenueFact Essbase Cube Load at "$(date +%T) >>$logfile ./startMAXL.sh load_RevenueFacts.mxl >>$logfile echo "Completed RevenueFact Essbase Cube Load at "$(date +%T) >>$logfile
Right, so the proof of the pudding and all that…I’ve now run a load in my data warehouse and loaded in another year’s worth of data – 2013. If I run my report in OBIEE I can’t see the 2013 data:
This is because OBIEE is using the Essbase cube as the source, not the warehouse and it doesn’t try to work out what’s the most up to date, or what has the best data. The logic in the BMM layer simply says if the user is accessing this data over these dimensions then go to Essbase for the data rather than the warehouse. This of course underlines the whole importance of automating the loading of the Essbase cubes created by the Acceleration wizard.
So, I run my new Essbase load script, and a few minutes later I can simply click refresh in the report and now the data in Essbase is up to date and 2013 data is appearing correctly:
So, there we have it, an Essbase cube created by the Essbase BI Acceleration wizard and now loaded via a custom-built load script. Not quite as simple as it should have been, but it is a usable option if you do want to benefit from the performance Essbase brings to reporting in OBIEE.