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 11, 2019
By Chris Reeves
No comments yet
CRM,Data Management,ERP,Power BI,Reporting,Tableau,ZAP Data Hub
Here’s ZAP’s CTO, Chris Reeves, with the first in a regular series of blogs digging into the technical side of business reporting from one or more data sources…
Welcome! This is my first post here. I look forward to discussing new and existing Data Hub capabilities and warehousing best practices. This post is the first in a series discussing historical reporting. It’s a subject that I find more exciting than I should care to admit. If I can’t pass on a little of my enthusiasm, I’ll at least be content to have passed on some knowledge.
Let’s kick this off with a definition (just one). Historical reporting is reporting over time with the goal of identifying trends: Are support tickets up? Is receivables-aging improving? Are sales opportunities growing? And the challenge is that business applications rarely consider historical reporting requirements.
The most common form of historical reporting is historical charting. The value of charted history is that the eye can pick trends with ease. Trends can drive actions and effective reporting should be actionable or really what is the point of it? To accurately identify business-impacting trends, it is necessary to report on the full history, not a limited subset.
(Action: cut down on donut consumption.)
This is where data warehouses can help. Data warehouses provide many benefits, not least: a sanctioned source of truth, improved data governance, and optimized reporting performance. Data warehouses can also capture changes over time. With the appropriate software, business applications can be queried periodically and the changes recorded. This history-of-changes then allows for all historical reporting requirements to be satisfied. The following diagram shows a history-of-changes in the warehouse that the CRM application failed to capture.
In upcoming blogs, I’ll provide some example requirements and address how to solve each with ZAP Data Hub‘s History Step. Charts will be produced using Tableau, Microsoft’s Power BI or ZAP Data Hub itself. A follow-up post for each example will provide a full technical exposé. If you’re not using ZAP but you’re technically-inclined, these follow-up posts are where you can learn how to roll-your-own historical reporting implementation.
For the remainder of this post, we’ll take a quick look at two real-world examples where the business application fails to capture historical information. The following chart shows sales-per-region over three quarters. It’s not obvious at first glance that the results presented in this chart have been distorted by a lack of consideration for historical reporting.
In this scenario, the salesperson’s associated region is stored against the salesperson (as opposed to the sale record). The salesperson has migrated from East to West to be closer to (or further from) their mother-in-law. Many business applications will not track the date that such a move takes place, nor the previous value. To demonstrate just how wrong a report can be when the history-of-changes is ignored, compare the previous chart to the following (based off the same data but now accounting for the flighty salesperson).
Clearly, if you require a chart as above and you require it to be accurate, you will need to capture the history of region changes. As a second example, take the following chart that shows a sales-opportunities growth chart. To be accurate, this chart should reflect changes to individual opportunities, where the prospect has been up-sold or offered a discount.
This example is interesting because it is the amount that we’re measuring that changes over time versus the attribute (region), as in the previous example. Handling this requires a much more sophisticated approach or a tool capable of taking care of it for you.
What’s also interesting about this example is, if we’d used Created Date for the horizontal axis, in all likelihood the business application would have provided all that was required. However, this wouldn’t really be reporting the same thing, as all up-selling and discounting would be omitted. This does identify the challenge in determining whether or not business application data will suffice. There is no litmus test for determining if a warehouse history-of-changes will be required. However, a defensive approach is to always start with a warehouse. Given the other benefits to data warehousing (listed above), and given the power of modern warehouse automation tools, it can be hard go wrong with this approach.
Hopefully these two examples have given you a bit of a feel for historical reporting. Before signing off, I’ll just quickly note that the ZAP Data Hub features and techniques demonstrated in upcoming posts are report-engine agnostic, and work equally well for reporting directly off a warehouse (SQL & Direct Query), or using an in-memory model of (e.g. Power BI, Tableau, or Data Hub etc.). I hope you’ll join me for the next post!
Get in touch for a personalised demo of ZAP Data Hub
Comments are closed.
All Rights Reserved