PerformancePoint Planning – Data change tracking / audit trail

PerformancePoint Planning does contain auditing out-of-the-box.  However, it’s not data level auditing but instead Metadata level auditing.  If you want to track when a user has been added, or a workflow cycle started then PPS-P keeps a track of all these changes plus a whole host of other system changes in an XML file located in the directory specified in the ‘Audit file name’ property of the ‘Auditing’ tab in PAC.

The auditing will answer questions such as:

“When was the staging database last synchronised?”
“Who re-started my assignment?”
“When and who changed these business rules?”

This audit file does contain and audit a lot, and one day, unless I’m beaten to it by Microsoft (hint hint!) I’ll either write an XSLT file to transform the XML into a ‘nice looking’ HTML page, or build some routines to suck the audit files into a usable database where I can stick some SSRS reports over the top.  Until then, notepad will have to do.

What’s missing out-of-the-box is the ability to audit data level changes.  Questions that are actually more pertinent to Business Performance Management and requirements under Sarbanes-Oxley (SOX), the Basel Accords and other, related regulations:

“Who increased the salary assumption for the North West sales force?”
“When were the revenue targets last increased?”
“How many times do the sales forecasts get amended before they are approved?”

This post attempts to detail a simple mechanism that could be utilised to implement a basic data change audit trail that could be used to answer all questions relating to data changes.  At this juncture I must point out that the approach described below entails making database level changes to the Planning Application database and therefore would result in an unsupported environment.  In reality you would leave the planning database well alone and perform these changes to a log shipped or equivalent synchronised copy of the database, well away from the main production planning application database.  There, you have been warned !

In this scenario I want to place an audit trail on a simple assumption model.  The assumption model contains five dimensions, Time, Account, Region(Entity), Role (Custom dimension) and Scenario.  I want to track all changes to data within this model.

To set up the auditing the following is required:

Audit Tables

Three new tables are required, these are essentially copies of existing Planning Application tables and are used to keep a history of inserts and updates.  I create these tables in an ‘Audit’ schema; this keeps them grouped together, and allows me to maintain the same name as the original.  The only difference between the two tables is the addition of a new primary key ‘AuditId’.

• MG_Drivers_MeasureGroup_default_partition
  This is the main fact table of the assumption model that will contain the audit trail.  A complete history of changes will be maintained in this table.
• BizUsers
  BizUsers contains all current users of the planning application.  As users can be removed from the application it is necessary to audit this table to ensure changes by non-current users can be forever traced.
• AssignmentsHistory
  Stores history information relating to an assignment.  Information such as when it was submitted (either draft or final).  The comments that were added by the submitter etc etc.  The key field contained in this table is the UserId that allows us to know where to point the finger of blame! (Figuratively speaking of course!).  Like the BizUsers table, when assignments are no more, the associated history records are purged too.  Auditing this table maintains the history.

You could argue that each of the dimension tables also need auditing for cases when members are removed from the dimension in the future.  I can’t argue against this and depending on your business process and application you might be quite right !

Example Audit Table Create Statement:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Audit].[AssignmentsHistory](
    [AuditID] [int] IDENTITY(1,1) NOT NULL,
    [AssignmentID] [bigint] NOT NULL,
    [ChangeDatetime] [datetime] NOT NULL,
    [CreateDatetime] [datetime] NOT NULL,
    [UserID] [bigint] NOT NULL,
    [Action] [nvarchar](100) NOT NULL,
    [Comments] [nvarchar](2000) NULL,
 CONSTRAINT [PK_AuditAssignmentsHistory] PRIMARY KEY CLUSTERED 
(
    [AuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Triggers

Now, one of my colleagues in particular will shoot me down for this as, for years I’ve been strongly and completely against triggers.  They cause no end of pain when performance tuning and debugging line of business application databases.  I always argue that all trigger logic can, and should, reside in (transaction based) stored procedures that actually perform the data updates.  I actually do still stand by this, but, in this situation we don’t really have general access to the data update stored procedures so, although I’m actually breaking one of my own 10 commandments, my hand is forced.

For each of the original tables a trigger is required to update the appropriate audit table.  To make the reporting easier I create a trigger for Update and Insert, this means that the most recent version of the record is in the audit table too.  (C’mon, storage is cheap !)

Example Original Table Trigger Create Statement:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trig_AssignmentsHistory_Audit] ON [dbo].[AssignmentsHistory]
FOR UPDATE, INSERT 

Not For Replication

AS

Set NoCount On

INSERT into Audit.AssignmentsHistory(
    AssignmentID, 
    ChangeDatetime, 
    CreateDatetime, 
    UserID, 
    Action, 
    Comments)
Select
    AssignmentID, 
    ChangeDatetime, 
    CreateDatetime, 
    UserID, 
    Action, 
    Comments
From Inserted

View

In order to report the audit trail, a simple view is required to show the changes over time of the values held in the fact.  This view is primarily based on the audit fact table and resolves all the dimension member ids to give you the dimension member names.

Example Audit Trail View: (This view is specific to my fact table.  It will need modification for each fact table).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [Audit].[AuditReport]
AS
SELECT
    AuditFact.ChangeDatetime,
    dbo.D_Scenario.Name AS Scenario, 
    dbo.D_Account.Name AS Account, 
    dbo.D_Entity.Name AS Region, 
    dbo.D_Role.Name AS Role,
    (SELECT TOP 1 UserName FROM Audit.AssignmentsHistory ah INNER JOIN Audit.BizUsers bu ON ah.UserID = bu.UserId WHERE AssignmentId = AuditFact.AssignmentId) ChangedBy,
    AuditFact.Value
FROM         
        Audit.MG_Drivers_MeasureGroup_default_partition AS AuditFact 
            INNER JOIN dbo.D_Role ON dbo.D_Role.MemberId = AuditFact.Role_MemberId 
            INNER JOIN dbo.D_Account ON dbo.D_Account.MemberId = AuditFact.Account_MemberId 
            INNER JOIN dbo.D_Scenario ON AuditFact.Scenario_MemberId = dbo.D_Scenario.MemberId 
            INNER JOIN dbo.D_Entity ON AuditFact.Region_MemberId = dbo.D_Entity.MemberId

The output of the view above will detail each of the changes made to each of the facts over time when queried like this:

SELECT
    Scenario, 
    Account, 
    Region, 
    Role, 
    ChangedBy,
    ChangeDateTime,
    Value 
FROM 
    [Audit].[AuditReport] 
WHERE 
    Scenario = 'Budget' AND
    Account = 'Annual Salary' AND
    Region = 'South West' AND
    Role = 'Assistant Manager'
ORDER BY 
    ChangeDateTime DESC

Resulting in the following output (most recent first):

Notice the HR Director set the initial salary value for the South West Assistant Manager, the rather indecisive Finance Director made a few amends for it to be later adjusted again by the HR Director.  PerformancePoint Planning Data Change Tracking !

Possible Enhancements

The above method describes a very simple way of auditing data level changes in PPS-P.  This can be taken much further and I’ve included some possibilities below:

• Develop a parameter driven SSRS report to report the audit trail for a dimension member combination
• Create some generic scripts that accept the fact table as a parameter and create the appropriate audit tables, triggers and views.
• Link up with the fact table annotations table and/or the submission comments (Held in the AssignmentsHistory table) to enrich the reporting.
• The data volume will obviously grow over time – intelligent purging/archiving of audit records.
• Update the view to handle certain conditions where adjacent audit trail records contain no apparent change in value (only the change date will be different – this can occur when a contributor submits, makes subsequent changes and submits again without first clearing current changes).