Social Icons

Tuesday, October 22, 2013

Where is @ISIDESC (Boolean Function) in ASO for MDX Formulas?

It has come out of surprise that ASO doesn't have a Boolean function similar to @ISIDESC function in BSO for writing MDX formulas. But, how can I achieve it? There are two different ways of doing it.
  1. Using ISANCESTOR Function
  2. Using CONTAINS function with a combination of Descendants function

[1] Using ISANCESTOR Function [ IsAncestor (Member1, Member2) ]

As the name says, this function will check if Member1 is ancestor of Member2. Let's take a simple example

IsAncestor ([Market].CurrentMember, [Florida])  This will check if the CurrentMember in the retrieve is Ancestor for [Florida].

Now, If we switch the member1, member2 we will be able to achieve the @ISDESC functionality.

IsAncestor ([Florida], [Market].CurrentMember) - This will check if [Florida] is ancestor of your CurrentMember and this condition will satisfy for all the members that fall under [Florida].

Using CONTAINS function with a combination of Descendants Function [ CONTAINS (Member_or_Tuple, Set) ]

Contains function will check if the Member_or_Tuple is present in Set.Below example will compute or check for Descendants

CONTAINS ([Market].CurrentMember, {Descendants([Florida])})

Hope this will help when you want to check for the Descendants in Boolean functions like CASE and IIF

Happy Learning!!!


Sunday, October 6, 2013

TWOPASS... Why TWO PASSES?

TWOPASS... Why do i need TWO PASSES for a calculation to get it right?

One of my colleague who was new to Essbase asked me.. What is TWOPASS? 

As usual, I took the standard example of % Calculation and said that 

"When you are performing % calculation using a member formula, you have to calculate again at Total level than to aggregate the percentages from child to parent. In such case, you have to re-calculate again at total level and in order for the essbase to do this, we tag the member as TWOPASS so that it will calculate again in the End. Below is the example i gave

Calculation Without TWOPASS

                 Jan        Feb      Mar      Q1
Sales        1000      1200    800      3000     
Profit          20          50      20        90

Profit %      50          24     40       114

Profit % has to be re-calculated again at Q1 to provide the correct results

Calculation With TWOPASS

                  Jan        Feb      Mar      Q1
Sales         1000      1200     800      3000     
Profit            20          50       20        90

Profit %        50          24     40       33.33
When we tag the member Profit % as TWOPASS, it calculates correctly. But, the next question he asked made me think as it's been a while that I was made to think :)

His question was not just one but two

  1. When we tag a member as dynamic calc and write a formula, it has to calculate correctly. Why do we have to tag as TWOPASS to tell essbase to calculate it again?
  2. Do we use TWOPASS only for percentage calculations or is there any other scenario where we use TWOPASS?
 There are two ways how you can perform Calculations in Essbase
  1. Build a calculation script to perform the calculation that you wanted (Batch Calculation)
  2. Use Member Formula
    • You can make the member as Dynamic Calc and essbase would calculate that member on the fly only when you retrieve (Dynamic Calculation)
    • Make it stored and calculate that member in Calculation Script (Batch Calculation because the member is stored and is explicitly calculated using a calculation script)
Essbase follows a specific order when performing a Batch Calculation or Dynamic Calculation. Understanding the order of calculation is very important which will give a clear picture to choose which option is better to perform a calculation.

So, You use TWOPASS on a member with Dynamic Calc to override the default calculation order.
Note: You can always control the order of calculation if you are using a calculation script to calculate that member

Below is the section of content from Essbase Database Administration Guide

