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

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!

How can ZAP Data Hub be configured to account for historical reporting in this migrating salesperson scenario?

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…

Configuring Power BI for accurate historical reporting

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 prior existing key into Sales Person (Sales Person Id)
• Filtering to the active record as at CreatedTime (based on the new Sales Person RevisionStart and RevisionEnd columns).

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:

• Backups: It’s very important to back up the history table (not necessarily the whole warehouse). The history table name can be found in the properties pane of the History Step. If you’re operating in ZAP Cloud or using an Azure Database, warehouse backups are taken care of.
• Migrations: If you’re migrating environments, you will need to migrate your history step data. From the History Step properties pane, you can “take ownership” of a migrated history table.
• Sampling: History is sampled every time the warehouse is processed. It’s possible to process only history-relevant pipelines, but be sure to process these pipelines at least twice as frequently as the changes you want to track. For example, for hourly changes process the pipeline(s) every 30 minutes.
• Key changes: Tracking history involves comparing data per key. So, keys cannot change. If you need to change the key for your History Step, you will need to backup, delete, and recreate the history table. You would then need to manually manipulate the data for the key adjustment.

(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")

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