November 9, 2016 at 1:58 pm
Hi all
I have a task to create a Data Mart from scratch. So far, I am designing a list of Users, and a list of Telephony user information. Here's my example:
DROP TABLE Users
CREATE TABLE Users
(Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
ForenameNVARCHAR(50),
SurnameNVARCHAR(50),
LocationCHAR(50),
EmailNVARCHAR(320),
SCD_StartSMALLDATETIME,
SCD_StopSMALLDATETIME,
IsActiveBIT
)
INSERT INTO Users (Forename,Surname,Location,SCD_Start,SCD_Stop,IsActive)
VALUES ('Test1','Test1','TestL1','2016-11-08',NULL,1)
,('Test2','Test2','TestL2','2016-11-08',NULL,1)
,('Test3','Test3','TestL3','2016-11-08',NULL,1)
,('Test4','Test4','TestL4','2016-11-08',NULL,1)
,('Test5','Test5','TestL5','2016-11-08',NULL,1)
,('Test6','Test6','TestL6','2016-11-08',NULL,1)
DROP TABLE Telephony
CREATE TABLE Telephony
(Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserId INT FOREIGN KEY REFERENCES Users(Id),
ForenameNVARCHAR(50),
SurnameNVARCHAR(50),
OfficePhoneVARCHAR(22),
MobilePhoneVARCHAR(22),
SCD_StartSMALLDATETIME,
SCD_StopSMALLDATETIME,
IsActiveBIT
)
INSERT INTO Telephony (Forename,Surname,OfficePhone,MobilePhone,SCD_Start,SCD_Stop,IsActive)
VALUES ('Test1','Test1','01619999999','07777999999','2016-11-08',NULL,1)
My question is, how does the Id from 'Users' get into UserId in 'Telephony'? I don't quite know how to explain my issue properly, but I am loading both tables with essentially the same list of users, probably both from CSV import, so how does "Joe Blogs" in the 'Users' table, with an Id of 1, end up in the Telephony table with a UserId of 1?
November 9, 2016 at 2:44 pm
If this is a datamart, then how are they related in the source system? If they are related does the Forename and Surname need to be in both tables or are these just staging tables from the source system and the data will be transformed in the datamart?
November 9, 2016 at 2:51 pm
In respect to this becoming a data mart, this is a very small insight into how it will eventually end up.
The reason for first & last name in both tables is, from experience, due to a colleague being entered in systems across a business with varying names, such as "Dan" and "Daniel". It may transpire I don't need this information, or could put the various permutations of the names into another table elsewhere, but for now my concern is how to I load 200+ colleagues into a 'User' table and at the same time have information populate into the 'Telephony' table with "Id" from 'Users' populating into "UserId" in 'Telephony'.
November 9, 2016 at 2:53 pm
There are three main ways.
1) The key is included in the source data.
2) Use the OUTPUT clause when you INSERT/MERGE the data into the user record to get the ID and any information necessary to link it to the Telephony table.
3) Look up the data using a natural key after inserting the user record.
Since we don't know what your source data looks like, we can't give you specifics.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply