Jeremy Kashel's Blog : SQL, PerformancePoint

Audit Trail in PerformancePoint Planning

Jeremy Kashel — Mon, 15 Dec 2008 15:49:05 GMT

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.

Technorati Tags: PerformancePoint,SQL,C#

Entering Dates in PPS Planning Assignments

Jeremy Kashel — Thu, 27 Nov 2008 09:14:57 GMT

In the recent PPS Planning projects that I've been involved in, the challenges have often been around subjects such as business rules, hence the often recurring theme of this blog. Recently the tables were turned though, as I was told by a user that they wanted to enter dates into a PPS assignment.

I was initially a bit concerned that the Excel add-in may not be able to deliver here - after all its great at capturing numbers, but knowing the rigid structure of the fact tables, I couldn't see how it would manage to store a date. Then I remembered something from my VBA days many years ago - that is that Excel stores dates as a number from 30/12/1899, meaning in theory it should be possible to get dates working in PPS. Thankfully it is possible, as this post explains.

Excel Setup

The first step to get this working when designing your form template is to set the matrix to have a matrix style of 'none'. If you don't do this, then the built-in matrix styles will over-ride your formatting changes to the required cells. Speaking of formatting, the next step is to format the data entry cells that will contain dates, just using the standard Excel formatting window:

Once these few simple steps are done, then the assignment will behave just like any other. As the date is stored as a number, the numeric representation of the date will end up in the fact table just as any other piece of data.

Dates in Business Rules

Once the numbers are in the fact table, we need to convert them to dates to use them in business rules in some way. We can't do much in PEL unfortunately, so the options are either NativeMDX or NativeSQL.

As Analysis Services can pickup some of the VBA functions, it's possible to use the VBA DateAdd() function to convert the stored number back into a date. So in the example below, I'm using the DateAdd() function to convert the number to a date, before comparing the resulting date against another date using the VBA DateDiff() function:

WITH 
MEMBER    [Measures].[DateExample] AS VBA!DateAdd("d", [Measures].[Value], "30/12/1899")
MEMBER    [Measures].[DateDiff] AS VBA!DateDiff("d", [Measures].[DateExample], "01/07/1987")
SELECT    Descendants([Time].[Monthly].[Year].&[2008],,leaves) ON 0
FROM      [Strategic Planning]
WHERE     ([Account].[Profit and Loss].&[5010], [Measures].[DateDiff], [Entity].[Divisions].&[5003])

Although the above is just a simple example, it should give you the idea of the kind of calculations that can be performed in Analysis Services. It's possible to use these functions via a NativeMDXScript or a NativeMDXQuery.

It's a similar story with SQL, as it also has its own DateAdd() function, as shown in the simple select statement below:

SELECT    DateAdd(d, [Value], '30/12/1899')
FROM      dbo.[MG_Strategic Planning_MeasureGroup_default_partition]
WHERE     Scenario_memberid = 4 AND Account_MemberId = 5010

So it's a shame that PEL can't work with dates, but the fact that both the database engine and Analysis Services have a DateAdd function means that it's possible to use dates for logic in both definition and procedural business rules.

Technorati Tags: PerformancePoint,MDX,SQL,VBA,Excel

Tracking PerformancePoint Planning Submissions Using Reporting Services

Jeremy Kashel — Fri, 26 Sep 2008 16:29:29 GMT

The standard operational reports that come with PerformancePoint will allow you to report on a variety of PerformancePoint admin related activities, such as cycles, assignments, forms, jobs and associations.

I find that the assignments report is particularly useful - after all, finding out who has/hasn't submitted is an important part of any data-gathering exercise. Whilst it is useful, I do find that the assignments report is the one that admin users want changed, especially when a model site exists containing many cycles and assignments.

Extra Functionality

With a large PPS Planning implementation you can easily end up with many assignment instances, cycles and users. I've been involved in such an implementation recently, and, due to the large number of assignments, the admin user requested a bit more filtering capability than the out of the box assignments report provides.

Also, the existing assignments report tells will tell you that user A has submitted their assignment, but it won't go into any detail about what the submission actually contained. E.g. did the user submit all their entities? For some users it is quite key to know what other users have been submitting - for one thing it makes navigation easier if as an approver you know exactly which department/entity to pick in the assignment filters.

Examples

By knowing which tables to use, you can write an SSRS report that provides the additional functionality mentioned above.

The starting point is to get the base report query right. In my case, as I'm in a separate auditing database, the query goes inside a custom stored procedure, and is as follows:

SELECT              A.AssignmentId,    C.CycleInstanceName, AD.AssignmentDefName, A.AssignmentName,
                    U.UserId, U.UserName, ENT.Name AS EntityName, CUST.Name As CustomerName,
                    CASE
                        WHEN A.Status = 'partial' OR A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END AS Draft_Submitted,
                    CASE 
                        WHEN A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END As Final_Submitted,
                    CASE
                        WHEN A.Status =  'Approved' THEN 1
                        ELSE 0
                    END AS Approved, Approve.UserName As Approver
FROM                dbo.Assignments A 
LEFT OUTER JOIN     dbo.[MG_Planning_MeasureGroup_default_partition] Fact
                    ON A.AssignmentID = Fact.AssignmentID
LEFT OUTER JOIN     dbo.AssignmentDefinitions AD ON AD.AssignmentDefID = A.AssignmentDefID
LEFT OUTER JOIN     dbo.CycleInstances C ON C.CycleInstanceID = A.CycleInstanceID
LEFT OUTER JOIN     dbo.D_Entity ENT ON ENT.MemberId = Fact.Entity_MemberId
LEFT OUTER JOIN     dbo.D_Customer CUST ON CUST.MemberId = Fact.[Customer_MemberId]
LEFT OUTER JOIN     dbo.BizUsers U ON U.UserID = A.ContributorUserId
LEFT OUTER JOIN     dbo.ApproverList AL ON AL.AssignmentID = A.AssignmentId
LEFT OUTER JOIN     dbo.BizUsers Approve ON Approve.UserID = AL.ApproverUserID

You can figure out most of the tables to use by looking at a view called AssignmentsView within the application database.

One thing that I have taken into account is assignment definitions. If you have large number of users completing an assignment, then the chances are that you will have set up an assignment definition that points at a business role or a submission hierarchy. You ideally want to be able to filter on the assignment definition to return all assignment instances that belong to that assignment definition. Therefore, in my case I have three filters for the report, but you could easily add more:

The final view is a report that shows the status of the assignments returned by the filter, but also, when expanded, shows the the entities and customers that the contributor has submitted:

The above is just a taster of what can be achieved. A couple of ways that it can be extended include:

Integrating with Sacha's data auditing idea to provide detailed history on what values the contributor has changed;
Including comments, annotations and deadlines.

Technorati Tags: PerformancePoint,Planning,PPS,SSRS,Reporting Services,SQL Server 2005

Using NativeSQL Business Rules for Seeding or What Ifs

Jeremy Kashel — Tue, 05 Aug 2008 11:45:00 GMT

You need to give really careful consideration to procedural business rules in PerformancePoint Planning. This is where multi-dimensional knowledge and experience helps, as you need to know whether its best to use a SQL or MDX implementation for your rules.

User Requirements

One area where I've found that this is particularly relevant is user requests for procedural rules that accept parameters that are member(s) of a dimension. E.g. Lets run the rule for the selected member in the entity dimension, perhaps to carry out a what if on a particular entity. This is fine for a single member, but on occasion I've found that the users want to pick either a)a leaf level member (which is fine) or b)a non-leaf member, and for the rule to apply the logic to all descendants of that member.

The Technical Problem

What technical problem? You can use the descendants function to achieve this right? You can with an MDX rule, but you can't with a SQL rule. So, surely just use an MDX rule then?

This is where you have to think relational world Vs OLAP world. Putting a statement such as Descendants($Entity$, 10, leaves) in the scope of your rule will mean that when the rule is run, then it will always operate on the leaves of the dimension, regardless of whether you pick a leaf or non-leaf member. But the issue is that if your scope statement covers cells that have definition rules against them, then an MDX query will pick up the results of of those cells and and shove them into the fact table!

So can you use the Except() function or something similar to filter out those cells that you don't need? Yes you can, but not if the members start to span different levels, the compiler won't let you do that. As I said, you can't use a SQL implementation because using Descendants($Entity$, 10, leaves) in the scope statement will tell you - "A SQL implementation is forbidden by a parameter or variable reference in a MemberSet expression unless the reference is the entire expression."

The Solution

Rules that operate on fact data should really use SQL, its quicker and avoids hitting calculated data. I'm sure that you've seen this coming a mile off (!), but one way to go about this is to use a NativeSQL rule.

You can reference database objects in your NativeSQL rule, one of which will make it easier for you carry out descendant/hierarchy type functions in SQL itself. A function will get created in the Calc schema of your Planning application database in the format fnCalc_0_DimensionName_MemberSetName_IsAncestorOrSelf - this will occur when you deploy your model site. Using this function in conjunction with NativeSQL will allow you to get the leaves of a member quite easily.

The following NativeSQL rule creates a copy of one scenario's data into another scenario, which is referenced by a parameter:

--Although integers, the parameters are actually 'members of a dimension' parameters
create proc Calc.[sp$procedure] @scenario int, @entity int as
begin
begin transaction

    insert into dbo.[tbl$factTable] (alldim$except(dim$Entity, dim$Scenario), dim$Entity, dim$Scenario,
    column$ruleId, Value, LoadingControlID, bmo$CreateDateTime, bmo$ChangeDateTime, ContextID)
    select alldim$except(dim$Entity, dim$Scenario), dim$Entity, @scenario, $ruleId, Value, -9, getutcdate(),
    getutcdate(), -1
    --No need to hard code the fact table name
    from dbo.[tbl$factTable] t
    inner join 
    (
        select e.memberId from dbo.d_Entity e
        --Get leaves of the member key that is in @entity
        where Calc.fnCalc_0_Entity_Divisions_IsAncestorOrSelf(@entity, e.memberid) = 1 and 
        e.memberid not in (select h.parentmemberid from dbo.H_Entity_Divisions h)
    )    lv on lv.memberid = dim$Entity
    where t.dim$Time >= 200701 and t.dim$Time <= 200712 and (dim$Scenario = 1)

if @@error <> 0
rollback transaction
else
commit transaction

end

I suppose the above rule may look a little bit daunting, due to all the dollar signs everywhere. These are just the preprocessor commands, which will get replaced when the stored procedure gets created. But other than that, its just an insert statement. In some cases the syntax is actually quite handy, as you can use statements such as alldim$except(), which is a short hand way of returning fact table dimension column names very easily. The idea behind the preprocessor commands is that you can protect the logic in your rule from minor structural database changes.

The key is that when the rule gets run, it has normal parameters, meaning the user will see the following interface:

In the case above, the member key of North America Operations will end up in the stored procedure, as I have a parameter in the stored procedure called @entity. Inside the stored procedure it is then used in combination with the IsAncestorOrSelf function to pick up the leaf level descendants.

The only caveat that I will make is that this hasn't come from a production environment, so I've skimped a bit on a few of the operational details such as deleting the existing data, decent error handling and returning the number of records affected. These are all things that you should add in if you use this kind of thing in a production environment!

Technorati Tags: PerformancePoint,SQL,NativeSQL,Business Rules