Creating an audit view of submissions in Planning Server

Updated: 2009-04-30

A business owner often needs to perform analysis on all submissions made in PerformancePoint Planning Server by using the PerformancePoint Add-in for Excel. The owner wants to see a comprehensive Audit Trail view of all submissions that were made by users and by type of data. Because several users have the ability to make changes to the same slice of data, the business owner needs to perform analysis on all submissions.

As details related to submission changes in Performance Point Planning Server are not stored in a simple retrievable format, code must be written to make this data readily available within a single audit view.

Submission change details that are helpful in an Audit View include which user submitted the data, what time and day was it submitted and what was the value before it changed.

Note

Currently, users can view the details of their submissions by accessing a View within the assignment named "Show Current Changes." The user can view this "Change List" when the Assignment is open, but this is accessed directly through the individual assignment.

The following solution is written for a user who is a Visual Studio developer with experience writing a C#/Visual Basic .NET application, a developer with SQL experience writing and optimizing stored procedures, and a developer with knowledge of how to create and schedule an executable file.

The solution has the following elements:

  • A PerformancePoint Planning Server application containing a Cycle and an Assignment for users

  • Visual Studio 2005/Business Intelligence Development Studio (BIDS) (installed through SQL Server 2005 tools)

  • SQL Server 2005 Service Pack 2 (SP2)

  • A PerformancePoint Planning Server Staging database

  • An account with PerformancePoint Server Business Data Modeler permissions on a PerformancePoint Planning Server Application. This account should also be an Administrator in the development environment.

Before you begin

You need to make some key design decisions on the audit lifetime, the data retention period, and the data volume before you begin. Consider the following points:

  • Audit lifetime   Decide on the intent of the Audit Log. Is this audit log to be used for the life of an Assignment, or as a complete historical audit for any Performance Point Planning activity?

  • Data retention period   Do you wish to store and retrieve the history of submissions after the Assignment has been deleted and after users are removed? If yes, it is recommended that your submission extract will pre-join to the Assignment and User tables, extracting the labels of these objects instead of just the ID’s.

  • Data volume   Choosing to pre-join the information of all submissions will cause the volume of data which is held by your audit log to grow very quickly. It is important that you be prepared to deal with this data volume and understand how quickly it will grow. Consider reducing the resulting query times if this path is chosen.

Following are additional important considerations to go over before you begin.

  • The details of all submissions need to be available through an Excel Pivot Table. You will not want to look at different Excel Pivot Tables for the different types of data submitted. It should all be available in one place.

  • The details of each submission need to be translated from PerformancePoint Labels into user friendly names. The details need to remain as part of the history during a given cycle even if a particular assignment has been deleted or is no longer active.

  • The Submission Table contains the necessary data, but the Change List is located in a column that is of binary image type and needs to be extracted to be usable.

  • The Submissions Table is located on the Application database. Do not modify the Application database.

  • Extract and copy the information from the Application database into the Staging (or other) database.

  • To minimize performance impact, only extract data from the submissions table which has changed from the last run of the audit process.

A code-based solution

Code must be written to make this data readily available within a single audit view. The steps below explain what the code is actually doing. Keep the following considerations in mind as you read through the steps.

  • This solution produces a log record for every submitted cell change, so it may become inefficient to extract all changed data upon every run. Keeping track of the extent of the last extracted submission thereby allows processing of only the latest submissions (that is, the difference between the submissions table and the audit history table).

  • Assignment ID is tied to users: If a user or assignment is deleted, the original data cannot be referenced.

  • Put users and assignment table info in the Audit table so that you retain the information after the assignments are deleted

  • Data in the change list column has a status flag. Confirm the status that the business wants to audit (Submit, Submit Draft)

Creating the solution

Create Staging database tables

  1. Create an Audit table.

    1. Include the Assignment IDs. (Alternately, perform a join to extract the Assignment Name, User Name, to keep the data when the assignment is purged.)

    2. Record all details included in the extracted change list from the working set of submissions.

  2. Perform a join to query using the actual labels.

    OR:

    Perform a join to record only the keys in the audit table for historical purposes.

  3. Create an Audit History table.

    1. Include the Submission IDs from the Submissions Table.

    2. Include the corresponding Audit History ID.

    Table-creation details:

    The process of creating an Audit table is specific to the attributes of your PerformancePoint Planning Model.

  4. To create an Audit table, script the creation of the Application DB MG table corresponding to your Model and change the name to something related to the Model, (for example, MG_Forecast_Audit).

    Implement the following two changes to the creation script:

    First, ADD the following lines to the script:

    [Delete_Value] [float] NOT NULL,
    [SubmissionID] [bigint] NULL,
     [Status] tinyint NOT NULL,
    [UserID] bigint NOT NULL
    

    Next, REMOVE the following lines from the script:

    [RuleID] [bigint] NULL,
     [ContextID] [bigint] NULL,
     [LoadingControlID] [bigint] NULL,
    
  5. Run the script to create an Audit table on the Staging DB of your PPS application.

Create a stored procedure to record audits

  • Create a stored procedure which takes the Submission and ChangeList values extracted by the code (to be created in the next procedure, "Create an executable file").

    The stored procedure will insert the values into the Audit table.

Create an executable file

  1. Run a SQL query against the Submissions table to determine which records do not exist in the logical Audit History Table. The results are you working set of submissions.

  2. Query the results from that query (your working set of submissions) for the change list of each submission. Insert this change list data into the Audit table.

  3. Record the submission IDs in the logical Audit History table to allow tracking showing that they have been processed.

    The submissions table contains a ChangeList column which holds a binary XML dataset.

    The data contained in the ChangeList column is specific to the attributes of the Model, and therefore is not generic to PerformancePoint Server, mandating custom code.

  4. Use the following sample code to view the ChangeList column. Note the use of the DataSetWrapper class that is included in Microsoft.Performance.Planning.Bmo.dll.

    This DLL is installed in the GAC with every PerformancePoint Server installation.

  5. Determining if a submissions record (and its ChangeList) has previously been placed in the Audit table (and updating it) could be accomplished in the following steps:

    1. Query for each ChangeList record — as shown with the sample code that follows.

    2. For each ChangeList record, determine if it exists in the Audit table.

    3. Insert the record only if it does not yet exist.

  6. Compile this code into an executable file so it may be scheduled to occur on a chosen period based on data volume.

    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);
    

We recommend using a SQL view to provide secure access to the information in the audit table.

Create a SQL view for reporting

  1. Write a SQL view for retrieving the data in the Audit table. Join the keys in the audit table to the Application database, or query the data directly from the Audit table, depending on your design decision.

    Important

    A view across two databases cannot be indexed due to the inability to perform schema binding across the Application database and Staging database.

  2. Consider keeping a historical table of all assignments and users in the Staging database so that you can run the query after the Assignments are deleted.

Create a report

  • Create an Excel Pivot by connecting to the SQL view. Consider using a Web page to view the data.

Create and schedule the process

  • Create the process so that you can generate a submission change log at any point.

    Note

    Depending on the volume of submissions, schedule the process to run hourly.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for PerformancePoint Planning Server.

See Also