MaxL
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.
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 /*---*/
/* 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.
/*---*/ /* 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;
MaxL Script with Variables
As I began writing MaxL scripts, I found the Hyperion documentation lacking sample scripts. The few sample scripts I found on the internet just didn’t have enough flexiblity in them. Scripts, in my opinion, must be descriptive, flexible, streamlined and above all accurate. So, with the help of a few friends who have crossed this road before me and a few sample scripts from them (Special Thanks to John Hayes), I started. Keep in mind, I’m an avid script writer. I’ve done everything from Windows and various Unix shell scripts to Oracle SQL scripts. So for me, it was more a matter of mastering the proprietary commands and testing them in a non-production environment. The script I’ll share with you is more to show the use of variables and scripting style than to explain what each command does. So, if you’re familiar with the Hyperion products and MaxL this will be a piece of cake!
There a many ways of writing scripts but they are always specific to the environment they are run in. So, for me, in this particular case the script below is one that was called by a korn shell script and CRON’d. The Unix/Linux CRON (basic scheduling) utility is as dumb as a doornail – you must provide full path to everything in the script or risk missing a critical step. My point: know your environment! Secondly, know what you are trying to accomplish within your script!
In the script below, the first two variables are specified externally at runtime or by the calling script. The remaining variables are a means of specifying very long paths in only one place. When using a variable in this way it’s much easier to read the script because you don’t have the long path overshadowing the command and have one general area to alter if the path changes. Comment lines in MaxL start with /* and end with */ .
Sample MXL script with variables: (I’ll try and change the font on the script – it’s easier to read in Courier-New) /* :::======================================================= ===== */ /* ::: SCRIPT: Appl_Bckup.mxl */ /* :::———————————————————— */
/* ::: This script accepts two input parameters, sets variables, */ /* ::: places the application in archive (read-only) mode, shells */ /* ::: out of MaxL to TAR all relevant application files and */ /* ::: then ends the archive mode to return the application to */ /* ::: read/write transactions. */
/* ::: */
/* ::: CALLING SCRIPT: EssbaseBkupFiles.ksh */ /* ::: PLATFORM: Linux */
/* ::: CREATED: 6/2008 CPena for <my company> */ /* ::: */
/* :::———————————————————— */ /* ::: DISCLAIMER: */
/* ::: This script is for use by <full company name> Certified */ /* ::: Database Administrators. <company> will not be held liable */ /* ::: when used by anyone other than a qualified DBA. */ /* ::: This script has been tested by <name> on specific hardware*/ /* ::: and software to ensure quality and reliability. */
/* :::———————————————————— */ /* ::: Date Initals Description */ /* :::———————————————————— */ /* :::06/14/08 CPena Initial Script Creation */ /* ::: */ /*
:::======================================================= ===== */
/* ::: STEP 1 SET VARIABLES and SPOOL LOCATION */ /* :::======================================================= ===== */ set ESS_APPL=$1; set ESS_DB=$2; set ARBORPATH=/u01/Hyperion/AnalyticServices; set ARBORAPPL=”$(ARBORPATH)/app/$(ESS_APPL)”; set SCRIPTDIR=/u01/apps/hypadmin/scripts; set BKUPDIR=/u01/apps/hypadmin/app_bkup; set ARCHFLE=”$(ARBORAPPL)/$(ESS_APPL)-$(ESS_DB).archive”; set LVL0FLE=”$(ARBORAPPL)/$(ESS_APPL)-$(ESS_DB).lvl0Extract.txt”; echo ‘APPL.DB: ‘ $(ESS_APPL).$(ESS_DB);
echo ‘ARCHFLE: ‘ $ARCHFLE;
spool on to “$(SCRIPTDIR)/logs/$(ESS_APPL)-$(ESS_DB)_bkup.log”; /*
===== */
/* ::: STEP 2 Start Archive (read-only) mode */ /*
:::======================================================= ===== */
alter database $ESS_APPL.$ESS_DB begin archive to file “$(ARCHFLE)”; /*
:::======================================================= ===== */
/* ::: STEP 3 Take a Level 0 backup - Comment out -> HOLD FOR NOW */ /*
:::======================================================= ===== */
/* export database $ESS_APPL.$ESS_DB level0 data in columns to data_file “$(LVL0FLE)”; */ /*
:::======================================================= ===== */
/* ::: STEP 4 Shell out to execute TAR command at LINUX Prompt */ /*
:::======================================================= ===== */
shell tar -czvf $BKUPDIR/$ESS_APPL-$ESS_DB-`date +”%Y%h%d_%H%M”`.tgz –exclude *.pag –exclude *.ind $ARBORAPPL/;
/*
:::======================================================= ===== */
/* ::: STEP 5 End Archive */ /*
:::======================================================= ===== */
alter database $ESS_APPL.$ESS_DB end archive; /*
:::======================================================= ===== */
/* ::: STEP 6 Logout and exit */ /*
===== */ spool off; logout; exit;
When testing scripts found on the internet, always keep the standard disclaimer in mind especially if there is not one mentioned in the script comments. If you are not the author of the script and are unsure of what it does – Don’t use it. Research any command you’ve not used before. Comment out any lines you’re unsure of!
The most proficient script writers will document each step with some comments. Substitution variables are nice because if you ever have to change a variables content you only have to change them in one place. Most scripts start out being a means to automate a process and can have a very long lifecycle. Do it well the first time and build on it.
In the next post I’ll review the calling shell script! Let me know if you have any questions! Windows .bat script calling MAXL to shutdown and startup Hyperion 9.3.1 services
and essbase database
We use 2 scripts, one to shutdown and one to restart. The shutdown scripts stop our monitoring agent first, then execute the maxl script, shuts down the Essbase server, and stops the Essbase Service. We schedule it about 10 minutes before the backup agent runs to give essbase time to shutdown all the applications. The scripts also add comments into the windows event log (we run windows Server 2003).
Occassionally, Essbase does not stop an application in time (e.g. long calc running). When that happens, we get a file contention and the backup for that Essbase application will fail and the file would not be include in that backup.
***** StopEssbase01.bat ******* REM Stop Patrol Agent
net stop PatrolAgent
REM add comment to event log
c:\batch\logevent -s w "PatrolAgent has stopped" REM Shutdown Essbase Server
essmsh e:\Hyperion\Scripts\System_Shutdown.msh hypeprd Rem Stop Essbase Service
net stop HyS9ESBAgent9.3.1_HypService REM add comment to event log
c:\batch\logevent -s w "Essbase Service has stopped" ***** System_Shutdown.msh ********
login 'admin' 'xxxxxxxxxxxx' on $1;
spool on to $HYPERION_HOME\\scripts\\output\\System_Shutdown.log; alte r system shutdown;
logout; spool off; exit;
Rem Start Essbase Service
net start HyS9ESBAgent9.3.1_HypService REM add comment to event log
c:\batch\logevent -s w "Essbase Service has started" REM Start Patrol Agent
net start PatrolAgent
REM add comment to event log
c:\batch\logevent -s w "PatrolAgent has started" try to use "SC" instead of "net" in your bat file Franck,
Oracle EPM 11.1.2 – Allocations in Essbase ASO Cubes – Using MaxL scripts In the last 2 blog entries, i showed how the calculations in ASO work. Today lets look at another new feature in ASO cubes i.e the ability to run allocations directly. In a BSO cube, Allocations and Calculations are all driven through a set of calculation commands. Some Allocation features in BSO can actually be implemented using normal calculation commands (without using ALLOCATE and MDALLOCATE functions). But in the case of ASO, calculations and allocations are treated separately. So, there are 2 important aspects that we need to remember while running calculations/allocations in a ASO cube
1. Dynamic References using CurrentMember, CurrentTupule etc are not supported currently (necessary if we want to run multiple allocations within the same dimension)
2. Hierarchical references like PARENT etc are not allowed in both calculations & allocations. These references are allowed for the parameters but not for the assignment scripts/allocation amounts.
The above 2 are significant drawbacks which i hope will be supported in future releases. Lets now look at a very simple example for allocation. I will be using the ASO version of the Demo->Basic cube for demonstrating this. The screenshot below shows the data that has been loaded into the cube.
If you notice, we have data for the Sales measure for all Regions except South. The idea is to allocate data into the South Region (both Sales & Cogs equally) based on the Total Expense values of the East Region. This is a very simple allocation example where our input data is at Level-0 but we are allocating from non Level-0. One good thing about this example is the fact that this shows that though ASO does not support input to non level-0 members, it supports allocation from them.
An Allocation in a ASO cube does not require a separate calculation script. All allocations are done through MaxLs directly. An allocation MaxL contains four main parts (in addition to other parameters which i shall not cover here).
1. POV – POV or a Point of View provides execution context to an allocation. A POV allows only reference to level-0 members. For every combination in the POV, the allocation will be
executed once. POV is specified in the form of a MDX-Set.
2. Amount – This can contain a static member, Tupule or a constant. This does not support MDX expressions. In our example above, the amount will the (Total Expenses,East) Tupule. Ideally, Amount & POV should have all the dimensions in the cube.
3. Target – Target is specified in the form of a MDX Tupule. Basically it represents the region that will act as a target for the allocation. In our case, this will be empty.
4. Region – Region is a MDX set expression. This represents the target region where the data will be allocated. The dimensions specified in POV, Target and Region should be mutually exclusive as they all combine to form the target region. In the example above, Children(Total Expenses) & the Children (South) will form the Region.
5. Basis – This is commonly used when we are doing member based allocations. In our case, since we are using dividing the values equally (Spread allocation), this is optional.
So the final formula that i shall be using for achieving this is given below execute allocation process on database DemoASO.Basic with
pov "Crossjoin(Descendants([Year],[Year].levels(0)), Crossjoin(Descendants([Product],[Product].levels(0)), Descendants([Scenario],[Scenario].levels(0))))" amount "([East],[Total_Expenses])" target "" range "CrossJoin({[Sales],[Cost_of_Goods_Sold]},
Descendants([South],[Market].levels(0)))" spread;
As you see, Essbase has generated the necessary cells to accomplish this. As i mentioned last time in the Calculation blog post, the allocations/calculations are done separately and then the updated cells are pushed through external data load buffers. So, you would start noticing cells being loaded into incremental slices.
And if you look at the data, the South region would now have data with the values uniformly distributed.
Though this does work there are some inherent drawbacks. As ASO does not accept data at non level-0 members, the way allocation maxL’s are designed it makes it very tough(though possible) to do hierarchical allocations within the same dimension. Hopefully the introduction of dynamic member references and member functions should enable that in the future.
People who are new to Essbase find it very difficult to memorize or understand as there are plenty of MaxL functions are used. This is my efforts to give a list of MaxL commands which we use in our day to day life, specially when we as Essbase Admin or Production Support. These are the list of MaxL commands along with some sample examples of MaxL file to load data and execute calculation script. Some Sample Scripts Example#1 login $1 $2; spool on to 'd:\temp\exec_cal.txt';
/*alter system load application 'FinOps';
alter application 'FinOps' load database 'Findb'; */ /* embed the calculation statement into maxl script */ execute calculation 'CALC ALL;' on 'FinOps'.'Findb' ; /* execute stored server calculation scripts */
execute calculation 'FinOps'.'Findb'.'cal-rank'; spool off;
logout;
Example#2 login $1 $2; /* login section */
spool on to 'd:\Data_file\Jan_Sales.txt'; /* log the result */ alter system load application 'FinOps';
alter application 'FinOps' load database 'Findb'; /* import dimension member from sql database */
import database 'FinOps'.'Findb' dimensions connect as 'xxxxxxxxxxxxxx' identified by 'xxxxxxxxx' using server rules_file 'addday' on error write to 'd:\addday.txt'; spool off; logout Some 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: 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. /*---*/ /* 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;
/*---*/ /* EXPORT LRO AS A MIGRATION STRATEGY */
/*---*/
export database FinOps.Findb lro to server directory 'exportedLROs';
/*---*/ /* CLEAR APPLICATION LOG FILE */ /*---*/
alter application Sample clear logfile;
/*---*/ /* CLEAR ESSBASE LOG FILE */ /*---*/
alter system clear logfile;
/*---*/ /* LOAD DATA TO ASO SLICE */ /*---*
Migrating/Upgrading Essbase to a New Server 3/16/2009 9:39:00 AM
Posted by: Kyle Goodfriend
There are many considerations that must be carefully planned when addressing an upgrade to version 9 or 11, or creating a backup strategy. Manually moving all the components
involved can take days and is extremely error prone. There is more to it than moving Essbase databases. Essbase calc scripts, reports, and load rules have to be considered. Server
variables need to be moved. All the Maxl and EssCmd scripts need to be copied and changed to reflect the new server and security model. Security filters need to be copied and altered slightly if moving to a new version of Essbase. All the security users and groups need to be created. As with any endeavor of this capacity, it can be time consuming. The benefits of the improved stability and features far outweigh the efforts.
Completing this for one server is tough enough. Imagine if corporate policy dictates that everything has to be done in a QA and/or test environment before it is moved to the new production area. Now factor in the number of Essbase servers and the fact that the security model might have to be consolidated to one (this occurs when upgrading from anything before 9, to version 9 or 11). Don’t forget that there is only a very small window for the current production servers to be down. If 4 Essbase servers exist, this effort might have to occur 12 times!
Doing the same work 3 times for every server is obviously redundant. I developed a small .NET application that significantly reduces the work involved. It virtually eliminates the need for any manual or redundant effort. .NET was selected because it was the quickest for me to develop the application, but JAVA, Perl, or any other similar development language could be used. The .NET application accepted the results of the following Maxl display commands. display application all;
display database all; display filter row all; display variable all;
display privilege group all;
alter system load application all; display partition all advanced;
Maxl scripts were created from the process to 1. create all the applications and databases
3. rebuild and update security filters 4. replicate all server variables
This Maxl can be executed on the destination server to setup the new environment. Examples of the scripts generated from the .NET application below.
/* Create Application: BUDGET */
create or replace application 'BUDGET' type nonunicode_mode; alter application 'BUDGET' set lock_timeout after 300;
alter application 'BUDGET' set max_lro_file_size unlimited; alter application 'BUDGET' set minimum permission no_access; alter application 'BUDGET' enable startup;
alter application 'BUDGET' disable autostartup; alter application 'BUDGET' enable commands; alter application 'BUDGET' enable updates; alter application 'BUDGET' enable connects; alter application 'BUDGET' enable security; /* Create Database: BUDGET */
create database 'BUDGET'.'Budget';
alter database 'BUDGET'.'Budget' set data_file_cache_size 1024000000; alter database 'BUDGET'.'Budget' set index_cache_size 76800000; alter database 'BUDGET'.'Budget' enable startup;
alter database 'BUDGET'.'Budget' enable autostartup;
alter database 'BUDGET'.'Budget' set minimum permission no_access; alter database 'BUDGET'.'Budget' set retrieve_buffer_size 102400; alter database 'BUDGET'.'Budget' enable two_pass_calc;
alter database 'BUDGET'.'Budget' enable aggregate_missing; alter database 'BUDGET'.'Budget' enable compression;
alter database 'BUDGET'.'Budget' disable create_blocks; alter database 'BUDGET'.'Budget' disable committed_mode;
alter database 'BUDGET'.'Budget' set implicit_commit after 10000 blocks; alter database 'BUDGET'.'Budget' disable cache_pinning;
alter database 'BUDGET'.'Budget' set retrieve_buffer_size 102400; alter database 'BUDGET'.'Budget' set compression bitmap;
alter database 'BUDGET'.'Budget' set retrieve_buffer_size 102400; alter database 'BUDGET'.'Budget' set retrieve_sort_buffer_size 102400; alter database 'BUDGET'.'Budget' set data_cache_size 512000000; alter database 'BUDGET'.'Budget' set io_access_mode buffered; alter database 'BUDGET'.'Budget' set note '';
alter system unload application 'BUDGET'; /* Create Filter: MRP100206310000 */
create or replace filter 'BUDGET'.'Budget'.'Audit' write on
'@DESCENDANTS("Time"),@DESCENDANTS("Year"),"Input","Working Budget",@DESCENDANTS("Product"),@DESCENDANTS("Total
Audit"),@DESCENDANTS("Expenses")';
DOS and UNIX scripts were generated to copy all of the database objects, data files, and Maxl and EssCmd scripts from the source server to the destination server. The program also
created all the files to import into Version 9 and System 11 to add users, groups, and replicate the security model.
All the Maxl and EssCmd scripts (username, password, server names, file paths, etc.) were updated so they could be executed on the new servers.
This process makes it extremely simple to migrate, or move, any Essbase application from one server to another. The entire process could be completed in hours, rather than days, and eliminates the possibility of human error. What would be budgeted to take weeks with
several resources can take less than a day. Hi,
I wrote this script that will log in, and export a database out to a few files. For some reason it doesn't return any errors, but also doesn't seem to create the files I specified in the script either. It says that it has completed, but I can't seem to find the files anywhere. What am I doing wrong? (I have changed the username, password, and IP address for posting purposes.) spool off;
login user password on 1.1.1.1;
alter system load application previous;
alter application previous load database previous;
export database previous.previous all data to data_file 'C:\My
Documents\alex\Liftoff\Essbase_Archive\new_archive_process\fileout1.txt','C:\My Documents\alex\Liftoff\Essbase_Archive\new_archive_process\fileout2.txt','C:\My Documents\alex\Liftoff\Essbase_Archive\new_archive_process\fileout3.txt'; spool on to 'C:\My Documents\alex\Liftoff\Essbase_Archive\new_archive_process\archive_new_error.txt'; echo $?; spool off; logout; exit; Thanks in advance, Alex
Minimally you need to change all the "\" characters to "\\" characters - eg "c:\path\file.txt" needs to become "c:\\path\\file.txt". Single "\" characters get tossed in most cases.
However, should you choose to use the spool on command (which would be a good idea if you want to further debug your Maxl statements, or to be sure everything executed without any errors), the spool path should use single "\" characters.
I followed the advice of both posters above and although no errors were reported in the spool, it reported being successful, and the spool file was output to the correct spot, the actual data files were not exported.
Here are my results:
49 - User logged in: ['user'].
MAXL> alter system load application previous; 52 - System altered.
MAXL> alter application previous load database previous; 54 - Application altered: ['previous'].
MAXL> export database previous.previous all data to data_file 'C:\\My
Documents\\alex\\Liftoff\\Essbase_Archive\\new_archive_process\\fileout1.txt','C:\\My Documents\\alex\\Liftoff\\Essbase_Archive\\new_archive_process\\fileout2.txt','C:\\My Documents\\alex\\Liftoff\\Essbase_Archive\\new_archive_process\\fileout3.txt';
90 - Database export completed: ['previous'.'previous']. 0
Export Data
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.
• For Aggregate Storage Applications, you can only export level-o data in non-column format. Big difference here is that you can't export all data and you can't export in column format. So, you really can't use this export file unless you plan on putting it back into the same outline structure as it was exported from.
• You can export to multiple files just like you can with a Block Storage model (see above example)
• Prior to Version 9, ASO cubes did not have the capability to perform an export. Report scripts were your only option, ugh.
• Users are able to retrieve on the database during the export process, it does not lock the cube
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'; DATAEXPORT Calculation function:
time ago.
• Only available in BSO applications as Calc Functions are not supported in ASO.
• You can export data to a text file while specifying the delimiter and file path/name (I have used this often since the release)
• You can export data to a binary file (haven't needed to go down this path yet, but looking forward to testing with it)
• You can export data to a relational database (like SQL Server) using an ODBC connection, be careful with this if you are using SQL Server 2005, had some issues about 1 year ago and the help desk really didn't help...but no issues with SQL Server 2000.
• The best part about this function is that it works like any other calculation function where you can FIX on any subset of data. So, if you only to fix on "Actual", for the Month of January, for a specific product..YOU CAN!!! The performance is very fast (assuming you have your dense/sparse settings set up properly)
• You have the ability to Export data based on certain conditions, like "Sales">500.
• Not only can you export using a Fix statement, you have ability to control the format of the text file (similar to report script functionality). Here are the options (I've used the ones in bold most often):
DataExportLevel ALL | LEVEL0 | INPUT; DataExportDynamicCalc ON | OFF; DataExportDecimal n; DataExportPrecision n; DataExportColFormat ON | OFF; DataExportColHeader dimensionName; DataExportDimHeader ON | OFF; DataExportRelationalFile ON | OFF; DataExportOverwriteFile ON | OFF; DataExportDryRun ON | OFF; Security Filters
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
/*---*/ /* 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.
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)';
/*---*/ /* WRITE ACCESS TO BUDGET & LEVEL 0 EAST */
/*---*/
The @RELATIVE function allows you to take any member within the hierarchy and get an associated level of that member. So we are able get all Level 0 members of East without having to take all Markets
/*---*/ /* WRITE ACCESS TO BUDGET & LEVEL 0 EAST (except NY) */
/*---*/ create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget",
@REMOVE(@RELATIVE("EAST",0),@LIST("New York")';
If you manually created each filter in Essbase Administration Services, you would put a READ statement on line 1 and a WRITE statement on Line 2. Well, with MAXL, simply seperate the lines with commas and put the actual filter in single quotes, like this:
/*---*/ /* WRITE ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*---*/ create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget"', READ on '@IDESCENDANTS("Scenario")';
Let's say you have data in the Sample.Basic Essbase cube, but don't want a given set of users to have access to it, you have two options: You can give them no access to that member or you can actually remove that member from the users profile (meaning, if they do a member selection, the will not even see that the member exists in the cube)
/*---*/ /* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*---*/ create or replace filter Sample.Basic.'FilterName' NONE on '"Budget"', READ on '@IDESCENDANTS("Scenario")';
or
/*---*/ /* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*---*/ create or replace filter Sample.Basic.'FilterName' NONE on '"Budget",
@REMOVE(@IDESCENDANTS("Scenario",0),@LIST("Budget")';
The two examples above will give the end user a NOACCESS when doing a retrieval. The below example will give the user a "Unknown Member" Error message when doing a retrival since you are removing that member from the database for that user (even though the
member is still there):
/*---*/ /* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*---*/
create or replace filter Sample.Basic.'FilterName' READ on '@IDESCENDANTS("Scenario")', META_READ on '@REMOVE(@IDESCENDANTS("Scenario",0),@LIST("Budget"));
Rule Files
To create an Essbase Rules Files, you have the following options
- Expand the Application >> Expand the Database >> Right Click on 'Rules Files' >> Select 'Create Rules Files' (Recommended Approach)
- Using your toolbar, you can select: File >> New >> Scripts >> Rules Files >> OK (Not Recommended Approach, see note 14 - Validate)
Once the rules file is open, you're toolbar will appears as below:
1. New/Open/Save/Print/Send Email - Pretty generic here, but if you need help, let me know.
2. Move Field - Moves any column to a new column (kind of like re-ordering your columns). This option is useful when creating rules that require you to sort columns in order to make for the rule file to load successful. For example, you are creating a dimension build rules file using parent-child references. Say you have 3 columns of data coming in from your source, column 1 = child, column 2 = parent and column 3 = alias. You have two options here, reorder your columns from the source (recommended option, but not always possible) or you can use this option to reorder you columns to Parent, Child, Alias.
3. Split Field - Like most of the titles for these buttons, they do what their titles says. This one will split a given field into two fields. It does NOT split the field based on a delimiter like "|" but rather after so many characters. So if you always want to split the field based on the 3rd character, for instance, then this is your tool, but if you want to split the field based on a "|" then you will need to modify your source data. Keep in mind, you do have the ability to split a data feed by a normal delimiter, but they will be for all columns. See note 11 for more details on adding a delimiter to your data source.
4. Join Field - Joins 2 or more fields into one field (Add more fields by using the holding down the Ctrl key). Please note, when using this option, if you join two fields, they will become 1 field. The original independent columns will no longer exist. If you would to have the
5. Create a Field Using Join - Creates a new field when using a join. Same idea as #4, but when you create a new field using 2 columns, the 2 columns remain intact after the field is created. Both 4 and 5 have their advantages, it just depends on your data and what you are trying to accomplish. If you need the original fields in addition to the new one, use option 5 (Create a Field Using Join), but if you do not need the original fields after the join, use option 4 (Join Field).
6. Create a Field Using Text - Adds a new column of whatever text you input. I've seen beginners use this option add a text field during a data load where a given dimension was missing from the data source. For instance, in the Essbase Sample.Basic database, you perform a data load but you do not have a column for Measures (highly unlikely, but this is just an example). So you can add a columns and add the text "Misc" to load to the Misc member name. The advance way to do this is detailed in Note 12 (Data Load Settings). 7. Field Properties - Please click here to see a detailed description of the Field Properties option.
For the Field Properties portion of an Essbase Load Rule, there are three tabs (Global Properties, Data Load Properties, and Dimension Build Properties). Global Properties affect both Data Load and Dimension Build rules, where as Data Load Properties and Dimension Build Properties are specific for each. Get used to this menu option, you will use it quite often.
1. This section deals with whether the data is to be applied in its Original Case,
Lowercase or Uppercase. Original is the default. You can add a Prefix (leading text) or a Suffix (ending text). You can Drop lead/trailing spaces. Keep in mind, adding a prefix/suffix with impact all members in a given dimension, not just one specific member. That's where #2 comes into play.
2. Instead of changing all members within a given dimension with a prefix/suffix, you can specify one member to be modified with the 'Replace/With' section. Be careful with this, if some members names are partial to other member names (East is a part of Northeast), so you don't want to change all East to "(E) East" because it will turn Northeast into "North(E) East", which is obviously what you don't want. Therefore, make sure you check the Match Whole Word option.
3. For your data load rules, you will use this section to specify a columns association to a dimension, you can manually enter the dimension name or, the easier way, double click the dimension name and it will populate the Field Name box. Keep in mind, any dimension that contains a space, quotes will be applied to the dimension name in the Field Name box (this is normal). Also, it is not always mandatory to use the dimension name, you may have 12 data columns which you would specify as "Jan", "Feb", "Mar", "Apr", etc instead of putting in "Year".
4. You can specify if a column is a data field or if you would like to ignore this column. You would want to ignore a column when your data is coming in with excess columns that is not necessary for this load rule. The scale option allows you take a number, say 1,000,000 and put a scale of 0.001. This will automatically change you data from 1,000,000 to 1,000. This comes in hand when you have an Millions/Thousands/Dollars member names...simply perform this pseudo calculation on the way in as opposed to changing it in you data source.
5. You will cycle through each column (usually starting from left to right) specifying the dimension it is associated with or whether to "Ignore field during dimension build" (#7). Simply double click the dimension name to apply it to the Dimension field at the top.
6. For the given column that you are in, you will first select a dimension (see Note 5), then you will select its association to the outline. For instance, you can build your dimension via Generation References, Level References or Parent-Child references. Generations are a top-down approach, where you database name is Generation 0, the dimension names are
Generation 1 and so on. Level References are just the opposite, starting from the leaf level (or lowest possible level) and working up from 0 to the database name. Each has its pros and cons based on your data because you may not always have the same amount of generations or levels, so it might be tricky to build your rules file. The recommended approach would be to utilize the Parent-child reference, because no matter how many generations or levels you have, you will always have a Parent-Child relationship. No matter which method you choose,
you need to associate a number to it, I've already discussed the generation and level numbers you would use, so for parent-child references, please use 0. Keep in mind, in this section you have the ability to not only assigned a member name, but its Alias (description), UDA (user defined attribute - which can be used in security filters and partitions), and its Property (i.e., + for aggregation, ~ to ignore or - for subtraction).
7. This option simply ignores the column during the dimension build
8. Select Record / Reject Records - Within a given column, this will give you the option select or reject certain records. I am not a big fan of these options as it is always better to have the appropriate data coming from the source.
9. Data Load Fields / Dimension Build Fields - These two options will toggle the view of your load rule (between the Data Load view and Dimension Build View). I recommend when building a data load rule or a dimension build, make sure the right button is toggled
appropriately. I've see beginners struggle with dimension builds since they were unaware of this option.
10. Associate Outline - When build a rules file, it is extremely helpful to associate the outline in which you are building a rules file for. This is not necessary, but you will be unable to validate your rules file without performing this operation (see note 14 - Validate, for more details)
11. Data Source Properties - The Data Source has four tabs:
- Delimiter - As mentioned in Note 3 (Split Field), this works like any other delimiter that splits a file or data source by a specified delimiter
- Field Edits - Lists all edits that have been made to this load rule (held in order of creation). For example, if you have 5 field edits, you can not delete #3 without deleting #'s 4 and 5 (since #'s 4 or 5 might be dependant on #3)
- Header - You can use this option to skip the first line of a data source (usually the header record) or if you data source is set up properly, you can use it as the data load field names or dimension build field name.
- Ignore Tokens - Here you can set up certain tokens to be ignored.
12. Data Load Settings - Please click here to see a detailed description of the Data Load Settings option.
1. When performing an Essbase Data Load, you have three data loading (mathematical) operations. For sake of an example, a specified intersection of data equals $10 and your new data is $3. You can "Overwrite existing values" which will turn $10 into $3, you can "Add to existing values" which will turn $10 into $13 and you can "Subtract from existing values" which will turn $10 into $7. Depending on your environment, each one of those can be utilized for different purposes.
2. This section can be tricky. An example i like to use is from an application i built a couple years ago, we have an revenue account coming in from the source with a "-". Its just the way the source worked, so I flagged all revenue accounts with a UDA of "RevFlip". By doing this, any dollars that came in for any of these accounts, any negative dollars were flipped to
positive and vise versa. So to make it work, you would specify the UDA and the dimension it is associated with.
3. As I mentioned in the Field Properties section, if a data feed does not have a specific dimension represented, a user could a new column with a given text string ("Misc"). This option will work for you, but it is not clean, instead, use the Header Definition identified here. Simply, add the default member name to the Name box at the top, or you can navigate to it in the and double click on it. When you have more than a couple dimensions not represented from the data source, this is a one stop shop for you to apply all your defaults.
13. Dimension Build Settings - Please click here to see a detailed description of the Dimension Build Settings option.
1. Here you will specify which Essbase dimension or dimensions you are building with this rules file. Yes, you can build two dimensions with one rules file, this is more advanced, so we'll leave it for another topic.
2. If the member is already existing, do you want to allow moves (new location in heirarchy), allow property changes (change the aggregation from ~ to a +), or allow formula changes. Other options will appear based on given dimensions selected, like UDA changes.
3. Build Method - this is where you specifiy whether or not a given dimension is to use Generation References, Level References or Parent/Child references. As I
mention in the Field Properties section, using Parent/Child references when available is the recommended approach. You can also check the box to process null values (this is really applicable when building your dimension using Generation references)
4. Upon building the dimension, you can sort the members Ascending, Descending or no sort at all. Be careful of this option. If you accidentally clicked on a dimension name, say "Scenario", but you were not building the Scenario dimension with this rules. Then if you checked the Sort Ascending button, it WILL sort your Scenario dimension even though you did not intend to.
5. This will "Merge" any existing members, plus the new ones coming in or "Remove unspecified" will delete any member that's not included in the data source being loaded.
Dimension Definition Tab:
The key feature here is that you have the ability change a Hyperion Essbase Block Storage database's dimension settings, but the one I've used the most in practice is changing the Dense/Sparse settings via a load rule.
- Right click on any dimension name (select "Edit Properties")
- If you dimension is currently Dense, but you want it to be Sparse (say for calculation performance), then you change the "Configuration" setting to "Sparse".
- Note: You can add implement this feature on a existing load rule (even if that load rule is building a different dimension) or create a seperate one, but your data source must contain some sort of data as it can not be blank.
14. Validate - As mentioned in note 10 (Associate Outline), in order to properly validate a rules file, an outline needs to be associated with the rules file. If you created your rules from the File menu, then Associating an outline is necessary, however, if you navigated Application >> Database >> Rules Files >> Create Rule File, then the outline is already associated.
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.
MDX Formula - Missing Quotes???
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.
Here is an example from a Time dimension file: Parent!Child!Property!Formula!
Time!TimeFormulaMember!~!CASE WHEN IsUda(Time.CurrentMember, "OPEN_MONTH") THEN...!
As you can see, I have quotes around OPEN_MONTH, but when I bring this record into my load rule, the quotes are gone...
Solution: Change the quotes in you dimension file to something other than quotes, try using a # and use the Global Replace option and replace the # with " and the quotes will appear in your MDX formula.
Replace zero's
One way to optimize your Block Storage application (BSO) is to replace zero's with #missing during a data load.
On the Global Properties tab of the Field Properties option, perform a replace of 0 with
#Missing. Now be careful with this, you want to make sure you select the Match Whole Word box instead of Replace All Occurrences. You don't want to replace 45098 with 45#Missing98. EXAMPLE #1 - Essbase Dimension Building Example (Parent/Child Build)
For this section, you need the 3 attached files:
- Test.otl : Shell outline to use for this example, all dimensions populated except 'Product'- which we will be building here.
- PROD1.rul : Rules file to build the Product dimension.
- ProductDimBuild.txt : Data file utilized to build the dimension. Let's analyze the ProductDimBuild.txt file first:
- Products laid out using Parent/Child references with the following column headers and associated data: "PARENT0,Product" "CHILD0,Product" "ALIAS0,Product" "PROPERTY0,Product" "UDA0,Product"
Now let's move onto the Rule file for Product:
- In order to utilize the row header from the Text file as my dimension name, you need to enable the following option:
Data Source Properties > Header Tab >
Input "1" without quotes in the "Record containing dimension building field names:" (Keep in mind, the syntax needs to be exact, otherwise you will get an error)
- We are using Parent/Child references, we need to enable this option: Dimension Build Settings >
Dimension Build Settings Tab >
Double Click "Product" dimension name >
Check "Use Parent/Child references" under the Build Method section.
- We are creating an alternate hierarchy we need to allow property changes on the member: Dimension Build Settings >
Dimension Build Settings Tab >
Double Click "Product" dimension name >
Check "Allow Property Changes" under the Existing Members section
EXAMPLE #2 - Essbase Dimension Building & DataLoad Sample (Building a Dimension and Loading Data from your Data)
This is a little trickier and not always the common practice, but there are times when you need to dynmically build a dimension from the data. For this example, we will build the Product dimension from our data. Keep in mind, this sample data does not include Parent/Child references, therefore, it will create a "flat hierarchy".
For this section, you need the 3 files:
- Test.otl : This is the same outline as above - PROD2.rul : This is an new Product Rules File
- ProductDimBuildData.txt : Data file utilized to build the dimension and load data.
- In order to utilize the row header from the Text file as my dimension name, you need to enable the following option:
Data Source Properties > Header Tab >
(Keep in mind, the syntax needs to be exact, otherwise you will get an error, we do not need this ability during the dim build, but we do during the data load)
- Highlight Column 1: Field Properties >
Dimension Build Properties >
Double Click the "Product" dimension name > Double Click Level
Make sure 0 is in the Number field
..FYI, a flat hierarchy is the same as all members being at the leaf level (or level 0).
- Once the above step is complete, cycle through all other columns and "Ignore field during dimension build"
- Since We are using Level references, we need to enable this option: Dimension Build Settings >
Dimension Build Settings Tab >
Double Click "Product" dimension name >
Check "Use Level references" under the Build Method section. You can also sort the members here, if desired.
Let's switch gears and load this same file as data. You can toggle your view between Data Load Fields and Dimension Build Fields. Since our data was set up efficiently from our source system and the column headers came in as the dimension names, this step just became
easier. The only thing you need to consider is whether to append your data to existing records or overwrite. In this case, I will be overwriting, but this is how you do it:
Data Load Settings > Data Values Section > Overwrite Existing values Add to Existing Values
Subtract from existing values
Remember, we loaded this data to the level 0 location, in order to see the data at the hierarchical rollups, we need to run a calc script, calc-all will work in this case.
Common Errors
Administrative Errors ESSBASE ERROR 1012703
"Unknown calculation type [0] during the dynamic calculation. Only default agg/formula/time balance operations are handled."
There is an error in a formula(s), therefore, no calculations will work. The problem is that when you have the outline open in Edit mode and you try to 'Validate', it does not identify any of these errors, nor does it write it to a log. However, you can go one by one and validate each formula. So any time you delete a member or modify a member name, be sure that this member is not included in any formula.
Aggregate Storage Model - Solve Order
When you member of a formula is dependant on another member, ensure the solve order is setup properly, making the first member have a solve order of 0, then the next to have 1, etc.
Essbase Administration Services has become corrupt. Can occur when performing a dim build that did not build successfully. I do not know the full cause of it, but restarting the EAS Server will rectify this issue.
ESSBASE ERROR - 1021001 Failed to Establish Connection with SQL Database Server. See Log file for more information. Couple things could be happening here:
- You do not have permissions to access that SQL environment - The ODBC Connection on the server is not set up properly. - The table/view that you are calling no longer exists
WARNING - 1003055 - Aggregate storage applications ignore update to derived cells. [XXXX] cells skipped.
You will get this warning message when attempting to load data to an ASO model to a specific member that is not level zero. Solution: Analyze your data to ensure only Level 0 data is coming in, otherwise, in an ASO, the Level 0 value will aggregate over the higher level value. Keep in mind, your data load will finish successfully, this message is simply a warning
regarding data being loaded to a parent member.
Error Codes
Essbase Error 1001000 - Unable to Open Report File [%s] on Server Essbase Error 1001001 - Unknown Command [%s] in Report
Essbase Error 1001002 - Incorrect Syntax for Range Format in Report Essbase Error 1001003 - Unknown Member [%s] in Report
Essbase Error 1001004 - Incorrect Format Following [%s] in Report Essbase Error 1001005 - Unknown Member [%s] in Report
Essbase Error 1001006 - Unknown Item [%s] Ignored in Report
Essbase Error 1001008 - Unknown Member [%s] in Reset Command in Report Essbase Error 1001009 - Unknown Member [%s] in Zoom Command in Report Essbase Error 1001010 - Unknown Member [%s] in Zoom In Command in Report Essbase Error 1001011 - Currency Command in Report and No Currency Database Set Essbase Error 1001013 - Unknown Member [%s] in Dimend Command in Report
Essbase Error 1001014 - Unknown Member [%s] in Page Command in Report Essbase Error 1001015 - Unknown Member [%s] in Row Command in Report Essbase Error 1001016 - Unknown Member [%s] in Column Command in Report Essbase Error 1001017 - Unable to Send Data Over Pipe in Report
Essbase Error 1001018 - Unable to Send Data Over Pipe in Report Essbase Error 1001019 - Unable to Read Data Over Pipe in Report Essbase Error 1001020 - Unable to Read Data Over Pipe in Report
Essbase Error 1001022 - Extractor debug input: [%s]
Essbase Error 1001023 - Not Enough Memory to Complete Command Essbase Error 1001025 - Not Enough Memory to Complete Command Essbase Error 1001026 - Incorrect Syntax in Row Command in Report Essbase Error 1001027 - Incorrect Syntax in Row Command in Report Essbase Error 1001028 - Incorrect Syntax in Column Command in Report Essbase Error 1001029 - Incorrect Syntax in Column Command in Report Essbase Error 1001030 - Incorrect Syntax in Page Command in Report Essbase Error 1001031 - Incorrect Syntax in Page Command in Report Essbase Error 1001032 - Column Width Exceeded 255 Columns in Report Essbase Error 1001033 - Missing Closing Brace in Report
Essbase Error 1001058 - Incorrect Asymmetric Report, [%s] Records Processed
Essbase Error 1001059 - Unrecognized Relationship Code in DdbSelMbrs [%s]-ignored Essbase Error 1001060 - Formats Too Long, Ignoring Format [%s] in Report
Essbase Error 1001062 - Member [%s] From Same Dimension As Previous Member
Essbase Error 1001063 - You do not have sufficient access to perform a lock on this database Essbase Error 1001064 - You do not have sufficient access to perform a read on this database Essbase Error 1001065 - Regular Extractor Elapsed Time : [%s] seconds
Essbase Error 1001069 - Missing parenthesis in <Link Command. Essbase Error 1001070 - Unknown Latest Member for [%s] in Report
Essbase Error 1001071 - Unmatched parenthesis in [%s] command in Report Essbase Error 1001072 - Syntax error in <LINK Command
Essbase Error 1001073 - Members from different dimensions are selected in the <LINK command.
Essbase Error 1001074 - Syntax error in <LINK Command. Number of arguments exceeded the maximum of [%s].
Essbase Error 1001075 - The report contains an Attibute Aggregation member with no Attribute members present.
Essbase Error 1001076 - Member [%s] is not an attribute member. Essbase Error 1001077 - Invalid date format in <Link Command
Essbase Error 1001078 - Cannot form a valid attribute value from [%s] Essbase Error 1001079 - Dimension [%s] does not have any base members
Essbase Error 1001080 - Report parser internal error near [%s]. Cannot continue processing. Essbase Error 1001081 - Report parser error at [%s]. Not enough memory to continue
processing.
Essbase Error 1001082 - Report scanner error. Error in Scanning command [%s] in report. Essbase Error 1001083 - Report parser error. Cannot have more than one RESTRICT command per script.
Essbase Error 1001084 - Report parser error. Cannot have more than one ORDERBY command per script.
Essbase Error 1001085 - Report parser error. Cannot have more than one TOP command per script.
Essbase Error 1001086 - Report parser error. Cannot have more than one BOTTOM command per script.
Essbase Error 1001087 - Report parser error. Missing left parenthesis at [%s]. Essbase Error 1001088 - Report parser error. Missing right parenthesis at [%s]. Essbase Error 1001089 - Report parser error. [%s] should be a numeric value.
Essbase Error 1001090 - Report parser error. [%s] should be a positive integer value greater than zero.
Essbase Error 1001091 - Report parser error. [%s] exceeds the [256] maximum number of columns allowed in a report.
Essbase Error 1001092 - Report parser error. [%s] should be a @DATACOL() Essbase Error 1001093 - Report parser error. Syntax error at [%s].
Essbase Error 1001094 - Report parser error. Member [%s] not found.
Essbase Error 1001095 - Report parser error. Top of Stack is reached. Command too long at [%s].
Essbase Error 1001096 - Report parser error. Missing Comma near [%s].
Essbase Error 1001097 - Report parser error. Illegal command operations near [%s]. Cannot continue processing.
Essbase Error 1001099 - Report parser error. Unknown token [%s].
Essbase Error 1001200 - Report error. Not enough memory to continue processing. Essbase Error 1001201 - Report error. The DATACOL() argument [%s] in the ORDERBY
command must have a value between [1] and the max number of columns [%s] in the report. Essbase Error 1001202 - Report error. [%s] in the ORDERBY command is not a row member. Essbase Error 1001203 - Report error. The DATACOL() argument [%s] in the TOP or BOTTOM command must have a value between [1] and the max number of columns [%s] in the report. Essbase Error 1001204 - Report error. [%s] in the TOP or BOTTOM command is not a row member.
Essbase Error 1001207 - Report error. The DATACOL() arguments [%s] and [%s] in the TOP and BOTTOM commands do not match.
Essbase Error 1001208 - Report error. The DATACOL() argument [%s] in the RESTRICT
command must have a value between [1] and the max number of columns [%s] in the report. Essbase Error 1001209 - Report error. TOP or BOTTOM returned rows argument value should be greater than 0.
Essbase Error 1001210 - Report error. Row Grouping member is not the same in the existing TOP, BOTTOM and ORDERBY statements.
Essbase Error 1001211 - Report error. The number of Columns in the Report exceed the allowed maximum of [256].
Essbase Error 1001212 - Invalid field name [%s] in <SelectMbrInfo command Essbase Error 1001213 - Invalid operator or value in expression involving [%s]
Essbase Error 1001214 - The UDA Command Does Not Support Attribute Dimension Members. Essbase Error 1001250 - Report Writer Sparse Extractor method will be executed
Essbase Error 1001251 - Sparse Extractor method is setting SUPMISSINGROWS on
Essbase Error 1001252 - No sparse row dimensions exist in the report. Optimization is not performed in Sparse extractor method
Essbase Error 1001253 - Report Writer Sparse Optimization method will be enabled up to row dimension [%s]
Essbase Error 1001301 - Report execution aborted. Sort buffer limit of [%s] rows have been exceeded.
Essbase Error 1002000 - Unable to Open Ascii File [%s] Essbase Error 1002001 - Reading Ascii Data File [%s]
Essbase Error 1002002 - Expecting Section Keyword and Received [%s] Instead Essbase Error 1002003 - Database Outline not Loaded for Database
Essbase Error 1002005 - Unable to Load Ascii File [%s] Essbase Error 1002006 - Ascii File [%s] Loaded
Essbase Error 1002022 - Config Parameter [%s] Too Low, Setting to Minimum [%s] Essbase Error 1002023 - Config Parameter [%s] Too High, Setting to Maximum [%s]
Essbase Error 1002030 - Database Name [%s] Has Invalid Characters, Name Must Be Valid File Name
Essbase Error 1002031 - Database [%s] Has Already Been Created
Essbase Error 1002032 - 64K Memory Segment Limit Prevents Creation of Another Database Context
Essbase Error 1002034 - Unable to Create Member Locking Semaphore Essbase Error 1002035 - Starting Essbase Server - Application [%s]
Essbase Error 1002076 - Unable to Update Server Configuration Information Essbase Error 1002077 - Error Reading Server Configuration Information Essbase Error 1002080 - Loading System CFG File [%s]
Essbase Error 1002081 - Processing System Command [%s]
Essbase Error 1002082 - Unknown System Config item [%s] Ignored Essbase Error 1002083 - System CFG File [%s] Load Completed
Essbase Error 1002084 - Missing System Config File [%s], Using Internal Defaults Essbase Error 1002086 - Unable to Open Spreadsheet Log File [%s]
Essbase Error 1002087 - Unable to create Spreadsheet Log Semaphore Essbase Error 1002088 - Starting Spreadsheet Log [%s] For Database [%s]
Essbase Error 1002089 - RECEIVED ABNORMAL SHUTDOWN COMMAND - APPLICATION TERMINATING
Essbase Error 1002091 - Must specify either session or system
Essbase Error 1002092 - Must specify one of enable|disable|dump|reset Essbase Error 1002093 - %s
Essbase Error 1002094 - Unable to create Last Rpl Semaphore Essbase Error 1002095 - Connection was terminated
Essbase Error 1002096 - Operation cancelled at user request Essbase Error 1002097 - Unable to load database [%s]
Essbase Error 1002098 - Timed out waiting for request queue
Essbase Error 1002099 - Essbase Kernel initialization (EssSmInit) failed. (userid = [%s] password = [xxxxxxx]).
Essbase Error 1002100 - Application [%s] init (EssSmAppInit) failed.
Essbase Error 1002101 - Application [%s] creation (EssSmAppCreate) failed. Essbase Error 1002102 - Illegal combination of reset values %d
Essbase Error 1002104 - Unable to create Performance Statistic Mutex
Essbase Error 1002105 - Attempt to register database for statistics gathering failed Essbase Error 1002107 - Invalid barrier synchronization type: %d
Essbase Error 1002108 - Cannot change owner for file: [%s] to [%s]
Essbase Error 1003000 - Unknown Item [%s] in Data Load, [%s] Records Completed
Essbase Error 1003001 - Duplicate Members From Same Dimension On Data Record, [%s] Records Completed
Essbase Error 1003003 - Unable to Continue Data Load After Item [%s], [%s] Records Completed
Essbase Error 1003004 - Incorrect Number Of Column Members In Symmetric File After [%s] Records Completed
Essbase Error 1003005 - Incorrect Number Of Column Members In Asymmetric File After [%s] Records Completed
Essbase Error 1003006 - Unknown Text File Type After [%s] Records Completed
Essbase Error 1003007 - Data Value [%s] Encountered Before All Dimensions Selected, [%s] Records Completed
Essbase Error 1003008 - With Data Value [%s], Too Many Values In Row, [%s] Records Completed
Essbase Error 1003010 - Data Value [%s] Does Not Match #Missing Value in Database, [%s] Records Completed
Essbase Error 1003011 - Data Value [%s] Does Not Match #Invalid Value in Database, [%s] Records Completed
Essbase Error 1003012 - Data Value [%s] is Greater Than Value in Database, [%s] Records Completed
Essbase Error 1003013 - Data Value [%s] is Less Than Value in Database, [%s] Records Completed
Essbase Error 1003014 - Unknown Member [%s] in Data Load, [%s] Records Completed Essbase Error 1003015 - Member [%s] is from the same dimension as members defined in Header Name, [%s] Records Completed
Essbase Error 1003022 - Unable to Open Data File [%s]
Essbase Error 1003023 - You have insufficient access privileges to perform a lock on this database
Essbase Error 1003024 - Data Load Elapsed Time : [%s] seconds
Essbase Error 1003025 - DATAERRORLIMIT reached [%s]. Rejected records will no longer be logged
Essbase Error 1003026 - Invalid Token Encountered near EOF Essbase Error 1003027 - Unable to open file [%s]
Essbase Error 1003028 - File [%s] is password protected and could not be read Essbase Error 1003029 - Encountered formatting error in spreadsheet file [%s] Essbase Error 1003030 - Unable to load file [%s]; see server logfile for details Essbase Error 1003031 - Commit at record [%s] during arithmetic data load Essbase Error 1003032 - Input line too long near record [%s]
Essbase Error 1003034 - Invalid member [%s] in data column
Essbase Error 1003035 - No data values modified by load of this data file Essbase Error 1003036 - Cannot load data. Member name too long: [%s]
Essbase Error 1004000 - Continuation Character Must Be Last Character on Line [%s] Essbase Error 1004001 - Extra { in Invalid Combination [%s]
Essbase Error 1004002 - Extra { in Invalid Combination [%s]
Essbase Error 1004003 - Processing Invalid Combination Group: [%s] Essbase Error 1004004 - Unknown Member [%s] in Invalid Combination Essbase Error 1004005 - ! Operator Not Implemented Yet. Ignored
Essbase Error 1004006 - '-' No Previous Member or Non-Matching Dimensions-Ignored Essbase Error 1004007 - Unknown Item [%s] in Invalid Combination
Essbase Error 1004008 - Only '-' Operator or Space Allowed Between Members of Same Dimension
Essbase Error 1004009 - ! Operator Not Implemented Yet. Ignored
Essbase Error 1004010 - '-' No Previous Member or Non-Matching Dimensions-Ignored Essbase Error 1004011 - Unknown Item [%s] in Invalid Combination