Essbase : Multiple SQL Data Loads in Parallel to Aggregate Storage Databases

Most of the time, loading Essbase data from SQL Server  in serial mode works just fine:

alter database EssApp.EssDb initialize load_buffer with buffer_id 1;

import database EssApp.EssDb data

connect as SQLUser identified by SQLPswd

using server rules_file   'Load.rul' to load_buffer with buffer_id 1

on error write to 'errorfile.err';

import database ASOsamp.Sample data from load_buffer with buffer_id 1;

But if you have a lot of SQL data loads that take a long time, you might run into complaints from your client to speed of the data load process.  Well, your in luck!

Essbase Member Properties for Dimension Builds

When building an Essbase dimension build file, 99% of the time, I will build it using a Parent / Child relationship.  I feel its the easiest to maintain and shared members are added automatically.  Here's a simple example for the Scenario dimension:

Scenario|Actual|+|
Scenario|Budget|~|
Scenario|Forecast|~|
Scenario|Revised Forecast|~|

In case you were wondering what the + and ~ mean or need more properties, here's a good list for you:

Essbase Member Properties for Aggregate Storage Database

% Set consolidation to express as a percentage of current total.
* Set consolidation to multiply by the current total
+ Set consolidation to add to the current total

Essbase Security Filter Samples via Maxl

Here are some examples of other types of filters you may need to create:


/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget"';


/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 MARKET */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @LEVMBRS("Market",0)';

Essbase Security Maxl Script

Here is a simple example of how to maintain Essbase security in a maxl statement:

spool on to Security.log;
login admin password on localhost;
set timestamp on;


/*-------------------------------------------------------------------------------------------------------------*/
/* READ ACCESS TO WEST AND ITS DESCENDANTS ONLY */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'Read_WestMarket_Filter' READ on '@IDESCENDANTS("West")';


/*-------------------------------------------------------------------------------------------------------------*/
/* CREATE GROUP(S) */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace group 'Read_WestMarket_Group';


/*-------------------------------------------------------------------------------------------------------------*/
/* GRANT FILTER ACCESS TO GROUP(S) */
/*-------------------------------------------------------------------------------------------------------------*/
grant filter TestApp.TestDb.'Read_WestMarket_Filter' to 'Read_WestMarket_Group';


/*-------------------------------------------------------------------------------------------------------------*/
/* ADD USERS TO GROUP(S) */
/*-------------------------------------------------------------------------------------------------------------*/
alter user testuser1 add to group 'Read_WestMarket_Group';


logout;
spool off;
exit;

Create all of your filters in one section, then create all of your groups, then grant the filters to the groups and finally put the users in those groups.

Essbase - Export Data (Post 2 of 3)

Report Scripts:

* You can export data using a report script. Prior to Version 9.3.1, this was the only means of getting a subset of data out of an Essbase database. Not a big fan of them, but if you are running a version prior to 9.3.1, then you need to use them.


/*--------------------------------------------------------------------------*/
/* EXPORT DATA USING REPORT SCRIPT */
/*--------------------------------------------------------------------------*/

export database ASOsamp.Sample using report_file "'$ARBORPATH/app/ASOsamp/Sample/XptData.rep'" to data_file 'C:\Temp\ReportExport.txt';

Essbase - Export Data (Post 1 of 3)

There are a couple ways to export data from an Essbase cube, the old-school way and now in version 9.3, there is the calculation function "DATAEXPORT". Let's discuss in detail:

Export Data (the capability that has been around all along):

* For Block Storage Applications, you can export all data, level-0 data or input-level data. The 'all data' option is nice, but that assumes your hierarchies don't change or level-0 member doesn't get remapped to a new Level-1 member. So, if you hierarchies don't change and it takes too long to aggregate (CALCALL) your cube, then the 'all data' option is the way to go. If your hierarchies change often, then you need your level-0 option (this is the option I prefer to use most of the time as the hierarchies will change at some point in time and its easier to reload based on level-0 then all data. I rarely use the input-level data, as my input data is usually my level-0 data.

* You can export the data into column format or non column format. This option works well depending on what you are trying to accomplish. Column format files will be larger in size than non column format files, but you can use a load rule to load that data into another cube. Say you have to make some sort of data manipulation to get that data into your new cube, it would be advantageous to use a load rule and perform your data manipulation. If you want to simply update your hierarchies, then you can use the non-column format option (which is not pretty), but the file size is smaller. Smaller files size will mean faster export time and faster import time, but you can't you use a load rule for any data manipulation.

* You can export to multiple files at one time, like this:


/*--------------------------------------------------------------------------*/
/* EXPORT DATABASE TO 8 FILES */
/*--------------------------------------------------------------------------*/
export database Sample.Basic level0 data in columns
to data_file 'C:\Temp\ExportData1.0.Txt',
'C:\Temp\ExportData2.0.Txt',
'C:\Temp\ExportData3.0.Txt',
'C:\Temp\ExportData4.0.Txt',
'C:\Temp\ExportData5.0.Txt',
'C:\Temp\ExportData6.0.Txt',
'C:\Temp\ExportData7.0.Txt',
'C:\Temp\ExportData8.0.Txt';

* When you run the level-0 export, you will get upper level data on all dense dimensions, don't be alarmed, its just how the functionality works, even if the member properties are set as dynamic calcs

* The export function will NOT export any values for Dynamic Calc members, only Stored members (unless its a dense dimension)

* In version 9 (not sure which release), but users are able to retrieve on the database during the export process. Previous versions used to lock the database from any Read-Only action.

Additional Maxl Samples

/*--------------------------------------------------------------------------*/
/* SET VARIABLE FOR CURRENTMONTH */
/*--------------------------------------------------------------------------*/
alter database Basic.Sample set variable "CurrentMonth" "July";


