Social Icons

Saturday, December 27, 2014

Copy Text data (Planning) as Copy Data (DATACOPY) in Essbase...Yes..It works!!!

Yes. You have read it right and I am talking right.

There is a saying that if you understand a process end-to-end you open lot of possibilities for improvement / betterment and it suits perfect in this case

As the title goes, we all have started our career in Essbase (atleast in my case) and moved to to different other tools. We might have received multiple requests / written different types of calculation scripts where we have to copy a subset of data from one combination to other combination. The real problem comes when a user who is well-versed with Essbase and even knows the technical stuff better comes with a requirement which blows your mind and you had to think / try every single possibility to convince him that this is not possible. The same thing happened with me

Background
We use planning application for Planning sessions, Yearly Budget and monthly forecast and initially the monthly forecasting in Excel (where they have numbers as well as text) and after moving to Oracle Hyperion Planning, they wanted to do the same way (store data and text). So, we have created a new version with data type as "Text" to accommodate the user requirement. So far so good. Everything is clear and fine. Business do forecasting to a max of 5 versions (VR01, VR02...VR05) before making it a final version. 
We usually get requests to copy over the Forecast data from one version to another. This can be done pretty straightforward by using a DATACOPY command. But, one particular day we got a request from this user where he asked to copy text data from one version to another. We never done this before (OR) have seen anyone doing it and the first thing that came out of our mind is "We can copy data, we cannot copy text data". But, the user is very adamant and he doesn't want every user to input text data for every version and this will impact their deadlines

So we have decided to dig deep inside to understand the process and we we able to find a solution to it and below is how we did it

Understanding Text Data in Planning
I have created few members and a form to demonstrate how we did it
Below is a screenshot of simple form with one comment
We have 3 accounts and a version memeber "VR_Commentary_V01" and we have defined the version dimension member as data type "text".
Note: Do not forget to set the evaluation order as "Version" and then followed by any other dimension. In this case we have data type text defined for version dimension and if you have any other dimension which has text data type / smartlists then you can define the evaluation order accordingly
When we submit any text data in planning, The text information gets updated to HSP_TEXT table in planning repository. The HSP_TEXT has two fields "ID" and "Description" and the ID is sequence and it gets incremented with 1 for every text data that is submitted and the ID gets submitted to essbase as a number.
 Below is how the table will look like



Let's have a look at the below form where we have submitted the text for T_Account_2 -> Nov and below is how it will look like


Below is the Essbase retrieve of the same


Let's copy the data that we have submitted in Nov to Dec and see what will happen when we retrieve the form. Below is how the form will look like

The text at T_Account_2 -> Dec is exactly same as for Nov and if you look at the table above, there is only one entry in the table. So, how does it work.

Planning table does not store the duplicate entries and when you submit any text data in the form, it will do the below steps
  1. Once the text data is submitted for a blank cell, it will check if the same text data exist in the HSP_TEXT table
    1. If the text does not exist, A new entry is added to the table with a new ID which is the max(ID) + 1 and submit the ID to Essbase (As Essbase store only numbers)
    2. If the text already exists, it doesn't create a new entry in the HSP_TEXT table. It will pick the ID from the table and submit it to Essbase
  2. If you modify an existing text data, it will not update the corresponding ID text but rather check if the modified text data is available in HSP_TEXT
    1. If the modified text is not available, a new entry is created in the table with the ID and that ID is submitted to Essbase
    2. If the modified text already exist, it doesn't create a new entry in HSP_TEXT table but it will pick up the ID for that text and submit it to Essbase

There is an exception to the above process. Let's look at that

If you look at the above screenshot, the text data got aggregated to Qtr4 and if you look at the text in Qtr4 it doesn't make sense at all. It is always best to never (^) aggregate the text data members 

I hope this gives you an overall picture of how the text data works. Always have backup in case if something goes wrong

I hope this gives you an overall picture of how the text data works. 

Happy Learning!!!


Long time...

It's been a long time that I haven't posted any posts and there are lot of posts that are pending

I have recently moved to US 4 months back and settling down over here. Working on few career commitments towards Oracle EPM and I hope to keep continue with the blogging again


Monday, July 21, 2014

Hyperion Performance Scorecard - Statement Of Direction

Oracle Product Management have released the Statement of Direction for Hyperion Performance Scorecard (HPS). This document is available to download from My Oracle Support (MOS) Knowledge Article:

Hyperion Performance Scorecard Statement of Direction -  Doc ID 1901891.1

11.1.2 is the final release version for Hyperion Performance Scorecard. If you have "Hyperion Performance Scorecard" implemented at your place, You might have to go through the document for the Oracle strategy

From the document, It is evident that Oracle future's direction is towards "Oracle Scorecard and Strategy Management" which has a very tight integration with OBIEE
Below is an excert from the SOD regarding the support policies








Note: You need to have a support ID to login to the support portal to access the document.

Monday, July 14, 2014

