Wednesday, November 12, 2014

MaxL Quick Tip - Loading Multiple Files with Wildcards

Hey Everyone,
Call me "late to the party" if needed, but I recently came across a bit of functionality that I had never used before and felt the need to compile a bit of research on the functionality.  Introduced in Essbase v11.1.2.2, there was a new MaxL feature that allows you to use wildcard characters in flat file names when loading a database.

Below is an example of how to import multiple files, in parallel, using these wildcards:

import database SCOTTAPP.SCOTTDB data from data_file "D:\\Data\\ThisIsData*.txt" using SERVER rules_file 'LoadItUp' on error append to "D:\\ErrorFiles\\ThisIsData.err";

This code above will load any file in the D:\Data directory that the name starts with "ThisIsData".  

For example the following files would be loaded:
  • ThisIsData.txt
  • ThisIsData_1.txt
  • ThisIsData_2.txt
  • ThisIsData_ButNotGoodData.txt

The following example files wound NOT be loaded:
  • ThisIsGoodData.txt
  • ThisIsData.csv

Below is an excerpt from the v11.1.2.2 user guide in regards to using wildcards in this scenario.

To import from multiple files in parallel, use the wildcard characters * and/or ? in the IMP-FILE name so that all intended import files are matched.
  • * substitutes any number of characters, and can be used anywhere in the pattern. For example, day*.txt matches an entire set of import files ranging from day1.txt - day9.txt.
  • ? substitutes one occurrence of any character, and can be used anywhere in the pattern. For example, 0?-*-2011.txt matches data source files named by date, for the single-digit months (Jan to Sept).

Hope this helps!  If you have any questions, please post them below in the comments section and I will answer as quickly as I can.

Monday, February 24, 2014

Mysterious Essbase Error When Loading Data With a Flat File

Hey Everyone,
I know it has been a while since I last posted, but I promise I am still here hard at work.  :)

I recently came across an error message from the output of one of my MaxL scripts that I have never seen before.  So my first thought is, "Hey Scott, why not Google that error message to see what is going on?".  This is usually a pretty safe attack on these types of errors, but in this case, I was mistaken.  At the time of searching for this error, there was nothing on Google about it.  Really, I am serious.  Nothing.

So here it is:
MAXL> import database PINBALL.PARTY_DB data from server data_file "some_great_text_file.txt" using server rules_file "LoadUp" on error write to "D:\EssbaseErr\dataload_some_great_text_file.err";

OK/INFO - 1019061 - Reading Rule SQL Information For Database [PARTY_DB].
OK/INFO - 1019025 - Reading Rules From Rule Object For Database [PARTY_DB].
   ERROR - 1013295 - Server Request Fails with error code [1003086].
   ERROR - 1241101 - Unexpected Essbase error 1013295.

The line I am referring to is the "ERROR - 1013295 - Server Request Fails with error code [1003086]" message.  What is that?

So, since Google failed in allowing me to be lazy, I am going to have to dig down a bit deeper and find out what is going on.  I grab a cup of coffee and prepare myself for a fun little research activity.

First thing I check?  Is the file physically in the app folder...  It is NOT.  So basically, this error message is only trying to tell me that the file I was trying to load into Essbase does not exist.  I didn't even get to take the first sip of my coffee.

Now you may be asking why would I post a problem with such a ridiculously easy solution...  Simple answer.  I did it because no information (at the time of posting this) exists on the internet for this error message.

You are very welcome!

Hope you enjoyed!

Tuesday, November 19, 2013

New Essbase Subreddit!

Hey Everyone,
I know it has been a while since my last update, but I found out some pretty exciting news.  Jason Jones has created an Essbase Subreddit where us Oracle EPM professionals can post links and talk about nerdy Essbase related topics. :)

If you are unfamiliar with Reddit, it is a website where anyone can post links, questions, stories, or whatever they want on the internet.  These posts are ranked and sorted based on upvotes and downvotes from the community.  So only the good stuff makes to to the top!  These posts are also organized into categories called Subreddits.  You can view these sorted posts by Subreddit which is pretty cool.  You just have to try it out to fully appreciate.  :D

Here is a link to the new Subreddit that Jason created:

Hope to see you there!

Monday, March 11, 2013

OBIEE 101 Blog Farewell