/*--------------------------------------------------------------------------*/
/* EXPORT DATA USING REPORT SCRIPT */
/*--------------------------------------------------------------------------*/
export database ASOsamp.Sample using report_file "'$ARBORPATH/app/ASOsamp/Sample/XptData.rep'" to data_file 'C:\Temp\ReportExport.txt';


/*--------------------------------------------------------------------------*/
/* EXECUTE CALCULATION */
/*--------------------------------------------------------------------------*/
execute calculation Sample.Basic.CALCALL;


/*--------------------------------------------------------------------------*/
/* KILL ALL REQUESTS TO DATABASE */
/*--------------------------------------------------------------------------*/
alter system kill request on application Sample;


/*--------------------------------------------------------------------------*/
/* LOGOUT ALL SESSIONS TO DATABASE */
/*--------------------------------------------------------------------------*/
alter system logout session on application Sample;


/*--------------------------------------------------------------------------*/
/* DISABLE CONNECTIONS TO DATABASE */
/*--------------------------------------------------------------------------*/
alter application Sample disable connects;


/*--------------------------------------------------------------------------*/
/* ENABLE CONNECTIONS TO DATABASE */
/*--------------------------------------------------------------------------*/
alter application Sample enable connects;


/*--------------------------------------------------------------------------*/
/* CLEAR APPLICATION LOG FILE */
/*--------------------------------------------------------------------------*/
alter application Sample clear logfile;


/*--------------------------------------------------------------------------*/
/* CLEAR ESSBASE LOG FILE */
/*--------------------------------------------------------------------------*/
alter system clear logfile;

/*--------------------------------------------------------------------------*/
/* LOAD DATA TO ASO SLICE */
/*--------------------------------------------------------------------------*/
import database ASOsamp.Sample data from load_buffer with buffer_id 1 override values create slice;

Export Database to Multiple files

/*--------------------------------------------------------------------------*/
/* EXPORT DATABASE TO 8 FILES */
/*--------------------------------------------------------------------------*/
export database Sample.Basic level0 data in columns
to data_file 'C:\Temp\ExportData1.0.Txt',
'C:\Temp\ExportData2.0.Txt',
'C:\Temp\ExportData3.0.Txt',
'C:\Temp\ExportData4.0.Txt',
'C:\Temp\ExportData5.0.Txt',
'C:\Temp\ExportData6.0.Txt',
'C:\Temp\ExportData7.0.Txt',
'C:\Temp\ExportData8.0.Txt';

NOTE: This process is useful when the export files are very large and go over the standard 2GB size and this gives you the ability to control how many files are created (always 8 files)...especially if you have automated processes that rebuilds your databases each night.

Other commonly used Maxl Script commands

/*--------------------------------------------------------------------------*/
/* LOAD APPLICATION AND DATABASE */
/*--------------------------------------------------------------------------*/
alter system load application ASOsamp;
alter application ASOsamp load database Sample;


/*--------------------------------------------------------------------------*/
/* COPY BSO OTL TO ASO APPLICATION */
/*--------------------------------------------------------------------------*/
CREATE OR REPLACE OUTLINE ON AGGREGATE_STORAGE DATABASE ASOsamp.Sample AS OUTLINE ON DATABASE Sample.Basic ;

/*--------------------------------------------------------------------------*/
/* BUILD ACCOUNTS DIMENSION */
/*--------------------------------------------------------------------------*/
set Dimension=Accounts;
set RulesFile=AcctASO;
import database ASOsamp.Sample dimensions
from local data_file 'C:\Temp\Accounts.txt'
using server rules_file 'Accounts'
on error write to 'DimAcct.err';

NOTE: Same syntax for Block Storage vs Aggregate Storage Databases

Maxl Script Sample

Here are some examples of Essbase Maxl Scripts. They were based off of the Sample.Basic and ASOsamp.Sample databases.


Load Data into the ASOsamp.Sample database. Those items in bold should be utilized in all maxl scripts.

spool on to LoadData.Log;
login admin password on localhost;
set timestamp on;

/*--------------------------------------------------------------------------*/
/* UNLOAD APPLICATION */
/*--------------------------------------------------------------------------*/
alter system unload application ASOsamp;

/*--------------------------------------------------------------------------*/
/* LOAD APPLICATION */
/*--------------------------------------------------------------------------*/
alter system load application ASOsamp;

/*--------------------------------------------------------------------------*/
/* CLEAR DATA FROM ASO APPLICATION */
/*--------------------------------------------------------------------------*/
alter database ASOSamp.Sample reset;

/*--------------------------------------------------------------------------*/
/* INITIALIZE ASO BUFFER */
/*--------------------------------------------------------------------------*/
alter database ASOSamp.Sample initialize load_buffer with buffer_id 1;

/*--------------------------------------------------------------------------*/
/* LOAD DATA TO BUFFER 1 */
/*--------------------------------------------------------------------------*/
import database ASOSamp.Sample data
from local data_file "'$ARBORPATH/app/ASOsamp/Sample/dataload.txt'"
using server rules_file 'dataload' to load_buffer with buffer_id 1
on error write to 'dataload.err';

/*--------------------------------------------------------------------------*/
/* LOAD DATA FROM BUFFER */
/*--------------------------------------------------------------------------*/
import database ASOSamp.Sample data from load_buffer with buffer_id 1;

/*--------------------------------------------------------------------------*/
/* AGGREGATE DATABASE */
/*--------------------------------------------------------------------------*/
execute aggregate process on database ASOSamp.Sample stopping when total_size exceeds 1.5;

logout;
spool off;
exit;


NOTE: You can put the spool on after the login code so that your log file will capture the login information.