Business data reporting against historical sales data and historical ERP data – Part 4

Posted by Chris Reeves on 05 February 2021

This post returns to the sales-opportunity growth chart from the introductory post. To be accurate, this chart should reflect changes to individual opportunities, where the prospect has been up-sold or offered a discount. In this scenario, it is the amount that we’re measuring that changes over time (versus the Region attribute in the previous post).

(Read the previous post in this blog series here: "Business data reporting against historical sales data and historical ERP data – Part 3")

The following chart shows our end goal:

 

And our source data looks like this:

From the above table, we can see opportunities created and closed over the chart period. Changes to the value of these opportunities will affect the totals per month. However, what the source data omits is that opportunity 2 was adjusted up from 1M to 1.5M on the 13th of November.

This is taken from a real-world example. It is a good starting assumption that your source system will also omit such changes, because tracking history would add a heavy burden of complexity on the source system. Let’s look at how ZAP Data Hub would be configured for this scenario.

Open the Opportunity pipeline and add a new History Step:

 

Our chart requires an up-to-date Amount and State (new, open, closed) for each opportunity and for any given date. For this we set the Amount and State columns to Tracked:

 

And because in this scenario the amount that we are measuring is changing over time, we need to select between the Snapshotting and Compensating history (transformation) types:

 

The Snapshotting history type transforms each history record into a set of snapshotting records – one for each day the record is in effect. For example, if there are no changes in the history, the single original-state record is duplicated for all dates from the record’s creation to the current (or last possible) reporting date.

The Compensating history type transforms the history-of-changes into a set of transactions, similar to ledger or inventory transactions. A value for a given date can be determined by totaling all transactions to date. In the next post, I’ll address a few of the considerations to choosing the history type.

For this example and in most scenarios, Compensating should be the preferred option. Select it now and we’re done. Your modeling is complete! ZAP Data Hub will do the heavy lifting of updating the underlying warehouse structure and capturing changes as they occur. As before, keep in mind the model needs to be configured as above before the event in order to track it. So, let’s assume this was the case and that we’re now reporting at the end of November.

For this example, we’ll use ZAP Data Hub reporting. For Compensating history type values, Data Hub makes reporting just a little bit simpler, by pre-packaging the calculation for totaling all transactions to date. This can be seen in the following screenshot, where Amount is preceded by the ∑ icon. Having said this, the calculation itself is trivial in Tableau, Power BI and even SQL.

 

We now take this calculation, filter for Open records and use Data Hub’s Months to Current function to provide our bars:

And that’s it for reporting too! This should appear very simple, but only because of all the work Data Hub is doing in the background. It’s also worth noting that because Data Hub uses a common form to store all historical information, it is possible to change between Snapshotting and Compensating as your requirements evolve, provided the underlying history-of-changes is maintained. Keeping in mind, if you start tracking a new column, you sensibly only have data for that column from the date you started tracking it.

About the Author:

Chris Reeves Chris joined ZAP in 2006 as a developer and has gone on to become our Chief Technology Officer. He's based out of our Brisbane, Australia office and blogs on technical aspects of data warehousing. View my social profiles: LinkedIn | Twitter