ETL Framework for Data Warehouse Environments

Name: ETL Framework for Data Warehouse Environments
Rating: 3.8 (607 reviews)

The non functional ETL requirements

Created bySid Inf

Last updated 2/2021

English

What you'll learn

This course provides a high level approach to implement an ETL framework in typical Data Warehouse environments. This approach can be used for a new application that needs to design and implement ETL solution which is highly reusable with data loading, error handling, audit handling, job scheduling and re-start-ability features. This framework will help reduce time and increase quality due to high re-usability and design standards.
Metadata Categories, learn the commonly used types of metadata in a real time project and how these are different from the Business and Technical viewpoints.
ETL Framework process flow, the process flow and different activities which should be taken care during the ETL framework implementation from file (source data) validations, Exception handling and Audit Control.
Data Sourcing, the different types of Data Sourcing possible in a Data Warehouse environment, different mechanisms in which the data sourcing can happen like the Scheduled events, Change Data Capture, Pub- Sub, Web services/API connectivity and the classification.
Different commonly required/used scripts for Data Sourcing, the different validations required to be performed for Data Sourcing and what functionality to be included in the scripts (shell/bat).
File Validation process, post file validation steps and file validation failure notifications.
Staging Layer, the need for staging layer, Reference Data, Audit columns for Staging and Reference tables, Data retention in the staging layer, partitions and DB standards.
Business Validation Layer, different situations possible during the data processing, concurrent workflow process, partitions in staging and business validation layer.
Data warehouse Layer, Dimension Load, Fact Load types/process, Fact partitions, Fact Summary Load and Source File Management/Archival.
Exception Handling/Error Handling, Data model for exception handling, Error Category, Error Code and different possible solutions for exception handling.
Sample Project Setup, Steps to download the project setup, executing the DDLs for metadata, project explanation and importing the code base into Informatica.
Extending the Operational Metadata’s Data Model for exception handling with additional supporting tables.
Error Handling Data Model, the framework for the data model design.
Using PMREP tables, for exception handling.
Audit, Balance and Control, the need, different technology components involved, table structure and data model, workflow example.
Configuration Management, Software Change Management, Identification, Tracking and Management of all the assets/objects of a project, One of the standard project management processes, the formal way for managing changes of the software and the process for deploying code from development to testing to production.

Course content

17 sections • 76 lectures • 6h 38m total length

Introduction3:15
In this lecture, we start with the need of ETL framework and how is it important for any Data Warehouse project.
What are we getting in to?4:38
A quick run thru of what will be covered in this course at a very high level.
Quick note on the commonly asked questions.5:30
Answers to the commonly asked questions by the participants from other courses (Data Warehouse Concepts, Informatica Developer, Informatica Administrator).
The Architecture which will be used for this course10:27
In this lecture, we will talk about the architecture we will use for this course.
Different stages of the Architecture2:28
Let's take a look at the different stage in this lecture.

Business Metadata3:05
Business metadata includes definitions of data files and attributes in business terms. It may also contain definitions of business rules that apply to these attributes, data owners and stewards, data quality metrics, and similar information that helps business users to navigate the information of the enterprise.
Technical Metadata3:00
Technical metadata is the most common form of metadata. This type of metadata is created and used by the tools and applications that create, manage, and use data.
Operational Metadata3:07
Operational metadata contains information that is available in operational systems and run-time environments. It may contain data file size, date and time of last load, updates, and backups, names of the operational procedures and scripts that have to be used to create, update, restore etc.

What is Data Sourcing?2:11
Sourcing or Extracting or Pulling the data from source systems in the form of flat files, XML files, relational databases, message queues, webservice calls, API's, cloud based connectivity, application connections etc. is called Data Sourcing. In this lecture, we understand the Data Sourcing process.
What are the different events of Data Sourcing?5:20
The different methods/events in which the Data Sourcing can be done are the following

Trigger files --> This event is discussed in detail in this lecture.
Based on Schedule
Change Data Capture (CDC)
Pub/Sub (Publish and Subscribe)
Web Services and API's
Scheduled Events3:39
The different methods/events in which the Data Sourcing can be done are the following

Trigger files
Based on Schedule --> This event is discussed in detail in this lecture.
Change Data Capture (CDC)
Pub/Sub (Publish and Subscribe)
Web Services and API's
CDC - Change Data Capture Events3:07
The different methods/events in which the Data Sourcing can be done are the following

Trigger files
Based on Schedule
Change Data Capture (CDC) --> This event is discussed in detail in this lecture.
Pub/Sub (Publish and Subscribe)
Web Services and API's
Pub - Sub Events2:03
The different methods/events in which the Data Sourcing can be done are the following

Trigger files
Based on Schedule
Change Data Capture (CDC)
Pub/Sub (Publish and Subscribe) --> This event is discussed in detail in this lecture.
Web Services and API's
WebServices/API Events2:02
The different methods/events in which the Data Sourcing can be done are the following

Trigger files
Based on Schedule
Change Data Capture (CDC)
Pub/Sub (Publish and Subscribe)
Web Services and API's --> This event is discussed in detail in this lecture.

File Validation Process4:53
Standard file validations
Physical availability
New/ Non duplicate
Not a ‘0’ byte file
Trailer record count
Record length
File name
Orphan records
Post File Validation Steps4:16
Standard file validations

