How to save your HFM Consolidation History
When task auditing is enabled, Hyperion Financial Management will store records of tasks in the <APPLICATION>_TASK_AUDIT table in the configured relational database. This table grows quickly and is often purged periodically by administrators or automated processes. In order to track consolidation history and more importantly, the duration of these consolidations, you will need to capture these records before they are purged.
One method of capturing this data is to export it prior to truncating the table via the HFM application. This is suitable for an environment where you are unable to access the back end database tables… like most environments.
- Log into Workspace as an HFM application administrator.
- Select Administration > Task Audit
- Select the date range, user, and or task types.
- Click the Export button and save the result.
The output from the export is saved as a CSV file with the same data that was displayed on screen. You can then save this file off someplace save and when you need to review the data, you can retrieve it.
Another method is to create a database trigger on the <APPLICATION>_TASK_AUDIT table that captures new consolidation records and inserts them into a new table. This method requires some knowledge of database management and SQL as well as access to SQL Server database associated with the HFM application.
The first step is to create a table to store the consolidation results.
Below is the SQL I use to create the history table, which I cleverly named CONSOLIDATION_HISTORY. The SQL also populates the table with existing consolidation records. In this example, the database I used is called TEST_HYP_HFM_1 and the database schema is DBO. The task audit table is titled using the HFM application name followed by “_TASK_AUDIT.” In the SQL, I called it APPLICATION_TASK_AUDIT. Just replace the name to match the table name in your database. Note the SQL is written using SQL Server T-SQL.
USE [TEST_HYP_HFM_1]
GO
IF (NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CONSOLIDATION_HISTORY'))
BEGIN
CREATE TABLE [dbo].[CONSOLIDATION_HISTORY](
[GUID] [nvarchar](32) NOT NULL,
[ActivityUserID] [int] NOT NULL,
[ActivitySessionID] [int] NOT NULL,
[ActivityCode] [int] NOT NULL,
[ServerName] [nvarchar](256) NOT NULL,
[AppName] [nvarchar](20) NOT NULL,
[Start] [datetime] NULL,
[End] [datetime] NULL,
[Duration] [int] NULL,
[Description] [nvarchar](1000) NULL,
[Scenario] [nvarchar](1000) NULL,
[Year] [nvarchar](1000) NULL,
[StartPeriod] [nvarchar](1000) NULL,
[EndPeriod] [nvarchar](1000) NULL,
[Entity] [nvarchar](4000) NULL,
[Parent] [nvarchar](4000) NULL,
CONSTRAINT [PK_CONSOLIDATION_HISTORY] PRIMARY KEY CLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT INTO [dbo].[CONSOLIDATION_HISTORY] (
[GUID]
,[ActivityUserID]
,[ActivitySessionID]
,[ActivityCode]
,[ServerName]
,[AppName]
,[Start]
,[End]
,[Duration]
,[Description]
,[Scenario]
,[Year]
,[StartPeriod]
,[EndPeriod]
,[Entity]
,[Parent])
(SELECT [strGUID]
,[ActivityUserID]
,[ActivitySessionID]
,[ActivityCode]
,[ServerName]
,[AppName]
,cast([StartTime]-2 as datetime) as [Start]
,cast([EndTime]-2 as datetime) as [End]
,datediff(s,cast([StartTime]-2 as datetime),cast([EndTime]-2 as datetime)) as [Duration]
,[strDescription]
,SUBSTRING([strDescription],CHARINDEX('The Scenario is',[strDescription])+16,(CHARINDEX(';',[strDescription],CHARINDEX('The Scenario is',[strDescription])))-(CHARINDEX('The Scenario is',[strDescription])+16)) as [Scenario]
,SUBSTRING([strDescription],CHARINDEX('The Year is',[strDescription])+11,(CHARINDEX(';',[strDescription],CHARINDEX('The Year is',[strDescription])))-(CHARINDEX('The Year is',[strDescription])+11)) as [Year]
,SUBSTRING([strDescription],CHARINDEX('The Start Period is',[strDescription])+19,(CHARINDEX(';',[strDescription],CHARINDEX('The Start Period is',[strDescription])))-(CHARINDEX('The Start Period is',[strDescription])+19)) as [StartPeriod]
,SUBSTRING([strDescription],CHARINDEX('The End Period is',[strDescription])+18,(CHARINDEX(';',[strDescription],CHARINDEX('The End Period is',[strDescription])))-(CHARINDEX('The End Period is',[strDescription])+18)) as [EndPeriod]
,CASE WHEN CHARINDEX('The Parent is',[strDescription]) > 0
THEN SUBSTRING([strDescription],CHARINDEX('The Entity is',[strDescription])+14,(CHARINDEX(';',[strDescription],CHARINDEX('The Entity is',[strDescription])))-(CHARINDEX('The Entity is',[strDescription])+14))
ELSE REPLACE(RIGHT([strDescription],LEN([strDescription])-CHARINDEX('The Entity is',[strDescription])-13),'.','')
END as [Entity]
,CASE WHEN CHARINDEX('The Parent is',[strDescription]) > 0
THEN REPLACE(RIGHT([strDescription],LEN([strDescription])-CHARINDEX('The Parent is',[strDescription])-13),'.','')
ELSE Null
END as [Parent]
FROM [TEST_HYP_HFM_1].[dbo].[APPLICATION_TASK_AUDIT]
where activitycode=4
)
The last step is to create the trigger. The SQL below was written to create a trigger on the APPLICATION_TASK_AUDIT table that will insert a record into the CONSOLIDATION_HISTORY table when a record with an ActivityCode of 4 is inserted. ActivityCode 4 is a consolidation record. Members from each of the dimensions described in the description are parsed out into their own field in the table so you can query them by similarity to look for trends. The duration is stored in seconds using the DATEDIFF command. The source data for the start and end times are stored numerically and it’s just a simple matter to convert them to a DATETIME. For some reason, I had to subtract two from the value to return the correct date. You can verify the result against the values returned in the Task Audit screen of the HFM application. Some records will depict the selected Parent member in the description while others will not. Because of this, I had to test for the string “the Parent is” in the description prior to parsing out the Entity and Parent members.
Once the SQL is modified and executed successfully, records will be inserted in the CONSOLIDATION_HISTORY table whenever consolidations are completed in the source HFM application. Accessing the data becomes the next challenge.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.InsertHistory
ON dbo.APPLICATION_TASK_AUDIT
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.CONSOLIDATION_HISTORY (
[GUID]
,[ActivityUserID]
,[ActivitySessionID]
,[ActivityCode]
,[ServerName]
,[AppName]
,[Start]
,[End]
,[Duration]
,[Description]
,[Scenario]
,[Year]
,[StartPeriod]
,[EndPeriod]
,[Entity]
,[Parent])
(SELECT [strGUID]
,[ActivityUserID]
,[ActivitySessionID]
,[ActivityCode]
,[ServerName]
,[AppName]
,cast([StartTime]-2 as datetime) as [Start]
,cast([EndTime]-2 as datetime) as [End]
,datediff(s,cast([StartTime]-2 as datetime),cast([EndTime]-2 as datetime)) as [Duration]
,[strDescription]
,SUBSTRING([strDescription],CHARINDEX('The Scenario is', [strDescription])+16, (CHARINDEX(';',[strDescription],CHARINDEX('The Scenario is',[strDescription])))-(CHARINDEX('The Scenario is',[strDescription])+16)) as [Scenario]
,SUBSTRING([strDescription],CHARINDEX('The Year is',[strDescription])+11,(CHARINDEX(';',[strDescription],CHARINDEX('The Year is',[strDescription])))-(CHARINDEX('The Year is',[strDescription])+11)) as [Year]
,SUBSTRING([strDescription],CHARINDEX('The Start Period is',[strDescription])+19,(CHARINDEX(';',[strDescription],CHARINDEX('The Start Period is',[strDescription])))-(CHARINDEX('The Start Period is',[strDescription])+19)) as [StartPeriod]
,SUBSTRING([strDescription],CHARINDEX('The End Period is',[strDescription])+18,(CHARINDEX(';',[strDescription],CHARINDEX('The End Period is',[strDescription])))-(CHARINDEX('The End Period is',[strDescription])+18)) as [EndPeriod]
,CASE WHEN CHARINDEX('The Parent is',[strDescription]) > 0
THEN SUBSTRING([strDescription],CHARINDEX('The Entity is',[strDescription])+14,(CHARINDEX(';',[strDescription],CHARINDEX('The Entity is',[strDescription])))-(CHARINDEX('The Entity is',[strDescription])+14))
ELSE REPLACE(RIGHT([strDescription],LEN([strDescription])-CHARINDEX('The Entity is',[strDescription])-13),'.','')
END as [Entity]
,CASE WHEN CHARINDEX('The Parent is',[strDescription]) > 0
THEN REPLACE(RIGHT([strDescription],LEN([strDescription])-CHARINDEX('The Parent is',[strDescription])-13),'.','')
ELSE Null
END as [Parent]
FROM inserted
WHERE [ActivityCode]=4)
END