For dynamically calculated values, on retrieval, Essbase calculates the values by calculating the database in the following order:

  1. Sparse dimensions
    • If the dimension tagged as time is sparse and the database outline uses time series data, Essbase bases the sparse calculation on the time dimension.
    • Otherwise, Essbase bases the calculation on the dimension that it normally uses for a batch calculation.
  2. Dense dimensions
    1. Dimension tagged as accounts, if dense
    2. Dimension tagged as time, if dense
    3. Time series calculations
    4. Remaining dense dimensions
    5. Two-pass calculations
    6. Attributes
 So, let's say that if your retrieve has multiple members which are dynamic calc (Across multiple dimensions) and you will tag that member as TWOPASS that needs to be calculated at last.

You can Achieve the same functionality in ASO using Solve Order Property. Using Solve Order, you can define the order of calculation similar to BSO Application.

Bottomline: Always evaluate if you really need TWOPASS as it has to calculate twice and it may take lot of time if you have complex calculations / multiple dynamic calc members.

Hope this information helps you when to use TWOPASS and when not to

Happy Learning!!!

Friday, October 4, 2013

Variance Reporting - How to do it - Part III

In the Last series of Posts Here (Part I) and Here (Part II), we have talked about the basics of Variance Reporting (Part I) and Variance Reporting Across Time Periods (Part II). 

In this post, we will talk about variance reporting Across Scenarios (Actual, Plan, Forecast).

Most of the Organizations who have been using Oracle EPM set of tools as part of their financial process would have the below process setup
  1. Fetch the Actuals (transaction level data) from different source systems for their reporting & analysis. Most of the actual data comes from oracle GL, Oracle EBS, PeopleSoft or any type of an OLTP system or it could even come from flatfiles or excel or could be any readable format
  2. Have a Planning application where users can input their PLAN data. Most of the organizations have long-term goals and target and plan to achieve it. What are the steps that are involved to achieve their goals and targets is part of their planning. I am not going to talk in details of about different types of Planning.
  3. organizations do make some assumptions based on their current & Historical Actuals. This is called Forecasting and is mostly considered as a short-term process. You can also employ a Rolling Forecasting process based on your running actuals. 
    • You Forecast for Jan based on the last year actuals. When you move to Feb, You forecast again for Feb based on Jan Actuals. This is really advantageous as it will help you to consider any uncertainty that arise during the course of your current business operations.
  4. organizations do have What-If modelling that will help them to understand the consequences that might arise and take precautionary measure to prevent them. 
    • For Example, Let's say that you expect that due to increase in fuel prices you predict an increase in "Transportation Expenses". 
    • Reduction in Raw Material Cost will reduce your Production Cost. But, that does not mean that you have to reduce your product cost. You might treat this as part of your Assumption and Forecast & Plan accordingly.
  5. organizations do have a Budget process on how to allocate money across various departments. Based on the amount of Budget that has been allocated, companies plan in order to reduce the cost of their operations.
Based on the above points, we can categorize scenarios as Actual, Plan* , Forecast

*Most of the organizations have multiple plans. Based on the number of planning sessions, you can create one scenario for each plan session

Taking all the points above in to consideration, below would be the variance scenarios that you can build in your applications
  • AvP (Act Vs. Plan) , AvP% (Act Vs. Plan %)
  • AvF (Act Vs. Forecast), AvF% (Act Vs. Forecast %)
As you plan and forecast, you always want to maintain multiple versions (Working v1, Working v2, Revised Working, Final) before finalizing your plan. These can be maintained in versions dimension (Standard Dimension when you create a Hyperion Planning Application).

As per point [4] , you can build Variance Scenarios considering What-If Scenarios

Hope this information will help you in building Variance Scenarios that will help your organization in taking better decision, better analysis and do better planning

Happy Learning!!! 

Friday, September 13, 2013

Variance Reporting - How to do it - Part II

In the last post here, we have talked about the basics of Variance Reporting. In this post, we will talk a bit detail about the variances scenarios that fall under two categories that we mentioned in the Part I of this series. 

Exert from the last post

You can categorize the Variance Reporting as
  1. Variance Reporting across Time Periods
  2. Variance Reporting across Scenarios (Actual, Plan, Forecast)
