Social Icons

Friday, August 16, 2013

Variance Reporting - How to do it - Part I



Looking at the word Variance Reporting reminds us about the @VAR and @VARPER functions available in Essbase with which you can do variance reporting. One of my good friend wrote a blog post how to perform variance reporting Here.

I would not re-invent the wheel but rather would speak about what are the different ways of performing variance analysis. I would split this in to three parts and woudl cover the basics in Part I.

These days, Essbase applications are not used just for analysis and reporting. They are used even for Planning, Forecasting and Actuals for baseline. It so happens that you might have to do a lot of Variance Reporting and it is not just restricted to Act Vs Bud but rather a more complex and advanced way of variance reporting.

We have recently upgdraded our application from sys9 to EPM11 and we were given requirement to build pre-built reports using Hyperion Financial Reports which they are currently using in Excel and they are even doing Variance Analysis in Excel which might go wrong somewhere in reporting.

So, we have proposed that we would build Variance reporting scenarios that would help them to retrieve the data from essbase rather than performing variance analysis in Excel.

 So, The Next Questions that comes is, How many such Variance Scenarios would I need?

Well, it depends on what type of data you are loading (Actual, Plan & forecast). You can categorize the Variance Reporting as
  1. Variance Reporting across Time Periods
  2. Variance Reporting across Scenarios (Actual, Plan, Forecast)
It so happens that users might request to have [1] and [2] and Q-T-D and Y-T-D level also. So, make sure that your application can handle Q-T-D and Y-T-D Reporting.

Have a checklist prepared and write down how many Variance Reporting scenarios would you require and check with the users.
Be prepared for the worst as users might come back tomorrow and say that may be "If i have this variance reporting then it would be good. Can you do it?" 
Once you have all the elements in place, it doesn't take much time to build as per the reporting requirements. Requirements and Design are important
In the next post, I will talk about more on how many would you need and how to approach for building.

Thursday, August 15, 2013

NONEMPTYMEMBER in MDX Formula to the Rescue - Essbase ASO

Most of the Essbase ASO Applications have View dimension for handling Dynamic Time Series (Mostly Q-T-D and Y-T-D). As you all know that ASO application does not have an in-built functionality to handle Dynamic Time Series.

We have a DTS Dimension with 3 members. MTD,QTD and YTD. 
  • All the data is loaded at MTD member
  • QTD and YTD have MDX formulas to calculate the Qurater-To-Date and Year-To-Date as we have both Balance Sheet Accounts and P&L Accounts
I was working on a report which is pretty simple but the combination of the data that has to be displayed is varied. Below is the layout of the report
  • ROWS - Legal Entities (around 600+), Analysis Codes (1200+) and 19 BS Accounts and 25 P&L Accounts
  • COLUMNS - Current Month, Last Month of Prior Quarter, Variance, Variance %
This is a Y-T-D report and when i am trying to retrieve the report it is taking very long time. I didn't knew what was the issue.Tried couple of things
  1. Changed from YTD to MTD and the report was opening in less that 2 mins. But this will not help as we have to look at YTD balances for P&L
  2. Ran an MDX query with Query Tracking ON and run the design aggregation. Didn't help much as most of the hierarchies in the report were alternate hierarchies
  3. Looked at the formula for QTD and YTD. Formula looks good. I didn't see a reason why the formula might not work
Something suddenly struck my brain. Let me see how many combinations I would make. It turned out to be 31,680,000 (600 x 1200 x 44). That's pretty huge cardinality. But I have suppression enabled on the report. But imagine when i use YTD, it will have to calculate approx 31 Million combinations x 2 (current Month, Prior Month) and that is pretty huge.

i have suppressed on Report now how can I do the same in Formula and then I thought of using NONEMPTYMEMBER which has come to the Rescue.

I used NONEMPTYMEMBER [MTD] as the first line of the formula and now the report opens in around 2 mins.  Such a huge difference.

------------------------------------------------------------------
Excert from Oracle EPM Documentation (Latest 11.1.2.3)

 The NONEMPTYMEMBER and NONEMPTYTUPLE properties enable MDX in Essbase to query on large sets of members or tuples while skipping formula execution on non-contributing values that contain only #MISSING data.

You can find more information at the link HERE 
------------------------------------------------------------

Note: NONEMPTYMEMBER has nothing to do with the report. It is an ASO MDX functionality which eliminates any Missing data during calculation and calculates only those combination where data exist.

 
Happy Learning!!!

Oracle Hyperion DRM Export Format

Oracle Hyperion DRM 11.1.2.2 enables you to export the hierarchies with different encodings (ASCII, Unicode, UTF-8, and so on). 

We had our environment setup where we send the Metadata Feed from DRM to a Staging environment and then it goes to Hyperion Essbase Application. This is an ASO Application and we had a lot of Key Metric Formulas which are very huge and we thought of having a seperate feed in Parent, Child,Formula format and send it directly to Essbase Server which is sitting on a UNIX Box and somehow the metadata build is not happening. After a lot of hits and trials, we could finally find out that the issue was with the encoding which was set to UTF-8 by default. 

We changed the Encoding to ASCII and when the file was FTPed to the UNIX box and ran the Metadata built, it ran successfully. 

There are a couple of things that you can do to overcome this issue.  


  1. Change the Encoding of file to ASCII if you are sure that your members does not have any special characters and just pure text.
  2. 2. Check the Configuration settings of the the FTP tool that you are using if you want to transfer in UTF-8 / Unicode Format

Hope this information Help. Let me know if there are any other ways to handle this.

Happy Learning!!!

Thursday, August 8, 2013

My Essbase Blog


It's good to start blogging after many years but this time it's all about technical.
This is my first blog on Oracle EPM. I have been working on Essbase, Planning, HFR, DRM and ODI (All tools part of Oracle EPM Product Suite) from quite a long time and I still feel that I am a beginnler.

Oracle EPM (Enterprise Performance Management) is a set of analytical process and business tools that allow a business to track, measure and achieve goals. The core components of Oracle EPM are Hyperion Financial Management, Oracle Hyperion Planning and powerful analytical tool Essbase.

Oracle EPM has undergone lot of changes from it's inception (from v11.1.1.1) to current version (v11.1.2.3) and new tools were added. EPM Stack is bundled with Performance Managenemt Applications and with powerful analytics to align financial close, planning, reporting, analysis, and modeling and unlock business potential. There has also been lot of integration between ERP Systems to EPM applications thereby reducing the cost of integrating source systems to EPM applications and also the implementation time.

You can find more information on Oracle EPM (Re-branded as Oracle Business Analytics) in Oracle site at http://www.oracle.com/us/solutions/business-analytics/performance-management/overview/index.html. Oracle Business Analytics includes Oracle EPM, Business Intelligence and Analytic Applications.

I will be blogging more on Essbase, Planning, HFR, DRM, ODI