Tag Archives: essbase

A @JExport odyssey: installing, updating, and troubleshooting the JExport custom defined function

Beginning

I have seen various references to JExport on the Network54 forum over the years but I have never had a use for it — until now. I would like to use a cube to sort of ‘manage’ data, but the SQL backend for the data needs to be updated at the same time. I could just use a report script out of the cube and load that to the RDBMS, but I want to be able to dynamically update based on changes to the cube, rather than have to sweep the cube at certain intervals. This also plays well into the fact that Dodeca will be used as a front-end to update the data, so that when the new data is sent in (essentially as a Lock & Send operation), everything gets updated on the fly.

There doesn’t really seem to be a lot of JExport documentation out there, and I couldn’t find anything at all where people are using JExport with a SQL backend.  After stumbling through things with my feeble Java skills, lots of trial and error, and numerous cups of coffee, I was able to get things all setup and working.

First of all, I had to get the necessary files. The JExport documentation labels it as “shareware” (and it is not an officially supported piece of functionality from Oracle/Hyperion), so for the time being I will simply host a zip of the files here. If it turns out someday that I have to take this link down, then so be it, but for now, here you are.

Extracting the zip revels a PDF whitepaper and another zip file. Inside the other zip you will find the following files:

  • ExportCDF.jar — this is the jar file (a jar file is a zip file that contains a bunch of Java classes and some other things).  You can open this up in 7-zip to view the contents.
  • ExportCDF_Readme.htm — some information on installing and configuration stuff
  • exportRDB.mdb — this is only necessary for demo purposes, you don’t really need it
  • rdb.properties — you’ll want this for configuring the JDBC connection.
  • RegisterExportCDF.msh — this is convenient to install the functions to Essbase for you (or you can do it through EAS).
  • src folder — this has the source for all of the methods, you’ll need this in order to compile a new .jar yourself (like if you need to make changes to the implementation).
  • Other files — there are some sample calc scripts, and a gif file (yay!)

So now what?

Following the instructions that are included is fairly straightforward.  I can vouch for the fact that these work on Essbase 7.1.6 but I do not know how things may have changed under the hood for newer versions, so if it works at all, then you may need to modify them accordingly.

As the directions indicate, you need to put the ExportCDF.jar file in to $ARBORPATH/java/udf.  Also put the rdb.properties in the same folder.  Here’s a tip if you want to use JDBC for the exports: ALSO put the JDBC jar file in this folder.  For example, the SQL Server .jar I am using is sqljdbc.jar, so I put this file in the same folder as well.

Still following the included directions, you need make some changes to your udf.policy file in the $ARBORPATH/java directory.  Add the following lines:

grant codeBase "file:${essbase.java.home}/../java/udf/ExportCDF.jar" {
    permission java.security.AllPermission;
};

Here’s another part where the included directions may fail you, if you are planning on using a SQL backend: ALSO add a line for your JDBC jar file.  After getting some Access Denied errors during my troubleshooting process, it finally occurred to me that I needed to add a line for sqljdbc.jar too.  Here’s what it looks like:

grant codeBase "file:${essbase.java.home}/../java/udf/sqljdbc.jar" {
    permission java.security.AllPermission;
};

Again, change the names accordingly if you need to use different .jar files. If you have to use the DB2 trifecta of .jar files, then I imagine adding the three entries for these would work too.  If you can’t access the udf.policy file because it’s in use, you may need to stop your Essbase service, edit the file, and restart the service.  Now we should be all set to register the functions.  The included MaxL file was pretty handy.

