Data warehouse
Data Warehouse is a central managed and integrated database containing data from the operational sources in an organization (such as SAP, CRM, ERP system). It may gather manual inputs from users determining criteria and parameters for grouping or classifying records.
That database contains structured data for query analysis and can be accessed by users. The data warehouse can be created or updated at any time, with minimum disruption to operational systems. It is ensured by a strategy implemented in a ETL process.
A source for the data warehouse is a data extract from operational databases. The data is validated, cleansed, transformed and finally aggregated and it becomes ready to be loaded into the data warehouse.
Data warehouse is a dedicated database which contains detailed, stable, non-volatile and consistent data which can be analyzed in the time variant.
Sometimes, where only a portion of detailed data is required, it may be worth considering using a data mart. A data mart is generated from the data warehouse and contains data focused on a given subject and data that is frequently accessed or summarized.
Keeping the data warehouse filled with very detailed and not efficiently selected data may lead to growing the database to a huge size, which may be difficult to manage and unusable. To significantly reduce number of rows in the data warehouse, the data is aggregated which leads to the easier data maintenance and efficiency in browsing and data analysis.
A well designed and maintained Data Warehouse can significantly improve the quality and accessibility of the company data and increase the amount of information delivered to the end users.
# Key Data Warehouse systems and the most widely used database engines for storing and serving data for the enterprise business intelligence and performance management: Teradata
# Oracle
# Microsoft SQL Server
# IBM DB2
# SAS
Sunday, June 29, 2008
Datastage tutorial and training
Datastage tutorial and training
Datastage guide Table of contents:
# Lesson 1. Datastage-modules - the lesson contains an overview of the datastage components and modules with screenshots.
# Lesson 2. Designing jobs - datastage palette - a list of all stages and activities used in Datastage
# Lesson 3. Extracting and loading data - ODBC and ORACLE stages - description and use of the ODBC and ORACLE stages (ORAOCI9) used for data extraction and data load. Covers ODBC input and output links, Oracle update actions and best practices.
# Lesson 4. Extracting and loading data - sequential files - description and use of the sequential files (flat files, text files, CSV files) in datastage.
# Lesson 5. Transforming and filtering data - use of transformers to perform data conversions, mappings, validations and datarefining. Design examples of the most commonly used datastage jobs.
# Lesson 6. Performing lookups in Datastage - how to use hash files and database stages as a lookup source.
# Lesson 7. Implementing ETL process in Datastage - step by step guide on how to implement the ETL process efficiently in Datastage. Contains tips on how to design and run a set of jobs executed on a daily basis.
# Lesson 8. SCD implementation in Datastage - the lesson illustrates how to implement SCD's (slowly changing dimensions) in Datastage, contains job designs, screenshots and sample data. All the Slowly Changing Dimensions types are described in separate articles below:
# SCD Type 1
# SCD Type 2
# SCD Type 3 and 4
# Lesson 9. Datastage jobs real-life solutions - a set of examples of job designs resolving real-life problems implemented in production datawarehouse environments in various companies.
# Lesson 10. Header and trailer file processing - a sample Datastage job which processes a textfile organized in a header and trailer format. This format may be represented for example by mainframe, EDI or EPIC files
Lesson 1:
Datastage modules
The DataStage components:
* Administrator - Administers DataStage projects, manages global settings and interacts with the system. Administrator is used to specify general server defaults, add and delete projects, set up project properties and provides a command interface to the datastage repository.
With Datastage Administrator users can set job monitoring limits, user privileges, job scheduling options and parallel jobs default.
* Manager - it's a main interface to the Datastage Repository, allows its browsing and editing. It displays tables and files layouts, routines, transforms and jobs defined in the project. It is mainly used to store and manage reusable metadata.
* Designer - used to create DataStage jobs which are compiled into executable programs. is a graphical, user-friendly application which applies visual data flow method to develop job flows for extracting, cleansing, transforming, integrating and loading data. It’s a module mainly used by Datastage developers.
* Director - manages running, validating, scheduling and monitoring DataStage jobs. It’s mainly used by operators and testers.
Lesson 3:
Designing jobs - ODBC and ORACLE stages
ODBC stages are used to allow Datastage to connect to any data source that represents the Open Database Connectivity API (ODBC) standard.
ODBC stages are mainly used to extract or load the data. However, ODBC stage may also be very helpful when aggregating data and as a lookup stage (in that case it can play role of aggregator stage or a hash file and can be used instead).
Each ODBC stage can have any number of inputs or outputs.
The input links specify the data which is written to the database (they act as INSERT, UPDATE or DELETE statements in SQL). Input link data can be defined in various ways: using an SQL statement constructed by DataStage, a user-defined SQL query or a stored procedure.
Output links specify the data that are extracted (correspond to the SQL SELECT statement). The data on an output link is passed through ODBC connector and processed by an underlying database.
If a processing target is an Oracle database, it may be worth considering use of ORACLE (ORAOCI9) stage. It has a significantly better performance than ODBC stage and allows setting up more configuration options and parameters native to the Oracle database.
There’s a very useful option to issue an SQL before or after main dataflow operations (Oracle stage properties -> Input -> SQL). For example, when loading a big chunk of data into an oracle table, it may increase performance to drop indexes in a ‘before SQL’ tab and create indexes and analyze table in a ‘after SQL’ tab ('ANALYZE TABLE xxx COMPUTE STATISTICS' SQL statement).
Update actions in Oracle stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Update actions available from the drop-down list:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.
Lesson 4:
Designing jobs - sequential (text) files
Sequential File stages are used to interract with text files which may involve extracting data from and write data to a text file. Sequential File stages can read files, create (overwrite) or append data to a text file. It can be processed on any drive that is local or mapped as a shared folder or even on an FTP server (combined with an FTP stage). Each Sequential File stage can have any number of inputs or outputs. However, trying to write to a sequential file simultaneously from two data streams will surely cause an error.
Lesson 5:
Designing jobs - tranforming and filtering data
It's a very common situation and a good practice to design datastage jobs in which data flow goes in the following way:
EXTRACT SOURCE -> DATA VALIDATION, REFINING, CLEANSING -> MAPPING -> DESTINATION
The data refining, validation and mapping part of the process is mainly handled by a transformer stage. Transformer stage doesn't extract or write data to a target database. It handles extracted data, performs conversions, mappings, validations, passes values and controls the data flow.
Transformer stages can have any number of input and output links. Input links can be primary or reference (used for lookups) and there can only be one primary input and any number of reference inputs.
Datastage guide Table of contents:
# Lesson 1. Datastage-modules - the lesson contains an overview of the datastage components and modules with screenshots.
# Lesson 2. Designing jobs - datastage palette - a list of all stages and activities used in Datastage
# Lesson 3. Extracting and loading data - ODBC and ORACLE stages - description and use of the ODBC and ORACLE stages (ORAOCI9) used for data extraction and data load. Covers ODBC input and output links, Oracle update actions and best practices.
# Lesson 4. Extracting and loading data - sequential files - description and use of the sequential files (flat files, text files, CSV files) in datastage.
# Lesson 5. Transforming and filtering data - use of transformers to perform data conversions, mappings, validations and datarefining. Design examples of the most commonly used datastage jobs.
# Lesson 6. Performing lookups in Datastage - how to use hash files and database stages as a lookup source.
# Lesson 7. Implementing ETL process in Datastage - step by step guide on how to implement the ETL process efficiently in Datastage. Contains tips on how to design and run a set of jobs executed on a daily basis.
# Lesson 8. SCD implementation in Datastage - the lesson illustrates how to implement SCD's (slowly changing dimensions) in Datastage, contains job designs, screenshots and sample data. All the Slowly Changing Dimensions types are described in separate articles below:
# SCD Type 1
# SCD Type 2
# SCD Type 3 and 4
# Lesson 9. Datastage jobs real-life solutions - a set of examples of job designs resolving real-life problems implemented in production datawarehouse environments in various companies.
# Lesson 10. Header and trailer file processing - a sample Datastage job which processes a textfile organized in a header and trailer format. This format may be represented for example by mainframe, EDI or EPIC files
Lesson 1:
Datastage modules
The DataStage components:
* Administrator - Administers DataStage projects, manages global settings and interacts with the system. Administrator is used to specify general server defaults, add and delete projects, set up project properties and provides a command interface to the datastage repository.
With Datastage Administrator users can set job monitoring limits, user privileges, job scheduling options and parallel jobs default.
* Manager - it's a main interface to the Datastage Repository, allows its browsing and editing. It displays tables and files layouts, routines, transforms and jobs defined in the project. It is mainly used to store and manage reusable metadata.
* Designer - used to create DataStage jobs which are compiled into executable programs. is a graphical, user-friendly application which applies visual data flow method to develop job flows for extracting, cleansing, transforming, integrating and loading data. It’s a module mainly used by Datastage developers.
* Director - manages running, validating, scheduling and monitoring DataStage jobs. It’s mainly used by operators and testers.
Lesson 3:
Designing jobs - ODBC and ORACLE stages
ODBC stages are used to allow Datastage to connect to any data source that represents the Open Database Connectivity API (ODBC) standard.
ODBC stages are mainly used to extract or load the data. However, ODBC stage may also be very helpful when aggregating data and as a lookup stage (in that case it can play role of aggregator stage or a hash file and can be used instead).
Each ODBC stage can have any number of inputs or outputs.
The input links specify the data which is written to the database (they act as INSERT, UPDATE or DELETE statements in SQL). Input link data can be defined in various ways: using an SQL statement constructed by DataStage, a user-defined SQL query or a stored procedure.
Output links specify the data that are extracted (correspond to the SQL SELECT statement). The data on an output link is passed through ODBC connector and processed by an underlying database.
If a processing target is an Oracle database, it may be worth considering use of ORACLE (ORAOCI9) stage. It has a significantly better performance than ODBC stage and allows setting up more configuration options and parameters native to the Oracle database.
There’s a very useful option to issue an SQL before or after main dataflow operations (Oracle stage properties -> Input -> SQL). For example, when loading a big chunk of data into an oracle table, it may increase performance to drop indexes in a ‘before SQL’ tab and create indexes and analyze table in a ‘after SQL’ tab ('ANALYZE TABLE xxx COMPUTE STATISTICS' SQL statement).
Update actions in Oracle stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Update actions available from the drop-down list:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.
Lesson 4:
Designing jobs - sequential (text) files
Sequential File stages are used to interract with text files which may involve extracting data from and write data to a text file. Sequential File stages can read files, create (overwrite) or append data to a text file. It can be processed on any drive that is local or mapped as a shared folder or even on an FTP server (combined with an FTP stage). Each Sequential File stage can have any number of inputs or outputs. However, trying to write to a sequential file simultaneously from two data streams will surely cause an error.
Lesson 5:
Designing jobs - tranforming and filtering data
It's a very common situation and a good practice to design datastage jobs in which data flow goes in the following way:
EXTRACT SOURCE -> DATA VALIDATION, REFINING, CLEANSING -> MAPPING -> DESTINATION
The data refining, validation and mapping part of the process is mainly handled by a transformer stage. Transformer stage doesn't extract or write data to a target database. It handles extracted data, performs conversions, mappings, validations, passes values and controls the data flow.
Transformer stages can have any number of input and output links. Input links can be primary or reference (used for lookups) and there can only be one primary input and any number of reference inputs.
ETL process and concepts
ETL process and concepts
ETL stands for extraction, transformation and loading. Etl is a process that involves the following tasks:
* extracting data from source operational or archive systems which are the primary source of data for the data warehouse
* transforming the data - which may involve cleaning, filtering, validating and applying business rules
* loading the data into a data warehouse or any other database or application that houses data
The ETL process is also very often referred to as Data Integration process and ETL tool as a Data Integration platform.
The terms closely related to and managed by ETL processes are: data migration, data management, data cleansing, data synchronization and data consolidation.
The main goal of maintaining an ETL process in an organization is to migrate and transform data from the source OLTP systems to feed a data warehouse and form data marts.
ETL Tools
At present the most popular and widely used ETL tools and applications on the market are:
# IBM Websphere DataStage (Formerly known as Ascential DataStage and Ardent DataStage)
# Informatica PowerCenter
# Oracle Warehouse Builder
# Ab Initio
# Pentaho Data Integration - Kettle Project (open source ETL)
# SAS ETL studio
# Cognos Decisionstream
# Business Objects Data Integrator (BODI)
# Microsoft SQL Server Integration Services (SSIS)
ETL process references
Sample business scenarios with a real-life ETL process implementations, each of which describes a typical data warehousing problem is brought by an ETL training.
A detailed step-by-step instructions on how the ETL process can be implemented in IBM Websphere Datastage can be found in the Implementing ETL in DataStage tutorial lesson.
Etl process can be also successfully implemented using an open source ETL tool - Kettle. There is a separate Pentaho Data Integration section on our pages: Kettle ETL transformations .
ETL and Data Warehouse training with examples
The ETL and Data Warehousing examples section is structured as a number of scenarios, each of which describes a typical business intelligence and data warehousing problem.
You may consider this module as an ETL and Data Warehouse cookbook with a series of recipes which show how to manage and implement the ETL process in a data warehouse environment.
Our intention is to show and analyze the most common real life problems you may encounter when designing ETL processes to feed data warehouses in various organization using a wide range of ETL tools. We discuss and propose an optimal solution for each of the scenarios and show how to organize the data flow process using various ETL applications.
Going through the sample implementations of the business scenarios is also a good way to compare ETL tools and see the different approaches to designing the data integration process. This also gives an idea and helps identify strong and weak points of various ETL tools.
ETL business scenarios
We present a number of sample business scenarios which are very often encountered accross the organizations and need to be addressed. ETL data integration process plays the major role in handling those problems.
Sample ETL process implementations:
# Surrogate key generation example which includes information on business keys and surrogate keys and shows how to design an ETL process to manage surrogate keys in a data warehouse environment. Sample design in Pentaho Data Integration
# Header and trailer processing - considerations on processing files arranged in blocks consisting of a header record, body items and a trailer. This type of files usually come from mainframes, also it applies to EDI and EPIC files. Solution examples in Datastage, SAS and Pentaho Data Integration
# Loading customers - the customers data extract is placed on an FTP server. It needs to be copied to an ETL server and loaded into the data warehouse. The transformation applies certain business rules and implements SCD to deal with the dimension changes. Sample implementation in Teradata MultiLoad
# Site traffic Data Warehouse - a guide for creation of a data warehouse and data marts for website traffic analysis and reporting. Sample design in Pentaho Kettle
# Data Quality Tests - ETL process design aimed to test and cleanse data in a Data Warehouse. Sample outline in PDI
# XML ETL - ETL processes to handle XML files
# Data allocation ETL process case study for allocating data. Sample Cognos implementation
Processing a header and trailer textfile
Goal
Process a text file which contains records arranged in blocks consisting of a header record, details (items, body) and a trailer. The aim is to normalize records and load them into a relational database structure.
Scenario overview and details
Typically, header and item processing needs to be implemented when processing files that origin from mainframe systems, and also EDI transmission files, SWIFT Schemas, EPIC files.
The input file in our scenario is a stream of records representing invoices. Each invoice consists of :
- A header containing invoice number, dates, customer reference and other details
- One or more items representing ordered products, including an item number, quantity, price and value
- A trailer which contains summary information for all the items
The records are distinguished by the first character in each line: H stands for headers, I for items and T for the trailers.
The lines in a section of the input file are fixed length (so for example all headers have the same number of characters but it varies from items).
Input data:
# Text file in a Header-trailer format. The file presented in our example is divided into headers, items and trailers.
- A header starts with a letter H and then contains an invoice number, a customer number, a date and invoice currency
- Every item starts with a letter I which is followed by product ID, product age, quantity and net value
- The trailer starts with a T and contains two values which act as a checksum: the total number of invoice lines and a total net value.
# Output data:
# INVC_HEADER and INVC_LINE relational tables: one with invoice headers and the other with the invoice lines
# rejects_080629.txt - a text file with loading errors desired output
Generate surrogate key
Goal
Fill in a data warehouse dimension table with data which comes from different source systems and assign a unique record identifier (surrogate key) to each record.
Scenario overview and details
To illustrate this example, we will use two made up sources of information to provide data about customers dimension. Each extract contains customer records with a business key (natural key) assigned to it.
In order to isolate the data warehouse from source systems, we will introduce a technical surrogate key instead of re-using the source system's natural (business) key.
A unique and common surrogate key is a one-field numeric key which is shorter, easier to maintain and understand, and independent from changes in source system than using a business key. Also, if a surrogate key generation process is implemented correctly, adding a new source system to the data warehouse processing will not require major efforts.
Surrogate key generation mechanism may vary depending on the requirements, however the inputs and outputs usually fit into the design shown below:
Inputs:
- an input respresented by an extract from the source system
- datawarehouse table reference for identifying the existing records
- maximum key lookup
Outputs:
- output table or file with newly assigned surrogate keys
- new maximum key
- updated reference table with new records
Proposed solution
Assumptions:
- The surrogate key field for our made up example is WH_CUST_NO.
- To make the example clearer, we will use SCD 1 to handle changing dimensions. This means that new records overwrite the existing data.
The ETL process implementation requires several inputs and outputs.
Input data:
- customers_extract.csv - first source system extract
- customers2.txt - second source system extract
- CUST_REF - a lookup table which contains mapping between natural keys and surrogate keys
- MAX_KEY - a sequence number which represents last key assignment
Output data:
- D_CUSTOMER - table with new records and correctly associated surrogate keys
- CUST_REF - new mappings added
- MAX_KEY sequence increased
The design of an ETL process for generating surrogate keys will be as follows:
# The loading process will be executed twice - once for each of the input files
# Check if the lookup reference data is correct and available:
- PROD_REF table
- max_key sequence
# Read the extract and first check if a record already exists. If it does, assign an existing surrogate key to it and update the desciptive data in the main dimension table.
# If it is a new record, then:
- populate a new surrogate key and assign it to the record. The new key will be populated by incrementing the old maximum key by 1.
- insert a new record into the products table
- insert a new record into the mapping table (which stores business and surrogate keys mapping)
- update the new maximum key
ETL stands for extraction, transformation and loading. Etl is a process that involves the following tasks:
* extracting data from source operational or archive systems which are the primary source of data for the data warehouse
* transforming the data - which may involve cleaning, filtering, validating and applying business rules
* loading the data into a data warehouse or any other database or application that houses data
The ETL process is also very often referred to as Data Integration process and ETL tool as a Data Integration platform.
The terms closely related to and managed by ETL processes are: data migration, data management, data cleansing, data synchronization and data consolidation.
The main goal of maintaining an ETL process in an organization is to migrate and transform data from the source OLTP systems to feed a data warehouse and form data marts.
ETL Tools
At present the most popular and widely used ETL tools and applications on the market are:
# IBM Websphere DataStage (Formerly known as Ascential DataStage and Ardent DataStage)
# Informatica PowerCenter
# Oracle Warehouse Builder
# Ab Initio
# Pentaho Data Integration - Kettle Project (open source ETL)
# SAS ETL studio
# Cognos Decisionstream
# Business Objects Data Integrator (BODI)
# Microsoft SQL Server Integration Services (SSIS)
ETL process references
Sample business scenarios with a real-life ETL process implementations, each of which describes a typical data warehousing problem is brought by an ETL training.
A detailed step-by-step instructions on how the ETL process can be implemented in IBM Websphere Datastage can be found in the Implementing ETL in DataStage tutorial lesson.
Etl process can be also successfully implemented using an open source ETL tool - Kettle. There is a separate Pentaho Data Integration section on our pages: Kettle ETL transformations .
ETL and Data Warehouse training with examples
The ETL and Data Warehousing examples section is structured as a number of scenarios, each of which describes a typical business intelligence and data warehousing problem.
You may consider this module as an ETL and Data Warehouse cookbook with a series of recipes which show how to manage and implement the ETL process in a data warehouse environment.
Our intention is to show and analyze the most common real life problems you may encounter when designing ETL processes to feed data warehouses in various organization using a wide range of ETL tools. We discuss and propose an optimal solution for each of the scenarios and show how to organize the data flow process using various ETL applications.
Going through the sample implementations of the business scenarios is also a good way to compare ETL tools and see the different approaches to designing the data integration process. This also gives an idea and helps identify strong and weak points of various ETL tools.
ETL business scenarios
We present a number of sample business scenarios which are very often encountered accross the organizations and need to be addressed. ETL data integration process plays the major role in handling those problems.
Sample ETL process implementations:
# Surrogate key generation example which includes information on business keys and surrogate keys and shows how to design an ETL process to manage surrogate keys in a data warehouse environment. Sample design in Pentaho Data Integration
# Header and trailer processing - considerations on processing files arranged in blocks consisting of a header record, body items and a trailer. This type of files usually come from mainframes, also it applies to EDI and EPIC files. Solution examples in Datastage, SAS and Pentaho Data Integration
# Loading customers - the customers data extract is placed on an FTP server. It needs to be copied to an ETL server and loaded into the data warehouse. The transformation applies certain business rules and implements SCD to deal with the dimension changes. Sample implementation in Teradata MultiLoad
# Site traffic Data Warehouse - a guide for creation of a data warehouse and data marts for website traffic analysis and reporting. Sample design in Pentaho Kettle
# Data Quality Tests - ETL process design aimed to test and cleanse data in a Data Warehouse. Sample outline in PDI
# XML ETL - ETL processes to handle XML files
# Data allocation ETL process case study for allocating data. Sample Cognos implementation
Processing a header and trailer textfile
Goal
Process a text file which contains records arranged in blocks consisting of a header record, details (items, body) and a trailer. The aim is to normalize records and load them into a relational database structure.
Scenario overview and details
Typically, header and item processing needs to be implemented when processing files that origin from mainframe systems, and also EDI transmission files, SWIFT Schemas, EPIC files.
The input file in our scenario is a stream of records representing invoices. Each invoice consists of :
- A header containing invoice number, dates, customer reference and other details
- One or more items representing ordered products, including an item number, quantity, price and value
- A trailer which contains summary information for all the items
The records are distinguished by the first character in each line: H stands for headers, I for items and T for the trailers.
The lines in a section of the input file are fixed length (so for example all headers have the same number of characters but it varies from items).
Input data:
# Text file in a Header-trailer format. The file presented in our example is divided into headers, items and trailers.
- A header starts with a letter H and then contains an invoice number, a customer number, a date and invoice currency
- Every item starts with a letter I which is followed by product ID, product age, quantity and net value
- The trailer starts with a T and contains two values which act as a checksum: the total number of invoice lines and a total net value.
# Output data:
# INVC_HEADER and INVC_LINE relational tables: one with invoice headers and the other with the invoice lines
# rejects_080629.txt - a text file with loading errors desired output
Generate surrogate key
Goal
Fill in a data warehouse dimension table with data which comes from different source systems and assign a unique record identifier (surrogate key) to each record.
Scenario overview and details
To illustrate this example, we will use two made up sources of information to provide data about customers dimension. Each extract contains customer records with a business key (natural key) assigned to it.
In order to isolate the data warehouse from source systems, we will introduce a technical surrogate key instead of re-using the source system's natural (business) key.
A unique and common surrogate key is a one-field numeric key which is shorter, easier to maintain and understand, and independent from changes in source system than using a business key. Also, if a surrogate key generation process is implemented correctly, adding a new source system to the data warehouse processing will not require major efforts.
Surrogate key generation mechanism may vary depending on the requirements, however the inputs and outputs usually fit into the design shown below:
Inputs:
- an input respresented by an extract from the source system
- datawarehouse table reference for identifying the existing records
- maximum key lookup
Outputs:
- output table or file with newly assigned surrogate keys
- new maximum key
- updated reference table with new records
Proposed solution
Assumptions:
- The surrogate key field for our made up example is WH_CUST_NO.
- To make the example clearer, we will use SCD 1 to handle changing dimensions. This means that new records overwrite the existing data.
The ETL process implementation requires several inputs and outputs.
Input data:
- customers_extract.csv - first source system extract
- customers2.txt - second source system extract
- CUST_REF - a lookup table which contains mapping between natural keys and surrogate keys
- MAX_KEY - a sequence number which represents last key assignment
Output data:
- D_CUSTOMER - table with new records and correctly associated surrogate keys
- CUST_REF - new mappings added
- MAX_KEY sequence increased
The design of an ETL process for generating surrogate keys will be as follows:
# The loading process will be executed twice - once for each of the input files
# Check if the lookup reference data is correct and available:
- PROD_REF table
- max_key sequence
# Read the extract and first check if a record already exists. If it does, assign an existing surrogate key to it and update the desciptive data in the main dimension table.
# If it is a new record, then:
- populate a new surrogate key and assign it to the record. The new key will be populated by incrementing the old maximum key by 1.
- insert a new record into the products table
- insert a new record into the mapping table (which stores business and surrogate keys mapping)
- update the new maximum key
What is meant by Business Intelligence
What is Business Intelligence?
Business intelligence is a broad set of applications, technologies and knowledge for gathering and analyzing data for the purpose of helping users make better business decisions.
The main challenge of Business Intelligence is to gather and serve organized information regarding all relevant factors that drive the business and enable end-users to access that knowledge easily and efficiently and in effect maximize the success of an organization.
Business intelligence produces analysis and provides in depth knowledge about performance indicators such as company's customers, competitors, business counterparts, economic environment and internal operations to help making effective and good quality business decisions.
From a technical standpoint, the most important areas that Business Intelligence covers are:
DW - Data warehouses - architecture, design, managing, processing and loading (including the ETL process)
Data cleansing and data quality management
Data mining, statistical analysis, forecasting
OLAP - Online Analytical Processing and multidimensional analysis
MIS - Management Information Systems
Reporting - Information visualization and Dashboards
CRM - Customer Relationship Management
DSS - Decision Support Systems
Etl Tools Info portal
ETL-Tools.Info portal provides information about different business intelligence tools and datawarehousing solutions, with a main focus on ETL process and tools. On our pages you will find overview and general, high-level information on various Business Intelligence applications and architectures, as well as technical documents, with a detailed and low-level description of the solutions.
A great attention is paid to the Datastage ETL tool and on the portal you will find a number of Datastage examples, Datastage tutorials, best practices and resolved problems with many real-life examples.
There's also a wide range of information on a rapidly growing Open Source Business Intelligence market (OSBI), with emphasis on applications from the Pentaho BI family with a Pentaho tutorial.
Recently we also added a new section - the SAS Guide with tutorial, which illustrates the vision of SAS on Business Intelligence.
Business intelligence is a broad set of applications, technologies and knowledge for gathering and analyzing data for the purpose of helping users make better business decisions.
The main challenge of Business Intelligence is to gather and serve organized information regarding all relevant factors that drive the business and enable end-users to access that knowledge easily and efficiently and in effect maximize the success of an organization.
Business intelligence produces analysis and provides in depth knowledge about performance indicators such as company's customers, competitors, business counterparts, economic environment and internal operations to help making effective and good quality business decisions.
From a technical standpoint, the most important areas that Business Intelligence covers are:
DW - Data warehouses - architecture, design, managing, processing and loading (including the ETL process)
Data cleansing and data quality management
Data mining, statistical analysis, forecasting
OLAP - Online Analytical Processing and multidimensional analysis
MIS - Management Information Systems
Reporting - Information visualization and Dashboards
CRM - Customer Relationship Management
DSS - Decision Support Systems
Etl Tools Info portal
ETL-Tools.Info portal provides information about different business intelligence tools and datawarehousing solutions, with a main focus on ETL process and tools. On our pages you will find overview and general, high-level information on various Business Intelligence applications and architectures, as well as technical documents, with a detailed and low-level description of the solutions.
A great attention is paid to the Datastage ETL tool and on the portal you will find a number of Datastage examples, Datastage tutorials, best practices and resolved problems with many real-life examples.
There's also a wide range of information on a rapidly growing Open Source Business Intelligence market (OSBI), with emphasis on applications from the Pentaho BI family with a Pentaho tutorial.
Recently we also added a new section - the SAS Guide with tutorial, which illustrates the vision of SAS on Business Intelligence.
Tutorals for datastage
1. Create custom operators for WebSphere DataStage
This is my favourite tutorial because this is a misunderstood and underutilised part of the product. It’s written by Blayne Chard a software engineer from IBM. Unlike the other tutorials on this page this one requires an IBM ID to access. If you are like me it usually a few goes before you can remember your login!
In this tutorial you learn:
1. How to write a simple DataStage operator
2. How to set up the development environment to compile and run a DataStage operator
3. The basics of the Orchestrate Shell (OSH) scripting language for DataStage jobs
4. How to load your operator into the DataStage Designer so you can use it on any job you create
The DataStage Transformer lets you call a custom routine instead of the standard list of functions on the right mouse click menu. This lets you take code that looks complex in the Transformer and put it into a module to simplify testing and re-use.
Custom routines used to be huge in DataStage, I’ve been to sites with hundreds of them, but they were for DataStage Server Edition. This edition supported routines written in Universe BASIC via a routine editor in the DataStage Manager tool. They were easy to write and easy to test and easy to re-use between jobs.
DataStage Enterprise Edition made things a bit harder. Custom routines are c++ components built and compiled external to DataStage. There was a lot more fiddling to get the routines recognised by a DataStage transformer and c++ tends to be a more challenging language than BASIC.
This is a comprehensive tutorial taking you through the steps of creating and using a routine. Fantastic.
compile your operator
Compiling your operator is a straight forward process once the environment has been setup. To compile the operator, make sure that you have run setup.bat in the current command window, then type the following commands.
2. Modify Stage Video Tutorial
The Modify Stage is a tricky parallel stage that converts fields from one metadata definition to another. It does not have any of the fancy GUI help of the Transformer such as right mouse click menus or syntax checking. You need to look up the functions in the Parallel Job Developers Guide, guess what the syntax is and use trial and error half a dozen times until you get it working!
This tutorial saves you a lot of the trial and error.
The tutorial does require premium membership to the dsxchange at US$99 a year but if it saves you a couple days of work that should be enough for your employer to splash out for membership.
3. WebSphere DataStage Parallel Job Tutorial Version 8
The mother of all DataStage tutorials - this is the one that comes on the Information Server installation CDs (or is that DVDs? Or BlueRays?) It’s available from the IBM publications centre and you can download it free of charge as a 1.05M PDF file.
The IBM WebSphere DataStage Parallel Job Tutorial demonstrates how to design and run IBM WebSphere DataStage� parallel jobs.
The exercises, sample data, and sample import files for the parallel job tutorial are in the TutorialData’DataStage’parallel_tutorial folder on the suite CD, DVD, or downloaded installation image.
It’s a simple tutorial covering opening and running a job, designing a job, using the Transformer, loading a target table and processing in parallel. Not a lot of interest to experienced DataStage users but useful to a newcomer.
4. Configure DB2 remote connectivity with WebSphere DataStage Enterprise Edition
I also reviewed this tutorial previously in DataStage Tip: Extracting database data 250% faster.
As with XML the DB2 connectivity can be a tricky beast to get running. Remote DB2 connections are even trickier. This tutorial will save you a lot of trouble and swearing as you get the wee DB2 beastie running.
5. Transform and integrate data using WebSphere DataStage XML and Web services packs
Getting started with XML processing in DataStage usually involves a lot of fumbling around before you even get a job running. By the end of it you are usually happy to never see another XML file as long as you live. The trick is that DataStage needs to read and validate the entire XML document before it starts processing any part of it as it needs to know how to flatten it into relational data. This is different to database and sequential file sources where DataStage reads and validates one row at a time.
As you can imagine this initial read and validation of the entire file puts a limit on what DataStage can handle in terms of volume. It can process a very large number of small XML files but not one very large XML file. The platform will eventually hit a RAM or resource limit.
This tutorial is invaluable for getting started with XML files in DataStage covering importing XML metadata and using it as a source or target.
DataStage XML Job Example
6. A flexible data integration architecture using WebSphere DataStage and WebSphere Federation Server
IBM wants us to combine the different products on the Information Server for a best of breed data integration platform. One of the themes of the new analytical warehouse concepts such as the IBM Dynamic Warehouse is the use of unstructured or semi structured text. DataStage and the Federation Server are a good combination for bringing this into a warehouse.
IBM are calling this a T-ETL architecture where the Federation server is a data pre-processor.
The T-ETL architecture proposed uses federation to join, aggregate, and filter data before it enters WebSphere DataStage, with WebSphere DataStage using its parallel engine to perform more complex transformations and the maintenance of the target.
Federation DataStage Job Example
This is a generous tutorial with a lot of use cases for T-ETL, ETL and ELT.
The strength of WebSphere DataStage as a data consolidation tool lies in its flexibility -- it is able to support the many different consolidation scenarios and flavors of ETL, including ETL, ELT, and TEL (against a single data source). Combining WebSphere DataStage with WebSphere Federation Server opens up a whole new area of consolidation scenarios, namely T-ETL, against homogenous or heterogeneous data sources.
7. Access application data using WebSphere Federation Server and WebSphere DataStage
I reviewed this tutorial in a previous blog post: DataStage Tip: Free IBM DataStage SAP Tutorial
As with the last tutorial it uses a combination of DataStage and the Federation Server to access atypical enterprise data, in this case SAP.
This is my favourite tutorial because this is a misunderstood and underutilised part of the product. It’s written by Blayne Chard a software engineer from IBM. Unlike the other tutorials on this page this one requires an IBM ID to access. If you are like me it usually a few goes before you can remember your login!
In this tutorial you learn:
1. How to write a simple DataStage operator
2. How to set up the development environment to compile and run a DataStage operator
3. The basics of the Orchestrate Shell (OSH) scripting language for DataStage jobs
4. How to load your operator into the DataStage Designer so you can use it on any job you create
The DataStage Transformer lets you call a custom routine instead of the standard list of functions on the right mouse click menu. This lets you take code that looks complex in the Transformer and put it into a module to simplify testing and re-use.
Custom routines used to be huge in DataStage, I’ve been to sites with hundreds of them, but they were for DataStage Server Edition. This edition supported routines written in Universe BASIC via a routine editor in the DataStage Manager tool. They were easy to write and easy to test and easy to re-use between jobs.
DataStage Enterprise Edition made things a bit harder. Custom routines are c++ components built and compiled external to DataStage. There was a lot more fiddling to get the routines recognised by a DataStage transformer and c++ tends to be a more challenging language than BASIC.
This is a comprehensive tutorial taking you through the steps of creating and using a routine. Fantastic.
compile your operator
Compiling your operator is a straight forward process once the environment has been setup. To compile the operator, make sure that you have run setup.bat in the current command window, then type the following commands.
2. Modify Stage Video Tutorial
The Modify Stage is a tricky parallel stage that converts fields from one metadata definition to another. It does not have any of the fancy GUI help of the Transformer such as right mouse click menus or syntax checking. You need to look up the functions in the Parallel Job Developers Guide, guess what the syntax is and use trial and error half a dozen times until you get it working!
This tutorial saves you a lot of the trial and error.
The tutorial does require premium membership to the dsxchange at US$99 a year but if it saves you a couple days of work that should be enough for your employer to splash out for membership.
3. WebSphere DataStage Parallel Job Tutorial Version 8
The mother of all DataStage tutorials - this is the one that comes on the Information Server installation CDs (or is that DVDs? Or BlueRays?) It’s available from the IBM publications centre and you can download it free of charge as a 1.05M PDF file.
The IBM WebSphere DataStage Parallel Job Tutorial demonstrates how to design and run IBM WebSphere DataStage� parallel jobs.
The exercises, sample data, and sample import files for the parallel job tutorial are in the TutorialData’DataStage’parallel_tutorial folder on the suite CD, DVD, or downloaded installation image.
It’s a simple tutorial covering opening and running a job, designing a job, using the Transformer, loading a target table and processing in parallel. Not a lot of interest to experienced DataStage users but useful to a newcomer.
4. Configure DB2 remote connectivity with WebSphere DataStage Enterprise Edition
I also reviewed this tutorial previously in DataStage Tip: Extracting database data 250% faster.
As with XML the DB2 connectivity can be a tricky beast to get running. Remote DB2 connections are even trickier. This tutorial will save you a lot of trouble and swearing as you get the wee DB2 beastie running.
5. Transform and integrate data using WebSphere DataStage XML and Web services packs
Getting started with XML processing in DataStage usually involves a lot of fumbling around before you even get a job running. By the end of it you are usually happy to never see another XML file as long as you live. The trick is that DataStage needs to read and validate the entire XML document before it starts processing any part of it as it needs to know how to flatten it into relational data. This is different to database and sequential file sources where DataStage reads and validates one row at a time.
As you can imagine this initial read and validation of the entire file puts a limit on what DataStage can handle in terms of volume. It can process a very large number of small XML files but not one very large XML file. The platform will eventually hit a RAM or resource limit.
This tutorial is invaluable for getting started with XML files in DataStage covering importing XML metadata and using it as a source or target.
DataStage XML Job Example
6. A flexible data integration architecture using WebSphere DataStage and WebSphere Federation Server
IBM wants us to combine the different products on the Information Server for a best of breed data integration platform. One of the themes of the new analytical warehouse concepts such as the IBM Dynamic Warehouse is the use of unstructured or semi structured text. DataStage and the Federation Server are a good combination for bringing this into a warehouse.
IBM are calling this a T-ETL architecture where the Federation server is a data pre-processor.
The T-ETL architecture proposed uses federation to join, aggregate, and filter data before it enters WebSphere DataStage, with WebSphere DataStage using its parallel engine to perform more complex transformations and the maintenance of the target.
Federation DataStage Job Example
This is a generous tutorial with a lot of use cases for T-ETL, ETL and ELT.
The strength of WebSphere DataStage as a data consolidation tool lies in its flexibility -- it is able to support the many different consolidation scenarios and flavors of ETL, including ETL, ELT, and TEL (against a single data source). Combining WebSphere DataStage with WebSphere Federation Server opens up a whole new area of consolidation scenarios, namely T-ETL, against homogenous or heterogeneous data sources.
7. Access application data using WebSphere Federation Server and WebSphere DataStage
I reviewed this tutorial in a previous blog post: DataStage Tip: Free IBM DataStage SAP Tutorial
As with the last tutorial it uses a combination of DataStage and the Federation Server to access atypical enterprise data, in this case SAP.
The Top 7 Online DataStage Tutorials
These are the top 7 online DataStage Tutorials for learning DataStage, becoming an expert or studying for DataStage certification.
IBM has been putting new content into the crevices and caves of the ibm.com website for some months now and some trawling has turned up half a dozen handy tutorials.
Here is the top 6 DataStage tutorials, if you keep reading you will see a review of each tutorial and why you would need it:
1. Create custom operators for WebSphere DataStage
2. Modify Stage Video Tutorial (requires dsxchange premium membership)
3. WebSphere DataStage Parallel Job Tutorial Version 8
4. Configure DB2 remote connectivity with WebSphere DataStage Enterprise Edition
5. Transform and integrate data using WebSphere DataStage XML and Web services packs
6. A flexible data integration architecture using WebSphere DataStage and WebSphere Federation Server
7. Access application data using WebSphere Federation Server and WebSphere DataStage
IBM has been putting new content into the crevices and caves of the ibm.com website for some months now and some trawling has turned up half a dozen handy tutorials.
Here is the top 6 DataStage tutorials, if you keep reading you will see a review of each tutorial and why you would need it:
1. Create custom operators for WebSphere DataStage
2. Modify Stage Video Tutorial (requires dsxchange premium membership)
3. WebSphere DataStage Parallel Job Tutorial Version 8
4. Configure DB2 remote connectivity with WebSphere DataStage Enterprise Edition
5. Transform and integrate data using WebSphere DataStage XML and Web services packs
6. A flexible data integration architecture using WebSphere DataStage and WebSphere Federation Server
7. Access application data using WebSphere Federation Server and WebSphere DataStage
Study Guide for DataStage Certification
This entry is a comprehensive guide to preparing for the DataStage 7.5 Certification exam.
Regular readers may be feeling a sense of de ja vu. Haven’t we seen this post before? I originally posted this in 2006 and this the Directors Cut - I’ve added some deleted scenes, a commentary for DataStage 7.0 and 8.0 users and generally improved the entry. By reposting I retain the links from other sites such as my DataStage Certification Squidoo lens with links to my certification blog entries and IBM certification pages.
This post shows all of the headings from the IBM exam Objectives and describes how to prepare for that section.
Before you start read work out how you add Environment variables to a job as a job parameter as they are handy for exercises and testing. See the DataStage Designer Guide for details.
Section 1 - Installation and Configuration
Versions: Version 7.5.1 and 7.5.2 are the best to study and run exercises on. Version 6.x is risky but mostly the same as 7. Version 8.0 is no good for any type of installation and configuration preparation as it has a new approach to installation and user security.
Reading: Read the Installation and Upgrade Guide for DataStage, especially the section on parallel installation. Read the pre-requisites for each type of install such as users and groups, the compiler, project locations, kernel settings for each platform. Make sure you know what goes into the dsenv file. Read the section on DataStage for USS as you might get one USS question. Do a search for threads on dsenv on the dsxchange forum to become familiar with how this file is used in different production environments.
Exercise: installing your own DataStage Server Enterprise Edition is the best exercise - getting it to connect to Oracle, DB2 and SQL Server is also beneficial. Run the DataStage Administrator and create some users and roles and give them access to DataStage functions.
Section 4 - Parallel Architecture (10%)
Section 9 - Monitoring and Troubleshooting (10%)
I’ve move section 4 and 9 up to the front as you need to study this before you run exercises and read about parallel stages in the other sections. Understanding how to use and monitor parallel jobs is worth a whopping 20% so it’s a good one to know well.
Versions: you can study this using DataStage 6, 7 and 8. Version 8 has the best definition of the parallel architecture with better diagrams.
Reading: Parallel Job Developers Guide opening chapters on what the parallel engine and job partitioning is all about. Read about each partitioning type. Read how sequential file stages partition or repartition data and why datasets don’t. The Parallel Job Advanced Developers Guide has sections on environment variables to help with job monitoring, read about every parameter with the word SCORE in it. The DataStage Director Guide describes how to run job monitoring - use the right mouse click menu on the job monitor window to see extra parallel information.
Exercises: Turn on various monitoring environment variables such as APT_PM_SHOW_PIDS and APT_DUMP_SCORE so you can see what happens during your exercises. It shows you what really runs in a job - the extra processes that get added across parallel nodes.
Try creating one node, two node and four node config files and see how jobs behave under each one. Try the remaining exercises on a couple different configurations by adding the configuration environment variable to the job. Try some pooling options. I got to admit I guessed my way through some of the pooling questions as I didn’t do many exercises.
Generate a set of rows into a sequential file for testing out various partitioning types. One column with unique ids 1 to 100 and a second column with repeating codes such as A, A, A, A, A, B, B, B, B, B etc. Write a job that reads from the input, sends it through a partitioning stage such as a transformer and writes it to a peek stage. The Director logs shows which rows went where. You should also view the Director monitor and expand and show the row counts on each instance of each stage in the job to see how stages are split and run on each node and how many rows each instance gets.
Use a filter stage to split the rows down two paths and bring them back together with a funnel stage, then replace the funnel with a collector stage. Compare the two.
Test yourself on estimating how many processes will be created by a job and check the result after the job has run using the Director monitor or log messages. Do this throughout all your exercises across all sections as a habit.
Section 2 - Metadata
Section 3 - Persistant Storage
I’ve merged these into one. Both sections talk about sequential files, datasets, XML files and Cobol files.
Versions: you can study this on DataStage 6, 7 or 8 as it is a narrow focus on DataStage Designer metadata. DataStage 8 will have slightly different CFF options but not enough to cause a problem.
Reading: read the section in the DataStage Developers Guide on Orchestrate schemas and partial schemas. Read the plugin guide for the Complex Flat File Stage to understand how Cobol metadata is imported (if you don’t have any cobol copybooks around you will just have to read about them and not do any exercises). Quickly scan through the NLS guide - but don’t expect any hard questions on this.
Exercises: Step through the IBM XML tutorial to get the tricky part on reading XML files. Find an XML file and do various exercises reading it and writing it to a sequential file. Switch between different key fields to see the impact of the key on the flattening of the XML hierarchy. Don’t worry too much about XML Transform.
Import from a database using the Orchestrate Import and the Plugin Import and compare the table definitions. Run an exercise on column propagation using the Peek stage where a partial schema is written to the Peek stage to reveal the propagated columns.
Create a job using the Row Generator stage. Define some columns, on the columns tab doubleclick on a column to bring up the advanced column properties. Use some properties to generate values for different data types. Get to know the advanced properties page.
Create a really large sequential file, dataset and fileset and use each as a reference in a lookup stage. Monitor the Resource and Scratch directories as the job runs to see how these lookup sources are prepared prior to and during a job run. Get to know the difference between the lookup fileset and other sources for a lookup stage.
Section 5 - Databases (15%)
One of the more difficult topics if you get questions for a database you are not familiar with. I got one database parallel connectivity question that I still can’t find the answer to in any of the manuals.
Versions: DataStage 7.x any version or at a pinch DataStage 8. Earlier versions do not have enough database stages and DataStage 8 has a new approach to database connections.
Reading: read the plugin guide for each enterprise database stage: Oracle, SQL Server, DB2 and ODBC. In version 8 read the improved Connectivity Guides for these targets. If you have time you can dig deeper, the Parallel Job Developers Guide and/or the Advanced Developers Guide has a section on the Oracle/DB2/Informix/Teradata/Sybase/SQL Server interface libraries. Look for the section called "Operator action" and read it for each stage. It’s got interesting bits like whether the stage can run in parallel, how it converts data and handles record sizes.
Exercise: Add each Enterprise database stage to a parallel job as both an input and output stage. Go in and fiddle around with all the different types of read and write options. You don’t need to get a connection working or have access to that database, you just need to have the stage installed and add it to your job. Look at the differences between insert/update/add/load etc. Look at the different options for each database stage. If you have time and a database try some loads to a database table.
Section 6 - Data Transformation (15%)
If you’ve used DataStage for longer than a year this is probably the topic you are going to ace - as long as you have done some type of stage variable use.
Versions: should be okay studying on versions 6, 7 or 8. Transformation stages such as Transformer, Filter and Modify have not changed much.
Reading: there is more value in using the transformation stages than reading about it. It’s hard to read about it and take it in as the Transformer stage is easier to navigate and understand if you are using it. If you have to make do with reading then visit the dsxchange and look for threads on stage variables, the FAQ on the parallel number generator, removing duplicates using a transformer and questions in the parallel forum on null handling. This will be better than reading the manuals as they will be full of practical examples. Read the Parallel Job Advanced Developers Guide section on "Specifying your own parallel stages".
Exercises: Focus on Transformer, Modify Stage (briefly), Copy Stage (briefly) and Filter Stage.
Create some mixed up source data with duplicate rows and a multiple field key. Try to remove duplicates using a Transformer with a sort stage and combination of stage variables to hold the prior row key value to compare to the new row key value.
Process some data that has nulls in it. Use the null column in a Transformer concatenate function with and without a nulltovalue function and with and without a reject link from the Transformer. This gives you an understanding of how rows get dropped and/or trapped from a transformer. Explore the right mouse click menu in the Transformer, output some of the DS Macro values and System Variables to a peek stage and think of uses for them in various data warehouse scenarios. Ignore DS Routine, it’s not on the test.
Don’t spend much time on the Modify stage - it would take forever memorizing functions. Just do an exercise on handle_null, string_trim and convert string to number. Can be tricky getting it working and you might not even get a question about it.
Section 7 - Combining and Sorting Data (10%)
Section 10 - Job Design (10%)
I’ve combined these since they overlap. Don’t underestimate this section, it covers a very narrow range of functionality so it is an easy set of questions to prepare for and get right. There are easy points on offer in this section.
Versions: Any version 7.x is best, version 6 has a completely different lookup stage, version 8 can be used but remember that the Range lookup functionality is new.
Reading: The Parallel Job Developers Guide has a table showing the differences between the lookup, merge and join stages. Try to memorize the parts of this table about inputs and outputs and reject links. This is a good place to learn about some more environment variables. Read the Parallel Job Advanced Developers Guide looking for any environment variables with the word SORT or SCORE in them.
Exercises: Compare the way join, lookup and merge work. Create a job that switches between each type.
Add various combinations of the SORT and COMBINE_OPERATORS environment variables to your job and examine the score of the job to see what sorts get added to your job at run time. A simple job with a Remove Duplicates or Join stage and no sorting will add a sort into the job - even if the data comes from a sorted database source. Use the SORT variables to turn off this sort insertion. See what happens to Transformer, Lookup and Copy stages when COMBINE_OPERATORS is turned on or off using the SCORE log entry.
Create an annotation and a description annotation and explore the differences between the two. Use the Multiple Compile tool in the DataStage Manager (version 6, 7) or Designer (version 8). Create and use shared containers.
Section 8 - Automation and Production Deployment (10%)
Versions: most deployment methods have remained the same from version 6, 7 and 8. Version 8 has the same import and export functions. DataStage 8 parameter sets will not be in the version 7 exam.
Reading: you don’t need to install or use the Version Control tool to pass this section however you should read the PDF that comes with it to understand the IBM recommendations for deployment. It covers the move from dev to test to prod. Read the Server Job Developers Guide section on command line calls to DataStage such as dsjob and dssearch and dsadmin.
Exercises: practice a few dsjob and dssearch commands. Practice saving a log to a text file. Create some job specific environment variables.
Regular readers may be feeling a sense of de ja vu. Haven’t we seen this post before? I originally posted this in 2006 and this the Directors Cut - I’ve added some deleted scenes, a commentary for DataStage 7.0 and 8.0 users and generally improved the entry. By reposting I retain the links from other sites such as my DataStage Certification Squidoo lens with links to my certification blog entries and IBM certification pages.
This post shows all of the headings from the IBM exam Objectives and describes how to prepare for that section.
Before you start read work out how you add Environment variables to a job as a job parameter as they are handy for exercises and testing. See the DataStage Designer Guide for details.
Section 1 - Installation and Configuration
Versions: Version 7.5.1 and 7.5.2 are the best to study and run exercises on. Version 6.x is risky but mostly the same as 7. Version 8.0 is no good for any type of installation and configuration preparation as it has a new approach to installation and user security.
Reading: Read the Installation and Upgrade Guide for DataStage, especially the section on parallel installation. Read the pre-requisites for each type of install such as users and groups, the compiler, project locations, kernel settings for each platform. Make sure you know what goes into the dsenv file. Read the section on DataStage for USS as you might get one USS question. Do a search for threads on dsenv on the dsxchange forum to become familiar with how this file is used in different production environments.
Exercise: installing your own DataStage Server Enterprise Edition is the best exercise - getting it to connect to Oracle, DB2 and SQL Server is also beneficial. Run the DataStage Administrator and create some users and roles and give them access to DataStage functions.
Section 4 - Parallel Architecture (10%)
Section 9 - Monitoring and Troubleshooting (10%)
I’ve move section 4 and 9 up to the front as you need to study this before you run exercises and read about parallel stages in the other sections. Understanding how to use and monitor parallel jobs is worth a whopping 20% so it’s a good one to know well.
Versions: you can study this using DataStage 6, 7 and 8. Version 8 has the best definition of the parallel architecture with better diagrams.
Reading: Parallel Job Developers Guide opening chapters on what the parallel engine and job partitioning is all about. Read about each partitioning type. Read how sequential file stages partition or repartition data and why datasets don’t. The Parallel Job Advanced Developers Guide has sections on environment variables to help with job monitoring, read about every parameter with the word SCORE in it. The DataStage Director Guide describes how to run job monitoring - use the right mouse click menu on the job monitor window to see extra parallel information.
Exercises: Turn on various monitoring environment variables such as APT_PM_SHOW_PIDS and APT_DUMP_SCORE so you can see what happens during your exercises. It shows you what really runs in a job - the extra processes that get added across parallel nodes.
Try creating one node, two node and four node config files and see how jobs behave under each one. Try the remaining exercises on a couple different configurations by adding the configuration environment variable to the job. Try some pooling options. I got to admit I guessed my way through some of the pooling questions as I didn’t do many exercises.
Generate a set of rows into a sequential file for testing out various partitioning types. One column with unique ids 1 to 100 and a second column with repeating codes such as A, A, A, A, A, B, B, B, B, B etc. Write a job that reads from the input, sends it through a partitioning stage such as a transformer and writes it to a peek stage. The Director logs shows which rows went where. You should also view the Director monitor and expand and show the row counts on each instance of each stage in the job to see how stages are split and run on each node and how many rows each instance gets.
Use a filter stage to split the rows down two paths and bring them back together with a funnel stage, then replace the funnel with a collector stage. Compare the two.
Test yourself on estimating how many processes will be created by a job and check the result after the job has run using the Director monitor or log messages. Do this throughout all your exercises across all sections as a habit.
Section 2 - Metadata
Section 3 - Persistant Storage
I’ve merged these into one. Both sections talk about sequential files, datasets, XML files and Cobol files.
Versions: you can study this on DataStage 6, 7 or 8 as it is a narrow focus on DataStage Designer metadata. DataStage 8 will have slightly different CFF options but not enough to cause a problem.
Reading: read the section in the DataStage Developers Guide on Orchestrate schemas and partial schemas. Read the plugin guide for the Complex Flat File Stage to understand how Cobol metadata is imported (if you don’t have any cobol copybooks around you will just have to read about them and not do any exercises). Quickly scan through the NLS guide - but don’t expect any hard questions on this.
Exercises: Step through the IBM XML tutorial to get the tricky part on reading XML files. Find an XML file and do various exercises reading it and writing it to a sequential file. Switch between different key fields to see the impact of the key on the flattening of the XML hierarchy. Don’t worry too much about XML Transform.
Import from a database using the Orchestrate Import and the Plugin Import and compare the table definitions. Run an exercise on column propagation using the Peek stage where a partial schema is written to the Peek stage to reveal the propagated columns.
Create a job using the Row Generator stage. Define some columns, on the columns tab doubleclick on a column to bring up the advanced column properties. Use some properties to generate values for different data types. Get to know the advanced properties page.
Create a really large sequential file, dataset and fileset and use each as a reference in a lookup stage. Monitor the Resource and Scratch directories as the job runs to see how these lookup sources are prepared prior to and during a job run. Get to know the difference between the lookup fileset and other sources for a lookup stage.
Section 5 - Databases (15%)
One of the more difficult topics if you get questions for a database you are not familiar with. I got one database parallel connectivity question that I still can’t find the answer to in any of the manuals.
Versions: DataStage 7.x any version or at a pinch DataStage 8. Earlier versions do not have enough database stages and DataStage 8 has a new approach to database connections.
Reading: read the plugin guide for each enterprise database stage: Oracle, SQL Server, DB2 and ODBC. In version 8 read the improved Connectivity Guides for these targets. If you have time you can dig deeper, the Parallel Job Developers Guide and/or the Advanced Developers Guide has a section on the Oracle/DB2/Informix/Teradata/Sybase/SQL Server interface libraries. Look for the section called "Operator action" and read it for each stage. It’s got interesting bits like whether the stage can run in parallel, how it converts data and handles record sizes.
Exercise: Add each Enterprise database stage to a parallel job as both an input and output stage. Go in and fiddle around with all the different types of read and write options. You don’t need to get a connection working or have access to that database, you just need to have the stage installed and add it to your job. Look at the differences between insert/update/add/load etc. Look at the different options for each database stage. If you have time and a database try some loads to a database table.
Section 6 - Data Transformation (15%)
If you’ve used DataStage for longer than a year this is probably the topic you are going to ace - as long as you have done some type of stage variable use.
Versions: should be okay studying on versions 6, 7 or 8. Transformation stages such as Transformer, Filter and Modify have not changed much.
Reading: there is more value in using the transformation stages than reading about it. It’s hard to read about it and take it in as the Transformer stage is easier to navigate and understand if you are using it. If you have to make do with reading then visit the dsxchange and look for threads on stage variables, the FAQ on the parallel number generator, removing duplicates using a transformer and questions in the parallel forum on null handling. This will be better than reading the manuals as they will be full of practical examples. Read the Parallel Job Advanced Developers Guide section on "Specifying your own parallel stages".
Exercises: Focus on Transformer, Modify Stage (briefly), Copy Stage (briefly) and Filter Stage.
Create some mixed up source data with duplicate rows and a multiple field key. Try to remove duplicates using a Transformer with a sort stage and combination of stage variables to hold the prior row key value to compare to the new row key value.
Process some data that has nulls in it. Use the null column in a Transformer concatenate function with and without a nulltovalue function and with and without a reject link from the Transformer. This gives you an understanding of how rows get dropped and/or trapped from a transformer. Explore the right mouse click menu in the Transformer, output some of the DS Macro values and System Variables to a peek stage and think of uses for them in various data warehouse scenarios. Ignore DS Routine, it’s not on the test.
Don’t spend much time on the Modify stage - it would take forever memorizing functions. Just do an exercise on handle_null, string_trim and convert string to number. Can be tricky getting it working and you might not even get a question about it.
Section 7 - Combining and Sorting Data (10%)
Section 10 - Job Design (10%)
I’ve combined these since they overlap. Don’t underestimate this section, it covers a very narrow range of functionality so it is an easy set of questions to prepare for and get right. There are easy points on offer in this section.
Versions: Any version 7.x is best, version 6 has a completely different lookup stage, version 8 can be used but remember that the Range lookup functionality is new.
Reading: The Parallel Job Developers Guide has a table showing the differences between the lookup, merge and join stages. Try to memorize the parts of this table about inputs and outputs and reject links. This is a good place to learn about some more environment variables. Read the Parallel Job Advanced Developers Guide looking for any environment variables with the word SORT or SCORE in them.
Exercises: Compare the way join, lookup and merge work. Create a job that switches between each type.
Add various combinations of the SORT and COMBINE_OPERATORS environment variables to your job and examine the score of the job to see what sorts get added to your job at run time. A simple job with a Remove Duplicates or Join stage and no sorting will add a sort into the job - even if the data comes from a sorted database source. Use the SORT variables to turn off this sort insertion. See what happens to Transformer, Lookup and Copy stages when COMBINE_OPERATORS is turned on or off using the SCORE log entry.
Create an annotation and a description annotation and explore the differences between the two. Use the Multiple Compile tool in the DataStage Manager (version 6, 7) or Designer (version 8). Create and use shared containers.
Section 8 - Automation and Production Deployment (10%)
Versions: most deployment methods have remained the same from version 6, 7 and 8. Version 8 has the same import and export functions. DataStage 8 parameter sets will not be in the version 7 exam.
Reading: you don’t need to install or use the Version Control tool to pass this section however you should read the PDF that comes with it to understand the IBM recommendations for deployment. It covers the move from dev to test to prod. Read the Server Job Developers Guide section on command line calls to DataStage such as dsjob and dssearch and dsadmin.
Exercises: practice a few dsjob and dssearch commands. Practice saving a log to a text file. Create some job specific environment variables.
Subscribe to:
Posts (Atom)