Audit Trail in PerformancePoint Planning

I’ve noticed that the PPS Technet documentation has been updated recently to include an official Microsoft method to carry out auditing in PPS Planning.

PPS will do some basic auditing out of the box, namely to the audit.log file on the server. This will automatically capture key events that occur on the server, e.g. creation of a model, updating of a dimension etc. The audit file does not, however, track changes to the model fact data. There has been a custom solution around for this for a while now – Sacha has written an excellent post here that details what you need to do in order to implement your own PPS audit trail.

Like Sacha’s method, the Microsoft approach involves creating auditing tables, which should then be populated by running a custom stored procedure. The stored procedure should then be scheduled on a periodic basis (e.g. hourly) to capture any new activity. This is a bit different to Sacha’s method, where triggers are used to capture changes in real-time as they occur. In both cases the idea is to use something like Reporting Services to to view detailed auditing reports on your PPS data.

One thing that did catch my eye on in the Technet documentation is a method to decode the binary ‘change list’ column that’s held in the dbo.Submissions table. Whereas you can manually export the change list to a CSV file, there has historically been no way to take what’s in the change list column and automatically decode it into a useful format. The following C# code will read the change list, and then insert it into your newly created auditing table:

DataSet ds = new DataSet();
DataLayer dl = new DataLayer("PPSConnection");
ds = dl.ExecuteDataSetFromSQL("SELECT [SubmissionID]FROM [_AppDB].[dbo].[Submissions] s1 where 
s1.SubmissionID not in (select SubmissionID from [_StagingDB].[dbo].[SubmissionsAudited]) 
and s1.[Status] = 0");
string sSQL = "";

foreach (DataRow r in ds.Tables[0].Rows)
{
    sSQL = @"INSERT INTO SubmissionsAudited(… ) VALUES(";

    // RETRIEVE THE CHANGELIST FOR THIS SUBMISSION 
    DataSetWrapper dsw = new DataSetWrapper((Byte[])r["ChangeList"]);
    foreach (DataRow cldr in dsw.DataSet.Tables[0].Rows)
    {
        // SUBMISSION ROW DATA
        sSQL += r[0].ToString() + ", "
        + r[1].ToString() + ", "
        + r[2].ToString() + ", "
        + r[3].ToString() + ", '"
        + r[4].ToString() + "', ";

        // CHANGELIST ROW DATA
        foreach (object o in cldr.ItemArray)
        {
            sSQL += "," + o.ToString();
        }
        sSQL += ")";
    }
    // STORE EACH CHANGE TO THE AUDIT TABLE
    dl.ExecuteNonQuery(sSQL);

 

Click here to view the Technet documentation.