DataGenX - Atul's Blog: ETL

Showing posts with label ETL. Show all posts

Tuesday, 28 February 2017

ETL Preformance Tuning : Identification of Performance Bottleneck

by Atul Singh / in Bottleneck, ETL, Performance, Tuning /

The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune the performance of a session, we need to identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until we are satisfied with the session performance. Test Load option can be used to run sessions while tune session performance.
The most common performance bottleneck occurs when the ETL Server writes to a target database. We can identify performance bottlenecks by the following methods:

Running test sessions. We can configure a test session to read from a flat file source or to write to a flat file target or any stage which can hold the data but not write, to identify source and target bottlenecks.

Studying performance details. We can create a set of information called performance details to identify session bottlenecks. Performance details provide information such as buffer input and output efficiency. Collect Performance Data option in Session Property will enable the Session to generate a Counter of Input and Output rows through each Transformation

Monitoring system performance. System monitoring tools can be used to view percent CPU usage, I/O waits, and paging to identify system bottlenecks.

Once the location of a performance bottleneck is determined, we can eliminate the bottleneck by following these guidelines:

Eliminate source and target database bottlenecks.
Optimize the query, Increase the database network packet size, or configuring index and key constraints.

Eliminate mapping bottlenecks.
Fine tune the pipeline logic and transformation settings and options in mappings to eliminate mapping bottlenecks.

Eliminate session bottlenecks.
Session strategy can be optimized performance details can be used to help in tuning session configuration.

Eliminate system bottlenecks.
Have the system administrator analyze information from system monitoring tools and improve CPU and network performance.

If all the bottlenecks above are tuned, further optimization of session performance can be done by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.

Like the below page to get update
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

Implementing Slowly Changing Dimension in ETL

by Atul Singh / in Datawarehouse, Dimension, DWH, ETL, Facts, SCD, Slowly Changing Dimension /

Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information. Slowly Changing Dimensions (SCD) are dimensions that have data that slowly changes.

For example, you may have a Dimension in your database that tracks the sales records of your salespersons in different pharmacies. Creating sales reports seems simple enough, until a salesperson is transferred from one pharmacy to another. How do you record such a change in your sales Dimension?

When we need to track change, it is unacceptable to put everything into the fact table or make every dimension time-dependent to deal with these changes. We would quickly talk ourselves back into a full-blown normalized structure with the consequential loss of understand-ability and query performance. Instead, we take advantage of the fact that most dimensions are nearly constant over time. We can preserve the independent dimensional structure with only relatively minor adjustments to contend with the changes. We refer to these nearly constant dimensions as slowly changing dimensions. Since Ralph Kimball first introduced the notion of slowly changing dimensions in 1994, some IT professionals—in a never-ending quest to speak in acronym—have termed them SCDs.

For each attribute in our dimension tables, we must specify a strategy to handle change. In other words, when an attribute value changes in the operational world, how will we respond to the change in our dimensional models? In the following section we'll describe three basic techniques for dealing with attribute changes, along with a couple hybrid approaches. You may decide that you need to employ a combination of these techniques within a single dimension table.

Type 1: Overwrite the Value
With the type 1 response, we merely overwrite the old attribute value in the dimension row, replacing it with the current value. In so doing, the attribute always reflects the most recent assignment.

Type 2: Add a Dimension Row
A type 2 response is the predominant technique for maintaining the historical data when it comes to slowly changing dimensions. A type 2 SCD is a dimension where a new row is created when the value of an attribute changes

Type 3: Add a Dimension Column
While the type 2 response partitions history, it does not allow us to associate the new attribute value with old fact history or vice versa. A type 3 SCD is a dimension where an alternate old column is created when an attribute changes

Type 6: Hybrid Slowly Changing Dimension Techniques
The Type 6 method is one that combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6). One possible explanation of the origin of the term was that it was coined by Ralph Kimball during a conversation with Stephen Pace from Kalido but has also been referred to by Tom Haughey. It is not frequently used because it has the potential to complicate end user access, but has some advantages over the other approaches especially when techniques are employed to mitigate the downstream complexity.
The approach is to use a Type 1 slowly changing dimension, but adding an additional pair of date columns to indicate the date range at which a particular row in the dimension applies and a flag to indicate if the record is the current record.

