Why Store Period Actuals in a Data Warehouse?

After I wrote my last blog, a co-worker asked me why I would want to store period actuals in the data warehouse when it seems as though I can get the same value from the historical data in the data warehouse.

Both Oracle’s Primavera Analytics and our EP-datawarehouse support capturing some kind of historical data – data that shows what the information in P6 looked like at some point in the past. It’s fairly easy to see how this historical data can be exploited to produce information that is in concept very similar to what you get from the financial period actuals.

If I’m capturing the information in P6 into a data warehouse on the first of each month, I will have a record in the data warehouse that shows what the data looked like in P6 on that date. Let’s take the example of the resource assignment I talked about in my last blog, where I incur a total of $30,000 of cost -- $5,000 in January, $15,000 in February, and $10,000 in March. My data warehouse data will look like this:

Remember, this is a plot of the Actual Cost field on the resource assignment. This is not looking at financial period actuals or spread data in P6. It is only showing how the Actual Cost field is changing over time.

As it turns out, this is naturally a cumulative curve. But it’s easy to take this cumulative data and make it periodic, by subtracting the previous snapshot’s value from the current value to see what the change was in that time period. For example, on March 1 (the “end” of February) the value was $20,000. On February 1 (the beginning of February) it was only $5,000. So the change in February was $15,000. Performing this calculation for all three months gives this result:

This matches the period actuals data from my last blog post. So why bother capturing the financial period values at all?

We added financial period actuals capturing to our EP-datawarehouse while we were working with a manufacturing company. They had a month end procedure that took a few days to complete. In this scenario, it would not have been appropriate to snapshot on the first of each month because the data in P6 would not yet be complete.

Our EP-datawarehouse could accommodate that, because we provide for a lot of control over how and when snapshots are captured. Users can snapshot on February 7 but indicate that it is the snapshot for January. Primavera Analytics doesn't provide this kind of flexibility.

But the other concern on that project was that the financial period data was the point of truth for the information the client wanted to report on. We had written an integration that ran each night to take relevant information from their accounting system and put it into the appropriate financial periods in P6. We wanted to show the client the same information in the reports that they were looking at in P6.

Finally, capturing the financial period actuals with each snapshot protects you against changes that might be made over time. The reporting solution we delivered for the customer enabled them to re-run a report for a previous period and we would know that the report would look the same every time. If the period actuals changed for a previous period, it would not interfere with our reports.