John Minkjan, who runs OBIEE 101 (, has just posted that he has gotten a new position at Ciber in the Neatherlands!  Congrats John!

Why is this so important to me that I made a post about it?

John has been providing the BI community with his shared experiences and best practices for many years through his Blog.  His promotion within Ciber is great , but an unfortunate one for us Oracle BI people since he will now be focusing on Microsoft BI and will not be posting to OBIEE 101 anymore.  No worries though, all of his existing content will still be available via his website and he has plenty of other Blogs that he runs that we can still browse.

I want to personally  thank John for his contributions to the BI space and his willingness to share his experiences and best practices with us over the years.  I know he has helped me greatly.  Congrats on the new position John!

Below is John's farewell post.

Monday, February 18, 2013

ASO Outline Compaction Using ESSCMDQ - The Good and Bad

ASO Essbase cubes have an amazing architecture overall.  Unfortunately, like every beautiful thing, there are some flaws up close.  Today, I am going to be talking about the issue of outline fragmentation and what to do about it.

The issue will arise when an ASO cube has been set up to automatically rebuilt it's dimensionality from an external source using a load rule.  Whether this be one or all dimensions in the outline, it does not matter.  This fragmentation also exists with ASO cubes built with EPMA Shared Libraries, but it is less of an issue as the fragmentation is less severe.  Just keep in mind the higher the frequency of dimensionality rebuilds, the higher fragmentation rate.  Eventually, without proper treatment, your ASO outline will expand in size and eventually you will not be able to open it in EAS.

Luckily, Oracle offers a free utility that will allow us to compact ASO outlines and return them to their normal size.  This utility is called ESSCMDQ.cmd.  No, that is not a typo, it is a utility similar to ESSCMD containing ASO outline compaction functions.  This utility is most likely not installed on your system and you will need to install it on the server you are running your batch scripts from.  You can download the utility from Oracle at the link below.

Please make sure that prior to installation, you read and understand the documentation accompanied with it.

Installing ESSCMDQ

  1. Download the documentation PDF file from the Oracle link
  2. Download the ZIP file that corresponds to your operating system and version of Oracle Essbase. 
  3. Unzip the ZIP file into a TEMP directory. 
  4. Copy the ESSCMDG and ESSCMDQ files to your [arborpath]\bin directory. 
    1. Typically: C:\Hyperion\products\Essbase\EssbaseClient\bin

Scott's Compaction Script

I created the windows batch script below that shows how to compact your ASO outlines.  This file as it sits is dynamic enough for you to call it from your batch scripts.

Please note: Do not run this utility on every dimension build.  (read why in the "ESSCMDQ Compaction Issues" section at the end of this post)

Batch Script Process Flow:

  1. Creates MaxL script on the fly (since you cannot pass variables to the MaxL using the ESSCMDQ utility)
  2. Launches the utility using the temporarily created MaxL
  3. Outputs the utility results to a log file
  4. Deletes the temporary MaxL script (Important because the MaxL will contain the password of the account used to compact the OTL)

Below is the code for the batch script. Please be aware that there are 2 environment variables that must be set for this script to function properly. I also recommend that you call the batch script CompactOTL.bat.

ECHO off

REM ###################### IMPORTANT NOTE ############################################
REM ## Before this batch can be run, the ESSCMDQ.exe 
REM ## application must be downloded and copied to the 
REM ## ESSCMDQpath below.
REM ##
REM ## You can get this utility and it's associated documentation from the URL below
REM ##
REM ##################################################################################

REM **********************Version*****************************************************
REM Version: 1.1
REM Created By Scott Danesi
REM **********************************************************************************

REM **********************PASSED PARAMETERS*******************************************
REM %1 = ServerName
REM %2 = AppName
REM %3 = DBName
REM %4 = UserName
REM %5 = Passord
REM **********************************************************************************

REM *************Environment Variable Declarations (Change These)*********************
SET ESSCMDQpath=C:\Hyperion\products\Essbase\EssbaseClient\bin
SET LocalPath=C:\Scripts\CompactOTL
REM **********************************************************************************

REM *************System Variable Declarations (DO NOT CHANGE)*************************
SET ServerName=%1
SET AppName=%2
SET DBName=%3
SET UserName=%4
SET Password=%5
set TimeStamp=%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%_%Time:~0,2%%Time:~3,2%
SET LogFilePath=%LocalPath%\CompactOutline_%TimeStamp%_%AppName%-%DBName%.log
SET MaxLpath=%LocalPath%\CompactOutline.mxl
REM **********************************************************************************


del %MaxLpath%

REM ****************Create MXL File from variables************************************
ECHO Login "%ServerName%" "%UserName%" "%Password%" ; >> %MaxLpath%
ECHO Select "%AppName%" "%DBName%" ; >> %MaxLpath%
ECHO Openotl "2" "1" "%AppName%" "%DBName%" "%DBName%" "y" "y" "1"; >> %MaxLpath%
ECHO Writeotl "1" "2" "1" "%AppName%" "%DBName%" "%DBName%" ; >> %MaxLpath%
ECHO Restructotl "1"; >> %MaxLpath%
ECHO Closeotl "1"; >> %MaxLpath%
ECHO Unlockobj "1" "%AppName%" "%DBName%" "%DBName%" ; >> %MaxLpath%
ECHO Logout; >> %MaxLpath%
REM **********************************************************************************

REM ************Call ESSCMDQ for Restructure******************************************
call %ESSCMDQpath%\esscmdq.exe %MaxLpath% > "%LogFilePath%"
REM **********************************************************************************

del %MaxLpath%


You can call the batch script from another batch script with the following syntax:
CompactOTL.bat EssbaseServerName AppName DBName UserName Password

ESSCMDQ Compaction Issues (Please Read Before Running)

Beautiful right?  Right, but again not without some minor flaws.  I have been using this script for many years without any issues until recently.  I have an environment that is running v11.1.1.3 and doing ASO dimension builds twice a day.  Out ASO outlines were starting to get out of hand in size.  I decided to put the compaction code in the twice daily batch scripts to compact after every rebuild.  This worked great for a little while, but eventually it corrupted one of the outlines so bad that it could not be recovered except from a backup.  This corruption was a result of the dimension build not making any changes to the outline.  Apparently compacting an outline that is 100% unfragmented will cause it to corrupt   I am still in the process of researching exactly why this is happening.  Please do not run this utility on every dimension build.  I will post back my findings as I get them.

Tuesday, February 12, 2013

Encrypting MaxL Passwords Using an Environment File

We all know the importance of using an environment file in our batch scripts right?  No?  Well I am not going to lecture you right now on that...  We are going to talk about how you can encrypt your MaxL passwords while still using an external environment file to store credentials and server information.

There is a ton of information out there on all these different Oracle blogs on how to properly encrypt your MaxL Scripts, but there is not too much out there on how to use this encryption while still utilizing an environment file.

For this example, we are going to walk through a series of files I created to demonstrate this functionality. Each file is prefixed with a step number and can be downloaded below if you would like to follow along.

Please note, in order to run these batch scripts included in this exercise, you need to have the Essbase Client installed on your local machine.  Some modifications to the batch scripts will need to be made for versions

Step 1: 01-CreateKeys.bat

This is a batch script that will automatically create a unique set encryption keys for your system.  Run this script as it is to create the file for the next step.

Example File Contents:
echo on
essmsh -gk >02-Keys.txt

Step 2: 02-Keys.txt

This text file is automatically generated by the batch file in the first step.  It will contain a unique set of keys to use for encryption and decryption of your passwords.

Example File Contents:
Essbase MaxL Shell - Release 11.1.1 (ESB11.
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.

Public Key for Encryption: 2257,1114744343
Private Key for Decryption: 52844593,1114744343

MaxL Shell completed

Step 3: 03-PasswordInfo.txt

This text file needs to be modified with the Public Key from the "02-Keys.txt" file generated in Step 2 along with the password that you want to encrypt.  This is technically a CSV file using the "pipe" as a delimiter.

Once you have modified this file, continue to the next step.

Example File Contents:

Step 4: 04-EncryptPassword.bat

For this step, you will run this batch script which will encrypt the password using the Public key that you entered into the file in Step 3.  This script will create a text file containing your freshly encrypted password.

Example File Contents:
FOR /F "tokens=1-2 delims=|" %%A IN (03-PasswordInfo.txt) DO (
SET NewPassword=%%A
SET PublicKey=%%B

essmsh -ep %NewPassword% %PublicKey% >05-EncryptedPassword.txt

Step 5: 05-EncryptedPassword.txt

This is the output file from Step 4 that contains your newly encrypted password.  You will need to reference this file when setting up the Environment File in the next step.

Example File Contents:
Essbase MaxL Shell - Release 11.1.1 (ESB11.

Copyright (c) 2000, 2009, Oracle and/or its affiliates.

All rights reserved.

Encrypted Data: 031017652655928821892075652309090891

MaxL Shell completed

Step 6: 06-EnvFile.txt

So here we are...  We now have everything that we need to populate the environment file.  This environment file is going to hold 4 peices of critical connection information for our test script.  For this example, the setting must be in the following order; username, encrypted password, server name, and private key from the 02-Keys.txt file.  Each of these values needs to be separated by the pipe | delimiter.  Take a look below for my example.

Example File Contents:

Step 7: 07-Test.bat

This is our main batch script for testing that our environment file is working properly.  It first reads from the environment file that we updated in Step 6 and sets the variables accordingly.  It will then run a MaxL script that I included that logs into Essbase and logs back out (08-Test.mxl).  This batch also creates the "09-TestMaxLOutput.txt" file so that you can verify that everything worked.

Example File Contents:
FOR /F "tokens=1-4 delims=|" %%A IN (06-EnvFile.txt) DO (
SET username=%%A
SET password=%%B
SET servername=%%C
SET key=%%D

echo on

essmsh -D 08-Test.mxl %key% %username% %password% %servername%

That is pretty much it!  So don't forget to download the zip file that contains all of this sample code and start experimenting for yourself.  Feel free to post any questions int he comments below and I will answer.


Wednesday, February 6, 2013

NCOAUG Winter Training Day 2013 Presentation

Great News Everyone!  I will be presenting at the North Central Oracle Application User Group Winter Training Day.  I will be discussing Managed Services and how to best utilize a Managed Services Partner.  I am very excited.

Event Info:
2013 Winter NCOAUG Training Day
Friday, February 22, 2013 8:00 AM - 4:00 PM (Central Time)
Drury Lane
100 Drury Lane
Oakbrook Terrace, Illinois 60181
(630) 530-8300

Registration deadline is Wednesday, February 13, 2013.

More info can be found at their website:

Hope to see you there!