Be sude to change admin and password to an appropriate user on the analytic server (unless of course, you use ‘admin’ and admin’s password is ‘password’…) and run it.  On a Windows server this would mean opening up the commandline, cd’ing to the directory with the MaxL script, and running ‘essmsh RegisterExportCDF.msh’.  If you can’t run this from the Essbase server itself, then change localhost to the name of the server and run it from some other machine with the MaxL interpreter on it.  If you go in to EAS and check out the “Functions” node under your analytic server, you should now see the three functions added there.  Due to the way they’ve been added, they are in the global scope — that is, any application can access them.  From here, the example calc script files included with JExport should help you find your way.  I would suggest trying to get the file export method working first, as that has the least potential to not work.  Here is the code (I have cleaned it up slightly for formatting issues):

/*
 *    Export to a text file
 *   arg 1:  specify "file" to export to a text file
 *   arg 2:  file name.  This file name must be used to close the file after the calculation completes
 *   arg 3:  delimiter.  Accepts "tab" for tab delimited
 *   arg 4:  leave blank when exporting to text files
 *   arg 5:  an array of member names
 *   arg 6:  an array of data
 */

/* Turn intelligent calc off */
SET UPDATECALC OFF

/*
 * Fix on Actual so that only one scenario is evaluated, otherwise a
 * record for each scenario will be written and duplicated in the export
 */
FIX ("Actual")	

  Sales (
    IF ("variance" < 0)
      @JExportTo("file","c:/flat.txt",",","",
         @LIST(
           @NAME(@CURRMBR(Market)),
           @NAME(@CURRMBR(Product)),
           @NAME(@CURRMBR(measures)),
           @NAME(@CURRMBR(year))),
         @LIST(actual,budget,Variance)
    );
    ENDIF;
  }

ENDFIX

/* Close the file */

RUNJAVA com.hyperion.essbase.cdf.export.CloseTarget "file" "c:/flat.txt" ;

Note that you have to anchor the JExport function to a member — so in this case, if you try to take out Sales and the parentheses that surround the IF/JExportTo/ENDIF, EAS will bark at you for invalid syntax.  The main thing going on here is that we are calling the function with the given set of parameters (hey, there actually is a use for @NAME!  I kid, I kid…).  For blog formatting issues, I broke the call to JExportTo up into multiple lines, but this is still syntactically correct.  In the case where we are exporting to a flat file, we also call a method to close the file (this part isn’t needed for JDBC).

Feeling brave?  Try and run it.  If everything works as planned, you are well on your way to CDF happiness.  In my case, I was aiming high and trying to get the JDBC stuff to work, but when I realized that was going nowhere, I decided to simplify things and go with the flat file approach.  Among other things, it showed me that I had to fix up my member combinations a bit before it would fit into a SQL table.

But I want to use JDBC!

This is where you get to benefit from my pain and experience.  As you saw above, you need to put the .jar file for your RDMBS in the folder I mentioned, AND you have to edit the udf.policy file for the jar(s) you add.  Now you need to shore up your rdb.properties file.  You can comment out everything in the file except the section you need, so in my case, I put a # in front of the DB2 entries and the Oracle entries, leaving just the TargetSQL entries.

# SQLServer entries:
TargetSQL.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
TargetSQL.url=jdbc:sqlserver://foosql.bar.com:1433;databaseName=EssUsers
TargetSQL.user=databaseuser
TargetSQL.password=databasepw

Notice that all of these entries start with “TargetSQL.” This can actually be anything you want it to be, but whatever it is, that’s how you will refer to it from your calc scripts.  This means if you just have one rdb.properties file but you want to do some JExport magic with multiple SQL backends, then you just put in another section like foo.driver, foo.url, and so on.  Note that the syntax I am using is explicitly calling out port 1433.  This is what my little SQL Server 2000 box is using — you may need to adjust yours.  Originally I did not specify my JDBC URL correctly, but make sure for SQL Server you put the databaseName parameter on the end.  You could perhaps get away with not specifying the database name here and instead prefixing it to your table name in the JExport command, but this works so let’s run with it.

So what does the code look like in the calc script now?  It’s the same, except we don’t need the RUNJAVA line at the end, and we change the JExportTo line to something like this:

@JExportTo("JDBC","TargetSQL","","TEST_TABLE",
  @List(@NAME(@CURRMBR(Product)),@NAME(@CURRMBR(Market))),
  @LIST(Actual));

The function is the same, but the parameters have changed a bit.  We now tell it through the first parameter that this is a JDBC connection, and we tell it “TargetSQL” in the second parameter.  This should look familiar because that’s essentially the prefix it’s looking for in the rdb.properties file.

The third parameter is blank (this was the delimiter field for text file exports).  We then tell it a table name in the RDBMS to put the data in to, then we give it a list of parameters and values.  In this case, the Java method quite literally creates a SQL INSERT statement that will look something like the following:

INSERT INTO TEST_TABLE VALUES ('<Product>', '<Market>', 2)

Of course, <Product> and <Market> will actually be the current member from those dimensions, and the 2 would be whatever the actual data cell is (based on the FIX statement you saw earlier, of course).  Assuming you did everything correctly (and the specification for TEST_TABLE in the given database is consistent with the data you are trying to insert), everything should be all hugs and puppy dogs now.

But it’s not all hugs and puppy dogs

If, like me, you got here, but things didn’t work, you are now wondering “how in the name of all that is holy do I figure out what is wrong?”  Let’s recap.  We did all these things:

  1. Copied the .jar file to the server in the proper folder
  2. Copied the rdb.properties file to the same folder
  3. Copied any necessary .jar files for our RDBMS to the same folder
  4. Edited udf.policy to add all of these .jar files
  5. Ran the .msh file or used EAS to add the functions to the analytic server
  6. Stopped and started the Essbase service as needed (and one more time, just for good measure)
  7. Added the example code to a calc script in one of our apps
  8. Verified the syntax and it verified for us (no “function not defined” type of errors)
  9. Ran it (duh)
  10. Verified that everything works, at least for the .txt file output

How do we troubleshoot?  If you’re like me, and don’t know Java inside and out, and know even less about how the custom defined functions are setup within Essbase, then you really have no idea how to go about this.  I tried looking at the server logs and the app logs and the Essbase console itself, but I just couldn’t find where, if anywhere, the output from the ExportCDF methods would go (you know, so I could see an actual error message about what might be wrong).

So I did what any normal Essbase developer would do and I dug in and brute forced it.  There are some System.out.println() commands at various places in the Export functions, so I know if I can get the output from these then I can see what the deal is.  At this point, I also knew that I was able to successfully write files on the Essbase server (with the Export function to a flat file method), so, lacking any other clear method, how about I output the error messages to a file instead?  This is actually pretty straightforward, but the tricky part (for me) was recreating the ExportCDF.jar file from the .java files I had.

First of all, before any code changes, let’s see if we can turn the .java files into a .jar that still works on the analytic server.  The source files are located in our original .zip file under the src folder.  Under src/ is the com/ folder and another series of folders that represent the Java package name.  Let’s start things off by putting a folder on the server somewhere so we have a place to work in.  I used the server for a couple of reasons: one, I’ll be using the JDK from the server to roll the .jar file, so keeping the versions consistent will help reduce a possible area that might not work, and two, when I need to revisit this, I have all the files I need location in a place that is easy to get to, and regularly backed up.  If I were a real Java master I would probably do it all on test and target the architecture to make sure they’re compatible (although just for kicks, I compiled with 1.4 and 1.6 and the output appears to be the same).

If you have some fancypants Java toolchain that can do all this for you, then you should probably do that.  If you just pretend to know Java, you can get by with the following directions.  After putting all of the files in a folder to work on, I ended up with a jexport/ folder containing the com/ folder hierarchy. Next, create a folder such as “test” under the jexport/ folder.  This is where we will put the compiled Java files so we don’t muddy up our com/ folder hierarchy.  Then we need a command to compile the .java files to .class files, and another command to roll the .class files up into a single jar.  You can use the following commands, and optionally stick them in a batch file for convenience:

