SSIS USER INTERFACE

Hope my last article gave a high level idea about the internal architecture of SSIS.

Today we shall focus on SSIS User Interface (SSIS Designer) where we develop the SSIS packages.

Here is how we can navigate to the SSIS Designer.

START>MS SQL SERVER 2008>SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO and Click on File>New>Project>Integration Services Project

Below is quick peek at the SSIS designer and also look at the different components in the pane.

Tool Box – Tool Box consists of various tasks which are specifically designed to perform some unit of work. Related tasks are grouped into categories.

Examples for Control flow Tasks - Execute SQL Task which is used to execute SQL Queries, Send Mail Task.

Package Execution button- Package Execution tab is used to execute the package from the SSIS Designer.

Connection Manager Pane – Connection manager pane is part or area where we define the connection managers to be used for the Package. The selection of the connection manager purely depends on the requirement. For e.g. if we are pulling the data from flat file(source) and pushing it into the SQL server table(Destination) then we have to define two connection manager a) Flat File Connection Manager for source b)OLEDB Connection Manager for destination. Connection Manager primarily contain the Connection string to the file/database.

Data Flow Task- Data flow Task is where we define the actual flow of the data from source to destination.

Properties- Properties pane allows us to set the properties of package object. For e.g. in the Description tab we can define the job performed by a particular package. It also gives the information like unique ID of package.

Enumerator – Enumerators are one of the control flow task which are used to loop through the object of same kind. For e.g. in the above diagram we have used For Each Loop enumerator which will process the all files in the same folder.

Task- Task is the unit of work that is performed by a package. They are categorized into Control flow and Maintenance Plan Task. Under Control Flow task we have several sub tasks for e.g. File System task can be used to copy files from one location to other. Similarly in the Maintenance Plan Task we have Backup Database task which is used to take the backup of a database.

Solution Explorer Pane – Solution explorer gives the hierarchal view of all the Projects and Data Sources within the solution and also the Packages within the respective Projects.

Control Flow Pane –Here we define our control flow tasks. We can select the Control flow task(as per our requirement) from the tools list, drag it and drop it to the control flow pane. The different tasks in the Control Flow are connected by unidirectional arrows called Precedence constraints.

Data Flow Pane – Data flow pane is the area where we define the actual flow of the data. The three major components involved here are a) Source Component b) Transformations c) Destination component.

Event Handler Pane – Event handler pane is the area where we define what needs to be done on specific events during the execution of package.

For e.g. If we need to intimate the Support Team in case of a failure of package, then we shall define a Send Mail Task in the Event Handler and configure it to be triggered “On Error”(Event).

Package Explorer Pane – Package explorer pane gives the hierarchal view of all the objects in the package like connections, event handlers, variables, log providers etc.

In the coming articles we shall explore some of the Panes in more detail and shall focus on the components involved in them.

Happy Learning !!!

SSIS Architecture

Part 1- Various modes of accessing/executing ETL. E.g. Command Line, SSIS Designer etc.

 

Part 2- It monitors the running of the ETL’s and manages the storage of ETL’s.

 

Part 3- Storage mechanism by which the ETL’s can be stored. There are two major ways of storing(Deploying) the ETL’s : a) In the file system b) In SQL Server MSDB database.

 

Part 4- Used to loop through multiple objects of same kind. E.g. Process multiple files in the same folder OR Iterate through all records in a table.

 

Part 5- Provides(Stores) the physical connectivity information to source and destination. In simple words this object stores the Connection string.

 

Part 6- Provides the option to take some action (handle the event) based on certain events. For e.g. we want to be intimated through an email when ETL fails. So here failure of the ETL can be one event and    notifying certain group through an email is the action.

 

Part 7- Used to move data from a particular source to a particular destination while performing transformations. This is the most important task in SSIS.

 

Part 8-  Various Source of the data(Self Explanatory). It can be in any form like .txt file, Excel file, OLEDB database.

 

Part 9- Used to audit the ETL. For e.g. tacking down all the information like user who executed the ETL, Time of Execution, Error Description etc.

 

Part 10- Task is the unit of work that is that is performed by the ETL. It can be as simple as moving a group of file from one location to other.

 

Part 11- This complete section controls the actual flow of actions to be performed in the ETL process.

 

 

In the coming articles we will be looking to each and every part in more detail and also how to implement/configure them in SQL Server Integration Services.

