How to search Planning Supporting Detail: Part 1 - Getting the data

Background Information

Supporting Detail is a powerful tool that helps planners build and communicate bottom-up values when planning such expenses as travel, salary, and projects, where you need to calculate aggregate values. Supporting detail can include text, values and operators that define how data aggregates.

This feature is particularly useful when budgeting when you have multiple expenses falling in the same line. However, one of the drawbacks is when you want to report on at the supporting detail level for particular line item. Suppose you want to look at the supporting detail for Facilities Expenses across multiple entities. You want to see all the entities that have the supporting detail item of Landscaping. This is rather difficult to do given the current mechanisms in planning for reporting on supporting detail.

One of the common complaints about supporting detail is this inability to report and search at the supporting detail level. However through the use of database objects such as views and Excel, this supporting detail can be searched and reported on. By no means do you have to use Excel to retrieve this data but it is the most common BI tool that all companies have. You can use any business intelligence reporting tools such as OBIEE to also provide the means to retrieve the supporting detail.

This blog entry will focus on setting up of the database to retrieve the data.

Overview

First, Supporting Detail is not stored in Essbase. This is the root of why you can’t get to it via the Add-In or Smartview. Supporting Detail is stored in the planning relational database in a couple of tables called HSP_COLUMN_DETAIL and HSP_COLUMN_DETAIL_ITEM. HSP_COLUMN_DETAIL provides the actual intersection where the supporting detail is stored whereas the actual supporting detail labels and values are stored in the HSP_COLUMN_DETAIL_ITEM table. The data is stored as keys so you also need to query the table HSP_OBJECTS and HSP_PLAN_TYPE to decode the key values to meaningful data as you would see in Planning.

The data that is stored in the HSP_COLUMN_DETAIL is stored as a member name so if you would like to see your supporting detail using the alias you will also need to include a mechanism to get the alias name from the HSP_ALIAS table.

There are many different ways to extract data from these tables ranging from stored procedures to database views. I prefer to use database views to give a real time look into the supporting detail but there are drawbacks and possible performance issues using this method.

For this blog entry I will focus on a simple method of extracting supporting detail. I will follow up this entry with additional blogs with methods for incorporating the alias and also converting the supporting detail value from the local currency to USD.

Example

For this example, assume you have a planning model that has 9 dimensions. The data is stored in HSP_COLUMN_DETAIL as DIM1, DIM2, etc ... so you will need to determine which dimension equates to the appropriate DIM column in the table. For this model the following table shows the dimensional mapping for the HSP_COLUMN_DETAIL table.


I have created a view using the following SQL. It is important to note that you will need to use outer join syntax to join HSP_COLUMN_DETAIL and HSP_OBJECT. HSP_COLUMN_DETAIL can have null values in the dimensional columns depending on your planning model.

select p.type_name PLAN_TYPE,
o1.object_name SCENARIO,
o2.object_name ACCOUNT,
o3.object_name DEPT,
o4.object_name PERIOD,
o5.object_name VERSION,
o6.object_name CURRENCY,
o7.object_name YEAR,
o8.object_name EMPLOYEE ,
o9.object_name PROJECT,
cdi.label SUPP_DETAIL,
cdi.value LOCAL_VALUE
from
hsp_column_detail cd inner join hsp_column_detail_item cdi on cd.detail_id=cdi.detail_id
left outer join hsp_object o1 on cd.dim1 = o1.object_id
left outer join hsp_object o2 on cd.dim2 = o2.object_id
left outer join hsp_object o3 on cd.dim3 = o3.object_id
left outer join hsp_object o4 on cd.dim4 = o4.object_id
left outer join hsp_object o5 on cd.dim5 = o5.object_id
left outer join hsp_object o6 on cd.dim6 = o6.object_id
left outer join hsp_object o7 on cd.dim7 = o7.object_id
left outer join hsp_object o8 on cd.dim8 = o8.object_id
left outer join hsp_object o9 on cd.dim9 = o9.object_id
left outer join hsp_plan_type p on p.plan_type = cd.plan_type;

By using the sql to create a view I am able to query the supporting detail as it is saved in planning. This view can then be queried via your business intelligence reporting tool. This view gives the user a the ability to look at the supporting detail that is stored in a planning model and the user can utilize the filter analysis functions of the reporting tool to take a close look at the supporting detail items.



I will follow up this blog entry with subsequent entries showing how to do currency conversion from local to USD with supporting detail, return the alias instead of a member name in the supporting detail record set, and utilize the planning security model to return specific supporting detail based on identity.