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.

MDX Formula - Missing Quotes???

One odd thing that I've come across in an Aggregate Storage Cube in version 9.3.1 is adding an MDX formula to a member via load rule using a text file as the source. If you have an MDX formula that contains a statement like IsUda(Time.CurrentMember, "OPEN_MONTH"), Essbase will not build the formula in the outline with the quotes in it. Instead, it will create the formula like this IsUda(Time.CurrentMember, OPEN_MONTH). This will cause a verification issue since OPEN_MONTH is not a current member, but rather a UDA. However, without the quotes, it will not recognize it as a UDA. Check out an example here

Data Manipulation

Load rules can be used to load data, modify outlines or both. I've always been a big believer on making any "data manipulations" in your source system. For instance, you are performing a dimension build that needs to combine 2 or more columns to create a new field. This can be done with a load rule, but when in doubt, perform this step in SQL or your source system to limit the amount of data manipulations that need to occur in a load rule. Believe it or not, over time, a load rule can become corrupt and if you don't have a backup (and you should always have a backup, but just in case you don't, it will be hard to recreate all the data manipulations that you made in a load rule.

With that being said, I've come across numerous clients that don't have the ability to change the source dimension files (for example, the files come directly from SAP) so you will need to make data modifications in a load rule at some point in time, here's some examples of things you can do:

--------------------------------

Add Prefix or Suffix
As I noted on the Field Properties page, you can add a Prefix and Suffix to any field. This is really straight forward, let's say your data comes in as 2009, 2010, 2011, but your members in the Year dimension are YR2009, YR2010, YR2011. Instead of changing your data files, simply add a prefix of YR on the Global Properties tab of the Field Properties option. After you make the change, you will see the preview of your data change to YR2009, YR2010, YR2011 instead of 2009, 2010, 2011.