Posted by Chris Reeves on 22 January 2021
Let’s start off our historical reporting strategy by laying out the end goal. This will be a chart showing the sales by region over time (calendar quarters), as below. Here we can see an (admittedly concocted) trend that clearly demonstrates why historical reporting works so well in chart form. The West Coast could do with some of that East Coast magic.
(Read the previous post in this blog series here: "Business data reporting against historical sales data and historical ERP data – Part 1")
And now let’s look at the source data:
Our data is limited to just two salespeople for simplicity. But now the curveball… Back in October, Jenny moved from the East Coast to the West Coast to help John lift his flagging sales. This report is being run in October – importantly, after Jenny’s move. The sales person data in the source system is now:
Naïvely reporting on this data would incorrectly credit all of Jenny’s sales to the West Coast:
From this, we can see that the source system doesn’t provide the information necessary to correctly report on this scenario. The date that Jenny’s new region took effect was not captured!
Open the Sales Person pipeline and add a new History Step:
Set the Region column to Tracked:
That’s it! ZAP Data Hub will do the heavy lifting, updating the underlying warehouse structure and capturing changes as they occur. Keep in mind the model needs to be configured as above, before the event in order to track it.
So, let’s assume this configuration was in place and that we’re now reporting in October. For this example, we’ll use Power BI. Load the following three tables into Power BI:
(We also recommend reading "Do Tableau and Power BI replace the need for a Data Warehouse?")
And it’s simply a matter of reporting on the Quarter, Sales Amount and Sales Person columns, as in the following:
We now have what we set out to achieve:
And if you’re interested in the details, we’ll now look at what magic just happened and consider some configuration options…
To start, note on the History Step above that the key of the Sales Person pipeline is now Sales Person Id and RevisionId. This allows for multiple revisions of data per sales person as they, say, change region. Updating the key to include RevisionId required updating all pipelines relating to the Sales Person pipeline. However, ZAP Data Hub took care of that for us. In the following, note the Historical section on the properties pane and the Infer Revision From field set to CreatedTime.
The behind-the-scenes magic: for every Sales Amounts record, the relevant Sales Person record is now determined both by:
The Infer Revision From drop-down allows the model designer to select a different source-system column for filtering, although in this case there is only a single DateTime source column (CreatedTime). The History Step offers similar configurability for the column on which to base RevisionStart and RevisionEnd (in this case ModifiedTime).
(You may also like "5 Reasons Why a Data Warehouse Improves Business Reporting")
At this point, we are now capturing data in our warehouse that cannot be reproduced from source data. So here are a few extra considerations that extend from this:
(Want to continue reading? Check out the next post in this blog series here: "Business data reporting against historical sales data and historical ERP data – Part 3")
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