javac -d test -classpath %ARBORPATH%\essbase.jar com\hyperion\essbase\cdf\export\*.java
jar cf ExportCDF.jar -C test com

Note that for the classpath we need to refer to the essbase.jar file that is in our Essbase folder somewhere.  Hopefully all compiles correctly and you end up with a shiny new ExportCDF.jar file.  Stop the Essbase service, copy the new file to the $ARBORPATH/java/udf folder, start things up, and test that it works.

It was at this point in my own tribulations that I was quite pleased with myself for having used Java source code to create a module that Essbase can use, but I still hadn’t solved the mystery of the not-working JDBC export.  And since I still don’t know how to get the output from Essbase as it executes the function, the next best thing seems to be just writing it to a file.  With a little Java trickery, I can actually just map the System.out.println commands to a different stream — namely, a file on the Essbase server.

You can add the following code to the ExportTo.java file in order to do so (replacing absolute file paths as necessary):

/* Yes, there are much better ways to do this
   No, I don't know what those are */

try {
    FileOutputStream out = new FileOutputStream("D:/test.txt");
    PrintStream ps = new PrintStream(out);
    System.setOut(ps);
} catch (FileNotFoundException e) {}

It was after doing this that I discovered that in my particular case, my JDBC URL was malformed, and I got my access denied error (which was fixed by putting the sqljdbc.jar references in the udf.policy file).  After all that, I fired up the JDBC test once more, and was thrilled to discover that the data that had been working fine getting exported to a flatfile, was now indeed being inserted to a SQL database!  And thus concluded my two days of working this thing backwards in order to get JExport working with a SQL backend.

And now there is some JExport documentation out on the web —  Happy cubing!

Speaking at ODTUG!

The highly regarded Essbase figure Tim Tow of Applied OLAP called me the other day to inform me that my submissions for ODTUG were accepted! Details are still being worked out, but the focus of the first talk will be on data load optimization (a matter very near and dear to my heart) as well as a sponsored presentation on Dodeca, which is one of Applied OLAP’s flagship products (and also increasingly near and dear to me).

These will be my first presentations in front of the Hyperion/Essbase community so I am hopeful when the time comes I will be able to cram the hour into one of the most useful and comprehensive sessions ever. For those of you out there in the Essbase community that I haven’t met or spoken to, I’ll be seeing you in Monterey next year!

Automate that old cube archive process!

Your server may have dozens or even hundreds of cubes on it.  A common strategy with a large and slowly changing Measures dimension (or some other dimension like Product) is to spin off a copy of the cube after a certain time period, typically the fiscal year end.  There are a number of different reasons that you might do this.  First, the cube may simply focus on Current Year and Prior Year, or a fixed number of years and scenarios such that the cube becomes too unwieldy when you start adding more.  Second, if you need to be able to go back and pull a report so that it looks exactly how it did in a certain fiscal year, then you may need to spin off the cube.  Depending on how many cubes you end up spinning off for each fiscal year, it may be necessary to go and clean them up at some point, but you might still want to keep them around, just in case.  You can do this by hand by stopping the app, zipping up the app folder and all its contents, and deleting the app from within EAS.

Here is an example of a batch file you could use on Windows.  This relies on the free 7-Zip package being installed somewhere.  The nice thing about this approach is that while it uses MaxL, it doesn’t actually have any MaxL files — it just injects the MaxL command via the command-line.  Edit the variables for your setup, and you’re on your way.  It’s not pretty but it’s nice if you have to go cleanup a bunch of apps!  Happy cubing — Jason. [download zip of the following batch file]

@echo off

SET USER=adminuser
SET PW=adminuserpw
SET SERVER=essbaseserver
SET APPPATH=D:\Essbase\App
SET ZIP="7zp\App\7-Zip\7z.exe"

