Business Rules in het Datawarehouse

Zoals de naam al zegt zijn Business Rules regels die voor een bedrijf gelden. Iedereen met een gezond verstand kan Business Rules bedenken. Ook bij Datawarehousing krijg je te maken met Business Rules en de afhandeling van deze Business Rules.

Worden records die niet aan Business Rules voldoen weggegooid, apart gezet in een uitvaltabel of gewoon geladen? Dit zijn keuzes die je moet maken bij het ontwikkelen van een Datawarehouse.

Voorbeelden van Business Rules
– Een leveringsdatum van een order mag niet voor de besteldatum liggen;
– Een order moet binnen 3 dagen verzonden zijn;
– Een sofinummer moet 9 cijfers zijn;
– Het aantal contracturen van een medewerker mag niet meer zijn dan 40 uur;
– ETC.

Business Rules in kaart brengen
Zoals je begrijpt kunnen het aantal Business Rules gigantisch zijn. Het is belangrijk om in kaart te brengen welke Business Rules nu echt belangrijk zijn. Zo zal het niet interessant zijn om te meten hoeveel mensen die Piet heten van het vrouw zijn. Wat je wel graag wilt weten is hoeveel medewerkers er in het systeem staan die een dan 40-urig contract hebben. Zodra je een lijstje hebt gemaakt van Business Rules en Criteria waaraan de Business Rules moeten voldoen is de vraag, wat wil je ermee? Een doel kan zijn om in kaart te brengen hoeveel records er in het Datwarehouse liggen opgeslagen die niet aan de Business Rules voldoen om de kwaliteit van de data te beoordelen. Een ander doel kan zijn om de kwaliteit van de data te willen verbeteren.

Datawarehouse Laden
Iedere dag wordt de data in het Datawarehouse aangevuld met nieuwe data uit de bronsystemen. Iedere dag zal er personeel bijkomen (bij grote organisaties) en weer vertrekken. Zodra het laadproces is voltooid (ETL) willen we graag weten hoeveel records er in het Datawarehouse zijn die niet aan de Business Rules voldoen. Dit willen we vervolgens voor iedere dag gaan opslaan. Door dit iedere dag op te slaan kunnen we een trendlijn creeren. Op 1-1-2008 waren het aantal medewerkers met meer 40+ contract bijvoorbeeld 1168, op 1 juli 2008 waren dit er nog maar 500. Doordat we dit per dag opslaan kunnen we dus zien of de kwaliteit van de data door de tijd heen verbeterd is.

Business Rule: Een medewerker mag niet meer dan een 40-urig contract hebben

Datum        Aantal
20080101    1168
20080102    1160
…
20080701     500
…

Dit artikel beschrijft een methode hoe je een Business Rule Validator kunt maken zodat je de data in het Datawarehouse kunt managen met behulp van Business Rules. Er wordt beschreven hoe je de aantallen kunt tellen en hoe je dit op moet slaan. Vervolgens kun je twee rapportages downloaden.

Stap 1 – Tabellen genereren
De eerste stap is een tabel genereren waarin we de Business Rules plaatsen.

CREATE TABLE [dbo].[BusinessRuleViews](
[BusinessRuleViewID] [INT] IDENTITY(1,1) NOT NULL,
[BusinessRuleViewName] [VARCHAR](256) NULL,
[BusinessRuleViewDesc] [VARCHAR](1000) NULL,
[BusinessRuleViewTableName] [VARCHAR](1000) NULL
) ON [PRIMARY]

De tweede tabel die we nodig hebben is een tabel waarin we het aantal records dat niet aan de Business Rule voldoet gaan opslaan.

CREATE TABLE [dbo].[BusinessRuleViewsCount](
[BusinessRuleViewCountID] [INT] IDENTITY(1,1) NOT NULL,
[BusinessRuleViewID] [INT] NULL,
[BusinessRuleViewCountRunno] [INT] NULL,
[BusinessRuleViewCountDate] [datetime] NULL,
[BusinessRuleViewCountCount] [INT] NULL,
[BusinessRuleViewCountTotTableCount] [INT] NULL,
[BusinessRuleViewCountDesc] [VARCHAR](100) NULL
) ON [PRIMARY]

Stap 2 – Creeer een View per Business Rule
Per Business Rule die we willen meten zullen we een Query maken die het aantal rijen telt die niet aan de Business Rule voldoen. Deze Query slaan we op in een View.

CREATE VIEW [dbo].[vwBR_DimMedewerker_MeerDan40UrigContract]
AS
 
