Update: I've posted a solution to Deleting a Custom Member Property here
I've done this countless times; I've created my perfectly named Custom Member Property when it suddenly dawns on me that I've forgotten to give it the right data type. No problem, right? Wrong! From within PBM, can you change the data type? No! Can you delete the member property? No! Can you rename the member property? No!
So, what are the options? Well, you could wait for version 2 (I truly hope you can edit/delete member properties in V2!), you could hack the back end database in the vague hope of removing the member property safely, or, as I have been doing in the past, create a new member property with a less than perfect name and try not to clench teeth and fists every time I glance at the original.
Well, I've had enough, and decided I'm going to take action.
Strangely, the Microsoft.PerformancePoint.Planning.BMO assembly contains a method called DeleteDimensionAttribute on the Dimension attribute collection.
I wonder...
Anyone tried?
Amidst my write up of the first day of the Microsoft BI Conference, I mentioned a new planning demo suite was imminent, and I would post more information about the demos soon. Well, as it has now been officially released (27th October) I can spill the beans...
Taken directly from the PPS Planning Forum announcement, the demo..
.. consists of Planning and Financial Consolidation demo. It shows how the fictitious Contoso Group uses Microsoft Office PerformancePoint Server for planning, statutory consolidation and data analysis.
Well, I'm proud to announce that Adatis, in the shape of my colleague Jeremy Kashel, designed and built the PerformancePoint Planning element of the suite. The PerformancePoint Financial Consolidation element was conceived and developed by our friends at Solitwork of Denmark.
The demo can be downloaded from here...
http://www.microsoft.com/downloads/details.aspx?FamilyId=00B97AC5-8B69-4F4D-AA0C-ACBFBFB9B48E&displaylang=en
...and is part of the next 'All Up BI VPC' (Version 7).
Great work guys!
The last day of the conference came round quickly and due to my relatively early flight I only attended a couple of sessions and spent most of the day meeting more people in and around the BI community. Shout out to Peter Eberhardy (PeterEb), a real highlight. Barry Tousley, Test Dev on the PPS Server; thanks for listening to my grumbles about the PPS Data Migration Tool and explaining why it does what it does. Norm Warren of NormBI fame. Patrick Husting, who I actually met on Day 2 and Brian Berry of BlumShapiro, who I met on Day 1 and reportedly follows this blog !
I thought the conference was great. The organisation was slick and right on the button, from registration, meals, session offerings right up to the party. I think last year, the main criticism was the sessions were not technical enough, they appear to have raised the technical level of some of the sessions but I still found most of them to be a bit dated and apart from a couple of choice sessions most BI people wouldn't have learnt a great deal - Nothing official at all about PPS v2 :o( Also, a couple of the sessions I wanted to attend clashed so I'll have to find the time to watch them on the reported DVD containing a video of every session. However, I did the feel the standard of presentation was excellent, well practiced, clear, funny and engaging.
I'll definitely be vying for a place at next years, where they really should have lots to show off!
Day 2 kicked off with some good key notes and still full of steak from day 1 I hauled myself to TK Anand and Akshai Mirchandani's session on Optimising Query Performance in AS 2008. For me this was one of the best sessions of the conference as I do spend a fair bit of trying to tune and optimise MDX queries. They gave a really good explanation of the difference between cell-by-cell calculation and the subspace calculation (or block computation) methods - the latter relies on a sparse cube - the most important aspect of speeding up queries using subspace calculations.
Another point they raised, particularly from an AS2008 perspective is that "Non Empty Behaviour is Evil!" - their words!
There was a good set of tips and tricks, some of which can also be applied and adopted to AS2005.
The afternoon started with what I thought would be the busiest session of the conference - New Horizons for BI with Self Service Analysis technologies. Effectively the deep dive presentation on Project 'Gemini'. It really is impressive, not only the tool and the capability of the tool, but the supporting framework. They have implemented an extremely rich administration console that keeps track of user created models on the server and a history of it's usage, query time etc etc. It allows IT to see who is using what, by how much and what impact it is having on servers, other models etc and allows them to take appropriate action by, for example, bringing it in house into IT by upgrading to PerformancePoint. We've got a few clients that would just go nuts for this stuff !
That evening, the Attendee Appreciation Party was held at Qwest Field stadium where I have to say, they put on a great party. I've never been on the field of a huge stadium like that, most impressive, and I've never eaten so many chocolate covered marshmallows, cookies or brownies in my life!
Da Boyz ! Jeremy and Tim
So, although later than the trail blazers, I thought I'd write up a brief note about day one of the Microsoft BI Conference. The 'Kilimanjaro' announcements have been done to death although I've noticed a couple of crossed wires. Kilimanjaro is NOT the next version of SQL Server - it sounds more like an interim release, whether that comes as part of a service pack or a new type of feature pack delivery method I guess we'll have to wait and see. However it arrives, we have to wait until the first half of calendar year 2010.
With regard to 'Gemini' I'm hoping they make the in-memory (column based storage?) engine part of the SQL Server engine proper, as this can then benefit PPS and any SQL Server dependent app, not just the 'Gemini' framework. Imagine PPS-P data entry/reporting running in memory ! It's certainly a game-changer and it will be interesting to see where and how it's positioned. I can't help thinking that it's verging on promoting data silos and 'multiple versions of the truth' and it wouldn't surprise me if it's misused by some customers. "We don't need a data-warehouse, we'll just use Gemini".. Although Tim did quiz the team on this. Having said all that, it's pretty damn exciting and will change the face of BI for both customers and BI implementers.
The first breakout session I attended was a Chalk and Talk by the engaging Peter Bull on moving from the spreadsheet world to PerformancePoint Planning. He outlined a suggested method for converting existing excel based 'business applications' into PerformancePoint models, he was open and honest about some of the shortcomings of the product but also brought our attention to the the built-in features that aid design and productivity.
The following tips were core to the session:
- Don't replicate the current Excel 'models'.
- Use filters to reduce scope and size of input matrices.
- Limit definition rules (Don't build cell by cell calculations)
- Don't use flat hierarchies.
- Don't assume all calculations need to be real time.
- Performance test by cut and pasting MDX.
Another Chalk and Talk followed, held by Michael Bower and Scott Sebelsky on using PPS-P for Financial Consolidation. They discussed the consolidation functionality available in PPS-P and using a two model site application, walked us through the implementation using US GAAP (Corporate Model Site) and IFRS (EMEA Model Site).
The demo, supporting white-paper, and a new planning demo will be available shortly and was shown off in the hands on labs at the conference. I'll shortly be able to post more information on these new demos...
My third session of the day effectively covered some elements, mainly Report Builder 2.0, of the SQL 2008 feature pack that is out later this month. One of the features demonstrated Component Based Report building from a self-service perspective and did look quite slick. The session was presented by the SSRS PM team and they had a clever way of obtaining feedback from the audience on what features they would like to see the most. They handed out a crib sheet of features and asked us to allocate a $100 budget to each feature - they collected in the sheets and will use this as a basis on what features to focus on. In addition to Component based self-service reporting, features such as Office Integration using the acquired Software Artisans technology, Notify Me, Interactive Reports and Rich Presentation were shown off to good effect.
Steve Hoberecht and Srini Nallapareddy were next on my list, taking us through Advanced PPS Planning rules and calculations. There was some good stuff - I always thought the fact the ALLOCATE statement appended data was a bug, but now I know why it does what it does and warrants a separate post. Some other tips, particularly for definition rules, some new some old, were also presented:
- Reduce Scope
- Avoid Hard-coded member labels to avoid security restriction errors
- Consider automatic rules/ scheduled assignments.
- Rule order is important / Avoid infinite loops
- Consider moving calcs to Excel
- Consider input/Reporting models
- Locate bad performing rules by commenting out the rules in BIDS and introducing the rules on by one (from within BIDS) rather than setting the active flag from within PBM as that is more tedious.
The day was rounded off by a great steak, with the other UK BI partners at Ruths and Chris Steakhouse.
I think I've found a bug in the way the Excel Add-In generates MDX under certain 'rolling' conditions. The requirement I have is to be able to forecast at the day level for a rolling 6 months; starting from the current period (which is to be updated each week) running for a period of 180 days (~ 6 months)
To prevent requiring 180 columns, a dimension property based filter must be available to select the month in which to forecast. This will provide a more concise data entry form detailing up to 31 days of the selected month in which to add forecast values.
My form is dimensioned up as follows:
| Dimension | Position |
| Employee | Filter |
| Time(Month) | Filter (Dimension Property) |
| Scenario | Filter |
| Location | Rows |
| Time (Day) | Columns |
I set up the columns as a dynamic range to ensure that the forecast 'rolls' with changes in current period. The range was set from current member id + 0 : current member id + 180. [Current Period is set to 16th September 2008 - today).
The simplified MDX that this produces is below:
select
{
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(0)
:
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(-180)
}
*
{
[Measures].[Value]
} on columns,
{
descendants([Location].[Standard].[All].[All Locations],,after)
} on rows
from
(
select
{[Time].[Month].[All].[September 2008]} on columns from [LocationPlan])
where
{[Employee].[Employee].[All].[John Doe]}
*
{[Scenario].[All Members].[All].[Forecast]}
The first element to notice is that the columns have been set to a range using ancestor at the member id level and lag to cover the 180 days:
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(0)
:
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(-180)
The next point to highlight is the sub=query that represents the selected time dimension property value (September 2008):
{[Time].[Month].[All].[September 2008]} on columns from [LocationPlan])
When you run this in SSMS, the following data set is returned:
The Locations appear on the rows, the days appear on the columns - exactly as required.
By changing the sub-query filter to October 2008 - the next month in the range, and definitely covered by the -180 day lag (Not sure why the Lead function isn't used here?) - results in a problem, the results returned now are missing the day level columns:
The root of this problem is the column expression - if you replace the column expression with a direct lag on the current period member the expected results are returned:
select
{
[Time].[Base View].[MemberId].&[20080916].Lag(0)
:
[Time].[Base View].[MemberId].&[20080916].Lag(-180)
}
*
{
[Measures].[Value]
} on columns,
{
descendants([Location].[Standard].[All].[All Locations],,after)
} on rows
from
(
select
{[Time].[Month].[All].[September 2008]} on columns from [LocationPlan])
where
{[Employee].[Employee].[All].[John Doe]}
*
{[Scenario].[All Members].[All].[Forecast]}
Now, the only workaround I can come up with is to build the form using a custom MDX formula so I reckon this warrants raising a bug on connect - which I've logged here:
https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=368206&SiteID=181
Wavesmash has posted a series of tips and tricks shared at a train the trainer event that took place in Denver recently. As suggested, most of the 'nuggets' are from the attendees themselves rather than the course material so, on the plus side there are some real experienced based tips however, I wouldn't treat all as official tips and tricks - I certainly frowned at a couple but that could be due to the explanation rather than the intent.
There's certainly some goodness, and one that made me smile: Regular Refresh of model = happy modeler
http://performancepointing.blogspot.com/2008/08/train-trainer-helpful-tricks.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-2.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-3.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-3_26.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-4.html
I've built a really simple GUI for a couple of commands of the PPSCMD utility. I always take far too long to work out the syntax and navigate to the appropriate directory (Yes, I ought to update the PATH environment variable) that I felt I could justify building a simple front end to help speed up the usage.
So far I've only implemented the MIGRATE and REPROCESS commands - I use these quite a lot outside of any automated SSIS package so they seemed the most sensible to implement in the GUI first. I do intend on extending it to encompass some of the other commands and I would welcome any feedback towards prioritisation, usage, features and the inevitable bugs. It's currently version 0.1 and more or less ready for 'Community Preview' - there are some omissions such as full error handling and validation that I do intend on implementing over time along with the other commands.
It's a .NET 3.5 application so you will need to deploy it to a client where you are happy to install .NET 3.5 if it's not already present.
You can download version 0.1 from here - (You will need to create a login first)
Below are the screen shots:
Migrate
The migrate command: both import and export variations can be set and executed directly from the GUI. In addition, the command line is generated so you can cut and paste into a command window, batch file or SSIS package.
Reprocess
Need to reprocess a model quickly? Rather than wait for PBM/SSMS to open you can reprocess a model directly from the GUI. Just like Migrate, the command is generated for cut and paste.
Console
Any output you would normally see in the command window is reported in the console as the command is being executed.
Log
You can enable logging to a log file of your choice to record all commands processed through the GUI. Useful for additional auditing and for creating batch files of multiple PPSCMD operations.
Preferences
Preferences and options are set on the preferences dialog.
I've blogged about some of the financial consolidation elements of PerformancePoint planning before. Most of the elements related to financial consolidation e.g. Share calculations, intercompany transactions etc are specific to consolidation and only implemented when performing some form of statutory consolidation. Currency conversion, on the other hand, can be common to any element of PerformancePoint Planning whether it's a budgeting, forecasting, planning or, in fact statutory consolidation solution.
In it's simplest, out of the box, form, currency conversion is pretty straightforward. You need an exchange rate assumption model to hold the exchange rates and you need a financial model without shares that contains a pre-defined currency conversion rule that utilses the exchange rate assumption model. You run the pre-defined rule on a set of financial data and currency conversion is calculated for you. There's little more to it than that.
This post looks a little (and I mean a little) deeper in to currency conversion and demonstrates the triangulation features contained within PPS-P.
Consider this set of exchange rates, taken from www.xe.com today:
On the face of it, we have a few rates that allow us to convert from British Pound to Japanese Yen, US Dollar to both British Pound and Euro, and finally a rate for converting Euro to Japanese Yen.
When the in-built currency conversion rule is run against, for example, a set of US financial data we can determine the British Pound equivalent. The Net Profit/Loss of 110,500 USD, converted to British Pound at a rate of 53.5 British pence per dollar converts to 59,117.50 GBP - As you would expect:
What would happen if we were to run the currency conversion rule again but this time try and convert the US Dollar figures to Japanese Yen?
The astute among you would point out that we don't have an exchange rate for converting US Dollar to Japanese Yen. The more astute among you might say that we could determine the JPY figures by first converting to GBP and then converting the GBP figures to JPY. The even more astute among you might suggest that you could calculate the Japanese Yen two ways, either by first converting to GBP and then to JPY or by first converting to EUR before converting to our desired JPY.
Who's right? Well, all of you, kind of!
If I run the currency conversion job again but instead of selecting GBP as the reporting currency, I select JPY, I get the following result:
No conversion ! Why - well, we don't have an exchange rate set for converting USD to JPY!
This is where triangulation comes in. With our current set of exchange rates there are two methods for converting USD to JPY. Either through GBP or EUR. You set which 'route' the triangulation takes by setting the model property 'Default Currency'. By default this is not set, hence our failed conversion above.
However, if you were to set it to either GBP or EUR you will get some results, but there is a catch - the 'route' you take returns different results. Incidentally, you don't have to redeploy the model when you change the property you just need to save the model.
Triangulate through GBP
110,500 USD at a rate of 0.535 equals 59,117.50 GBP. Convert the GBP to JPY at a rate of 205.524 and that equates to 12,150,065.07 JPY.
Triangulate through EUR
110,500 USD at a rate of 0.535 equals 75,029.50 EUR. Convert the EUR to JPY at a rate of 162.068 and that equates to 12,159,881.006 JPY.
The two 'routes' highlight a variance of 9815.936 JPY (about 90 USD)! A triangular arbitrage !
So, two questions:
1) Which 'route' should you use?
That's a business decision and if it's going to be a common conversion then direct rates ought to be favoured over triangulation.
2) How do you exploit this model to work out guaranteed triangular arbitrage to make millions through currency trading?
I'm working on it !
I was unable to make the first Microsoft BI Conference in Seattle last year but Tim made it, and reported that it was a great excuse for an extended weekend of partying and snowboarding in Whistler way to meet like minded people, learn the latest buzz and feel part of the BI Community.
Well, this year I can make it and I will be joining Tim and Jeremy for the duration. Bad luck for me though, it's too early for snow ! ;o)
I've checked out the Tracks and Sessions and there is quite a few I'm up for. They haven't published the schedules yet so whether I can jump tracks and still fit everything will remain to be seen but here are my picks:
Business Value
- Emerging Trends and Technologies in the Business Intelligence Market
- The Path to Pervasive Performance Management
- The ROI of Performance Management
Clients and Applications Track
- Advanced Office PerformancePoint Server 2007 Planning Business Rules and Calculations
- Integrating Microsoft Dynamics Data for Planning, Analysis and Reporting in Office PerformancePoint Server 2007
- The 4th Annual BI Power Hour
Customer Track
- Master Data Management (MDM) Customer Roundtable
- Successfully Implementing the Complete Microsoft BI Stack
- The Evolution of “Difference Makers” at Energizer Holdings, Inc. Using Microsoft Business Intelligence
Deployment & Best Practices Track
- Designing High Performance Cubes in SQL Server 2008 Analysis Services
- Mastering Master Data Management
- Office PerformancePoint Server 2007 Planning - Implementation Tips And Tricks
Partner Track
- BI Unplugged - a Panel Discussion
Platforms and Infrastructure Track
- Optimizing Query Performance in Microsoft SQL Server 2008 Analysis Services
- SQL Server 2008 & Beyond
- SQL Server 2008 Integration Services New Features and Enhancements
Phew! Well, after that lot I'm definitely gonna need a drink, so if you fancy a bevvy, hunt me down, be great to hook up !
There's a new workbook property that was introduced in PPS Service Pack 1. The 'Clear Changes After Workflow Action' effectively splats the change list for the workbook once the assignment has been submitted (either draft or final).
The property can only be reached through the Report Properties dialog, and is at the workbook level:
This property defaults to false which, under certain circumstances can hinder performance.
Whenever you change data on a matrix, the slice that you affected is saved to a change list. You can view what's on the change list by choosing 'View -> Show Current Changes' from the PPS Add-In for Excel.
Here's an example change list; two budget accounts for the same time period and department have been updated to the included new values.
The default behaviour (and the behaviour prior to SP1) is that, for the life of the assignment, the change list is maintained for every cell that is updated The change list is simply appended to, so you can imagine, on a large workbook with several matrices spanning several filter slices, the change list can become quite large.
Submitting the assignment effectively submits the change list for processing by the server, first updating/inserting the appropriate records into the fact table and subsequently re-processing the Analysis Services partition. It follows then, that the larger the change list, the slower the submission process.
Before SP1, this forever growing change list issue was resolved with little user training. As part of the submission process you would invite your users to manually clear the change list:
By 'Clearing Current Changes' you throw away the changes to the cells and have to rely on the data being safe and sound on the server. This process helped keep the change list to a more manageable size thus gaining on the submission performance.
The new 'Clear Changes After Workflow Action' property in SP1, if set to true, will perform the 'Clear Current Changes' step for you automatically. This helps keep the change list lightweight (providing of course, the user regularly submits). However, as I have already implied, there is one issue to be wary of; with the property set to clear changes, if your submission fails, the change list is lost and there is a real danger of losing data.
More for my benefit this post; I can never remember the format for the CSV file to load the Account dimension.
Below is an example csv file in it's simplest form that should help get me started faster the next time I need it !
Label,Name,Description,AccountTypeMemberId
1000,Salaries Permanent Staff,,Expense
2000,Temporary Staff Workload,,Expense
4000,National Insurance Costs,,Expense
5000,Pension Costs,,Expense
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).
We recently had to turn a couple of demo boxes into a 2 client development environment for an overly aggressive PPS-P deadline. Both demo boxes were connected to the network and both were PPS-P Servers. We wanted to nominate a specific box as the PPS-P server for the build.
Typically, the one we nominated, for some reason, would not allow us to add domain users to the Users tab of the PAC whereas, the other one would.
We received the following (not particularly helpful) error message when attempting to add a domain level user through PAC.
The following items cannot be saved
The total number of errors during submission is 1.
An unknown server error occurred when submitting User - <domain>/<username> The server log may contain more information about this error.
In our environment both boxes were using LOCAL (but different) accounts as the Application Pool identity and the Planning Service Account Identity. When we changed the local identity accounts over to DOMAIN level accounts we could add users on both boxes. It appears that the PPS-P web service needs access to the domain controller to check whether the user entered actually exists. In fact it does a little more than that as it retrieves the user SSID to store in the Planning database too.
Note: In addition to changing the user to a domain account we had to ensure that account existed in the local IIS_WPG (IIS Worker Process Group) also.
But, why, when using local accounts, would one server allow the addition of domain users and the other not? The answer was pass through authentication ! The server that worked under the local account was using an account and password combination that matched an account and password combination on the domain !
Nick Barclay posted about connection problems with PPS M&A a good while back and gave a great write up of how to overcome issues encountered. Now that's worth a read if you are not familiar with IIS Application pools and identity accounts as the issue we encountered were related.
Andrew has posted a great list of community developed utilities for SQL Server. There is a few on there that have been on my 'must-do-one-day' list for far too long !
More Posts
« Previous page -
Next page »