Example Case
Let us do this by using a business case. Let us assume that we need to extract customers from an OLTP system to the data warehouse system.
The following is the customer table structure in the OLTP system:
Let us assume that we have ten records in the tblCustomer. From the following script, you can create those records.
SET IDENTITY_INSERT [dbo].[tblCustomer] ON
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (1, ‘Stefanie Alexander’, ‘749 East Rocky New Road’, ’36 West Fabien St.’, ‘POB 854’, ‘2002-09-15 00:00:00.000’, ‘UT’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (2, ‘Lee Buckley’, ’30 North Green First Avenue’, ‘935 Nobel Way’, ‘POB 06447’, ‘2002-04-11 00:00:00.000’, ‘NH’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (3, ‘Daniel Gallagher’, ‘377 East Green Oak Avenue’, ’91 Hague Parkway’, ‘POB 2357’, ‘2002-02-07 00:00:00.000’, ‘WI’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (4, ‘Myra Lucero’, ’78 Fabien Freeway’, ’50 Cowley Avenue’, ‘POB 5478’, ‘2005-01-01 00:00:00.000’, ‘TN’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (5, ‘Annie Brennan’, ‘990 Clarendon Drive’, ‘525 North Old Parkway’, ‘POB 364’, ‘2003-09-16 00:00:00.000’, ‘VA’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (6, ‘Jenifer Harvey’, ‘702 South Milton Parkway’, ’48 Old Freeway’, ‘POB 03163’, ‘2007-06-05 00:00:00.000’, ‘SD’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (7, ‘Chastity Shea’, ‘144 Nobel Boulevard’, ‘545 New Way’, ‘POB 6900’, ‘2001-03-03 00:00:00.000’, ‘NJ’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (8, ‘Jeannie Rich’, ‘644 East Rocky Clarendon Boulevard’, ’80 White New St.’, ‘POB 9102’, ‘2007-08-09 00:00:00.000’, ‘MN’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (9, ‘Bobbie Dalton’, ‘457 Fabien Parkway’, ’16 Nobel St.’, ‘POB 547’, ‘2004-10-07 00:00:00.000’, ‘OK’)
INSERT INTO [dbo].[tblCustomer] ([ID], [Name], [AddressI], [AddressII], [AddressIII], [DateJoin], [Location]) VALUES (10, ‘Shawna Merritt’, ’69 Green New Way’, ‘361 Milton Way’, ‘POB 06557’, ‘2000-12-09 00:00:00.000’, ‘MO’)
SET IDENTITY_INSERT [dbo].[tblCustomer] OFF
The following is the dimcustomer table and it’s linked tables for the data warehouse system.
