As the name suggests, Business Rules are rules that apply to a company. Anyone with common sense could think of Business Rules. Even when you have to deal with Datawarehousing Business Rules and the handling of these Business Rules. Are records that do not comply with Business Rules discarded, put in a separate table or just starting load? These are choices you must make when developing a data warehouse.

Examples of Business Rules
- A delivery date of an order may not be the order date;
- An order must be shipped within 3 days;
- A Social Security number must be 9 figures;
- The number of contractures an employee may not exceed 40 hours;
- ETC.

Business Rules Mapping
As you can understand the number of Business Rules gigantic. It is important to identify what Business Rules are really important. So it will not be interesting to measure how many people called Peter of being a woman. What do you want to know how many employees are there in the system than a 40-hour contract. Once you've made a list of Business Rules and Characteristics of Business Rules must meet the question, what do you do with it? A goal may be to map out how many records are stored in the Datwarehouse not comply with the Business Rules for data quality assessment. Another goal may be to the quality of the data would improve.

Loading data warehouse
Every day the data in the data warehouse updated with new data from the source systems. Every day there will be personnel recovery (in large organizations) and leave again. Once the charging process is completed (ETL) we want to know how many records in the data warehouse are not complying with the Business Rules. We want to save for each day thereafter. This store every day we can create a trendline. On 01.01.2008 the number of employees had more 40 + contract in 1168 for example, on July 1, 2008 were still just 500. Because we save a day we can see if the quality of the data over time has improved.



Business Rule: An employee may not exceed a 40-hour contract

Date Number
20080101 1168
20080102 1160
...
20080701 500
...


This article describes a method how to create a Business Rule Validator can make so you can manage data in the data warehouse using Business Rules. It describes how the numbers and how you can count on to hit. Then you have two reporting here.




Step 1 - Generate Tables
The first step is to generate a table where we place the Business Rules.

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

The second table that we need is a table showing number of records that we are not going to save the Business Rule compliance.

  [ dbo ] . [ BusinessRuleViewsCount ] ( Create table [dbo]. [Business Rule Views Count] (
  BusinessRuleViewCountID ] [ INT ] IDENTITY ( 1 , 1 ) NOT NULL, [BusinessRuleViewCountID] [INT] IDENTITY (1, 1) NOT NULL,
  BusinessRuleViewID ] [ INT ] NULL, [BusinessRuleViewID] [INT] NULL,
  BusinessRuleViewCountRunno ] [ INT ] NULL, [BusinessRuleViewCountRunno] [INT] NULL,
  BusinessRuleViewCountDate ] [ DATETIME ] NULL, [Business Rule View Count Date] [DATETIME] NULL,
  BusinessRuleViewCountCount ] [ INT ] NULL, [Business Rule View Count Count] [INT] NULL,
  BusinessRuleViewCountTotTableCount ] [ INT ] NULL, [BusinessRuleViewCountTotTableCount] [INT] NULL,
  BusinessRuleViewCountDesc ] [ VARCHAR ] ( 100 ) NULL [BusinessRuleViewCountDesc] [VARCHAR] (100) NULL
 ON [ PRIMARY ] ) ON [PRIMARY] 

Step 2 - Create a View by Business Rule
Business Rule by which we want to measure, we will make a query that counts the number of rows that do not comply with the Business Rule. This query we store in a View.

  [ dbo ] . [ vwBR_DimMedewerker_MeerDan40UrigContract ] CREATE VIEW [dbo]. [VwBR_DimMedewerker_MeerDan40UrigContract]
 AS

 SofiNummer SELECT SSN
 DimWerknemer FROM DimWerknemer
 ContractUren > 40 WHERE contractures> 40 

Make sure you keep the name constitent, otherwise by all views come into conflict. Best Practice is to start with "vwBR (View Business Rule), followed by the table name you want to measure and then the name of the Business Rule. This way, you go for each Business Rule create a View that retrieves the rows that do not comply with the Business Rule.




Step 3 - Table filling
Then we created in step 1 table form. We must save what views we have made so we can read them later.

Column Name	Content
[BusinessRuleViewID]	Identity field (filled automatically)
[View Business Rule Name]	vwBR_DimMedewerker_MeerDan40UrigContract
[BusinessRuleViewDesc]	Number of employees with more than 40 hours on their contracts are
[View Table Business Rule Name]	DimWerknemer

As fill for each record in a Business Rule.




Step 4 - Number of days per load
The next step is to run a Stored Procedure that all numbers in the Views is read and then go write in the table that we created in step 1. You can create the following Stored Procedure. Just remember that you modify the database names to the database where your tables / views in've created!

  [ dbo ] . [ SP_Execute_BusinessRules_Views ] CREATE PROCEDURE [dbo]. [SP_Execute_BusinessRules_Views]
 AS
 BEGIN
  @vViewName VARCHAR ( 100 ) DECLARE @ vViewName VARCHAR (100)
  @SQLString NVARCHAR ( 500 ) DECLARE @ SQLString nvarchar (500)
  @ParmDefinition NVARCHAR ( 500 ) DECLARE @ ParmDefinition nvarchar (500)
  @iCount_rec VARCHAR ( 30 ) DECLARE @ iCount_rec VARCHAR (30)
  @iRunNo INT INT DECLARE @ iRunNo
  @vcTableName NVARCHAR ( 100 ) DECLARE @ vcTableName nvarchar (100)
  @iCount_TotRec INT INT DECLARE @ iCount_TotRec
  @icount_TotRecOUT INT INT DECLARE @ icount_TotRecOUT
  @iTotRowCount INT DECLARE @ INT iTotRowCount

  @iRunNo = ( SELECT MAX ( BusinessRuleViewCountRunno ) SET @ iRunNo = (SELECT MAX (BusinessRuleViewCountRunno) 
 DATABASENAAM.. BusinessRuleViewsCount ) DATABASE NAME .. FROM Business Rule Views Count)

  @iRunNo IS NULL IF @ iRunNo IS NULL 
  @iRunno = 1 SET @ iRunno = 1
  ELSE
  @iRunno = @iRunno + 1 SET @ @ iRunno iRunno = + 1

  view_cursor CURSOR FOR DECLARE CURSOR FOR view_cursor 
  name SELECT particular
  DATABASENAAM. sys . views FROM DATABASE NAME. Sys. Views 
  name like 'vwBR!_%' ESCAPE '!' Particularly like WHERE 'vwBR! _%' ESCAPE '! "

  view_cursor OPEN view_cursor

  view_cursor FETCH NEXT FROM view_cursor
  @vViewName INTO @ vViewName

  @@FETCH_STATUS = 0 @ @ FETCH_STATUS = 0 WHILE
  BEGIN
  @SQLString = N 'SELECT @icount_recOUT = count(*) SET @ SQLString = N 'SELECT @ icount_recOUT = count (*)  
  + @vViewName; DATABASE NAME .. FROM '+ @ vViewName;
  @ParmDefinition = N '@icount_recOUT int OUTPUT' ; SET @ ParmDefinition = N '@ icount_recOUT int OUTPUT';
  - Print @ SQLString
  SP_EXECUTESQL @SQLString, @ParmDefinition, EXECUTE @ SP_EXECUTESQL SQLString, @ ParmDefinition, 
 ; @ = @ Icount_recOUT iCount_rec OUTPUT;

  @vcTableName = ( SELECT BusinessRuleViewTableName SET @ vcTableName = (SELECT Business Rule Name Table View 
 DATABASENAAM.. BusinessRuleViews DATABASE NAME .. FROM Business Rule Views 
 BusinessRuleViewName = @vViewName ) Business Rule WHERE Name = @ vViewName View)

  @SQLString = N 'SELECT @icount_TotRecOUT = count(*) SET @ SQLString = N 'SELECT @ icount_TotRecOUT = count (*)  
  + @vcTableName ; DATABASE NAME .. FROM '+ @ vcTableName;
  @ParmDefinition = N '@icount_TotRecOUT int OUTPUT' ; SET @ ParmDefinition = N '@ icount_TotRecOUT int OUTPUT';
  - Print @ SQLString
  SP_EXECUTESQL @SQLString, @ParmDefinition, EXECUTE @ SP_EXECUTESQL SQLString, @ ParmDefinition, 
 ; @ = @ Icount_TotRecOUT iCount_TotRec OUTPUT;

  - Print @ vViewName
  @iTotRowCount = ( SELECT COUNT ( * ) FROM SET @ iTotRowCount = (SELECT COUNT (*) FROM 
 DATABASE NAME .. Business Rule Views 
 BusinessRuleViewName = @vViewName ) Business Rule WHERE Name = @ vViewName View)

  DATABASENAAM.. BusinessRuleViewsCount ( BusinessRuleViewID, INSERT INTO DATABASE NAME .. Business Rule Views Count (BusinessRuleViewID, 
 BusinessRuleViewCountRunno, Business Rule View Count Date, 

 Business Rule View Count Count BusinessRuleViewCountTotTableCount)
   BusinessRuleViewID , @iRunNo, GETDATE ( ) , SELECT BusinessRuleViewID, @ iRunNo, GETDATE (), 
 ICount_rec @, @ iCount_TotRec 
  BusinessRuleViews FROM Business Rule Views 
  BusinessRuleViewName = @vViewName Business Rule WHERE Name = @ vViewName View

  view_cursor FETCH NEXT FROM view_cursor
  @vViewName INTO @ vViewName
  END

  view_cursor CLOSE view_cursor
  view_cursor LOCATE view_cursor DEAL
 END 

Step 5 - Schedule of Stored Procedure
Finally you calling the Stored Procedure to scheduling. You can call him the best when the ETL has finished and all records are loaded.




Step 6 - Graphical representation
Finally I have two reports on the table are built. Here are the numbers for each Business Rule displayed and you can still retrieve details.




Download the reports here


Do you have tips or suggestions, please give us a message!