ZAP Data Hub’s pre-built reporting solutions are the fastest, most cost-effective way to get accurate, trusted reporting from the most widely used ERP, CRM, and finance systems.
Get in touch
April 24, 2019
By Chris Reeves
No comments yet
Data Management,ZAP Data Hub
Part Two – “When things are a-changin’ (slowly changing dimensions)” – of our series from ZAP’s CTO Chris Reeves…
Let’s start 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.
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:
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).
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:
We’re done! If you’re a technically-minded-kind-of-person with knowledge of SQL, I’ll expand on exactly how this was all done in the next post. If not, please join me again in the future for our Sales Opportunities Growth walk-through.
Get in touch for a personalised demo of either ZAP Data Hub or Sage Data Hub
Comments are closed.
All Rights Reserved