ETL

What is ETL?
ETL is acronym for Etract Transform Load

Companies required data to make Strategic decisions.
Data from various sources in various data formats need to store in Data Ware House
with required format which is used to make Business decisions.

Example: I worked with Mobile Company. We have more Stores to sell the mobile connections. Some of them are having online connectivity to the database and
some of them are not having online connectivity.
End of the day we need to populate the data to the DataWare house from Online database and offline Data(MsAccess Tables) to create Reports for top management.

For this we have two type of data sources
1) Online Transaction Database (SQL Server)
2) MS Access table

In SQL Server 2000 DTS was using to Extract, Transform, Load the data.

Extract - the process of reading data from a database
Transform - the process of converting data from one form into another
i.e changing data types and data formats
Load - the process of writing data into the target database

Extraction
Gathering the Data
. Raw data that want to written directly into the disk.
. Data writen to flat files or relational tables from structured source systems.
. Data can be read multiple times, if needed.
Cleansing the Data
. Eliminate duplicates or fragmented data.
. Exclude unwanted/ unneeded information.

Tansformation
Preparing the data to be housed in the data ware house
Converting the extracted data
. Using rules and lookup tables
. Combining data
. Verification / Validity checks
. Standardization

Loading
Storing the transformed data in the data warehouse.
Batch / Real-Time processing.
Can follow Star Schema and Snowflake Schema.


DTS packages extract the data from these sources and transfer the data by transforming into various data formats to the Dataware house.

In SQL Server 2005 we call it as SQL Server Integration Services (SSIS).

Any ETL Architecture must meet the following requirements
• Business Requirements
• Compliance Requirements
• Data Profiling
• Data Security
• Data Integration
• Right Data at the Right Time
• Archiving and Lineage
• Final End-USer Delivery Interfaces
• Available Skills
• Legacy Licenses
• Alignment with overall Enterprise Architecture

Comments

Post a Comment

Popular posts from this blog

Data Warehouse

Image
Importance of Data Warehouse Data received from different sources We may receive the data from various sources it could be in flat files, mdb, SQL Server or Oracle etc... End of the day our business requires to integrate the data from different sources  to take business decisions. It is not simple task to create a single report by adding all different source of data. It can be handled by data ware house. Data ware house collects data from different sources and store the data in Data Marts. Data marts are well designed for Reporting tools. Load on Production System Users who are working the production database always expects the faster results with present required details. If we use Production System data base to generate   'ad-hoc'   eports with huge data, it could impact the performance for users who are working on the applications. Example: When we are going to buy a product from online store, we always expect ...
Read more

SQL Server Integration Services (SSIS)

Image
Integration Services Project Items Package - DTSX file Data Source - DS file Data Source View - DSV file Package A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.                                                 Data Source A data source is a connection reference that you create outside a package. A data source represents a simple connection to a data store, which includes all tables and views in the data store. For more advanced features, such as the a...
Read more

CTE/Temp/Table Variable

Image
CTE:(Common Table Expression) A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. How to use CTE Refer the Microsoft link http://msdn.microsoft.com/en-us/library/ms175972.aspx Good Example from Microsoft on "Recursive Queries Using Common Table Expressions" http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Temp tables  1. The table "exists" - that is, it's materialized as a table, at least in memory, which contains the result set and can be reused. 2. In some cases, performance can be improved or blocking reduced when you have to perfor...
Read more