Creating a day hierarchy in Hyperion Planning

In this blog, I would be showing you the steps to create a custom day hierarchy in Hyperion Planning.Generally, the would not be a need to create a day hierarchy in a Planning application, but with ASO plan types available now, it looks like operational planning is now very much possible in Hyperion Planning...

Step 1 :- Create a Planning application with the desired dimensions and choose the calendar to reflect the time period that you want. 

In this case, I have created a Planning application where the period dimension is a custom time period with 366 level 0 members that mimic each day of the year. The prefix for this custom time dimension will be “Jan_”

Below are the snapshots of the application creation:-

A snapshot of the Period dimension after creating the application is as follows:-

 

Step 2:- Updating the Period dimension from the backend table to reflect the period dimension correctly

Since the application is has a custom time period, we will have to update the names of the custom time period so that it reflects the names correctly and overrides the default naming convention of Hyperion Planning…

To do this you will need to have access to the backend data source where the planning application metadata is stored…

In this case, my backend data source is Microsoft SQL server.

The metadata from Hyperion Planning applications goes into tables like HSP_ACCOUNT ( Account dimension details), HSP_ENTITY, HSP_SCENARIO, HSP_VERSION…. ( table names are associated with the dimension data stored here)

The time period information is stored in two tables called HSP_CALENDAR and HSP_TIME_PERIOD…

The member information for time period is stored in a table called HSP_OBJECT … ( This table also stores information for any another custom dimensions that may have been created… )

Please note that these queries are created for Microsoft SQL server…Syntax may change based on native RDBMS used…

I used the below query to get the list of all custom time dimensions members…

SELECT 

object_name,

object_id,

cast(cast ([EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id] as datetime) as date ),

cast(cast([EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id]-212997+1460 as datetime) as date)

FROM

[EPM_HYBRID_TEST].[dbo].[HSP_object]

WHERE

[EPM_HYBRID_TEST].[dbo].[HSP_object].[OBJECT_NAME] LIKE '%Jan_%'

ORDER BY [EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id]

We now need to manipulate the period object id so that it reflects the months correctly…

The things that need to be considered is as follows:-

• When Planning creates the object ID for the period dimension it does it sequentially. So if my first custom time period has an object ID of say 1, my second will have object id as 2 and so on…
• Most relational databases start of the date/timestamp from some predefined start period…For example Microsoft SQL server considers the start to be 1^st January 1900.
• Since the time period has got 366 members, we need to find a leap year after 1900…1904 was a leap year that we have chosen for this example…

The below query is used to derive the month and year mapping for the corresponding members:-

SELECT

              object_name,

              object_id,

              cast(cast ([EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id] as datetime) as date ),

              substring(convert(varchar(20),cast(cast([EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id]-212997+1460 as datetime) as date),107),1,6)

FROM

              [EPM_HYBRID_TEST].[dbo].[HSP_object]

WHERE

              [EPM_HYBRID_TEST].[dbo].[HSP_object].[OBJECT_NAME] LIKE '%Jan_%'

ORDER BY

              [EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id]

 

The output of the query is present below:-

The logic of the getting the required date format is as follows:-

• Cast the object ID as a date time and then convert it to a date object.
• Once that is done, subtract the starting value of the custom time period object ID. In this case, the value is 212997. This basically gives us the calendar for the 1900 i.e. the start of date and time period in SQL server.
• Add 365*[1904-1900] =1460 to the above derivation so that we get the calendar for the year of 1904 i.e the first leap year after 1900.
• Once the above value is derived I format it using the convert function to “Mon DD,YYYY” format and then extract the first six characters using the substring function.

 

This basically gives us a mapping of the custom time period to the calendar days as shown in the above query output.

Once the query is verified, I run the following update query to reflect the object names.

 

UPDATE

              [EPM_HYBRID_TEST].[dbo].[HSP_object]

SET

              OBJECT_NAME=substring(convert(varchar(20),cast(cast([EPM_HYBRID_TEST].[dbo].[HSP_object].[object_id]-212997+1460 as datetime) as date),107),1,6)

WHERE

              [EPM_HYBRID_TEST].[dbo].[HSP_object].[OBJECT_NAME] LIKE '%Jan_%'

The output of the above query will change the OBJECT_NAME as shown in the below snapshot:-

 

Once the data is updated, just refresh the planning application to reflect the changes in the period dimension.