HomeDISE ReportingReadymade Analytic ModulesTally Business IntelligenceNavision Business IntelligenceData Capture ServicesData CleaningDashboard and Report Design-Data MiningCustomersManagement TeamContact Us

Extract Transform and Load/Data Transformation

ETL process: Extract Transform and Load is the process of transferring data from the source databases to a target database after performing the following set of operations:

·         Extract: this process entails extracting data from single or multiple datasources. The data sources could be unstructured(such as Excel spreadsheets) or structured data as in relational databases. For example your input data sources could be SQL server inputs, Oracle inputs, Excel files, flat files etc.

·         Transform: This process entails the following set of processes:

o   Cleansing the data: this entails removing the test and the redundant data from the input data sources.

o   Transforming the data: this entails performing operations on the input data set so that the output is in the required format. We could perform the following set of operations for executing the same:

§  Choosing a subset of columns from the input source.

§  Joining

§  Aggregation

§  Union

§  A large number of operations on the character data set

§  Generate surrogate keys.

§  Lookups: this entails finding the required values from standalone tables.

§  Validation

§  Case transform: performing multiple ‘ifs’.

·         Load: The data is normally loaded onto the target databases. The target databases could be Operational data stores(in 3rd normal format), Data staging(keeping the copy of the database from input data sources after data cleansing on a single platform), Datawarehouse(could be in 3rd normal or star schema format). When we load the data onto the target databases we need to keep the following into consideration:

o   Update strategy: truncate the tables before loading the database tables or append the targets. Bulk insert is also an option.

o   Parallel processing: in how many parallel threads would an ETL job be executed.

o   Job dependencies: this entails deciding on the order in which the ETL jobs need to be executed. This is normally taken care of by the script in the ETL tool.

Data loading into the Datawarehouse or operational reporting sources: when we load the data into the target databases, we need to consider the following intricacies:

·         Handling type-1 entities: initial and incremental load.

·         Handling type-2 entities: for initial and incremental load.

·         Working with sources with time stamps

·         Working with sources without time stamps.

Our firm has written a paper to handle the above four issues during our ETL projects. For downloading the same, you could click on the following link:

Handling of fact table load: a fact table should contain the surrogate keys from the dimension tables and the measures to be analyzed. In order to load the fact tables we extensively use Look up or outer join functions.

Handling of aggregate table loads: when we need to aggregate the fact table data for a higher level of granularity(say fact table is loaded on a daily basis but the aggregate needs to be on a monthly basis), then we generate aggregate tables. This operation normally entails performing sum, average operations on the measures in the fact table and grouping by dimension entities.

Factless fact: a factless fact table is a table with no measures. It is used in those cases where we need to perform aggregation operations on entities in dimension tables. Say you have a person dimension, you desire to find what is the count of the persons in a particular district. Then one generates a factless fact table that is a copy of the dimension table.

 

Data Services-incremental and Initial Load