The aim is to move data from the source application system to the Reporting Warehouse for analysis and reporting of a worldwide pharmaceutical major. The code here is responsible for the movement of data between the Operational Data Store (ODS) and the data warehouse.

Implementation Flow:

ETL Strategy #2

by Atul Singh / in Delta, design, develop, ETL, Initial, Load, Planning, Process, Strategy /

Continued......ETL Strategy #1

Delta from Sources extracted by Timestamp
This project will use the Timestamp to capture the deltas for most of the Operational Database sources where a date/time value can be used. ETL process will extract data from the operational data stores based on date/time value column like Update_dt during processing of the delta records, and then populate it into the Initial Staging area. The flow chart shown below shows step by step flow.

As shown in the flow chart above. It is shown in two parts, one for initial load and the other for delta processing.

Ref #              Step Description
1    Insert record into control tables manually or using scripts for each ETL process. This is done only once when a table gets loaded for the first time in data warehouse
2    Set the extract date to desired Initial load date on the control table. This is the timestamp which the ETL process will use to go against the source system.
3    Run ETL batch process which will read the control tables for extract timestamp.
4    Extract all data from source system greater than the set extract timestamp on the control table.
5    Check if the load completed successfully or failed with errors.
6    If the load failed with errors, then the error handling service is called.
7    If the load completed successfully then the load flag is set to successful.
8    The max timestamp of the ETL load is obtained
9    A new record is inserted to the control structure with the timestamp obtained in the above step.
10    The process continues to pull the delta records with the subsequent runs.


Delta from Sources extracted by comparison
Where a transaction Date or Timestamp is not available, a process will compare the new and current version of a source to generate its delta. This strategy is mostly used for files as source of data. This is manageable for small to medium size files that are used in this project and should be avoided with larger source file. A transaction code (I=Insert; U=Update; D=Delete) will have to be generated so that the rest of the ETL stream can recognise the type of transaction and process it.
Files are pushed into ETL server or they are pulled from the FTP servers to ETL server. If the files contain delta records, then the files are uploaded directly to the Data warehouse. If the file is a full extract file, then the file comparison delta process will be used to identify the changed records before uploading to the Data warehouse.


E10 Validate Source Data transferred via FTP
Input:    Source Data File and Source Control File.
Output:    NONE.
Dependency: Availability of other systems files.
Functions:
•    Validate if the number of records in the Source File is the same number as the one contained in the Source Control File. This will guarantee that the right number of records has been transferred from Source to Target.

ETL Strategy #1

by Atul Singh / in Delta, design, develop, ETL, Initial, Load, Planning, Process, Strategy /

The ETL Load will be designed to extract data from all the required Data Sources. The data to feed the Next Gen BI Database will have to be brought from the sources with specific Extraction, Transformation & Load (ETL) processes. It is essential to define a good ETL strategy to ensure that the execution of these tasks will support appropriate data volumes and Design Should be Scalable, and Maintenance free.

Initial Load Strategy

The Initial Load process is there to support the requirement to include historical data that can’t be included through the regular Delta Refresh.
For the Next Gen BI project, it is expected to have a full extract available for the required sources to prepare an Initial Load prior to the regular Delta Refresh. This Initial extraction of the data will then flow through the regular transformation and load process.
As discussed in the control process, Control tables will be used to initiate the first iteration of the Initial ETL process, a list of source table name with extraction dates will be loaded in the control tables. ETL process can be kicked off through the scheduler and the ETL process will read the control tables and process the full extract.
The rest of the process for an Initial load is same as the delta refresh. As shown in the flow chart under the section (Delta from Sources extracted by Timestamp), The only difference is the loading of the control tables to start the process for the first time when a table gets loaded in the Data Warehouse.

Delta Refresh or CDC Strategy

The Delta refresh process will apply only the appropriate transactions to the Data Warehouse. The result is a greatly reduced volume of information to be processed and applied. Also the Delta transactions for the Warehouse can be reused as input for the different Data mart, since they will be part of the Staging area and already processed. As discussed in the Control Process Strategy, control tables will be used to control delta refresh process.

Continued......ETL Strategy #2

ETL Data Validation

by Atul Singh / in data, ETL, testing, type, Validation /