SELECT SofiNummer
FROM DimWerknemer
WHERE ContractUren > 40

Let op dat je de naamgeving constitent houdt, anders zullen alle Views door elkaar komen te staan. Best Practice is om te beginnen met “vwBR” (View Business Rule), gevolgd door de tabelnaam die je wilt meten en dan de naam van de Business Rule. Op deze manier ga je voor iedere Business Rule een View creeeren die de rijen ophaalt die niet aan de Business Rule voldoen.

Stap 3 – Tabel vullen
Vervolgens gaan we de in stap 1 gecreeerde tabel vullen. We moeten opslaan welke Views we hebben gemaakt zodat we deze straks kunnen uitlezen.

Zo vul je voor iedere Business Rule een record in.

Stap 4 – Aantallen per dag laden
De volgende stap is het uitvoeren van een Stored Procedure die alle aantallen in de Views gaat uitlezen en vervolgens gaat wegschrijven in de tabel die we hebben gemaakt in stap 1. Je kunt onderstaande Stored Procedure aanmaken. Let even op dat je de databasenamen aanpast naar de database waar je de tabellen / views in hebt aangemaakt!

CREATE PROCEDURE [dbo].[SP_Execute_BusinessRules_Views]
AS
BEGIN
DECLARE @vViewName        VARCHAR(100)
DECLARE @SQLString        nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @iCount_rec        VARCHAR(30)
DECLARE @iRunNo            INT
DECLARE @vcTableName    nVarchar(100)
DECLARE @iCount_TotRec    INT
DECLARE @icount_TotRecOUT    INT
DECLARE @iTotRowCount    INT
 
SET @iRunNo = (SELECT MAX(BusinessRuleViewCountRunno) 
FROM DATABASENAAM..BusinessRuleViewsCount)
 
IF @iRunNo IS NULL 
SET @iRunno = 1
ELSE
SET @iRunno = @iRunno + 1
 
DECLARE view_cursor CURSOR FOR 
SELECT name
FROM DATABASENAAM.sys.views 
WHERE name LIKE 'vwBR!_%' ESCAPE '!'
 
OPEN view_cursor
 
FETCH NEXT FROM view_cursor
INTO @vViewName
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = N'SELECT @icount_recOUT = count(*) 
FROM DATABASENAAM..'+ @vViewName;
SET @ParmDefinition = N'@icount_recOUT int OUTPUT';
--print @SQLString
EXECUTE sp_executesql @SQLString, @ParmDefinition, 
@icount_recOUT= @iCount_rec OUTPUT;
 
SET @vcTableName = (SELECT BusinessRuleViewTableName 
FROM DATABASENAAM..BusinessRuleViews 
WHERE BusinessRuleViewName = @vViewName)
 
SET @SQLString = N'SELECT @icount_TotRecOUT = count(*) 
FROM DATABASENAAM..'+ @vcTableName ;
SET @ParmDefinition = N'@icount_TotRecOUT int OUTPUT';
--print @SQLString
EXECUTE sp_executesql @SQLString, @ParmDefinition, 
@icount_TotRecOUT = @iCount_TotRec OUTPUT;
 
--print @vViewName
SET @iTotRowCount = (SELECT COUNT(*) FROM 
DATABASENAAM..BusinessRuleViews 
WHERE BusinessRuleViewName = @vViewName)
 
INSERT INTO DATABASENAAM..BusinessRuleViewsCount(BusinessRuleViewID, 
BusinessRuleViewCountRunno, BusinessRuleViewCountDate,
 
BusinessRuleViewCountCount, BusinessRuleViewCountTotTableCount)
SELECT  BusinessRuleViewID , @iRunNo, getdate(), 
@iCount_rec, @iCount_TotRec 
FROM BusinessRuleViews 
WHERE BusinessRuleViewName = @vViewName
 
FETCH NEXT FROM view_cursor    
INTO @vViewName
END
 
CLOSE view_cursor
DEALLOCATE view_cursor
END

Stap 5 – Schedulen van de Stored Procedure
Tot slot zul je de aanroep van de Stored Procedure gaan schedulen. Je kunt hem het beste aanroepen zodra de ETL-klaar is en alle records geladen zijn.

Stap 6 – Grafische weergave
Tot slot heb ik nog twee rapportages die op de tabel zijn gebouwd. Hierin worden de aantallen per Business Rule getoond en kun je nog details opvragen.

Download de rapportages hier

Heb je tips of opmerkingen, laat dan even een berichtje achter!