Physical availability
New/ Non duplicate
Not a ‘0’ byte file
Trailer record count
Record length
File name
Orphan records
File Validation Failure Notifications2:17
Failure Validation

Stakeholder notification
Log file output

What is the need for a Staging layer?4:40
In this lecture, we talk about the need of the staging layer and how it will be useful for the Data Warehouse projects for performing the Data Validation and Business Validations.
What is Reference Data and Should it be stored in the Staging Layer?6:10
Should Reference Data be stored in Staging layer. Let's find out.
Different Real time examples of Reference Data Set up and Usage.4:16
Different examples of the Reference data are discussed in this lecture.
Are Audit columns required for the Staging and Reference Data?2:27
In this lecture, we will see if the Staging layer should have the Audit attributes included or not.
How many days of data should be stored in the staging area?3:56
In this lecture, we talk about the number of days the data should be kept in the Staging layer and how this is related to the Business decision.
Do we need to set up the Partitions based on the Data Retention?1:04
In this lecture, we talk about the best practices for creating the partitions included in the Staging layer.
What kind of DB Standards should be followed for the Staging area setup?1:21
We will quickly review the basic Data base standards to be followed for the Staging layer.

What is the the Business Validation Layer?2:17
In this lecture, we talk about the Business Validation Layer. All the Business Critical Aggregations and Calculations happen in this layer.
Different situations possible during the data processing at this layer4:25
In this lecture, we will talk about the different situations possible for the file loads.
Single source, one location, one landing area
Secured Database connectivity
Secured Web service connectivity
Multiple Source, different locations, same format
Multiple Source, different locations, same format with different landing area
Indirect Data Load Porcess - Using Informatica1:24
In the Informatica PowerCenter, there are two ways to load a flat file into a Target definition:
Direct and Indirect.
We can set the Source File type to ‘Direct’ when loading a single flat file. ‘Indirect’ Source File type is when you need to load multiple flat files.
Concurrent Workflow Process4:49
In large data integration projects, it is quite common to source data from multiple systems, sources, regions etc. As the number of data sources increases, the ETL load time also increases, because of the increasing data volume. One way to reduce the load time is by running different ETL process in parallel. Informatica PowerCenter's capability to run workflow concurrently can be used in such scenarios to reduce the ETL load time.
Partitions in Stage Layer and Business Validation Layer13:39
Are partitions really required for Staging and Business validation layer? Let's find out in this lecture.

Dimension Load10:02
Once the Stage load is complete, Dimension load will be triggered. For each business context, there would be a separate dimension table.
Fact Load Process9:08
Fact Partitions/ Fact Summary Load2:20
Fact Summary Load3:07
Source File Managment/Archival8:13
Source File Management or Archival is another important requirement which is usually ignored during the inception of the project or the requirement scope. This is as important as the data processing requirements. In this lecture, we understand how this will be done.

Requirements

Basic understanding of Data Warehouse Concepts
Basic understanding of ETL Concepts
Basic understanding of SQL commands and RDBMS concepts
Basic understanding of UNIX commands
Basic understanding of Shell/BAT Scripting

Description

This course provides a high level approach to implement an ETL framework in any typical Data Warehouse environments. The practical approaches can be used for a new application that needs to design and implement ETL solution which is highly reusable with different data loading strategies, error/exception handling, audit balance and control handling, a bit of job scheduling and the restartability features and also to any existing ETL implementations. For existing implementations this framework needs to be embedded into the existing environment, jobs and business requirements and it might also go to a level of redesigning the whole mapping/mapplets and the workflows (ETL jobs) from scratch, which is definitely a good decision considering the benefits for the environment with high re-usability and improved design standards.

This course is a combination of standard and practical approaches of designing and implementing a complete ETL solution which details the guidelines, standards, developer/architect checklist and the benefits of the reusable code. And, this course also teaches you the Best practices and standards to be followed in implementing ETL solution.

Though this course, covers the ETL design principles and solutions based on Informatica 10x, Oracle 11g, these can be incorporated to any of the ETL tools in the market like IBM DataStage, Pentaho, Talend, Ab-intio etc.

Multiple reusable code bundles from the marketplace, checklists and the material required to get started on UNIX for basic commands and Shell Scripting will be provided.

Who this course is for:

ETL Developers/Administrators
ETL Testing Professionals
Data Architects and Data Modelers
Data Scientists and Big Data Experts who want to understand the practical Data Warehouse Process
Database Administrators who want to explore the DWH/ETL/BI areas
BI/ETL/DW Technology experts and Team Leaders
Software Engineers who are already part of any Data Warehouse and Business Intelligence Projects
Software Engineers from different technology background who want to explore the Data Warehouse and Business Intelligence development process
Mainframe developers who want to switch their carrier into the Data Warehouse stream
Freshers/Engineering Graduates who are looking for placements
Non IT professionals who like to learn how data is handled in enterprises

ETL Framework for Data Warehouse Environments

What you'll learn

Explore related topics

Course content

Getting Started5 lectures • 26min

Metadata Categories3 lectures • 9min

ETL Framework - Process Flow1 lecture • 8min

Data Sourcing6 lectures • 18min

Data Sourcing - Classification2 lectures • 6min

Script Requirements for Data Sourcing1 lecture • 5min

File Validation3 lectures • 11min

The Staging Layer7 lectures • 24min

Business Validation Layer5 lectures • 27min

DataWarehouse Layer5 lectures • 33min

Requirements

Description

Who this course is for: