Friday, November 9, 2012

Managing Essbase data load error kickouts

With the latest version of the Star Command Center File Plugin, a list of of kickouts from an Essbase data load can be easily extracted.  This function enables administrators to isolate values in the error file and use the results to perform a variety of tasks such as refresh metadata or notify users that an error has occurred with specific detail.   Typically administrators need to write sophisticated code in PowerShell, Python or Perl to automate this type of task, but with Star Command Center a solution can be quickly created.  In this post, we will explore how.

In the typical Essbase Error file, we see an example like the following.  Note the Essbase members that are highlighted:
\\ Member Caffeine Free ColaX Not Found In Database
California,Caffeine Free ColaX, Sales, 145,132,125,110,106,96,87,87,109,109,116,102

\\ Member CaliforniaX Not Found In Database
CaliforniaX,Caffeine Free Cola, Sales, 145,132,125,110,106,96,87,87,109,109,116,102

\\ Member CaliforniaY Not Found In Database
CaliforniaY,Caffeine Free Cola, COGS, 95,104,109,123,127,141,154,154,122,122,113,127

\\ Member CaliforniaX Not Found In Database
CaliforniaX,Caffeine Free Cola, Marketing, 30,33,34,39,40,45,49,49,39,39,36,40

\\ Member CaliforniaY Not Found In Database
CaliforniaY,Caffeine Free Cola, Payroll, 22,22,22,23,23,23,22,22,22,22,22,22


In Star Command Center, we leverage the Extract List task and isolate any string after the "\\ Member".  To provide more power in isolating string values, Regular Expressions can be used as in the example below:

The above task creates the following output.  Note that 11 records have been identified.

Optionally, a few more tasks are added to remove the prefix and suffix to isolate just the kickout members that cause the data load error.  The final results looks like the following:

A sample of this process can be downloaded here.