Let’s say you are a Business Analyst or BI Analyst.
You are responsible for providing the numbers to your managers and explaining the inaccuracies and discrepancies.
You started working on the presentation for the weekly management meeting and the numbers in the management dashboard do not seems reasonable! Just yesterday you entered the dashboard and the total revenue for Finland was at least 100K$ higher…
So, where did the money disappear??
No need to panic, the meeting is in the afternoon, you just need to locate the root cause and alert the BI team to correct the issue ASAP so data will be accurate again.
Let’s start top down and drill to root cause:
1. Alert non-valid numbers according to business rules:
First, you should create a test that will alert this issue automatically, because you know that this regression in numbers is not reasonable, and if you wouldn’t have prepared the presentation, the issue would have come up later.
So, In order to validate the company’s KPIs you should use the KPI Validation test.
We will enter a threshold for valid values, values that are in a warning range and out of range values which we want to be alerted on, we will look at a period that we can evaluate – closed and billed month (previous month). The period is set according to billing date period and not according to the invoice date. This is a user defined manual field in the billing system. We use a parameter to filter population on the billing previous month. The threshold can be determined for the column or per country, since we do not expect the same revenues from all countries, we’ll define them at the row level.
After setting up the KPI validation, you can schedule the test to run daily and alert you when the KPIs are not as expected, problematic countries will come up immediately.
Define Aggregation of the Revenue KPI according to the country field
Define threshold for failure and warning for each county
Here, we can see the Finland issue as expected, it should be stable around 28,000,000 for a closed billed month (previous month), and so we have an issue here and will drill down to find it. We also saw that Greece is a bit under the threshold and we received a warning. Economic situation in Greece is problematic and the numbers are lower recently, you are aware to this business issue, and promotion budgets have increased, so no need to drill down here at the moment.
2. Check for discrepancies in Data Gates:
The dashboard is based on the Revenue table in the DWH, the source of the table is the invoice table in the billing system. We will want to check consistency for the billing-DWH data gate.
We’ll check if the billing invoice amount is equal to the DWH revenues table and also see that all invoices were inserted to the DWH.
For that we’ll add a Check Sum test that compares both measures and count rows for each country and for current month invoices.
Define aggregation for selected measures and the count rows for each country to check discrepancies
Execute test to alert on mismatched or missing records between the billing system and the DWH
The Check Sum test shows us that we have one 140K$ invoice which was entered to the billing system without a country. The null value was transferred into a default value in the DWH (N/A).
According to the invoiced customer we can track the country and complete the missing value in both systems.
The only issue is that one day earlier you are sure you saw the correct value and now it is missing!
This means that the value was correct and was updated. Now, we just need to validate it and make sure that this field would not be updateable in source system.
3.Check for discrepancies in Data Gates – Track mismatched records:
We have not kept a snapshot of yesterday’s data (this can be done for now on by keeping the results at a log table).
If we do not have a snapshot we can validate it by the billing system audit if exists or by checking the system update date.
We can set up a Compare Table test in order to present us the mismatched records automatically after scheduling the tests.
Define datasets (same as in the Check Sum)
Execute and Track the bad records
Make sure this kind of incidents will not be reproducible!
4.Create an Execution Flow for Revenues
We will want to automate this scenario in Quality Gates. In order to that we will create an Execution Flow which will run 3 tests.
1 – KPI Validation by Country
2 – Check Sum by Country will run after the KPI Validation completion (successfully or with failure) because there can still be discrepancies that will not raise according to the threshold business rule
3 – Detailed Compare Table is not necessary in this case unless the Check Sum has failed, so we will set to run it on failure of the Check Sum.
This is it!
We have created 4 quick steps to automate this kind of scenario which will alert us on different discrepancies or incomplete data in our Revenue model.