You build brilliant reports with the best figures, revenue, productivity and KPIs. Over time more and more reports appear within the organization. Until someone suddenly notices that the values of the KPIs are not the same across the different reports. Oops! In this article I discuss a way to deal with this problem. It is a best practice that grew out of a lot of experience with definitions and KPIs.
Many people program their formulas inside their reports. A big downside of this, apart from performance, is that the formulas are not stored in one central place. Say the definition of a formula changes over time (and this happens a lot). Then you have to go through all your reports to change the formulas! A hellish job. One way to prevent this is to centralize the formulas. I explain how here.
I have a data warehouse with history (a kind of Data Vault) and behind it a data mart/star schema that holds all the business logic. The latter is where you find the various facts and dimensions. What I want is one central place for my KPI definitions. I like to work with a SQL function in which I program all KPI formulas. Each KPI gets its own number and a definition. I can then call the function with a start date, an end date and a KPI ID. The function then returns the result I want. The condition is that the grain of the KPIs is the same.
As a diagram, this approach looks like this:
On the left you find the data warehouse and on the right the data mart. The data mart holds several facts. The function is stored in the data mart and calculates its results on the facts. So all facts and dimensions must be filled before the function can work. Behind the function you find the FactKPI. This is where we store the result of the function.
The idea is that in the ETL you first update the DWH, then the data mart, and as the last step of the data mart update the KPI table.
Useful? Definitely. All formulas are in the function. When a KPI formula changes, you change the function. That is much less work than going through all the reports. All reports use the KPI table, and no longer the “normal” fact tables (where possible).
To fill the KPI table you call the function several times, once per KPI, and merge the results with a UNION.
In my case the grain of the KPI table is: DatumID, MedewerkerID, KostenPlaatsID. After that come lots of columns with the calculated KPIs.
I have to say, this works very well in practice. It saves a lot of hassle and time. Make sure the function you use has enough comments, so the definition of each KPI is clear.
If you have a good method to deal with this too, share your experience!
Frequently asked questions
How do I centralize KPI formulas in a data warehouse?
Program all KPI formulas in one SQL function in the data mart. Each KPI gets its own number and definition. The function takes a start date, an end date and a KPI ID and returns the result.
Why do KPI values differ between reports?
Formulas often live in each report separately. When a definition changes, you have to change every report. When the formulas are in one place, all reports apply the same definition.
What is a FactKPI table?
It is the table in the data mart that holds the result of the KPI function. Reports read from this table instead of from the normal fact tables, where possible.
In which order do I fill the KPI table in the ETL?
First you update the data warehouse, then the data mart. You fill the KPI table as the last step of the data mart. All facts and dimensions must be filled by then, or the function does not work.
What condition applies to a central KPI function?
The grain of the KPIs must be the same. In the example the grain of the KPI table is DatumID, MedewerkerID and KostenPlaatsID. You call the function per KPI and combine the results with a UNION.
3 comments
Ik zou graag de code van je sql functie willen zien waarin alle de Kpi's formulas staat.
Wat je moet doen is een functie maken die mbv een startdate en enddate en kpiid een resultaat teruggeeft.
Als je vervolgens een KPI tabel wilt vullen kun je meerdere keren een SELECT uitvoeren op deze functie waarbij je het resultaat aan elkaar koppelt met een UNION. Het KpiID wat je meegeeft is dan elke keer anders.
Bijv.
SELECT DatumID, 1 AS KpiID, SUM(KpiWaarde) AS KpiWaarde
FROM dbo.KpiFunctie (20120101, 20121231, 1)
UNION ALL
SELECT DatumID, 200 AS KpiID, SUM(KpiWaarde) AS KpiWaarde
FROM dbo.KpiFunctie (20120101, 20121231, 200)
De inhoud van de functie kan er zo uitzien (verkorte weergave en 1 stukje ervan);
IF @KpiID = 10820
INSERT @MyResult
SELECT KostenPlaatsID, WerknemerID, CONVERT(VARCHAR,CASE WHEN DD.DagNummerVanWeek = 6 THEN DD.DatumWaarde - 1
WHEN DD.DagNummerVanWeek = 7 THEN DD.DatumWaarde - 2
ELSE DD.DatumWaarde END,112) AS DatumID,
10820 AS KpiID, SUM(AbsentieUren) AS KpiValue
FROM ps.FactAbsentie AS FA
INNER JOIN dbo.DimDatum AS DD ON FA.DatumID = DD.DatumId
INNER JOIN ps.DimAbsentieReden AS AR ON FA.AbsentieRedenID = AR.AbsentieRedenID
INNER JOIN ps.DimAbsentieStatus AS DAS ON FA.AbsentieStatusID = DAS.AbsentieStatusID
WHERE AR.AbsentieRedenCode = '25'
AND DAS.AbsentieStatusCode = '1'
AND FA.DatumID BETWEEN @StartDatumID AND @EindDatumID
GROUP BY KostenPlaatsID, WerknemerID, DD.DagNummerVanWeek, DD.DatumWaarde
Erg bedankt! En ik ga het properen.
Related blogs
Dutch public holidays in SQL Server
Calculate the nearest working day
After update trigger