Collaborate 13 - Stored Spread for Reporting: An Overview
By Brian Diehl on Apr 12, 2013
As Collaborate 13 comes to an end, I have a chance to reflect on conversations and comments from the various Primavera sessions I attended. This is, by far, the most valuable part of attending these conferences for developers like myself. It gets you thinking in some new directions and, sometimes, refocuses you on old topics. My take-away is to look deeper at spreads as they related to operational reporting.
A quick primer on spreads. Spreads are the life-blood of P6. They are the details of how individual values (durrations, units, costs) on activities and assignments "bucket" over time. This is based on both the values in those records combined with related calendar information. Spreads may expand a single activity/assignment row into many buckets depending on the duration and the granularity of the spreads (weekly,daily,hourly).
Spreads for reporting have come a long way. Before the Enterprise Reporting Database (ERDB), access to spreads was limited to reports in P6 Windows Client. And this is only reporting on projects loaded into memory. A time consuming process that make reporting across multiple projects difficult or even impossible.
Beginning with the ERDB 2.0, we introduced stored spreads. This was a major leap in spread reporting. It set the standard format and field names for the spread tables: ACTIVITYSPREAD and RESOURCEASSIGNMENTSPREAD. Additionally, this established that we would store spreads at a daily granularity. This means, for any activity/assignment, there is one row in the corresponding table for each day in the duration. These spread tables are sparse. There are no rows for days without data such as weekends if no work is scheduled.
Data size becomes a concern with spreads. Now the number of spread rows is a function of both the number activity/assignment rows and their durations. To control size, spread data is always confined in a “window” of time. That is a minimum and maximum date for spread details (ex. 01-Jan-2010 to 31-Dec-2015). Spread buckets only exist between those dates and data before or after the dates are bucketed as an aggregate on those dates. Originally this was a fixed window. Today this is a rolling window with a fixed begin date and rolling future date; both of these configurable.
Since P6 v8.1, the spread data is also in OLTP (PM Schema) and available for operational reporting in BI Publisher. Spreads are calculated as part of normal Publish Project service. Now you have access spread data for reporting in real-time. This data is also serves as the foundation for all the P6 Analytics fact metrics.
And that brings me to thinking about the future of spreads in the operational system. I heard a lot of comments about “shift level” granularity and "hourly spreads"; something we don't do today. But I would ask you first to take a look at the existing spread data. You may find it covers many of your existing use cases.