Surrogate key

Surrogate key

• What is surrogate key?
• Where do we use a surrogate key?
• How it is different from the natural key?
• How do we implement surrogate key?
• What is the advantage of using a surrogate key?

• What is surrogate key?

As the name suggests Surrogate means ‘substitute’.

The surrogate key is just another substitute key, that will be used to uniquely identify the rows in a dimension table. The surrogate key will have no business meaning.

It’s internally generated by the system and is invisible to the user or application. Thus its value cannot be modified by user or application.

• Where do we use a surrogate key?

If the table is not having any attribute (or natural key), that uniquely identify a row.

If the attribute used as unique identifier uses much storage than required. For example, if the attribute is alphanumeric.

When there is doubt of changes in the values of the attribute (used for unique identification of the row)

If the table participates in more than one relationship from the logical data model. Then data needs to be fetched from multiple related tables using joins.

If we want to improve the performance of data retrieval from multiple tables.

• Difference between Surrogate key and natural key

Surrogate key	Natural key
Don’t have business meaning	Have business meaning, for e.g. SSN number
	These can be changed.
Small in size, generally of integer type. Can be created as identity column	Bigger in size, generally alphanumeric or string,
Comparison takes less time , as it compares to integer, joins are fast	Comparison  takes much time, as it converts string or alphanumeric to Ascii , and then comparison takes place, joins are slow
No locking contentions because of unique constraint, as the surrogates get generated by the DB and are cached – very scalable.	Locking contention may occur, as two sessions may try to insert the same unique business key,
Not searchable from application	searchable
An additional index is needed.	No additional index required
There are often generated by the system .	Records cannot be inserted untill the key is not known, as it has got business meaning

Let's take a simple, classical example:

On the 1st of January 2002, Employee 'E1' belongs to Business Unit 'BU1' (that's what would be in your Employee Dimension). This employee has a turnover allocated to him on the Business Unit 'BU1' But on the 2nd of June the Employee 'E1' is moved from Business Unit 'BU1' to Business Unit 'BU2.' The entire new turnovers have to belong to the new Business Unit 'BU2' but the old one should belong to the Business Unit 'BU1.'

If you used the natural business key 'E1' for your employee within your datawarehouse everything would be allocated to Business Unit 'BU2' even what actually belongs to 'BU1.'

If you use surrogate keys, you could create on the 2nd of June a new record for the Employee 'E1' in your Employee Dimension with a new surrogate key.

This way, in your fact table, you have your old data (before 2nd of June) with the SID of the Employee 'E1' + 'BU1.' All new data (after 2nd of June) would take the SID of the employee 'E1' + 'BU2.'

You could consider Slowly Changing Dimension as an enlargement of your natural key: natural key of the Employee was Employee Code 'E1' but for you it becomes

Employee Code + Business Unit - 'E1' + 'BU1' or 'E1' + 'BU2.' But the difference with the natural key enlargement process, is that you might not have all part of your new key within your fact table, so you might not be able to do the join on the new enlarge key -> so you need another id.

Lookup vs Join

Lookup transformation in ssis vs. join in T-sql

Lookup transformation in SSIS and joins in sql server, both are used to reference some table or referece set. Still both have adavantages over each other.

Lookup	T-sql join
If the reference dataset is small, then use lookup file. As the whole file is loaded into main memory at the time of processing. So it can easily search and get back the matched record quickly than getting from the local disks.	If the reference datasets are big enough to cause trouble, use a join. This can involve I/O if the data is big enough, but the I/O is all highly optimized and sequential. Join processing is very fast and never involves paging or other I/O.
SSIS can well handle the source dataset coming from a heterogeneous database like text file, DB2 etc. and can reference the tables via lookup.	Its complex to implement through join in sql server
	TSQL can cope with more complicated business logic especially in the case of dependency between two datasets.
SSIS buffer cannot hold all dimension data (>1M rows), and can the whole package can stuck for some time at lookup.	It can handle as many data as required.
Blocking(or say “asynchronous”) components used like “Sort”, “aggregation
Error out No-Match rows – we can use this facility of lookup transformation, and use the rows which have no matching key in the reference table, we can redirect the no match rows to different destination for further analysis	N/A
via lookup in SSIS solution, we can easily configure the DB connections to solve this problem, which makes the design more loose-coupled and flexible to the changes like DB name changing or even DB migrate to the other server.	If using JOIN, what you have to do is to hard code the DB name or say to use corss-DB join.
Use if you have got SSIS developerJ	Use join when customer requests to wrap the business logic outside of SSIS and put more logics close to Database layer as much as possible.

SSIS: Logging in SSIS

What is Logging?

SSIS includes log provider, which can be used to implement logging in Package, container and tasks. With logging, one can capture run-time information about a package, helping audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

Also when SSIS log provider for SQL Server is enabled, then one table ‘sysssislog’ is created in the database. This table can be customized by adding some more columns like number of records transferred.

When you add the log to a package, you choose the log provider and the location of the log. 

Integration Services includes the following log providers:

• The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. The default file name extension for this provider is .log.

• The SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler. The default file name extension for this provider is .trc.

• The SQL Server log provider, which writes log entries to the sysssislog table in a SQL Server database.

• The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.

• The XML File log provider, which writes log files to an XML file. The default file name extension for this provider is .xml.

How to implement Logging in Package 

This post will show how to configure logging through SQL Server log provider.

Step1: 

Create one SSIS package create some DFTs. Or we use any already available SSIS package. My package includes two DFTs SCD Type1 and SCD Type 2.

Step 2:

 Right click, and select Logging

Step 3:

a.    Select provider Type ‘SSIS log provider for SQL Server’ from dropdown, and click on ‘Add’ Button

b.    Check the checkbox and select ‘New Connection’ from configuration dropdown

c.    Create a new OleDB connection to the database in which you want ‘sysssislog’ table to be created.

d.    Then go details tab and select the events which you want to be logged during package execution. One can select events for package, container or some tasks also.

In the below snapshot, I selected events for package and two DFT’s SCD type 1 and SCD type 2. One can select various events like ’On error’,’On Post Validate’, ‘On Pre Validate’ etc.

e.    If we click on ‘Advanced’ button, then one can select the events along with the information which values will be recorded in sysssislog table.

After everything is done, click on Ok button. But this will not create sysssislog table in the database (for this post I have used adventure works) also the table will not be created if we only execute some task like DFT etc. The table sysssislog will only get created, if we execute the whole package.

 So when you execute the package you can check the sysssislog table in your database, which you configured.

We will get to see the below information in sysssislog table

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

SSIS: Sysssislog

What is sysssislog?

It’s a table created in database. This table is automatically created, when we enable logging for SSIS package (SSIS log provider for SQL Server), and after execution of package first time.  For this we need to configure one database connection to a database in which this table will be created.

This table contains one row for each logging entry generated by SSIS package or their tasks during runtime. Each row entry depends contains event for which we have enabled logging such as OnPostValidate, PackageStart, OnPreExecute, OnPostExecute, PackageEnd etc.

By default, each row contain below columns. We can add more column as per our requirement.

id	event	computer	operator	source	sourceid	executionid	starttime	endtime	datacode	databytes	message
1	OnPostValidate		ragini.gupta	Package9			2013-06-13 14:20:10.000	2013-06-13 14:20:10.000	0	0x

How it is created?

Integration Services writes logging entries in this table only when packages use the SQL Server log provider.

Sysssislog table is not created just by executing the task, but we need to execute the package first time to create the table.

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

SSIS: SCD Type 2, step by step

SCD Type 2, step by step

Type 2 (historical attribute): when we need to maintain the history of records, whenever some particular column value changes. 

By considering Type2, I assume that the employee gets promotion, Job Title changes. In such case we need to maintain the history of the employee, that with which designation or job Title he joined, and when his designation or Job Title changed.

  

For making such scenario, I have created two tables; one is tmpPerson, which is created as follows by using Adventure Works database’s tables:

---Start-TmpPerson table ---

create table tmpPerson (EmpId int, Title varchar(10) Null,FirstName varchar(20), MiddleName varchar(20),LastName varchar(20),

JobTitle varchar(100),BirthDate Date,MaritalStatus char(1),Gender char(1),NationalIDNumber varchar(20)

,CurrentFlag int , StartDate date,EndDate date default ('2050-12-31'))

insert into tmpPerson  (EmpId , Title ,FirstName , MiddleName ,LastName,

JobTitle ,BirthDate ,MaritalStatus ,Gender ,NationalIDNumber

,CurrentFlag, StartDate )

select e.BusinessEntityID,per.Title,per.FirstName,per.MiddleName,per.LastName,

e.JobTitle,e.BirthDate,e.MaritalStatus,e.Gender,e.NationalIDNumber,

e.CurrentFlag,e.ModifiedDate

from HumanResources.Employee e inner join Person.Person per on e.BusinessEntityID=per.BusinessEntityID

----End –TmpPerson table----

I have also created one table tmpPersonStage similar to tmpperson, which stores the records which are changed by changing any of the column value. For Type 2, I have made some changes in Job Title, as below:

---Start--tmpPersonStage table----

create table tmpPersonstage (EmpId int, Title varchar(10) Null,FirstName varchar(20), MiddleName varchar(20),LastName varchar(20),

JobTitle varchar(100),BirthDate Date,MaritalStatus char(1),Gender char(1),NationalIDNumber varchar(20)

,CurrentFlag int , StartDate date,EndDate date default ('2050-12-31'))

insert into tmpPersonstage  (EmpId , Title ,FirstName , MiddleName ,LastName,

JobTitle ,BirthDate ,MaritalStatus ,Gender ,NationalIDNumber

,CurrentFlag, StartDate,EndDate )

select EmpId , Title ,FirstName , MiddleName ,LastName,

JobTitle ,BirthDate ,MaritalStatus ,Gender ,NationalIDNumber

,CurrentFlag, StartDate,EndDate

from dbo.tmpperson where empid in (3,6,8,58,49)

----changes for type 2

 update tmpPersonstage set JobTitle ='Design Engineer' where EmpId=3

 update tmpPersonstage set JobTitle ='Production Technician - WC10' where EmpId=6

 update tmpPersonstage set JobTitle ='Production Technician - WC50' where EmpId=8

 update tmpPersonstage set JobTitle ='Research and Development Engineer' where EmpId=49

 update tmpPersonstage set JobTitle ='Engineering Manager' , LastName='Keill' where EmpId=58

 ---End---tmpPersonstage table -------

In the above script, I have updated the JobTitle of some employees, for which initially the job titles in tmpPerson table were as below

 After preparing the initial steps for the scenario, let’s move to SSIS package to apply SCD transformation. The below steps are the implementation of SCD Type 2

Step 1:

 Drag and drop one Data Flow Task in the Control flow tabs.

And name this DFT as ‘scd type 2’

Step2:

 

Go to Data Flow tab, Drag and drop OleDB Source and Slowly Changing Dimension transformation from Data flow transformations.

Step 3:

Configure OleDB source, here the source will be the table which has changed or new records, and for which history will be maintained, or inserted in the main table. In our scenario the source table is ‘tmpPersonStage’, which keeps some updated and new records for updated records history will be maintained and new records will be inserted into the main table ‘tmpPerson’

 

 Step 4: 

Configure the SCD transformation; double click on SCD transformation, one wizard will open as below:

Click on next and create either new connection or already created connection to AdventureWorks2008, and while mapping the input columns to dimension column, at least one column need to be mentioned as ‘Business Key’.

 Business key is the column on the basis of which the updation or insertion into ‘tmpPerson’ will be made. Here we have mentioned EMPID as BusinessKey, so if EmpId of input table ‘tmpPersonStage’ is found in ‘tmpPerson’ table then the respective row  in ‘tmpPerson’ will be updated with the changes  from ‘tmpPersonStage’ input table 

Click on next. In the below window, change type need to be mentioned on certain column. Change Type can be ‘Fixed attribute’, ‘Changing attribute’ and ‘Historical Attribute’

As in this scenario is based on Historical attribute we will select some columns for which history will be maintained if found modified and new record will be inserted.

 Go to Next and select one of the below option, 

As in ‘tmpPerson’, we have both the option available one through ‘CurrentFlag’ column and another through ‘Startdate’ and ‘EndDate’.

We can opt for first option and use column ‘CurrentFlag’ as the indicator of the current record. By this the updated record will be inserted with ‘CurrentFlag’ value as ‘1’ and the records which became old for the respective ‘EmpID’ the ‘CurrentFlag’ will be set to ‘0’.

And if we opt for second option then we need to update the old record’s EndDate with ‘GetDate ()’ and need to insert the updated record with ‘StartDate’ as ‘GetDate ()’. For new records, we need to use StartDate as ‘GetDate ()’ .

Through this post, I am selecting first option, and I will be using ‘CurrentFlag’ Column to show the current record.

Go to next and don't select  ‘Enable inferred member support’, as we don’t require it for this scenario.

 

Click next, which displays New Records, other Outputs(Historical Attribute Output), which means there will be two arrows from SCD, one will take the new records, another will take the records for which history needs to be maintained.

Click next and Finish the wizard. As we finish the wizard we see some transformation like ‘OleDb command’ and ‘Insert Destination’ are automatically created.

 Oledb command will update the ‘CurrentFlag’ column of old records and will set it to ‘0’ for the ‘EmpID’ for which records are updated. These all will be union all with all new records along with the new updated records having ‘CurrentFlag’ 1 and will be inserted into the main table ‘tmpPerson’

Step 5:

Step 5.a:

Double click on Derived Column, and we see that one column with ‘Replace Currentflag’ has been derived from current flag and the value is set to ‘0’.

Step 5.b:

Double click on Oledb command, and it will be configured automatically as below. The connection will be automatically configured to table ‘tmpPerson’ of Adventure works 2008, in which the records will be updated.

Also when clicked on Component Properties tab then the update command is also configure, which we can see on string value editor.

The update command updates Current Flag to 0 on the basis of EmpId , and Current Flag(value 1)

In column mapping tab the parameter are mapped with the columns mentioned in query. These Parameters contain the Replace Current Flag column from derived transformation and EmpID from SCD

Step 5.c:

When clicked on next Derived Column transformation, the same Current Flag column is derived column of Replace Current flag, and set it to 1, for new and updated records.

Step 5.d:

Insert Destination will also be configured automatically, to insert the new records coming from SCD to tmpPerson table. 

 

Step 7:
Execute the DFT 

 

 After execution, we can see that, we updated 5 records in the tmppersonstage table, which are inserted into tmpperson table, along with the old records current flag updated as 0. As there was no new records, so no row is inserted into tmpperson.

 

Click For implementation of SCD type1 step by step
for alternate mehods of SCD
For more information on SCD 

Please provide your feedback for the post, if you find this post useful.