Data Management, BI Analytics, ERP Reporting & Data Warehousing Blog | Talk Data to Me by ZAP

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

Chris Reeves

Posted by Chris Reeves on 22 January 2021

Business data reporting against historical sales data and historical ERP data

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

Historical Data Bar Chart

And now let’s look at the source data:

Historical Data Table

Historical Sales 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:

Historical Sales Data

Naïvely reporting on this data would incorrectly credit all of Jenny’s sales to the West Coast:

Historical Data Bar Chart

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:

Sales Reporting ZAP Data Hub

Set the Region column to Tracked:

Sales Reporting ZAP Data Hub

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:

Power BI Historical Reporting

(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:

Sales Quarter Historical Reporting

We now have what we set out to achieve:

Sales Reporting Historical Data

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.

Sales Reporting Power BI

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

Get actionable historical reporting from your ERP, CRM and all other data sources

About the Author:

Chris Reeves
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 |

Any feedback or questions? Leave a comment below...

Subscribe to Talk Data To Me

Microsoft Dynamics Users

Are you looking to upgrade or extend the life of your current platform? ZAP Data Hub buys you time to transition.


Latest Posts: