Business Rules in the Data Warehouse

As the name says, Business Rules are rules that apply to a company. Anyone with common sense can come up with Business Rules. In Data Warehousing you also deal with Business Rules and with how you handle them.

Do you throw away records that do not meet the Business Rules, put them aside in a reject table, or just load them? These are choices you have to make when you develop a Data Warehouse.

Examples of Business Rules
- The delivery date of an order may not be before the order date;
- An order must be shipped within 3 days;
- A sofi number (Dutch social security number) must have 9 digits;
- The contract hours of an employee may not be more than 40 hours;
- ETC.

Mapping the Business Rules
As you can imagine, the number of Business Rules can be huge. It is important to map out which Business Rules really matter. There is no point, for example, in measuring how many people named Piet are female. What you do want to know is how many employees in the system have a contract of more than 40 hours. Once you have made a list of Business Rules and the criteria the Business Rules must meet, the question is: what do you want to do with it? One goal can be to map how many records stored in the Data Warehouse do not meet the Business Rules, to judge the quality of the data. Another goal can be to improve the quality of the data.

Loading the Data Warehouse
Every day, new data from the source systems is added to the data in the Data Warehouse. Every day staff will join (in large organisations) and leave. As soon as the load process (ETL) is done, we want to know how many records in the Data Warehouse do not meet the Business Rules. We then want to store this for every day. Because we store this every day, we can create a trend line. On 1-1-2008 the number of employees with a 40+ contract was 1168, for example, and on 1 July 2008 it was only 500. Because we store this per day, we can see whether the quality of the data has improved over time.

Business Rule: An employee may not have more than a 40-hour contract

Date Count
20080101 1168
20080102 1160
…
20080701 500
…

This article describes a method to build a Business Rule Validator, so you can manage the data in the Data Warehouse with Business Rules. It describes how to count the numbers and how to store them. After that you can download two reports.

Step 1 - Create the tables
The first step is to create a table in which we put the Business Rules.

1
CREATE TABLE [dbo].[BusinessRuleViews](
2
[BusinessRuleViewID] [int] IDENTITY(1,1) NOT NULL,
3
[BusinessRuleViewName] [varchar](256) NULL,
4
[BusinessRuleViewDesc] [varchar](1000) NULL,
5
[BusinessRuleViewTableName] [varchar](1000) NULL
6
) ON [PRIMARY]

The second table we need is a table in which we store the number of records that do not meet the Business Rule.

1
CREATE TABLE [dbo].[BusinessRuleViewsCount](
2
[BusinessRuleViewCountID] [int] IDENTITY(1,1) NOT NULL,
3
[BusinessRuleViewID] [int] NULL,
4
[BusinessRuleViewCountRunno] [int] NULL,
5
[BusinessRuleViewCountDate] [datetime] NULL,
6
[BusinessRuleViewCountCount] [int] NULL,
7
[BusinessRuleViewCountTotTableCount] [int] NULL,
8
[BusinessRuleViewCountDesc] [varchar](100) NULL
9
) ON [PRIMARY]

Step 2 - Create a View per Business Rule
For every Business Rule we want to measure, we make a Query that counts the rows that do not meet the Business Rule. We store this Query in a View.

1
CREATE VIEW [dbo].[vwBR_DimMedewerker_MeerDan40UrigContract]
2
AS
3

4
SELECT SofiNummer
5
FROM DimWerknemer
6
WHERE ContractUren > 40

Keep the naming consistent, or all Views will end up mixed together. Best Practice is to start with “vwBR” (View Business Rule), followed by the name of the table you want to measure and then the name of the Business Rule. This way you create a View for every Business Rule that gets the rows that do not meet the Business Rule.

Step 3 - Fill the table
Next we fill the table we created in step 1. We have to store which Views we made, so we can read them later.

This way you fill in one record for every Business Rule.

Step 4 - Load the counts per day
The next step is to run a Stored Procedure that reads all the counts in the Views and then writes them to the table we made in step 1. You can create the Stored Procedure below. Make sure you change the database names to the database in which you created the tables / views!

1
CREATE PROCEDURE [dbo].[SP_Execute_BusinessRules_Views]
2
AS
3
BEGIN
4
DECLARE @vViewName        varchar(100)
5
DECLARE @SQLString        nvarchar(500)
6
DECLARE @ParmDefinition nvarchar(500)
7
DECLARE @iCount_rec        varchar(30)
8
DECLARE @iRunNo            Int
9
DECLARE @vcTableName    nVarchar(100)
10
DECLARE @iCount_TotRec    int
11
DECLARE @icount_TotRecOUT    int
12
DECLARE @iTotRowCount    int
13

14
SET @iRunNo = (SELECT Max(BusinessRuleViewCountRunno)
15
FROM DATABASENAAM..BusinessRuleViewsCount)
16

17
IF @iRunNo IS NULL
18
SET @iRunno = 1
19
ELSE
20
SET @iRunno = @iRunno + 1
21

22
DECLARE view_cursor CURSOR FOR
23
SELECT name
24
FROM DATABASENAAM.sys.views
25
WHERE name like 'vwBR!_%' ESCAPE '!'
26

27
OPEN view_cursor
28

29
FETCH NEXT FROM view_cursor
30
INTO @vViewName
31

32
WHILE @@FETCH_STATUS = 0
33
BEGIN
34
SET @SQLString = N'SELECT @icount_recOUT = count(*)
35
FROM DATABASENAAM..'+ @vViewName;
36
SET @ParmDefinition = N'@icount_recOUT int OUTPUT';
37
--print @SQLString
38
EXECUTE sp_executesql @SQLString, @ParmDefinition,
39
@icount_recOUT= @iCount_rec OUTPUT;
40

41
SET @vcTableName = (SELECT BusinessRuleViewTableName
42
FROM DATABASENAAM..BusinessRuleViews
43
WHERE BusinessRuleViewName = @vViewName)
44

45
SET @SQLString = N'SELECT @icount_TotRecOUT = count(*)
46
FROM DATABASENAAM..'+ @vcTableName ;
47
SET @ParmDefinition = N'@icount_TotRecOUT int OUTPUT';
48
--print @SQLString
49
EXECUTE sp_executesql @SQLString, @ParmDefinition,
50
@icount_TotRecOUT = @iCount_TotRec OUTPUT;
51

52
--print @vViewName
53
Set @iTotRowCount = (SELECT count(*) FROM
54
DATABASENAAM..BusinessRuleViews
55
WHERE BusinessRuleViewName = @vViewName)
56

57
INSERT INTO DATABASENAAM..BusinessRuleViewsCount(BusinessRuleViewID,
58
BusinessRuleViewCountRunno, BusinessRuleViewCountDate,
59

60
BusinessRuleViewCountCount, BusinessRuleViewCountTotTableCount)
61
SELECT  BusinessRuleViewID , @iRunNo, getdate(),
62
@iCount_rec, @iCount_TotRec
63
FROM BusinessRuleViews
64
WHERE BusinessRuleViewName = @vViewName
65

66
FETCH NEXT FROM view_cursor
67
INTO @vViewName
68
END
69

70
CLOSE view_cursor
71
DEALLOCATE view_cursor
72
END

Step 5 - Schedule the Stored Procedure
Finally you schedule the call to the Stored Procedure. The best moment to call it is as soon as the ETL is done and all records are loaded.

Step 6 - Graphical display
Finally I have two reports that are built on the table. They show the counts per Business Rule, and you can drill down to the details.

Download the reports here

If you have tips or comments, please leave a message!