Essbase Data load & Dimension Build Error log limit

If you are working on a project or in support, there are changes when you do not have the metadata in the system and you try to load data and you get lot of data rejects. 
     You then try to rebuild those members and then try to load the error file again and you get some more rejects. You might be wondering why not all the errors gets updated to the log file? Is there any limit to the number of errors that are written to the file?

Yes. There is a limit both for DataLoad & DimBuild which can be defined in essbase.cfg file and the limit is different for "Dimension Build" and "Data Load"

If you have checked the Technical Reference, you will see three configuration settings

Taken from Dbag

NOMSGLOGGINGONDATAERRORLIMIT

Prevents data load or dimension build errors from being written to the application log after the limit described by the value of DATAERRORLIMIT is reached.
Syntax
NOMSGLOGGINGONDATAERRORLIMIT TRUE | FALSE
The default value is FALSE.

DATAERRORLIMIT

Determines the number of records that can be written to an error log during a data load operation.
Syntax
DATAERRORLIMIT n
Where n is the number of records, per data load or dimension build, that can be written to the error log, dataload.err. Default: 1000. Maximum: 65,000.
Errors would still be updated to the log file unless you set the NOMSGLOGGINGONDATAERRORLIMIT

DIMBUILDERRORLIMIT

Determines the number of records that can be written to an error log during a dimension build operation.
Syntax
DIMBUILDERRORLIMIT n
Where n is the number of records, per dimension build, that can be written to the error log, dimbuild.err. Default: 20,000. Maximum: 65,000.


You have to restart your essbase services as the configuration settings will get affected only after the services restarted

Hope this information helps and reduce the number of iterations that you do while loading the data to find out that you still have error records showing up

One ideal solution is to list down the unique number of members and do an adhoc retrieve and see how many members are there in the system and how many new members have come up.

Tuesday, May 27, 2014

Feather in the cap - Oracle Exalytics Certification

I was busy with some related work on Hyperion and couldn't get enough time to write a post. 

Last Friday was a day that will be remembered. I have passed the "Oracle Exalytics Implementation certification". Link to the certification page (Here).

I feel proud to move ahead with this certification and even proud to be the first one in my company to have this certification. Moving ahead, would like to gain more knowledge and would like to explore more opportunities

Feel free to post your queries and any topic which you would like me to talk about




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

Thursday, February 20, 2014

Oracle Planning and Budgeting in the Cloud - GA

Oracle has announced the public availability of "Planning and Budgeting on the cloud" known as "Planning and Budgeting Cloud Service" (PCBS). You can find the press release : Hyperion Planning and Budgeting on Oracle Cloud Service (Link)

It is offered at $120/user with a minimum of 10 users. Not bad for small and mid-size organizations with little user base


----------------------------Excert Taken from EMEA Partner BI EPM Blog-------------------------------------------------
You can also JOIN THE WEBCAST for a Partner Deep-Dive on Planning and Budgeting Cloud Service.This live 120 minutes technical and functional deep-dive into the upcoming PBCS offering webcast will be on Wednesday February 26, 2014 at 09:00 UK / 10:00 CET. Oracle Hyperion EPM experts will not be disappointed, but novices are welcome. We assume you are already familiar with the Hyperion Planning & Budgeting Application, and need to know how the Cloud SaaS approach effects how you will implement this for your clients. Topics include:
  • Changing the game: Oracle Planning and Budgeting Cloud Service
    • Introduction and latest news
    • Guided live demo tour with focus on
    • How to create an application in minutes
    • Access the Cloud via familiar Excel Interface? Yes, please!
    • Application Life-Cycle Management
    • Data Management options
    • Rock-solid Security and reliability
  • Resources for Partners
  • Live Q&A
JOIN THE WEBCAST on Wednesday February 26, 2014 at 09:00 UK / 10:00 CET.
----------------------------------------------------------------------------------------------------------------------------------
Few resources that might give you a kick-start
Oracle has conducted a Partner Webcast in November 2013. Planning and Budgeting Cloud Service - Partner Webcast Replay (Link)
For more information download this Whitepaper: Oracle Planning and Budgeting Cloud Service (link)
Documentation on "Oracle Planning and Budgeting Cloud Service" (Link)
Demos and Video Tutorials (Link)
Eric Helmer from Linium has already posted this on his blog (Link)

HTH
Amarnath


Monday, January 20, 2014

Oracle EPM December 2013 Patch Set Updates

Quite a few patch updates from Oracle related to Oracle EPM Suite. You can find more about this on Support blog at "December EPM "Patch Set Updates" released (updated)". 

You need to have a support ID to actually download the patches. Would recommend you to update these patches to avoid any issues that you might face.

We had an issue recently and the Essbase Database (we are on 11.1.2.2) was corrupted and we had to restore it from Backup. We have not seen that issue before and we went through the patch readme document and we are in the process of applying that patch to our system.

Hope this information helps.