Partition Functions, Partition Scheme and Partitioned Table

Introduction

We have seen the advantages of Partitions which improves the performance of the query against large volume tables. We discussed in detail about the Range in the Partition. How we do achieve this. It’s time to get our hands dirty digging into the implementation of Partition Functions and Partition schemes.

Partition Functions

Partition function defines the number of partitions on the table. This is the first step in the implementation of a Partition on a database object and a single partition function can be used for multiple objects. The type of partition is also specified in the partition function, which currently can only be 'RANGE'.

Based on the fact about boundary values for partitions that which partition they should belong to, we can divide partition function into two types:

1. Left: The first value is the maximum value of the first partition.
2. Right: The first value is the minimum value of the second partition.
   

Syntax to create a partition function

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

input_parameter_type supported is generally numeric. The list of partition functions in the database can be queried using the sys.partition_functions catalogue view.

The first thing that you would want to do is to test whether your partition function is implemented as per your desire or not. Specially, we can check if it is working on boundary values. You can check it with the special function provided: $Partition. We test the partition function MyPartitionFunc2 created by us earlier. In this SQL, we are verifying to which partition, (Partition Key = 100), would belong to.

Select $PARTITION.MYPARTITIONFUNC2 100 [PARTITION NUMBER]

Partition Scheme

This is the physical storage scheme that will be followed by the partition. To define scheme, different file groups are specified, which would be occupied by each partition. It must be remembered that all partitions may also be defined with only one file group.

After the definition of a partition function, a partition scheme is defined. The partition scheme just like specifying an alignment for data i.e. it specifies the specific file groups used during partitioning an object. Though it is possible to create all partitions on PRIMARY but it would be best if these different partitions are stored in a separate file groups. This gives some performance improvement even in the case of single core computers. It would be best if these file groups are on different discs on a multi core processing machine.

The syntax for creating partition schema is as follows:

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

Partitioned Table

After creation of a partition scheme, a table may be defined to follow that scheme. These index and view may be based on different partition strategy (partition function and partition scheme).Now you might be wondering whether your existing tables could be partitioned or not. For partitioning your existing table just drop the clustered index on your table and recreate it on the required partition scheme.

Syntax to create a table on the Partition scheme.

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ] [ ; ]

To check if the table is partitioned or not ->  Select the table and Right click ->  Properties -> Storage -> check the bit column for the “Table is Partitioned”

We can discuss about Partitioned Indexes in detail in my next  post.

Happy Learning!!!

Partitioning

Now that we have a good learning experience on Slowly changing dimensions (SCD) and how to design  tables as per the requirement to maintain history or not for the important business columns. We now move on to a new feature Introduced in SQL server 2005 and is being used extensively for large volume tables from SQL server 2005 to SQL server 2008.

Introduction

Partitioning is an important feature which helps improve the performance of the queries against a very large table. The reason for this speedy retrieval of the data is that all the rows are not directly stored in the table, but are distributed in different partitions of this table. Due to the presence of the data in different subsets the query results retrieval will be faster on the server.

In this article, we will concentrate on the Range partitions , specifically horizontal range partitions. Based on the range that the value of the partitioned column falls in.

simply saying when we partition a table we are setting a criteria of where  a particular row is being stored and in which portion of the table is the row being stored. How do we set the criteria to fill the table partitions is through the Range that we define. The range is based on a particular column of the table and we call this the partitionkey. We set the range through Partition function and Partition Scheme.

When tables and indexes become very large, partitioning can help by partitioning the data into smaller, more manageable sections. This article focuses on horizontal partitioning, in which large groups of rows will be stored in multiple separate partitions. The definition of the partitioned set is customized, defined, and managed by your needs. Microsoft SQL Server 2008 allows you to partition your tables based on specific data usage patterns using defined ranges or lists. SQL Server 2008 also offers numerous options for the long-term management of partitioned tables and indexes by the addition of features designed around the new table and index structure.

Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations. The performance of large-scale operations across extremely large data sets (for instance many million rows) can benefit by performing multiple operations against individual subsets in parallel. example, instead of aggregating a single large table, SQL Server can work on partitions independently, and then aggregate the aggregates. In SQL Server 2008, queries joining large datasets can benefit directly from partitioning.

Below example simplifies the idea of partitioning a yearly sales table. The data is partitioned horizontally based on the month of the year making the MONTH as the partitionkey.  The range here would be January, February, March…..December.

