Social Icons

Sunday, April 20, 2014

Custom Tools/Utilities for Oracle EPM (Mostly Essbase) - Part I

     It's been around 8 years that I have started working on Essbase and from then there has always been a need to a functionality / custom tools which are not available with Essbase and have to either build one. In current world, there are lot of consultants who have built their own custom tools and few of them have made them available to public.

When Oracle has made Oracle EPM set of tools available on eDelivery site, it has opened up the opportunities to lot of people who has started working on Essbase. This post is about for all those who might have thought that "I wish I had a tool that can accomplish this". This post is about a collection of such tools that are available open to the public. 

I would divide this in to multiple posts. In Part I, We will talk about Log Utilities

Log Utilities

Essbase Log Reconciler

Logs are always the entry point to check for any issues / calculation but Essbase logs has always been difficult to decipher to search for any issues. This was one of the first log utility available for Essbase (Essbase was the lonely kid at that time very young).

You can find the tool Here and there are many other tools available at the same link. This link has been there from ages and some of them might not work for the latest version. 

You don't need to be an expert in using Log Reconciler. You can follow the below steps to get it working

  1. Download the Log Reconciler from the above link
  2. Place it in a folder
  3. Drag and drop the log file which you want to parse and hit RETURN (Press ENTER)
  4. You will get a nicely formatted pipe delimited file with the timestamp 
Where Essbase Log Reconciler Failes

Let's say that you don't want to parse the entire log file but rather want to parse and filter only the errors to the output file. Log Reconciler doesn't have that much of flexibility. Our very own Essbase Expert Sebastin Roux has built a tool (Essbase Right Log. More information below)  which will do that. All the tools mentioned below are from Sebastin Roux

Essbase RightLog
Similar to Log Reconciler. Imagine that the log file is very big / has loads of information and I need only the error 
Both still works and I didn't fin any issues other nor do I have any complaints. You can find it Here. The link says it is for v5 - v11.1.1 but It will still work with the latest version (11.1.2.3) bcos the essbase log still remains the same. There might be few additional categories that might be added after 11.1.1 but still the rest of the part will work. For Example, new error codes that were added after 11.1.1 might not work.

You need to have PERL in your system for RightLog to work. Below is an excert from the site on how to execute the RightLog and various parameters that it will take. You can find more information in the link I have given above
Note: You would need an External Spreadsheet-Excel write module. Refer to the main link above
USAGE: EssbaseRightLog -i <logfile(s)> [-o , -c, -d , -t, -s , -f , -x , -q, -h]

 -i specify Essbase log(s), args: <logfile1[;logfile2;...]>
 -o specify output file, arg: 
 -c specify message categories
 -d specify date format, arg: <ISO|EUR|US>
 -t specify headers on top
 -s specify separator, arg: <*>
 -f specify filter (case sensitive), arg: 
 -x specify Excel output, arg: 
 -q specify query output
 -h display usage
Other benefits of using the EssbaseRightLog is that you can use batch script to write you own scripts and run it instead of typing everything

# Update on 22nd Apr 2014 - Start#

If you don't want to use any other external modules, then there a light version which is called as RightLogLite and can be found Here

If you are not very much familiar with Perl and are very much comfortable with Java then you have java version of the same tool (JRightLog), you can find it Here and you don't need PERL interpreter installed in your system to get this working
# Update on 22nd Apr 2014 - End#

I don't want to use any of these custom utilities and is there any tool available to parse Essbase log files? Yes, there is. 


What would I do with these formatted Essbase logs?
Well, that's up to you. If you are powerful using Excel, then you can just use the Excel to analyze the logs, Build some macros and generate log charts.
(OR)
You can build a Essbase log application and load these logs (as data files using Rule files) and see what different type of errors you are getting. As a Administrator, the duty is always to minimize the errors and keep the applications available most of the time

Essbase Log Charts (Using EAS Console)

Your very own, friendly, one and only EAS Console.You can create and view log charts. This data would get stored in EAS repository. However, It just gives graphs and statistics and you may not be able to get the full information and cannot work or manipulate or play around with your logs. That's why "Essbase Log Reconciler" and "Essbase Right Log" are powerful.

ssauditmerger

In earlier versions, when Essbase Excel Addin (Best Pal for administrators :) ) was available, there was a way to track the data submitted to essbase using Lock & Send for auditing purposes. This was very effective as you would know who has submitted the data and who has changed the numbers and this has always helped Administrators like us to trace back. Imagine, in order to trace back, either you have to get the SSAUDIT log file, open it in some advanced text editor like (Notepad++ or textpad, Both are my favorite and it's free) and search for the combination. Have you ever thought that there could be a better way of doing this rather than going through all the manual process?

Yes. Someone like us has faced the same thing and thought why not we have a tool that can parse the SSAUDIT logs and here it is. ssauditmerger merge all your AUDIT log files and generates the output to a file. You can find it Here. Below is an excert from the site on how to use the ssauditmerger

Usage: perl SSAM.pl -i <.atx & .atg directory> [-o , -d , -h]

Usage: SSAM.exe -i <.atx & .atg directory> [-o , -d , -h]
  -i specify SSAudit logs' directory, args: 
  -o   specify output file, arg: 
  -d   specify date format, arg: 
  -f   specify filter on headers (case sensitive), arg: <*>
  -h   display usage
# Update on 22nd Apr 2014 - Start#
If you are not very much familiar with Perl and are very much comfortable with Java then you have java version of the same tool (JRightLog), you can find it Here and you don't need PERL interpreter installed in your system to get this working
# Update on 22nd Apr 2014 - End#

Oracle Standalone Utility

Oracle has been very late in the game but if Oracle has released a Log Utility for parsing the logs. This is not just Essbase but for the entire EPM toolset. The reason behind releasing the Log Utility is that starting from 11.1.2.2, Oracle has centralized the location where the logs will be stored and it is very difficult to parse each of the logs with so many components. You can find it posted on the Oracle Support Blog Here. If i understand it correctly, this is for application logs and might not include Essbase (Correct me if i am wrong).
John Goodwin has put a nice post on how to use the Log Utility Here and no one can explain it better than John Goodwin

# Update on 22nd Apr 2014 - Start#
In the next post, I will test these tools and see to what extent they are compatible with the current version of Essbase 11.1.2.3
# Update on 22nd Apr 2014 - End#

If you know any such tools, you can just drop me an email (amarnath.kothapalli@gmail.com) and will include in further posts

No comments:

Post a Comment