Social Icons

Featured Posts

Saturday, November 21, 2015

Planning (BSO) to Reporting(ASO) Replicated Partition Issue (Not really an issue) - Solution

There was an issue reported from user that a small sheet with 15 rows and 6 columns from reporting cube (ASO) is taking around 6 minutes to retrieve and getting timed-out

We tried from our end couple of times and identified that it was sporadic and tried all possible options to fix it. Sometimes, it retrieves very quickly and sometimes it takes a huge amount of time.

Option 1: Outline size was around 200MB and we compacted the outline which brought down the outline size to 22 MB. But, this didn't work

Option 2: We cleared aggregations (We were very sure that this will not improve performance). It didn't work

Option 3: We have a database where we capture all our metadata and data loads with start time, end time and elapsed time. We noted down the times when the retrievals were faster and looked in to the database to find what process were run before the retrievals.

We found that whenever a dimension build process is completed, the retrievals are faster but after that the retrieval times increased gradually. We have a process which runs every 10 mins which does currency conversion in Planning (BSO) and refreshes a replicated partition to push data to reporting (ASO) cube. We kept retrieving after each refresh is completed and could see that the retrieval times were increasing gradually. We have also noticed that the Plan to report process time was also increasing gradually

We identified that the Plan to Report process is the issue. After lot of digging and analysis, we identified that every time a partition is refreshed a new slice gets created in reporting (ASO) cube and if a user retrieves any combination which needs to check in both main slice and incremental slice and then has to do some internal processing to get you the right numbers

As a solution to the issue (It's not an issue but rather essbase behavior), we have to merge the slices. This improved the retrieval times from 6 minutes to 30 seconds. This has also reduced our Plan to Report process and the timings were very consistent at 6 minutes which was taking somewhere between 6 mins - 15 mins depending the number of slices

We also had to look from a performance standpoint to decide which type of "slice merge" we had to choose

  • Merge all slices to main cube
  • Merge incremental slices to one slice. Since, it is creating a new slice, everything went fine as 
Merge to the main cube gave us two issues for which we dropped the option

  • Merge was taking more time as we have aggregations on the cube. We had to drop the aggregations to make the merge to cube faster which was not an option
  • We tried merging without the aggregations and was taking more than a minute

Merge to the slice was the way to go. merge takes around 25 seconds with / without the cube aggregation

It was very difficult to identify the problem as what was causing the retrieves to be slower but now everything works as normal

Monday, August 10, 2015

Analytics outside of my work

My first encounter with Essbase started in 2007 when I started my career and never looked back. I loved working in essbase and how different it was compared to other databases. it wasn't that difficult for me to make a switch-over with the knowledge of relation Db's as I was a very quick learner. 

It's not just Essbase that I love but I am in love with data and anything that is related to data and analytics. As part of my work, I work on Essbase applications and outside of my work, I tend to analyze data that is available public and try to analyze the data. This isn't that easy as the data that is available is not complete. that's what I love. I love to fill the gaps / identify where the gaps are and come up with solutions. 
If the data is not available, I just scrape the data from websites / social networks and look at what people are talking about
Sounds good till now but what tools / programming languages I use?
I use R mainly for analyzing the data as I felt comfortable and relatively simple
I do use Python if there is something which I don't find in R. Mostly, I have used Python where huge amount of data is involved and R tends to give me problems as my system is not that highly configured

I have been busy working on something of my interest to be applied on what I am working on. I will be able to post it soon in couple of days / in a month

Monday, July 27, 2015

ASO Report Optimization - Short Tip

Another short post on ASO Report Script Learning

I was working on a project where we have an ASO cube and we had to extract the data from ASO cube to a different team. There were multiple requirements, where they wanted level0 of few dimensions and summary level of other dimensions

I used an existing report script that was in a BSO cube and then updated the dimensions accordingly and to my surprise, the script ran for around 2 hours to generate around 20MB file

I have written report scripts earlier and never did the script ran so long. It took a while for me and figured out that I was using LEV and when replaced with LEAVES, the report script ran for 40 seconds

I expanded the scope of my report script and was able to generate 400MB file in 4 minutes

Always use LEAVES if you need Level0 members of a dimension in ASO cube

Happy Learning!!!

Wednesday, January 28, 2015

Oracle EPM Released

Finally the Oracle EPM is released!!!
I wish it was but it's just the Beta that got released and they are made available at Here

They are not yet available in edelivery and the document is also not available yet. They should be available in couple of days. Wait for it to be available in eDelivery

I though this time atleast I will be the first one to post but Celvin have already blogged Here followed by John Goodwin about the installation and configuration (Beta version) Here

I am late in the game :( Nevertheless I will wait for the eDelivery version :)

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

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.