In next post we shall discuss on implementation of partition using partition functions and partition schemes.

Slowly Changing Dimensions (SCD)

Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information. Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.  

In a transaction system, many a times the change is overwritten and track of change is lost.  However, a data warehouse needs to maintain all the  history as the key benefit of a warehouse is to provide historical information to analyze the trend.

Below SCD Types are the most implemented methods to handle these changing dimensions  in a warehouse. Let’s understand these with an example below

Example:

You have a dimensional table with Customer_ID ' C01' with marital status as 'single' mentioned below. Overtime, customer gets married and also moves to a new location.

Let’s see how this scenario is managed with different SCD types.

Initial Data Record:

Surrogate Key(Surrogate Key)	Customer ID(Natural Key)	Date Valid	Marital Status	Date of Birth	City
100	C01	Jan 23, 2008	Single	Jan8, 1982	Palo Alto

SCD Type1:

The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all. This is obviously done, when we are not analyzing the historical information.

Surrogate Key	Customer ID	Date Valid	Marital Status	Date of Birth	City
100	C01	July 7,2012	Married	Jan8, 1982	Francisco

The record is simple over-written and no history is maintained here.

SCD Type 2:

The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. With Type 2, we have unlimited history preservation as a new record is inserted each time a change is made.

This is implemented using a version column or through  effective date columns to know the active record.


Implemented through a Version Column

Surrogate Key	Customer ID	Date Valid	Marital Status	Date of Birth	City	Version
100	C01	Sept 23, 2004	Single	Jan8, 1982	Palo Alto	0
101	C01	Sept 23, 2004	Married	Jan8, 1982	Francisco	1

Implemented through Effective  Start and End Date Columns

Surrogate Key	Customer ID	Date Valid	Marital Status	Date of Birth	City	Start-Date	End-Date
100	C01	Sept 23, 2004	Single	Jan8, 1982	Palo Alto	01-Sep-2000	23-Sep-2004
101	C01	Sept 23, 2004	Married	Jan8, 1982	Francisco	24-Sep-2004	31-12-9999

A new record is added every time there is a change in the source with the version or Effective Date columns updating accordingly..

SCD Type 3:

The Type 3 method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation, Type 3 has limited history preservation, as it's limited to the number of columns designated for storing historical data and will  have only the recent historical change. Where the original table structure in Type 1 and Type 2 was very similar, Type 3 adds additional columns to the tables

Implemented through additional Original Columns

Surrogate Key	Customer ID	Date Valid	Original Marital Status	Marital Status	Date of Birth	Original City	City
100	C01	Sept 23, 2004	Single	Married	Jan8, 1982	Palo Alto	Francisco

Original Columns have been added to capture the most recent historical change .

Additional SCDs which are occasionally used:

SCD Type 0:

·         The Type 0 method is a passive approach to managing dimension value changes, in which no action is taken.

·          Values remain as they were at the time of the dimension record was first entered.

 

SCD Type 4

·         The Type 4 method is usually referred to as using "history tables", where one table keeps the current data,

·         An additional table is used to keep a record of some or all changes.

 

SCD Type 6 / hybrid:

·         The Type 6 method combines the approaches of types 1, 2 and 3.

·         This method is also called as  "Unpredictable Changes with Single-Version Overlay" in The Data Warehouse

Factless Facts

On the outlook it seems an interesting term but also contradictory.

A confusion arises straightaway; Is it a fact? But the term says factless!!

Before we get to the definition of factless facts, lets quickly revisit the definition of fact tables- “Fact Tables consist of the measurements or metrics of a business process”

Now let’s look into in the definition of Factless Facts and seize the difference.

“Factless fact table is a fact table that does not contain any quantitative measures and are so called because they simply contain keys which refer to the dimension tables”.

(There are some tables that may not contain any straightforward measures like SalesAmount or QuantitySold, however they contain relations between Dimensionskeys and are facts in themselves).

Let us see the below example to ease the understanding:

Consider the scenario where a Project Manager wants to track the details of the leave taken by his team member during a certain period.

                                               

As we can see that there is one fact table(Fact_Employee_Leave_Type) in the centre which mapped to 3 dimensional tables.

The Fact table has no measures but still it provides a detail of employee taking a leave on particular day and its type.

*Tracking the no. of leaves taken by employees is helpful. But the real analysis will based on the type of leaves taken by employee helping the managers/stakeholders to take appropriate decisions.