Hyperion Essbase as its backend. On the outset, HPCM primarily provides Functional Users with the ability to automatically allocate Costs & Revenue to various departments, accounts thereby giving the ability to do proper & complete profitability reporting.
HPCM primarily has 3 main sweet spots
1. Every company will have indirect costs. For example, a Consulting company where Revenue is obtained through driving projects will have a lot of indirect costs like HR Costs, Admin Costs etc. HPCM provides an ability to allocate the costs back to the projects so that proper project profitability is derived. How the costs are allocated will be defined through HPCM itself. For example, if a company is running say 3 consulting projects with 20, 30 & 40 resources each, then the indirect costs are allocated back to the project based on the number of people(or time logged etc) in each project.
2. Every company will be storing the incoming Revenue & Costs in a Ledger. Due to various reasons, even the direct costs & revenue might not actually be tied back to a project (Consulting company example above). So, there might be a need to allocate the project based Direct Costs & Revenue as well to the Projects (allocation possibly by head count etc).
3. Allocation of Costs is pretty dynamic in nature depending on the type of business. It can vary quite frequently. So, the key is to ensure that the allocation logic can be changed frequently and easily. In addition, one more key point is to find the lineage back to the source on how the costs are obtained.
HPCM provides all the 3 above. If you are an Essbase or a Planning person, you could argue that we can do the same thing using these 2 products itself. Though true, in many cases Cost & Revenue allocation rules are defined by Functional Users. So, it is not possible for Functional users to create Business Rules/Calculation Scripts every time there is a change. In addition though Essbase is very good, it is very difficult to do a data lineage from a calculation script, to find out how the costs are allocated. Thats the main reason why, HPCM is a solution positioned primarily at Business/Functional users for providing that cost & Revenue Breakdown.
Though i have mentioned that HPCM is a functional tool, its underlying technology is very interesting. It has a relational metadata that stores the metadata related to HPCM. In addition each HPCM application will have 2 Essbase databases. One is Block Storage cube which will be used for the allocation & calculations. The other is a reporting Aggregate Storage cube which will be used for reporting. Data push from BSO and ASO is automatically available out of the box from HPCM. Also, one important point to note, change to dimensions, change to metadata, pushing data from BSO to ASO are all achieved within HPCM without writing any external code/scripts. Everything is done out of the box. This architecture is shown below.
In addition, the most interesting aspect of HPCM is the way it handles dimensions. This is what we will be covering in this article today. HPCM uses EPMA for managing its dimensions & attributes. HPCM as an application has 3 types of dimensions
1. System Dimensions – There are 2 System Dimensions – Measures & AllocationType. Generally while creating a HPCM application through the Wizard we can pre-create these 2 dimensions. AllocationType is used by HPCM internally for doing allocations. It is generally not needed to make any changes to this dimension. But Measures dimension is the most important dimension that HPCM uses for pushing costs & allocating them. We can create custom members in the Measure dimension if needed.
2. POV Dimensions – HPCM supports upto 4 point of view dimensions. These dimensions are generally for storing Year, Period, Scenario & Version. True to their names, they generally are used as POVs and are not used directly in any allocation (the POVs are always fixed in the calculation scripts).
3. Business Dimensions – Business Dimensions are those dimensions where allocations happen. These dimensions drive the allocation logic.
In addition HPCM also supports alias and attribute dimensions. For this article, i will use a simple case of demonstrating how to go about allocating HR Costs in a Consulting Company recognising its revenue through Projects. Lets make an assumption that on a monthly basis we record the HR Costs (including Salary paid to HR, other misc costs) etc. Lets also assume that we have 3 projects running in the company with the following break-up
a. Project A – 300 people full time
b. Project B – 500 people full time
c. Proejct C – 200 people full time
b. Project B – 500 people full time
c. Proejct C – 200 people full time
We will start off with creating the application through the Application Wizard (pre-create System Dimensions) and then we shall define the necessary dimensions.
We basically have 2 business dimensions – one for Accounts which will hold the HR Costs. Then we have the Project dimension which will record the revenue and costs specific to the project.
Lets deploy this application and then login to the application through Workspace.
In HPCM all allocation happens through stages. Stages is where allocation happen. HPCM supports uppto 9 stages. Each stage also supports intra stage allocation. Lets try to understand what this means from a Multi-Dimensional Essbase database Standpoint. In our example, we will have 2 stages. The first stage will have just the Accounts dimension – basically HRCosts in Accounts dimension will flow from Stage 1 to Stage 2 and will get allocated in Stage 2. So, Stage 2 will contain both the Accounts and Project dimensions.
After creating these 2 stages(ensure you also have a POV defined) lets go ahead and deploy this to Essbase from the Manage Database screen(both Calculation & Reporting Database). What you will notice is 3 things
1. There will be 2 essbase databases one suffixed with letter C and the other suffixed with letter R. C database is the Block Storage database that is used for allocation. R database is the Aggregated Storage database that will be used for reporting.
2. You will notice that for each stage there will be a corresponding set of dimensions prefixed by the Stage prefix given at the time of creation. So effectively, if there are 2 stages with 2 dimensions each, then Essbase will have 4 dimensions (though the 2 dimensions might be the same in EPMA).
3. You will also notice that each dimension will have a dummy member called NoMember. This is one of the most important members that controls the grain of the data. This member is the key in loading multi-grain data for allocation into HPCM.
Now that we have the Essbase cubes deployed as well, lets try to understand how the allocation logic works. To begin with lets assume for the month of Jan 2011, the HR Costs for the Consulting Company is say 1000 USD as shown below
This is the input data into Stage 1. So to load this in we will have be creating a text file and load it directly into Essbase. There are 3 options to load data into HPCM
1. Manual Data Entry – HPCM provides a screen where we can update data manually.
2. Staging Tables – We can load the data temporarily into a set of staging tables, and then from within HPCM we can push the data from Staging tables into Essbase.
3. Directly loading data into Essbase
In our case, we will load the data directly into Essbase as that will give more clarity on how HPCM works. For doing data load for Stage 1, remember we have a total of 9 dimensions in Essbase (Measures,AllocationType,Year, Scenario, Period, Version, ACAccount,PRAccount,PRProject). But our input data of HRCosts comes at a grain of only 7 dimensions (Measures,AllocationType,Year, Scenario, Period, Version, ACAccount). So, load this in we will have use the NoMember intersection of the remaining 2 dimensions (PRAccount & PRProject). The input data file to Essbase is shown below
CostInput,DirectAllocation,2011,Jan,Actual,Working,HRCosts,[PRAccounts].[NoMember],[PRProgram].[NoMember],1000
Our idea is to allocate the 1000 USD down to the 3 projects for the January Month. So, our end result should look like this
Our idea is to allocate the 1000 USD down to the 3 projects for the January Month. So, our end result should look like this
If you notice, the 1000 USD is split across the 3 projects based on the overall number of resources(Resource Count for each project/Total Resources*HRCost) in each project. So basically for the allocation to happen, we need to load the Resource Count data. Resource Count data for all projects and the individual projects have to be loaded as shown below
FixedDriverValue,DirectAllocation,2011,Jan,Working,Actual,HRCosts,HRCosts,Project A,300
FixedDriverValue,DirectAllocation,2011,Jan,Working,Actual,HRCosts,HRCosts,Project B,500
FixedDriverValue,DirectAllocation,2011,Jan,Working,Actual,HRCosts,HRCosts,Project C,200
Weight,DirectAllocation,2011,Jan,Working,Actual,HRCosts,[PRAccounts].[NoMember],[PRProgram].[NoMember],1000
FixedDriverValue,DirectAllocation,2011,Jan,Working,Actual,HRCosts,HRCosts,Project B,500
FixedDriverValue,DirectAllocation,2011,Jan,Working,Actual,HRCosts,HRCosts,Project C,200
Weight,DirectAllocation,2011,Jan,Working,Actual,HRCosts,[PRAccounts].[NoMember],[PRProgram].[NoMember],1000
In the above data file there are 2 things we can notice
1. We have used a measure called FixedDriverValue and Weight. We will see their significance a little bit later. For now think of them as intersections that will hold the Resource Count Data.
2. The first 3 records above have HRCosts repeated twice to load into both the Accounts dimension we have in the Essbase Cube. Again we will see the significance of why we are doing this below.
So far we have loaded all the necessary data into the Essbase Cubes and have also setup the stages. The next step is in defining the Allocation Logic. This is done through a concept called Drivers. From the Perspective of HPCM, drivers define how the allocation values get pushed from source to target. HPCM supports different allocations like Activity Based Costing etc through the concept of Drivers. In our case, the Driver for allocation is the Resource Count. Just to recap, within Essbase now we have dimensions catering to two stages – Stage 1 and Stage 2. This is illustrated below. So basically we have 3 separate sub-cubes each having its own intersection.
In the above diagram, whenever we want to do allocation there can be different ways of doing it. The 3 most common ways are
1. Allocation Based on Source – Here all the driver values are obtained from the source stage and data from source stage is then assigned to the Destination Stage based on the Source driver values.
2. Allocation Based on Destination – Here all the driver values are obtained from Destination stage and data from source stage is then assigned to the Destination Stage based on the Destination driver values.
3. Allocation Based on Source & Destination – Also known as Assignments – Here all the driver values are obtained from the intersection of Source & Destination and data from source stage is then assigned to the Destination Stage based on the Assignment driver values.
If you look at our Driver data above (3 records containing FixedDriverValue & 1 containing Weight), you can see that our driver (Resource Count) is loaded in 2 ways. First the FixedDriverValue is loaded at the intersection of Source & Destination stages. The second driver Weight is loaded at the Source Stage intersection. We have chosen these 2 measures (FixedDriverValue & Weight). We could have chosen any other measure like Rate, Volume etc. But each measure has a logical meaning and it is better to stick to the ones we think is logically close to what we are trying to do. If we are not able to use the existing measures and if they don’t relate to our driver names then we can create custom measures. So, this allocation handling through drivers is defined through the Driver Definition screen in HPCM.
As you see in our formula we are basically doing a division of each Resource Count from Stage 2 by the Total Resource Count in Stage 1. HPCM will automatically multiply the resulting values of the driver to the CostInput measure (input data) thereby allocating.
Once we have defined the driver the next step is to assign the driver to both the stages. HPCM allows us to have multiple drivers for each stage. And even within a single dimension in a stage we can define exceptions so that multiple drivers can be assigned within the same dimension. This way we can have allocation logic based not only on Resource Count but also on say Clocked Time of a resource in certain cases. Driver assignment is done through the Driver Selection screen in HPCM.
After the assignments, one other important feature of HPCM is its ability to assign allocations on a cell by cell basis. So, what we can do is, for each cell in the Source Stage we can assign the Destination cell(s) in the Destination Stage. That way we can clearly find out what intersections will get affected by what allocation logic. If you had to do them through say Essbase Calculation Scripts alone, it would have been such a laborious task not only while doing the allocations but also in maintaining them. In our example, lets assign the Data intersection of HRCosts in Stage 1 to the 3 Destination Projects in Stage 2 as shown below
Now lets run the Calculation & then immediately transfer the data to the ASO cube from the Manage Calculation screen.
If you now look at the data you can clearly see that each project now will have the corresponding costs allocated.
To validate how the costs have flown through the stages, HPCM provides an option to do a stage balancing report. This will show us how the costs have moved from stage to stage. In addition one biggest advantage of HPCM is, if there are any unassigned costs in the source, those will automatically move into Idle Costs (or we can configure it throw an error to ensure all costs are always allocated).
In terms of reporting, the ASO cube that HPCM provides will be more than sufficient. But based on what i have seen, we will have a lot of un-necessary dimensions especially when we are not concerned about the inter-stage essbase dimensions. In such cases, we can build our own ASO cube and extract only the necessary intersections that we need. But for now, i will showcase how BI EE 11g can be used for reporting again the native HPCM ASO cube. I will quickly show the lineage breakup reports as well
We start off with importing the ASO cube into BI EE which is pretty straight-forward as shown below.
Then lets start with building the report just for looking at our Stage 1 data. Remember Stage 1 input HRCosts data is loaded against NoMember intersections of the other dimensions. So we will have to explicitly filter to arrive at the right data.
Similarly to get the final allocated data we will have to apply filters as shown below
In the same way we can get the driver values directly from the cube using a simple BI EE report. All of these are shown in the form of a dashboard – showing how the costs from stage to stage using the drivers.