Variance Reporting across Time Periods

Most of the organizations often compare data across Time Periods to know where exactly their stand and how are they progressing.

For Example, a sales organization want to see 
  • How much their sales has increased from Last Quarter/Month
  • What is their revenue when compared to Last Quarter/Month
  • what are the companies expenses when compared to Last Quarter/Month
  • What is the profit margin when compared to Last Quarter/Month
When a company releases their Quarter Results, you might have heard that
  • Our company profits have increased to 19% when compared to Last Quarter
  • Our Sales have increased to 35% when compared to Last Quarter
  • YoY we have an increase of 12% in profits
So, how do we implement in Essbase?

Most of the Essbase/Planning Application will have Scenario Dimension and Scenario is a good candidate to create Variance Reporting Scenarios

So, in general if we want to have all these done in Oracle Essbase, below are the list of comparisons that you would do
  1. Current Month Vs Prior Month (Month On Month Variance)
  2. Current Quarter Vs Prior Quarter (Qtr On Qtr Variance)
  3. Current Year Vs Prior Year (Year On Year Variance)
These should be flexible enough to work at Q-T-D and Y-T-D level and they should also work on any Month/Qtr/Year for which you want to retrieve the data.
 At times, you might want to see just the variance of Current Year YTD with Previous Year Closing Balance mostly for Balance Sheet Accounts. 

Let's go a bit deep and see how your Formula would like for the Variance Scenarios
Make sure that you categorize your Hierarchies for better understanding. This is how my Scenario Dimension looks like

Scenario
    Actual Scenarios
    Plan Scenarios
    Variance Scenarios
        SCN_CM_Vs_PM (Curr Mth Vs Prior Mth) (~)
        SCN_CQ_Vs_PQ (Curr Qtr Vs Prior Qtr) (~)
        SCN_CY_Vs_PY (Curr Year Vs Prior Year) (~)

Current Month Vs Prior Month (Month On Month Variance)
Let's name the Scenario as SCN_CM_Vs_PM (Curr Mth Vs Prior Mth). This would be a Dynamic Calc member (In ASO, this member would fall under a Dynamic Hierarchy) with a Formula on it.

IF(@ISMBR("Jan"))
/* For Jan, You might want to take the last Year Dec as prior month */

      "Actual"->"Jan" - @PRIOR("Actual"->"Dec",1,"Years") 
ELSE
/* @LEVMBRS("Period",0) is an optional parameter. By Default, it will take level0 members of the Period Dimension (Dimension tagged as Time) */
      "Actual" - @PRIOR("Actual",1,@LEVMBRS("Period",0))
END

Current Quarter Vs Prior Quarter (Quarter On Quarter Variance)

Similar to "Month On Month Variance", you can write the similar formula for "Quarter On Quarter Variance"

Current Year Vs Prior Year (Year On Year Variance)

You may have lot of  years in your application but you may have data starting from some specific year / As a part of your archival strategy, you might archive your historical data to a separate application. In such cases, you can have a substitution variable that will take the first year of the application from where the data is available and below is how the formula would look like:
IF(@ISMBR(&FirstYear))
/* FirstYear is that year from which you have data in your application */
      "Actual"; 
ELSE
/* @LEVMBRS("Years",0) is an optional parameter. By Default, it will take level0 members of the Period Dimension (Dimension tagged as Time) */
      "Actual" - @PRIOR("Actual",1,@LEVMBRS("Years",0))
END

Note: Similar to the above variance Scenarios, you can also build Variance % Scenarios. 

Once you have all the scenarios in place, you can build a nice Smart View Retrieve template / Build an HFR Report and show it to your users. I hope they will love it.

We will talk about "Variance Reporting across Scenarios (Actual, Plan, Forecast)" in the next post. As an Add-on (At the END), we will also cover how these variance reporting scenarios will work in ASO.

Happy Learning!!! 

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