Organizations typically have “dirty data” that must be cleansed or scrubbed before being loaded into the data warehouse. In an ideal world, there would not be dirty data. The data in operational systems would be clean. Unfortunately, this is virtually never the case. The data in these source systems is the result of poor data quality practices and little can be done about the data that is already there. While organizations should move toward improving data quality at the source system level, nearly all data warehousing initiatives must cope with dirty data, at least in the short term. There are many reasons for dirty data, including:

Dummy values. Inappropriate values have been entered into fields. For example, a customer service representative, in a hurry and not perceiving entering correct data as being particularly important, might enter the store’s ZIP code rather than the customer’s ZIP, or enters 999-99-9999 whenever a SSN is unknown. The operational system accepts the input, but it is not correct.

Absence of data. Data was not entered for certain fields. This is not always attributable to lazy data entry habits and the lack of edit checks, but to the fact that different business units may have different needs for certain data values in order to run their operations. For example, the department that originates mortgage loans may have a federal reporting requirement to capture the sex and ethnicity of a customer, whereas the department that originates consumer loans does not.

Multipurpose fields. A field is used for multiple purposes; consequently, it does not consistently store the same thing. This can happen with packaged applications that include fields that are not required to run the application. Different departments may use the “extra” fields for their own purposes, and as a result, what is stored in the fields is not consistent.

Cryptic data. It is not clear what data is stored in a field. The documentation is poor and the attribute name provides little help in understanding the field’s content. The field may be derived from other fields or the field may have been used for different purposes over the years.

Contradicting data. The data should be the same but it isn’t. For example, a customer may have different addresses in different source systems.

Inappropriate use of address lines. Data has been incorrectly entered into address lines. Address lines are commonly broken down into, for example, Line 1 for first, middle, and last name, Line 2 for street address, Line 3 for apartment number, and so on. Data is not always entered into the correct line, which makes it difficult to parse the data for later use.

Violation of business rules. Some of the values stored in a field are inconsistent with business reality. For example, a source system may have recorded an adjustable rate mortgage loan where the value of the minimum interest rate is higher than the value of the maximum interest rate.

Reused primary keys. A primary key is not unique; it is used with multiple occurrences. There are many ways that this problem can occur. For example, assume that a branch bank has a unique identifier (i.e., a primary key). The branch is closed and the primary key is no longer in use. But two years later, a new branch is opened, and the old identifier is reused. The primary key is the same for the old and the new branch.

Non-unique identifiers. An item of interest, such as a customer, has been assigned multiple identifiers. For example, in the health care field, it is common for health care providers to assign their own identifier to patients. This makes it difficult to integrate patient records to provide a comprehensive understanding of a patient’s health care history.

Data integration problems. The data is difficult or impossible to integrate. This can be due to non-unique identifiers, or the absence of an appropriate primary key. To illustrate, for decades customers have been associated with their accounts through a customer name field on the account record. Integrating multiple customer accounts in this situation can be difficult. When we examine all the account records that belong to one customer, we find different spellings or abbreviations of the same customer name, sometimes the customer is recorded under an alias or a maiden name, and occasionally two or three customers have a joint account and all of their names are squeezed into one name field.

ETL Code Review Checklist

by Atul Singh / in Checklist, Datastage, designer, develop, Environment, ETL, Parameter, Partitioning, Review, Variable /

Guideline
Design jobs for restartability/ if not designed then what is the reason ?
Do not follow Sequential File stage with "Same" partitioning.
check if the APT_CONFIG_FILE parameter is added. This is required to change the number of nodes during runtime.
Do not hard-code parameters.
Do not hard-code directory paths.
Do not use fork-joins to generate lookup data sets.
Use "Hash" aggregation for limited distinct key values. Outputs after all rows are read.
Use "Sort" aggregation for large number of distinct key values. Data must be pre-sorted. Outputs after each aggregation group.
Use multiple aggregators to reduce collection time when aggregating all rows. Define a constant key column using row generator. First aggregator sums in parallel. Second aggregator sums sequentially.
Make sure sequences are not too long. Break up into logical units of work.
Is the error handling done properly? It is prefered to propogate errors from lower jobs to the highest level( ex a sequence)
What is the volume of extract data( is there a where clause in the SQL)
Are the correct scripts to clean up datasets after job complete revoked ?
Is there a reject process in place ?
Can we combine or split so we can reduce number of jobs or complexity respectively?
It is not recommended to have an increase in the number of nodes if there are too many stages in the job( this increases the number of processes spun off)
Volume information and growth information for the Lookup/Join tables?
Check if there is a select * in any of the queries. It is not advised to have select * , instead the required columns have to be added in the statement
Check the paritioning and sorting at each stage
When a sequence is used make sure none of the parameters passed are left blank
Check if there are separate jobs for atleast extract, transform and load
Check if there is annotation for each stage and the job, the job properties should have the author,date etc filled out
Check for naming convention of the jobs, stages and links
Try avoiding peeks in production jobs, peeks are generally used for debug in the development
Make sure the developer has not suppressed many warnings that are valid
Verify that the jobs conform to the Flat File and Dataset naming specification. This is especially important for cleaning up files and logging errors appropriately.
Verify that all fields are written to the Reject flat files. This is necessary for debugging and reconciliation.