@echo.
@echo -------------------------------------------
@echo This is the cube archiver utility...
@echo.
@echo Looking for App %1 ...

IF NOT EXIST %APPPATH%\%1 GOTO NoApp

@echo.
@echo I found it at %APPPATH%\%1 ...
@echo.
@echo Attempting to stop the app...

REM essmsh -l %USER% -p %PW% -s %SERVER% StopCube.msh %1

echo alter system unload application %1; | essmsh -s %SERVER% -l %USER% %PW% -i

@echo Archiving the app ...

%ZIP% a -tzip EssApp_%1.zip %APPPATH%\%1

echo.

choice /M "Okay to delete app %1"

IF ERRORLEVEL 2 GOTO Done

echo alter application %1 enable startup; | essmsh -s %SERVER% -l %USER% %PW% -i
echo drop application %1 cascade force; | essmsh -s %SERVER% -l %USER% %PW% -i

GOTO Done

:NoApp

@echo I could not find that app at %APPPATH%\%1 !!!

:Done

Optimizing Essbase Automation Jobs (for fun and profit!) – Part 1

I freely submit that I am a complete geek.  But it’s okay, because I have come to accept and embrace that inner geek.  Anyway… Many organizations run jobs to perform a task as certain intervals.  Your job as a good Essbase/Hyperion administrator is to be lazy — that is, make the computers do the boring stuff, while you get to do the fun stuff, like thinking.  What we end up doing is setting up batch and script files that do all of the things in a particular sequence that we might do by hand.  So instead of logging in, copying a data file, clearing a cube, running a calc, loading some data, running another calc, and all that other stuff, we do this automatically.

After a while, you get a feel for how long a certain job takes.  The job that loads last week’s sales might take about an hour to run.  The job to completely restate the database for the entire fiscal year might take six hours to run.  This probably also becomes painfully obvious when it turns out that the job didn’t run correctly, and now you need to run it again (and keep those users happy).  I am all about jobs running as fast as they can.  This is useful on a few different levels.  First and probably foremost, it finishes faster, which is nice, especially for those times when the sooner you have data the better.  It’s also nice when you need to re-run things.  It can also open up some new possibilities to use the tool in ways you didn’t think or know you could — such as during the business week, getting live updates of data.

Typically there are a few big time-killers in an automated process.  These are the data loads, calculations, and reports that are run.  There are also some others, such as calling out to a SQL server to do some stuff, restructuring outlines, pulling tons of data off the WAN, and all that.  You can always go through your database logs to see how long a particular calculation took, but this is a bit tedious.  Besides, if you’re like me, then you want to look under EVERY single nook and cranny for places to save a little time.  To borrow an a saying from the race car world, instead of finding one place to shave off 100 pounds, we can find 100 places to shave off one pound.  In practice, of course, some of these places you can’t cut anything from, but some of them you can cut down quite a bit.

The first, and probably most important step to take is to simply understand where you are spending your processing time.  This will give you a better window into where you spend time, rather than just a few things in the normal Essbase logs.  There are numerous ways you can do this.  But, if you know me at all by now, you know that I like to do things on the cheap, and make them dead-simple.  That being said, the following posts will be based around Windows batch file automation, but the concepts are easily portable to any other platform.

First of all, take a look at the below graph:

Total Seconds to Run Essbase Automation Job

Total Seconds to Run Essbase Automation Job

What you’re looking at is a graph that has been created in Excel, based on data in a text file that was generated as a result of a profiling process.  The steps are as granular as we want them to be.  In other words, if we simply want to clump together a bunch of jobs that all run really fast, then we can do that.  Similarly, if we want to break down a single job that seems to take a relatively long amount of time, we can do that too.  Each segment in the starcked bar chart is based on the number of seconds that the particular step took.  We can see that in the original (pre-optimization) scenario, the orangey-brown step takes up a pretty considerable amount of the overall processing time.  After some tweaking, we were able to dramatically slash the amount of time that it took to run the whole job.  And, in the third bar, after yet more tweaking, we were able to slash the overall processing time quite a bit again.

