Business Intelligence (BI) is a procedure of converting raw data into information or knowledge and using that knowledge for better decision making. To perform a BI activity there are three key steps or tasks that are to be carried out. These three steps or tasks are collecting data, analysing data and viewing data. In the data collection stage, one will need to implement the ETL (Extraction, Transformation & Loading) processes and this can be done using the Microsoft SSIS (SQL Server Integration Services) package. Subsequently, analysing data can be done on Microsoft SQL Server Analysis Services (SSAS) and in order to view or present the data; Microsoft SQL Server Reporting Services (SSRS) is a useful software to use.
ETL sometimes called ETT (extraction, transformation, transportation) or ETM (extractions, transformation, move) is a term used to describe the data movement and transformation processes. These processes are used for data integration and migration, key concepts in data warehousing, between data sources and a data warehouse (DW or DWH) [1]. Additionally, the design and implementation of an ETL process is a very important part in the development of a data warehouse. A simple overview would be that ETL presents three database functions that are combined into one tool in order to extract data out of one database and then transfer as well as load it onto another database.
A data warehouse depending on size and scope is a large database system for collection of data in either complete or summary form from many other corporate databases (data sources) [2]. (Read more about data warehousing).
To relate ETL processes to the data warehousing industry, data warehousing is a vital aspect in handling and maintaining business data in businesses with huge databases. Moreover, as most companies nowadays rely on technology to handle, store and maintain their company’s data into a data warehouse and data-marts; ETL is an essential component of a Business Intelligence (BI), used to extract business data from multiple data sources (such as APPs or flat files) and load it into the data warehouse after being clean and integrated.
The ETL approach is not necessarily unique to data warehouse environments as it has been well known for many years. Data sharing can be performed by integrating between two or more applications or systems. This data sharing is typically addressed by mechanisms similar to what we now call ETL process. The ETL processes are designed to be efficient, scalable, and maintainable which makes it a very important part of data warehousing solution [3].
Figure 1 shows a simple over view of how data is being transferred from the data sources into a data warehouse through the ETL process.
These processes can be easily be achieved by tools, controls and features available in Microsoft SQL Server Integration Services.
References:
- Source : Last accessed 18th Oct 2013.
- R. L. Warrender (2003). Databases. Exeter: Crucial. 154.
- Source: Last accessed 18th Oct 2013 5pm.
- Figure 1: ETL overview diagram Source : 21/10/2013 6PM