ETL Development Standards

by Atul Singh / in Administration, Code, develop, Development, ETL, Guide, Guidelines, Naming, Standards /

These development standards provide consistency in the artifacts the ETL developers create. This consistency improves testing, operational support, maintenance, and performance.

Code Development Guidelines

1 Commenting in Code and Objects

As a primary guideline where it is possible and does not interfere with the operation of the applications, all code must contain a developer comment/note.
• All ETL jobs must have a proper annotation (short description of the functionality of the job).
• The target output files (.csv files) should not contain any leading or trailing spaces.
• While deciding record level delimiter, “Delimiter Collision” issue needs to be considered. No such delimiter should be used as a field defaults that is present as a part of data.

2 ETL Naming Standards

The standardized naming conventions ease the burden on developers switching from one project to another. Knowing the names and where things are located are very useful to understand before the occurrence of the design and development phases.

The following table identifies DataStage elements and their standard naming convention.

2.1 Job and Properties Naming Conventions

GUI Component	Entity	Convention
Designer	Parallel Job	<<Application>>_<<job_Name>>_JOB
Designer	Sequence	<<Application>>_<<job_Name>>_SEQ
Designer	Server Job	<<Application>>_<<job_Name>>_SVR
Designer	Parameter	<<Application>>_<<job_Name>>_PARM

2.2 Job Processing Stage Naming Conventions

GUI Component	Entity	Convention
Designer	Aggregator	AGG_<<PrimaryFunction>>
Designer	Copy	CP_<<PrimaryFunction>>
Designer	Filter	FLT_<<PrimaryFunction>>
Designer	Funnel	FNL_<<PrimaryFunction>>
Designer	Join (Inner)	JN_<<PrimaryFunction>>
Designer	FTP Enterprise	FTP_<<PrimaryFunction>>
Designer	Lookup	LKP_<< Value Name or table Name>>
Designer	Merge	MRG_<<PrimaryFunction>>
Designer	Modify	MOD_<<PrimaryFunction>>
Designer	Sort	SRT_<<PrimaryFunction>>

2.3 Links Naming Conventions

GUI Component	Entity	Convention
Designer	Reference (Lookup)	Lnk_Ref_<<Number or Additional descriptor, if needed to form a unique object name>>
Designer	Reject (Lookup, File, DB)	Lnk_Rej_<<Number or Additional descriptor, if needed to form a unique object name>>
Designer	Input	Lnk_In_<<Number or Additional descriptor, if needed to form a unique object name>>
Designer	Output	Lnk_Out_<<Number or Additional descriptor, if needed to form a unique object name>>
Designer	Delete	Lnk_Del_<<Number or Additional descriptor, if needed to form a unique object name>>
Designer	Insert	Lnk_Ins_<<Number or Additional descriptor, if needed to form a unique object name>>
Designer	Update	Lnk_Upd_<<Number or Additional descriptor, if needed to form a unique object name>>

2.4 Data Store Naming Conventions:

In the case of a data store, the class word refers to the type of data store (e.g. Dataset, Sequential File, Table, View, and so forth).

GUI Component	Entity	Convention
Designer	Database	DB_<<DatabasName>>
Designer	Table	TBL_<<TableName>>
Designer	View	VIEW_<<ViewName>>
Designer	Dimension	DM_<<TableName>>
Designer	Fact	TRAN_<<TableName>>
Designer	Source	SRC_<<Table or Object Name>>
Designer	Target	TRGT_<<Table or objectName>>

2.5 File Stage Naming Conventions:

GUI Component	Entity	Convention
Designer	Sequential File	SEQ_
Designer	Complex Flat File	CFF_
Designer	Parallel dataset	DS_

Audit Strategy in ETL #2

by Atul Singh / in Audit, Concept, ETL, Strategy /

First - Audit Strategy in ETL #1

As shown in the above diagram, the audit services are dependent on source systems and target systems for capturing audit data; unlike error handling services are dependent on ETL process to capture error data. Audit services are created as shared services that plug in to each ETL process.

Within the proposed Next Gen BI environment there are 4 stages (Initial Staging, Clean Staging, EDW, and Data Marts) where data is landed after extracting from source systems.
There will be at least one ETL job flow process at each of these stages extracting and loading data. Audit services are called at each stage by the ETL job flow process and the audit data is captured and reported.

Some of the key audit data that will be captured by the audit services is given below :

Field #	Field	Description
1	Data Server Name	A name that identifies a database service. This is called a "server" by some products. Oracle refers to it by the name "instance" or "database".
2	Database Name
3	Data Table Name	A name that identifies a Target Data Table within a Database.
4	Source Name	A name that identifies the file, table or other data structure that is the origin of the data to be loaded into the target table.
5	ETL Process Name	DataStage Sequencer

6	ETL Sub Process Name	DataStage Job

7	New Rows in Source Count	The number or rows in the source table that have been added since the prior run date. This is the number that the process attempted to add to the target table. (If the source is a file, this field should be NULL.)
8	Changed Rows in Source Count	The number of rows in the source table which were modified since the prior run date. This is the number that the process attempted to modify in the target. (If the source is a file, this field will be NULL.)
9	Processed Rows in Source Count	The total number of rows in the source table or file that the process attempted to load to the target. (If the source is a table, this is the sum of the New_Rows_in_Source_Cnt and Changed_Rows_in_Source_Cnt.)
10	Starting Row Count	The number of rows in a table at the beginning of a Table Load.
11	Ending Row Count	The number of rows in a table at the conclusion of a Table Load.
12	Insert Row Count	The number of relational database table rows that are processed in an INSERT operation.
13	Update Row Count	The number of relational database table rows that are processed in an UPDATE operation.
14	DW_CREATE_DTTM	Date/time at which audit data was captured

Audit Strategy in ETL #1

by Atul Singh / in Audit, Concept, ETL, Strategy, Tutorial /

The Audit service will provide simple measures on the ETL process: number of records input/output, number of rejected records, number of records loaded, Starting and Ending Row Count in the Target tables, New Rows in the source Table/Files, Inserted Rows and Updated Rows.

These measures are captured during the batch load process and stored in the audit tables for load audit checks and reporting purposes. Each process of the ETL should produce these key statistics by calling the audit services at the beginning and ending of a load process. All Audit tables will be stored in the staging repository. Exception reports can be produced for action to be taken by the business users and production support personnel.

Ref #	Step Description
1	Represents all the source systems from which data is extracted. Audit services will collect all the source audit data during the ETL process from source systems
2	Represents the ETL process which extracts data from Source systems and loads the data to target systems. Mostly audit data is not captured at the ETL process level.
3	Represents the target systems where the data gets loaded after the completion of the ETL process. Audit services will collect all the target audit data at the end of the ETL process from Target systems.
4	Audit Tables are internal tables where all the audit data related to source and target systems are loaded. Entry is made in this table every time an ETL batch process runs. These tables reside internally in the staging area. Audit reports can be generated using these tables.
5	Once the Audit data is captured. Audit check routines can be used to detect any discrepancy in the source or the target systems. This will be an automated process which will run at the completion of each ETL job based on the predefined rules.
6	As Mentioned in the previous step, These audit rules can be set to fail the job and stop the batch process or the batch process can continue if the rules did not fail.
7	Once the Audit check routines are complete for any job. Audit flag will be set to specify a success or failure on the audit check.
8	If the audit check routine does not indicate a failure flag, next batch process continues to run.
9	If the audit check routine indicates a failure, then the batch process is aborted.
10	When the batch process is aborted, the control passes to the alert system where appropriate action is taken by sending email notifications and pager messages.
11	Audit reports can be generated which will be useful to audit the ETL process and as well the source systems.

Continue in next part - Audit Strategy in ETL #2

DataGenX - Atul's Blog