The important thing here is that we are now starting to build an understanding of exactly where we are spending our time — and we can therefore prioritize where we want to attempt to reduce that time.  My next post will detail a sample mechanism for getting this kind of raw data into a file, and the next few posts after those will start to dig in to where and how time was shaved off due to better outline, system, and other settings and changes.  Happy profiling!

A MaxL quickie to unload those databases that are eating your precious memory

Due to various business requirements, some organizations end up archiving many of their cubes each year.  For example, if you have a huge Measures dimension that is constantly changing (even in subtle ways), but you need to be able to go back at some point in the future and see what some numbers looked like at some particular point in time, you might find yourself spinning off a copy of the cube that sits on the server.  Most of the time it just sits there, dormant, not being used.  But every now and then someone comes along and spins it up so they can refresh some obscure report.  It’s got a decent sized outline to it, so the overhead just on having this cube running is probably in excess of 50 megs of memory, just to sit there!  If you aren’t rebooting your servers that frequently, that app and database are just going to sit there until someone comes along and stops it.  This might not be a problem for your current situation, but for this particular server, there are over two-hundred apps available at any given time — and RAM is a finite resource.

Now, we could just setup a job to unload everything — and indeed, that’s part of the solution.  But I like to keep the core apps hot so they don’t have to spin up when people (or myself) login.  So what to do?  Create a whitelist of core apps and databases and write a little MaxL to unload everything, then start just the things I want.

For the purposes of brevity, I will just assume that connect.msh has a valid login statement in it.  Then the code to unload the apps (unloadall.msh) is pretty straightforward (spool to some output file as needed…):

msh “connect.msh”;
alter system unload application all;
logout;
exit;

Then we have a script file that starts up a specific app/db passed on the command line (startappdb.msh):

msh “connect.msh”;
alter application $1 load database $2;
logout;
exit;

So, then we have a simple text file with the list of each app/database combination to fire up (whitelist.txt):

App1 Db1
App2 Db2

Then, in a simple Windows batch file we could do the following (which I imagine you could port pretty easily to whatever platform/scripting combination you have):

essmsh unloadall.msh
FOR /f “eol=; tokens=1,2 delims= ” %%i in (whitelist.txt) do essmsh startappdb.msh %%i %%j

That’s it!  Dead simple, but effective.  The FOR in batch is basically broken out as follows: eol is the end-of-line or comment character (which we aren’t using in the data file in this instance), the first and second fields are broken down into %i and %j, and the delimiter between them is a space.  Then we call the script that will start it up (named startappdb.msh, passing along the App and Db).  There are many ways you could do this (passing commands on the command line itself) but this method to me is clean and simple.

Jason’s Top 10 Essbase Data Load Optimization Tips

