ETL processes typically will run across the need to delete duplicate records. The fastest and simplist way I know of is to create a Primary Key (or unique index) on the table with the instruction to IGNORE_DUP_KEY=ON . Then when you bulk copy or insert data into the table, SQL quickly ingores the duplicates.
Example:
CREATE TABLE #Temp (ID INT NOT NULL)
ALTER TABLE #Temp
ADD PRIMARY KEY (ID ASC)
WITH (IGNORE_DUP_KEY = ON)
GO
INSERT INTO #Temp (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 1
SELECT * FROM #Temp
DROP TABLE #Temp
But sometimes you need to delete duplicate data in a more controlled and auditable way. Here is an approach that does it in a single set statement.
Example:
CREATE TABLE #Temp(ID INT, Name VARCHAR(255))
INSERT INTO #Temp (ID, Name)
SELECT 1, 'Name 1'
UNION ALL
SELECT 2, 'Name 2'
UNION ALL
SELECT 3, 'Name 3'
UNION ALL
SELECT 4, 'Name 4'
UNION ALL
SELECT 5, 'Name 2'
UNION ALL
SELECT 6, 'Name 1'
UNION ALL
SELECT 7, 'Name 3'
UNION ALL
SELECT 8, 'Name 5'
UNION ALL
SELECT 9, 'Name 4'
UNION ALL
SELECT 10, 'Name 4'
SELECT 'Deleted Data Set'
DELETE FROM #TEMP
OUTPUT DELETED.* -- Display the Deleted Rows
FROM #TEMP T
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY #TEMP.Name ORDER BY #TEMP.ID) as ROWID, #TEMP.*
FROM #TEMP
) AS DupRows -- Find Dubs and assign rowID which resets for each new name
ON DupRows.ID = T.ID
WHERE DupRows.ROWID > 1
-- OR Here is an even more eligant solution --
;
WITH Dubs(ROWID, ID, Name) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY #TEMP.Name ORDER BY #TEMP.ID) as ROWID, #TEMP.*
FROM #TEMP
)
DELETE FROM Dubs
OUTPUT DELETED.* -- Display the Deleted Rows
WHERE ROWID > 1 ;
SELECT 'Clean Data Set'
SELECT * FROM #TEMP
DROP TABLE #TEMP
GO
Monday, December 10, 2007
Monday, November 05, 2007
Free Code: Stored Procedure Code Generator
The above link provides the following:
1. DBCodeGeneration.zip
Description:
You will find that 80% of all stored procedures in a transaction database are all very predictable and can be auto generated. Using a code generator will save you time and create symmetry within your database which increases predictability and stability for the overall system. This will allow you to focus on the other 20% of the stored procedures which are more complex and critical to the system.
I have found that creating stored procedures that create stored procedures was an easy way to do code generation for your database. Creating a script that depended on Visual Studio is just too bulky and slow to load when you want quick results. This solution I can take with me any were without the need of a complex environment.
Feature List:
1. Creates Get Stored Procedures
2. Creates Set Stored Procedures (Performs Insert or Update process depending on conditions)
3. Creates Delete Stored Procedures
Wednesday, August 01, 2007
How To: Simple Query-able Compression (No need to decompress to read file)
Do you wish to compress your data without having to decompress it to read it? Most people will think of RAR or ZIP compression when they need to save space on the hard drive. This may save storage and I/O load, but the side effect of this approach will require you to decompress the file every time you need to access it. The following is a means to compress your data without requiring decompression to read it.
Normalization: Compression is a natural byproduct when normalizing your data (Please see articles below on normalization and modeling). By normalizing your data you remove redundant data. It’s an effective non-destructive means of compressing your data into a query enabled format.
Binary Conversion: Converting from a string formatted file into a binary formatted file is another natural means of compressing your data. Reducing a string value of “1002000032” in to 4 bytes saves 6 bytes. The strongly data typed binary file can be trusted and read by other business processes without the need to do string conversion.
Hashing Long String Values: Hashing long string values into a binary hash value and placing the string and corresponding hash value into a lookup table is another natural means of compressing your data. URL links are common storage hogs. Reducing a 255 byte URL string to a 64 bit hash can save lots of space if that URL string occurs multiple times within the file. (NOTE: Make sure you select the most appropriate Hashing algorithm and the right hash bit length to reduce your odds of collisions.)
Roll Ups (Aggregation): By only recording one unique row and placing an aggregation count for each time it was recorded within a unit of time you can reduce the amount of data being recorded(Example: John Doe hit your website home page 3 times in 1 hr. In the log there would be one record with an aggregate count value of 3). This is destructive to your data set, because you lose the retreading of a user’s event path. But this may be a minimal and acceptable loss of data depending on your business.
Or Get Up To 36x Compression
The typical compression results from using one or more of the above suggestions can result in 2x-6x compression ratio. The above suggestions are extremely valuable even if you don’t care about having query enabled compression. If you add RAR on top of it all you can save another 6x compression which can give you between 12x-36x compression. Not bad for saving space eh!
Normalization: Compression is a natural byproduct when normalizing your data (Please see articles below on normalization and modeling). By normalizing your data you remove redundant data. It’s an effective non-destructive means of compressing your data into a query enabled format.
Binary Conversion: Converting from a string formatted file into a binary formatted file is another natural means of compressing your data. Reducing a string value of “1002000032” in to 4 bytes saves 6 bytes. The strongly data typed binary file can be trusted and read by other business processes without the need to do string conversion.
Hashing Long String Values: Hashing long string values into a binary hash value and placing the string and corresponding hash value into a lookup table is another natural means of compressing your data. URL links are common storage hogs. Reducing a 255 byte URL string to a 64 bit hash can save lots of space if that URL string occurs multiple times within the file. (NOTE: Make sure you select the most appropriate Hashing algorithm and the right hash bit length to reduce your odds of collisions.)
Roll Ups (Aggregation): By only recording one unique row and placing an aggregation count for each time it was recorded within a unit of time you can reduce the amount of data being recorded(Example: John Doe hit your website home page 3 times in 1 hr. In the log there would be one record with an aggregate count value of 3). This is destructive to your data set, because you lose the retreading of a user’s event path. But this may be a minimal and acceptable loss of data depending on your business.
Or Get Up To 36x Compression
The typical compression results from using one or more of the above suggestions can result in 2x-6x compression ratio. The above suggestions are extremely valuable even if you don’t care about having query enabled compression. If you add RAR on top of it all you can save another 6x compression which can give you between 12x-36x compression. Not bad for saving space eh!
Monday, December 18, 2006
How To: Sessionize Events
This is a how to do sessionizing using SQL with just a set of events. We use alot of self-joins to accomplish this task.
Steps:
-- This is an example of how to implicitly determine sessions with just a set of event date/times.
--------------------------------------------------------------------
-- Setup test scenerio.
--------------------------------------------------------------------
-- DROP TABLE WebLog CREATE TABLE WebLog(UserID INT NOT NULL, EventDate DATETIME NOT NULL)
DECLARE @SessionTimeOut INT
SET @SessionTimeOut = 1800 -- 60 seconds * 30 minutes
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:01:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:02:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:03:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:06:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:10:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:20:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 01:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:30:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:31:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:32:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:33:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:10');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:30');
INSERT INTO WebLog VALUES (123,'01-jan-2005 04:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:01:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:02:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:03:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:04:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:05:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:01:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:02:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:03:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:06:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:10:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:20:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 01:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:30:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:31:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:32:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:33:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:10');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:30');
INSERT INTO WebLog VALUES (121,'01-jan-2005 04:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:01:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:02:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:03:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:04:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:05:00');
DECLARE @WebLogWithRowID TABLE (UserID INT NOT NULL,
EventDate DATETIME NOT NULL,
RowID BIGINT NOT NULL)
DECLARE @WebLogExitPoint TABLE (UserID INT NOT NULL,
EventDate DATETIME NOT NULL,
RowID BIGINT NOT NULL,
Duration BIGINT NOT NULL,
ExitPointFlag BIT NOT NULL)
--------------------------------------------------------------------
-- Assign Record ID
--------------------------------------------------------------------
INSERT INTO @WebLogWithRowID(UserID, EventDate, RowID)
SELECT UserID, EventDate, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventDate) RowID
FROM WebLog
--------------------------------------------------------------------
-- Determine Duration and ExitPoint
--------------------------------------------------------------------
INSERT INTO @WebLogExitPoint(UserID, EventDate, RowID, Duration, ExitPointFlag)
SELECT a.UserID, a.EventDate, a.RowID,
ISNULL(DATEDIFF(SECOND, a.EventDate, b.EventDate), @SessionTimeOut) AS Duration,
CASE WHEN b.UserID IS NULL THEN 1 -- Last record
WHEN DATEDIFF(SECOND, a.EventDate, b.EventDate) > @SessionTimeOut THEN 1
ELSE 0
END AS ExitPointFlag
FROM @WebLogWithRowID a
LEFT OUTER JOIN @WebLogWithRowID b
ON b.RowID = a.RowID + 1
AND b.UserID = a.UserID
--------------------------------------------------------------------
-- Determine SeqNumber within Session and SessionID
--------------------------------------------------------------------
SELECT a.UserID, a.EventDate, a.RowID,
a.RowID - IsNULL(b.RowID, 0) AS SeqNumber,
a.Duration,
a.ExitPointFlag,
ISNULL(b.RowID, 1) AS SessionID
FROM @WebLogExitPoint a
LEFT OUTER JOIN @WebLogExitPoint b
ON b.RowID = (SELECT MAX(c.RowID)
FROM @WebLogExitPoint c
WHERE c.RowID < a.RowID
AND c.ExitPointFlag = 1 )
AND b.UserID = a.UserID
Steps:
- Determine your session timeout length.
- Order events by date.
- Identify the Exit Point Event and the duration between events. The Exit Point is the event that has a duration greater than the session timeout length.
- Finally you can identify the sessionID and assign a sequence number for each event within a session.
-- This is an example of how to implicitly determine sessions with just a set of event date/times.
--------------------------------------------------------------------
-- Setup test scenerio.
--------------------------------------------------------------------
-- DROP TABLE WebLog CREATE TABLE WebLog(UserID INT NOT NULL, EventDate DATETIME NOT NULL)
DECLARE @SessionTimeOut INT
SET @SessionTimeOut = 1800 -- 60 seconds * 30 minutes
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:01:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:02:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:03:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:06:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:10:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:20:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 01:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:30:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:31:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:32:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:33:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:10');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:30');
INSERT INTO WebLog VALUES (123,'01-jan-2005 04:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:01:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:02:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:03:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:04:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:05:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:01:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:02:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:03:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:06:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:10:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:20:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 01:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:30:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:31:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:32:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:33:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:10');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:30');
INSERT INTO WebLog VALUES (121,'01-jan-2005 04:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:01:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:02:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:03:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:04:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:05:00');
DECLARE @WebLogWithRowID TABLE (UserID INT NOT NULL,
EventDate DATETIME NOT NULL,
RowID BIGINT NOT NULL)
DECLARE @WebLogExitPoint TABLE (UserID INT NOT NULL,
EventDate DATETIME NOT NULL,
RowID BIGINT NOT NULL,
Duration BIGINT NOT NULL,
ExitPointFlag BIT NOT NULL)
--------------------------------------------------------------------
-- Assign Record ID
--------------------------------------------------------------------
INSERT INTO @WebLogWithRowID(UserID, EventDate, RowID)
SELECT UserID, EventDate, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventDate) RowID
FROM WebLog
--------------------------------------------------------------------
-- Determine Duration and ExitPoint
--------------------------------------------------------------------
INSERT INTO @WebLogExitPoint(UserID, EventDate, RowID, Duration, ExitPointFlag)
SELECT a.UserID, a.EventDate, a.RowID,
ISNULL(DATEDIFF(SECOND, a.EventDate, b.EventDate), @SessionTimeOut) AS Duration,
CASE WHEN b.UserID IS NULL THEN 1 -- Last record
WHEN DATEDIFF(SECOND, a.EventDate, b.EventDate) > @SessionTimeOut THEN 1
ELSE 0
END AS ExitPointFlag
FROM @WebLogWithRowID a
LEFT OUTER JOIN @WebLogWithRowID b
ON b.RowID = a.RowID + 1
AND b.UserID = a.UserID
--------------------------------------------------------------------
-- Determine SeqNumber within Session and SessionID
--------------------------------------------------------------------
SELECT a.UserID, a.EventDate, a.RowID,
a.RowID - IsNULL(b.RowID, 0) AS SeqNumber,
a.Duration,
a.ExitPointFlag,
ISNULL(b.RowID, 1) AS SessionID
FROM @WebLogExitPoint a
LEFT OUTER JOIN @WebLogExitPoint b
ON b.RowID = (SELECT MAX(c.RowID)
FROM @WebLogExitPoint c
WHERE c.RowID < a.RowID
AND c.ExitPointFlag = 1 )
AND b.UserID = a.UserID
Thursday, March 23, 2006
Model Design Patterns For: Document Management System
The following design pattern I’ve known for several years and have implemented it to great success. The subtles are what will save you:
1. Having an explicit ExpirationDate will save you having to look up what an initial contract’s end date is when you imply that the Start date of the replacing contract is the end of initial contract (When in doubt, be explicit not implicit);
2. You can use Between statements for getting contracts that are currently active;
3. Not mixing non-customer-visible change history with official versioned data will save you from having to always filter change history out of the resultset.
The design pattern below will cover a simplified contract management model that will track the work flow status of a contract and provide simple change control. This example is just to demonstrate the design pattern and can be used in variation for other concepts as well. It will simplify the understanding of your system.
Concept: Contract
A contract defines the terms for services to be rendered in exchange for money between two or more legal entities. A contract can not be changed once it has been activated. If you require to change the contract after the contract is activated you need to Terminate the contract and create a new contract with the required changes.
Concept: Contract Effective Date
A contract’s effective date represents the date the contract will be activated. This date can’t be changed once the current date is less than its effective date.
Concept: Contract Expiration Date
A contract’s expiration date represents the date the contract expires. Open ended contracts have no expiration date.
Concept: Contract Termination Date
A contract’s termination date represents the date the contract was terminated. Use this date to prematurely cancel a contract and yet preserve the original expiration date.
Concept: Contract Status
A contract’s work flow is controlled by its status: Draft, Approved, Active, Terminated, Expired.
1. Draft – Represents that the contract is a draft. Contract and terms can be changed freely.
2. Approved– Represents that the contract has been approved. Contract and terms can NOT change except the Contract Status, Effective Date and Expiration Date.
3. Active – Represents that the contract is activated. Contract and terms can NOT change except the Contract Status and Expiration Date.
4. Terminated – Represents that the contract has been terminated prematurely. Contract and terms can NOT be changed.
5. Expired – Represents that the contract has naturally Expired based on the contract’s expiration date. Contract and terms can NOT be changed.
Concept: Change history tracking
Change history tracking tracks all changes done to a table. Please see http://dataglass.blogspot.com/2006/03/how-to-build-change-log-into-your.html for technique.
Contract
------------------
ContractID (PK)
ContractNumber (AK)
… (Other attributes about the contract)
…
…
EffectiveDate
ExpirationDate
TerminationDate
StatusCode
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
ContractHistory
-----------------------
ContractID, RecordedDateTime (PK)
ContractNumber
… (Other attributes about the contract)
…
…
EffectiveDate
ExpirationDate
TerminationDate
StatusCode
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
ActionType
Status
----------
StatusCode (PK)
StatusName (AK)
StatusDesc
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
1. Having an explicit ExpirationDate will save you having to look up what an initial contract’s end date is when you imply that the Start date of the replacing contract is the end of initial contract (When in doubt, be explicit not implicit);
2. You can use Between statements for getting contracts that are currently active;
3. Not mixing non-customer-visible change history with official versioned data will save you from having to always filter change history out of the resultset.
The design pattern below will cover a simplified contract management model that will track the work flow status of a contract and provide simple change control. This example is just to demonstrate the design pattern and can be used in variation for other concepts as well. It will simplify the understanding of your system.
Concept: Contract
A contract defines the terms for services to be rendered in exchange for money between two or more legal entities. A contract can not be changed once it has been activated. If you require to change the contract after the contract is activated you need to Terminate the contract and create a new contract with the required changes.
Concept: Contract Effective Date
A contract’s effective date represents the date the contract will be activated. This date can’t be changed once the current date is less than its effective date.
Concept: Contract Expiration Date
A contract’s expiration date represents the date the contract expires. Open ended contracts have no expiration date.
Concept: Contract Termination Date
A contract’s termination date represents the date the contract was terminated. Use this date to prematurely cancel a contract and yet preserve the original expiration date.
Concept: Contract Status
A contract’s work flow is controlled by its status: Draft, Approved, Active, Terminated, Expired.
1. Draft – Represents that the contract is a draft. Contract and terms can be changed freely.
2. Approved– Represents that the contract has been approved. Contract and terms can NOT change except the Contract Status, Effective Date and Expiration Date.
3. Active – Represents that the contract is activated. Contract and terms can NOT change except the Contract Status and Expiration Date.
4. Terminated – Represents that the contract has been terminated prematurely. Contract and terms can NOT be changed.
5. Expired – Represents that the contract has naturally Expired based on the contract’s expiration date. Contract and terms can NOT be changed.
Concept: Change history tracking
Change history tracking tracks all changes done to a table. Please see http://dataglass.blogspot.com/2006/03/how-to-build-change-log-into-your.html for technique.
Contract
------------------
ContractID (PK)
ContractNumber (AK)
… (Other attributes about the contract)
…
…
EffectiveDate
ExpirationDate
TerminationDate
StatusCode
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
ContractHistory
-----------------------
ContractID, RecordedDateTime (PK)
ContractNumber
… (Other attributes about the contract)
…
…
EffectiveDate
ExpirationDate
TerminationDate
StatusCode
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
ActionType
Status
----------
StatusCode (PK)
StatusName (AK)
StatusDesc
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
Tuesday, March 21, 2006
Modeling Questionnaire
Here are some strategic questions to ask key business personnel to help accurately model how the business uniquely defines its information.
Entity Questions:1. What is the list of Items (or Entities) that you are tracking or would like to track?
2. How do you uniquely identify each {Entity Name Here} ?
3. Do you manually assign identification to each {Entity Name Here}?
4. Can the unique identifier change for a {Entity Name Here}?
Entity Relationship Questions:
1. Can {Entity A} have 1 or more {Entity B}?
2. Can {Entity B} have 1 or more {Entity A}?
3. Can {Entity A} exist without {Entity B}?
4. Can {Entity B} exist without {Entity A}?
Entity Subtyping Questions:
1. Are there one or more types of {Entity Name Here}?
2. Is a {Entity Name Here} a type of {Entity Name Here}?
3. Can a {Entity Name Here} be classified under more than one subtype?
Entity Attribute Questions:
1. What is the list of attributes that you directly associate with a {Entity Name Here}?
2. Is this {Entity Attribute Name Here} Required?
3. Is this {Entity Attribute Name Here} Changeable?
4. Is this {Entity Attribute Name Here} made up of multiple values?
5. Is this {Entity Attribute Name Here} a Calculated value?
6. Is this {Entity Attribute Name Here} unique?
7. Does this {Entity Attribute Name Here} be selected from a standard list of values?
Entity Mastering and Business Ownership Questions:
1. What is the list business areas that are affected by this information model?
2. Does {Entity Name Here} get mastered by this {Business Area}?
3. Does {Entity Name Here} get used by this {Business Area}?
Entity Questions:1. What is the list of Items (or Entities) that you are tracking or would like to track?
2. How do you uniquely identify each {Entity Name Here} ?
3. Do you manually assign identification to each {Entity Name Here}?
4. Can the unique identifier change for a {Entity Name Here}?
Entity Relationship Questions:
1. Can {Entity A} have 1 or more {Entity B}?
2. Can {Entity B} have 1 or more {Entity A}?
3. Can {Entity A} exist without {Entity B}?
4. Can {Entity B} exist without {Entity A}?
Entity Subtyping Questions:
1. Are there one or more types of {Entity Name Here}?
2. Is a {Entity Name Here} a type of {Entity Name Here}?
3. Can a {Entity Name Here} be classified under more than one subtype?
Entity Attribute Questions:
1. What is the list of attributes that you directly associate with a {Entity Name Here}?
2. Is this {Entity Attribute Name Here} Required?
3. Is this {Entity Attribute Name Here} Changeable?
4. Is this {Entity Attribute Name Here} made up of multiple values?
5. Is this {Entity Attribute Name Here} a Calculated value?
6. Is this {Entity Attribute Name Here} unique?
7. Does this {Entity Attribute Name Here} be selected from a standard list of values?
Entity Mastering and Business Ownership Questions:
1. What is the list business areas that are affected by this information model?
2. Does {Entity Name Here} get mastered by this {Business Area}?
3. Does {Entity Name Here} get used by this {Business Area}?
Wednesday, March 15, 2006
How To: Build a Change Log into Your Database
How do you track all changes for a table within a database? Easy. Build a corrisponding history table that mirrors the table in question with the addition of a datetime column to the primary key and an action column to indentify the action that got this item into the history table (Insert, Update, Delete). The history table is maintained by a trigger on the master table.
Example:
Person
-------------
PersonID (PK)
FirstName
LastName
CreatedBy
CreatedDateTime
UpdatedBy
UpdatedDateTime
PersonHistory
-------------
PersonID, RecordedDateTime (PK)
FirstName
LastName
CreatedBy
CreatedDateTime
UpdatedBy
UpdatedDateTime
ActionType
Example:
Person
-------------
PersonID (PK)
FirstName
LastName
CreatedBy
CreatedDateTime
UpdatedBy
UpdatedDateTime
PersonHistory
-------------
PersonID, RecordedDateTime (PK)
FirstName
LastName
CreatedBy
CreatedDateTime
UpdatedBy
UpdatedDateTime
ActionType
Saturday, March 11, 2006
Logical Data Modeling: An Introduction
Data Modeling In General
I found it worthy to note that the purpose of creating a normalized logical data model is to accurately document the business entities and relationships between them in a detailed model. The value of this logical model is two fold: One is for the business owners to have detailed comprehension of their own business information; Second is to transfer this business knowledge to the developers to equip them to accurately build the system to the exact business needs. They will also create an optimized physical data model (Schema) for the system based on this logical model. Therefore it is critical that you interview the appropriate knowledgeable business people and document the business as much as you can. You will run across business areas in which the business may not be well defined. It will then be your responsibility to understand this area as much as you can, model it accurately, and get approval on the design by the business owners.
Meta Modeling: A WarningDO NOT fall into the temptation to meta model a business area that is not well understood by the business owners in the attempt to avoid your due diligence. This includes throwing in many-to-many relationships into the model to solve problems where the relationship between entities were unclear. This will lead to complications in development and result in a system hard to maintain, use, and raises the cost of ownership. Think like a lawyer. The point is to model the business accurately and provide a natural means to describe and enforce the definition and relationships of the business entities that need to be managed. Only after you have exhausted all avenues of research is when you should use a meta model. But isolate that meta model to be applied specifically to the area that is requiring that level of flexability. Then the meta model will be your friend and not your enemy.
Step 1: Use Case Modeling
The first thing you need to do is document the real world scenarios of the business areas you need to model. This should be detailed in proper sentences to work out the nouns and verbs. The nouns and verbs will be your key to discovering the data model. Of course there are many other benefits as well: Classifying your users, identifying external systems and data dependences, identifying user interfaces, discovering required reports, understand business processes, and finding the scope of the system you are to build.
Step 2: Extended Relational Analysis
Data modeling using the rules of normalization (http://www.datamodel.org/NormalizationRules.html) is not the most natural and easy way to model any system. This can be proven by the very fact that many people don’t truly understand how to normalize a data model. But there is good news. There is a different approach that makes it childs play to create a normalized data model using a technique called Extended Relational Analysis (ERA). I’ve learned it years ago and has been critical to my career. So I highly recommend it. This doesn’t mean that you don’t need to understand the normalization rules, but it does mean that you don’t have to memorize them in to a daily magical chant so you don’t forget. Think of ERA as an as a way of thinking and a means to organize your thoughts rather than a software tool.
ERA technique is broken up into three areas: Entity Analysis; Relationship Analysis; Attribute Analysis. Entity analysis uses nouns out of the use case model to help define each entity that a business is required to deal with. Relationship Analysis uses verbs out of the use case model to help define each relationship between business entities. Attributes Analysis uses the modifiers (Adjectives and Adverbs) to help finish the definition of each business entity defined or create new ones missed with the other two analysis steps. There are many classes you can take out there to help you learn this technique in modeling (http://www.era-sql.com/). Its worth the expense, trust me. It will make your modeling efforts much easier and the end results increase your chances of lowering your total cost of ownership of any system you build. Just do it.
For more detail see http://www.pmcomplete.com/BPM/HTML/bpm659v.asp.
I found it worthy to note that the purpose of creating a normalized logical data model is to accurately document the business entities and relationships between them in a detailed model. The value of this logical model is two fold: One is for the business owners to have detailed comprehension of their own business information; Second is to transfer this business knowledge to the developers to equip them to accurately build the system to the exact business needs. They will also create an optimized physical data model (Schema) for the system based on this logical model. Therefore it is critical that you interview the appropriate knowledgeable business people and document the business as much as you can. You will run across business areas in which the business may not be well defined. It will then be your responsibility to understand this area as much as you can, model it accurately, and get approval on the design by the business owners.
Meta Modeling: A WarningDO NOT fall into the temptation to meta model a business area that is not well understood by the business owners in the attempt to avoid your due diligence. This includes throwing in many-to-many relationships into the model to solve problems where the relationship between entities were unclear. This will lead to complications in development and result in a system hard to maintain, use, and raises the cost of ownership. Think like a lawyer. The point is to model the business accurately and provide a natural means to describe and enforce the definition and relationships of the business entities that need to be managed. Only after you have exhausted all avenues of research is when you should use a meta model. But isolate that meta model to be applied specifically to the area that is requiring that level of flexability. Then the meta model will be your friend and not your enemy.
Step 1: Use Case Modeling
The first thing you need to do is document the real world scenarios of the business areas you need to model. This should be detailed in proper sentences to work out the nouns and verbs. The nouns and verbs will be your key to discovering the data model. Of course there are many other benefits as well: Classifying your users, identifying external systems and data dependences, identifying user interfaces, discovering required reports, understand business processes, and finding the scope of the system you are to build.
Step 2: Extended Relational Analysis
Data modeling using the rules of normalization (http://www.datamodel.org/NormalizationRules.html) is not the most natural and easy way to model any system. This can be proven by the very fact that many people don’t truly understand how to normalize a data model. But there is good news. There is a different approach that makes it childs play to create a normalized data model using a technique called Extended Relational Analysis (ERA). I’ve learned it years ago and has been critical to my career. So I highly recommend it. This doesn’t mean that you don’t need to understand the normalization rules, but it does mean that you don’t have to memorize them in to a daily magical chant so you don’t forget. Think of ERA as an as a way of thinking and a means to organize your thoughts rather than a software tool.
ERA technique is broken up into three areas: Entity Analysis; Relationship Analysis; Attribute Analysis. Entity analysis uses nouns out of the use case model to help define each entity that a business is required to deal with. Relationship Analysis uses verbs out of the use case model to help define each relationship between business entities. Attributes Analysis uses the modifiers (Adjectives and Adverbs) to help finish the definition of each business entity defined or create new ones missed with the other two analysis steps. There are many classes you can take out there to help you learn this technique in modeling (http://www.era-sql.com/). Its worth the expense, trust me. It will make your modeling efforts much easier and the end results increase your chances of lowering your total cost of ownership of any system you build. Just do it.
For more detail see http://www.pmcomplete.com/BPM/HTML/bpm659v.asp.
Tuesday, February 21, 2006
Reasons for Partitioning Your Data
Partitioning splits data across multiple tables, databases, and/or database servers. There are two types; vertical and horizontal partitioning. Horizontal partitioning divides data across mutiple tables based on rows that fall within the appropriate partition rules. Each partition has the same columns, but has its own partition rule (Example: Between 01/01/2005-02/01/2005). Veritical partitioning splits the table definition into two or more tables based on the columns.
Here are some basic reasons why a system may need to have its data partitioned: Performance, Workflow, Security, & Change Tracking.
Performance: Volume
Performance issues due to data volume is the most common reason for partitioning tables. Data warehouses commonly use Horizontal partitioning to partition based on a clearly defined set of rules. If a row fits within a partition rule set, it is inserted into that partition. When defining partitions, database designers look for natural partitions for the business; Example:
· Date Range: By Year, Month, or Day
· By Database Source: Database System X, Y, & Z
· By Collecting Point: Data is collected by Nth number of data collectors
· By Business Unit
· By User
· By User Location
Workflow: Status
Workflow partitioning is used in systems like document tracking and management services. It’s a horizontal partition that partitions based on Workflow Status Rules; Example:
· (Partition 1) Draft Incomplete, Draft Complete, Draft Cancelled, Draft Approved
· (Partition 2) Active, Suspended, Expired
· (Partition 3) Archived
WorkFlow: User Type
Some of the reasons for partitioning based on Workflow Status may be based on types of users as well. Contracts are the most common managed documents and are used by several different types of users. Example:
· Sales Department
· Legal Department
· Service Fulfillment Departments
· Accounting Department
Security: PII
Partitioning data based on levels of security risk is used to Isolate PII (Personal Identification Information). Example:
· SSN
· Phone and Address
· Email
· Name
· Credit Cards and Accounts
· Passport ID
Security: Need To Know
Other type of information that may be partitioned for security reasons is information that requires a security clearance and/or a need to know.
· Medical History
· Sensitive Documents
· Trademark Secrets
Security partitions are usually horizontal partitioned using tables, databases, and/or servers. This type of partitioning lowers the security risk and increases the manageability of enforcing security requirements.
Change Tracking
Change tracking and history tracking partitions are very common in mission critical applications requiring full tracking of data changes within a database. These partitions are horizontal partitions with the addition of extra tracking attributes. These partitions are usually managed by a trigger on its corresponding master table. This trigger inserts a new row into the partition every time there is an update or delete. With this technique, it is easy to determine what changed when, and by whom.
Sunday, February 12, 2006
How To: Hierarchal Lookups Without a Cursor
Reporting on hierarchal tables (Child/Parent relationship) tables can be a huge time bottleneck when using an iterative cursor process. Here is a speedy way to look up hierarchal information.
Original Hierarchal Table
Traditionally hierarchal tables self reference its own primary key and giving it a parent role name. Example:
Person
PersonID (PK) , Name (Attributes) , ParentPersonID (Self Ref. Person.PersonID FK)
1 , John Doe , NULL
2 , Tom Doe , 1
3 , Jill Doe , 1
4 , Harry Doe , 2
5 , Jim Smith , NULL
Hierarchal Index Table
The key is building an index table that maps a parent to every child, grand child, great grand child, etc... and assign the generation it belongs to in relationship. Example:
PersonHierarchyIndex
AncestoryID (Person.PersonID PK, FK), ChildID (Person.PersonID PK, FK), GenerationLevel1 (John Doe) , 1 (John Doe), 0
1 (John (Doe), 2 (Tom Doe), 1
1 (John Doe), 3 (Jill Doe), 1
1 (John Doe), 4 (Harry Doe), 2
2 (Tom Doe), 2 (Tom Doe), 0
2 (Tom Doe), 4 (Harry Doe), 1
3 (Jill Doe),2 (Jill Doe), 0
4 (Harry Doe), 4 (Harry Doe), 0
5 (Jim Smith), 5 (Jim Smith), 0
Index Usage
With the aid of the hierarchal index you will not need a cursor for your reports. Example:
-- Get all Progeny (Children, Grand Children, etc…)
Select Person.Name, Parent.Name, Index.GenerationLevel
From PersonHierarchyIndex
Join Person
On Person.PersonID = PersonHierarchyIndex.ChildID
And ParentHierarchyIndex.AncestoryID = 1 -- (John Doe)
Join Person Parent
On Parent.PersonID = PersonHierarchyIndex.AncestoryID
Index Population
The following example uses the new feature in 2005 SQL Server. We populate the Index using a recursive query using a CTE (Common Table Expression). It is used to build the index at the time of creating or modifying the hierarchy. Most hierarchies are slowly changing domain data. With that in mind it makes sense to take the cost of building the index at the time when the domain changes rather then at the time of selecting from the hierarchy to create your reports.
Use this function to then insert into the PersonHierarchyIndex table:
-- Recursive Query using Common Table Expression
CREATE Function dbo.BuildPersonHierarchyIndex () RETURNS Table
ASRETURN(
WITH AncestoryTree (PPID, PID, PersonID, ParentPersonID, DirectChild, GenerationLevel)
AS (SELECT PPID = Person.ParentPersonID, PID = Person.PersonID, Person.PersonID, Person.ParentPersonID , DirectChild = 1, GenerationLevel = 1
FROM Person
UNION ALL
SELECT AncestoryTree.PPID, AncestoryTree.PID, Person.PersonID, Person.ParentPersonID, DirectChild = 0, GenerationLevel = AncestoryTree.GenerationLevel + 1
FROM Person
JOIN AncestoryTree
ON AncestoryTree.ParentPersonID = Person.PersonID
WHERE Person.PersonID <> Person.ParentPersonID)
SELECT PersonID = AncestoryTree.PID, AncestoryTree.ParentPersonID, AncestoryTree.DirectChild, AncestoryTree.GenerationLevel
FROM AncestoryTree
WHERE AncestoryTree.PPID IS NOT NULL
UNION ALL
SELECT PersonID, PersonID, 0, 0
FROM Person
--OPTION (MAXRECURSION 200) -- Default is 100: Make sure the recursion limit is set high enough
);
Original Hierarchal Table
Traditionally hierarchal tables self reference its own primary key and giving it a parent role name. Example:
Person
PersonID (PK) , Name (Attributes) , ParentPersonID (Self Ref. Person.PersonID FK)
1 , John Doe , NULL
2 , Tom Doe , 1
3 , Jill Doe , 1
4 , Harry Doe , 2
5 , Jim Smith , NULL
Hierarchal Index Table
The key is building an index table that maps a parent to every child, grand child, great grand child, etc... and assign the generation it belongs to in relationship. Example:
PersonHierarchyIndex
AncestoryID (Person.PersonID PK, FK), ChildID (Person.PersonID PK, FK), GenerationLevel1 (John Doe) , 1 (John Doe), 0
1 (John (Doe), 2 (Tom Doe), 1
1 (John Doe), 3 (Jill Doe), 1
1 (John Doe), 4 (Harry Doe), 2
2 (Tom Doe), 2 (Tom Doe), 0
2 (Tom Doe), 4 (Harry Doe), 1
3 (Jill Doe),2 (Jill Doe), 0
4 (Harry Doe), 4 (Harry Doe), 0
5 (Jim Smith), 5 (Jim Smith), 0
Index Usage
With the aid of the hierarchal index you will not need a cursor for your reports. Example:
-- Get all Progeny (Children, Grand Children, etc…)
Select Person.Name, Parent.Name, Index.GenerationLevel
From PersonHierarchyIndex
Join Person
On Person.PersonID = PersonHierarchyIndex.ChildID
And ParentHierarchyIndex.AncestoryID = 1 -- (John Doe)
Join Person Parent
On Parent.PersonID = PersonHierarchyIndex.AncestoryID
Index Population
The following example uses the new feature in 2005 SQL Server. We populate the Index using a recursive query using a CTE (Common Table Expression). It is used to build the index at the time of creating or modifying the hierarchy. Most hierarchies are slowly changing domain data. With that in mind it makes sense to take the cost of building the index at the time when the domain changes rather then at the time of selecting from the hierarchy to create your reports.
Use this function to then insert into the PersonHierarchyIndex table:
-- Recursive Query using Common Table Expression
CREATE Function dbo.BuildPersonHierarchyIndex () RETURNS Table
ASRETURN(
WITH AncestoryTree (PPID, PID, PersonID, ParentPersonID, DirectChild, GenerationLevel)
AS (SELECT PPID = Person.ParentPersonID, PID = Person.PersonID, Person.PersonID, Person.ParentPersonID , DirectChild = 1, GenerationLevel = 1
FROM Person
UNION ALL
SELECT AncestoryTree.PPID, AncestoryTree.PID, Person.PersonID, Person.ParentPersonID, DirectChild = 0, GenerationLevel = AncestoryTree.GenerationLevel + 1
FROM Person
JOIN AncestoryTree
ON AncestoryTree.ParentPersonID = Person.PersonID
WHERE Person.PersonID <> Person.ParentPersonID)
SELECT PersonID = AncestoryTree.PID, AncestoryTree.ParentPersonID, AncestoryTree.DirectChild, AncestoryTree.GenerationLevel
FROM AncestoryTree
WHERE AncestoryTree.PPID IS NOT NULL
UNION ALL
SELECT PersonID, PersonID, 0, 0
FROM Person
--OPTION (MAXRECURSION 200) -- Default is 100: Make sure the recursion limit is set high enough
);
Subscribe to:
Posts (Atom)