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

Posted by Chris Reeves on 15 January 2021

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.

Historical reporting explained

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.

(We recommend reading "The Indispensable Guide to Chart Design and Data Visualization")

(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 such as Salesforce or Microsoft Dynamics failed to capture.

(You might also like "5 Reasons Why a Data Warehouse Improves Business Reporting")

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 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.

Examples of historical reporting

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.

(Want to continue reading? Check out "Business data reporting against historical sales data and historical ERP data – Part 2")

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