I received an email today from someone looking to speed up their data loads, specifically their ASO data loads that seem to be taking too long.  This is, of course, an important topic for many Essbase cube wranglers.  I would be willing to bet that many people spend more time optimizing calcs and may even neglect profiling their performance on data loads.  You might be surprised just how long your automation spends doing a data load.  That being said, there are several different scenarios you may find yourself in and different places to optimize.  Of course there are more items that can go on this list, but here are the big ones off the top of my head.  In other words, if I was tasked with improving my data load speed, here’s what I would look at:

  1. Do you need to load the data in the first place? I know this seems a bit rudimentary, but if you are loading data that you don’t even need (and can possibly help it), then don’t waste your time on it.
  2. Use the fastest connection. Architecturally you may simply have to load data one way or another (from a text file, from a SQL server, off the SAN, etc), but, hands down the fastest data loads I’ve seen (short of trying to load from a RAMdisk, although I’m dying to try it) are from different physical hard drives attached to the Essbase server.  With a good RAID setup the performance is still quite good if you are loading text files from the Essbase server.  If you are loading your records from an RDBMS across the WAN, you might be killing your performance due to network bottlebecks.  Another option is to put the RDBMS on the same box as Essbase.  I know many people do this, but personally I am not a huge fan of this option.  My Essbase servers tend to have plenty enough to do without having SQL software on the box to worry about.  Additionally, we license SQL by the CPU, and since my Essbase servers are all quad-proc and the SQL servers are getting by just fine with dual-proc, the cost to license it for two more CPUs is quite significant — particularly when all of the other optimization methods are essentially ‘free’.
  3. If loading from a SQL RDBMS: your bottleneck may very well be the network speed here.  If you can, make sure your Essbase server is on the same LAN as the SQL server, with the fastest possible connections (Gigabit or better).  If you are loading from a SQL table and using the WHERE clause in your load rule, or you are loading from a SQL view, make sure you have good indexes setup in SQL.  This can make a HUGE difference if you are loading just the records from Period 08 and you have an index, versus making the SQL server scan the entire table.  If performance is extremely critical see if you can pre-stage text files on the Essbase server (like tip #2).  If loading to BSO, order the rows to match your dense/sparse settings and the order of the outline (basically, you are trying to give Essbase a hand and load up a whole dense datablock in as few passes as possible).
  4. Do as little work as possible in the load rule.  The cost of doing text replacements, column swaps, accept/reject rules, and all that stuff can really add up.  If you can do this elsewhere then do it there (e.g., in a SQL view or having your ETL software prep it for you in the format you need).
  5. Tweak your settings. The Essbase.cfg has some black magic stuff in it.  Try the DLTHREADSWRITE parameter (check the DBAG for details) to see if you can throw some threads at the problem.  Watch your performance on the server — slamming all the CPUs may cause performance for other users to decrease.
  6. (BSO) Sort the data.  Try to give Essbase a a hand.  If your records are sorted such that Essbase is looking at the fewest blocks at a time, and reading the items in the same order as the outline, you’ll reduce the punishment to Essbase and help it load records faster.
  7. Outline optimization.  This one, of course, applies to just about everything you do in Essbase.  Smaller datablocks are your friends — in the sense that if you are committed to your dense/sparse settings, see if you can lighten up the dense blocks with some strategic dynamic calcs and labels.  For instance, my Time dimension is usually Time –> Quarters –> Periods and nine times out of ten, when Time is dense, the Quarters and Time members are dynamic calc instead of stored.  Of course, there may be numerous other reasons that the dense/sparse settings are what they are (calc performance, retrieve performance, etc), so don’t go making changes without understanding them.
  8. Load and Swap. You may find it useful to load up a cube separate from the production cube, then implement MaxL to drop it on top of the production cube (all on the same server of course).  This way you do all of the hard work in one and when it’s ready you can just pop it in place.  I think this works better in theory than in practice, at least for me.  I initially tried this with some very large ASO cubes, and although the performance wasn’t terrible, at least with version 7.1.x of Essbase, the swap process (the MaxL was a “create or replace” command) was not very graceful — it would shake the server to its knees during the swap process.  I eventually dropped this method in favor of using all the other points to optimize and make the down time even smaller.
  9. Use your own load rules instead of EIS. Out of convenience, and particularly on small cubes, I will load data with EIS.  However, you are going to see better performance when you use your own load rules and optimize them.  Besides, you probably already did it this way since you are concerned about performance, and this is probably in an automation script anyway.
  10. Reduce the size of the data members.  Any time your bottleneck is the speed of the transmission of information, try to cut it down (particuarly for SQL loads across the LAN).  For example, don’t have the field be “Period 01” if you can use “P01” instead.  Use “08” instead of “Yr2008”.  Try to balance this with how much work your load rule is doing (Tip #4).

As always, experiment!  Try different combinations to see what works and what doesn’t.  Remember that for squeezing out that extra bit of performace, you are trying to help Essbase do its job better.  Always remember that dimensions can be and probably are (on cubes you didn’t build) setup how they are setup for a reason — i.e., you might have worse load time but the benefit is faster calc or retrieve time.  If you have your own tips please let me know!

I submitted two abstracts for ODTUG!

Last year in New Orleans, the Hyperion track at ODTUG was an unqualified success.  It was such a success, in fact, that hundreds of abstracts were submitted for next years conference in Monterey.  I submitted two of those, so wish me luck.  I’d like to talk about an end-to-end Essbase automation system profiling/performance/optimization effort which would cover all the Essbase optimizationi techniques collectively (Outlines/Dense/Sparse, Calculations, MaxL, and some other tricks I have up my sleeve).  I also submitted an abstract to talk about how using Dodeca has improved our reporting systems in many cases, and allowed us to ditch some spaghetti code VB apps in the process.  So I am anxiously waiting to see if I get to talk next year….

The Essbase Spreadsheet Automation Trick

This one is a bit of a blast from the past.  And I mean that — the timestamp on this file is over five years ago.  You may have heard people refer to this method.  If you’ve ever found yourself getting confused over what-job-runs-when-and-on-what, then this might be a technique that works for you.  Obviously, this one was written when ESSCMD was all the rage, but you could no doubt adapt it to whatever you want.

In the attached spreadsheet, there are rows and rows of different jobs — just ESSCMD scripts — that all serve a particular purpose (and in this case, there are two sets of databases: a “Weekly” database and a “Daily” database.  This is for historical performance reasons — everything is in one cube now).  The days of the week are in columns, with an X to denote if the job runs on that day.  Note that the jobs are numbered so you always run them smallest to largest.  There is a simple Excel formula that populates the corresponding columns to the right if that script has an X for that day.  The idea is that you can then copy that and paste it into your batch file and then call all those scripts in that day’s file.  It’s remarkably simple, and it works well.  It’s also self-documenting: any time you update the spreadsheet, and the subsequent batch file, you just print up the schedule again and you have up-to-date documentation.

This particular example shows its age a little (ESSCMD scripts, hard coded paths, and all that), but it shows the concept quite nicely.

ninja_schedule

Automated EIS outline builds

Here’s a little quickie that I thought of and put in the other day. If you use the “export script” feature in EIS so that you can use that in conjunction with EIS in order to script your outline builds, you get a script that is specific to a particular server. I like to keep my test machine automation totally synced up with my production machine automation, but if I needed different scripts for different servers, then I would have to have a different automation file. Well, here’s what I did:

Name the outline build script with thte name of the server in it. I have my server name already set in an environment variable (it’s used all over the place for various automation and logging things). So if the .cbs was normally foo.cbs, and the name of the machine (prod) is bar01, and the test machine is bar02, and I reference the outline build command as foo_%SERVERNAME%.cbs, then whichever machine the script runs on, it’ll grab the right script. Kind of a small thing but I like it!

Normalize a fact table for a better EIS model

Generally I don’t load data through EIS because quite honestly, I fudged some of the EIS models when I first made them and now I’m pretty much married to them.  They are perfect for loading the outlines, but not data.  Earlier this week I was playing around with one of my newer models, trying to keep it ‘clean’ as it were, and after playing around with it, I got it set just right so the default SQL (quite a gnarly little query, really), much to my amazement, loaded exactly the data it needs from the fact table.  The one major thing I have had to tweak is the format of the fact table.  Upstream stores 3 facts in the table: for each period/location/account we store the value for the current year, prior year, and the budget value.  What I do is kind of normalize the data with a SQL script that takes all that data and puts it into a different table that has a year (YR) field and a scenario type.  This way in the EIS model, I can link this modified fact table cleanly to TIME, YEARS, SCENARIO, LOCATION, and possibly DEPARTMENTS.  It works quite nice!