Data Profiling for ETL Processes

                                   Maunendra Sankar Desarkar

                                         IIT Kanpur







                                               Abstract

        The ultimate success of ETL processes ends with the delivery of correct, reliable, relevant

     and complete information to end users. Hence data cleaning is an important part of any ETL

     process. In this report, we look at some common errors in data stored in databases and describe

     the functionality of our tool that helps to discover rules to filter those dirty data. We also

     present some idea about how to develop custom operators that will be able to perform some

     data cleaning operation in Europa platform. Such operators will be of immense help in designing

     ETL processes using Europa.

         Keywords: ETL, data cleaning, data profiling, Europa operator





1    Introduction

Any company’s profitability depends on its ability to make sound business decisions, based on

complete, accurate view of the customers, suppliers and transactions. However, this information

is not maintained at a single place, rather scattered throughout the enterprise - across multiple

departments, divisions and applications. Additionally, there are company mergers and acquisitions

which bring diverse operational systems together. Hence these organizations need a Business In-

telligence platform that can consolidate and deliver data from multiple locations into a single and

trustworthy source of information. Then the organization can do reporting, query and analysis,

performance management and take sound business decisions. However, the data present in the con-

solidated repository must be error-free for the decision to be an helpful one for the enterprise. As a

consequence, any data integration process must include a data cleaning phase. Obtaining a profile

or a set of business rules that are followed by the data can help in data cleaning. ETL processes

aim at data integration. Hence, data profiling becomes an important part of ETL processes. In

this report we discuss some of these rules. We also discuss how to perform data cleaning with the

help of these rules.

The organization of the report is as follows. In section 2, we give brief introduction to ETL. Section

3 contains some examples of dirty data. Section 4 introduces some concepts of data profiling. In

section 5 we describe the tool that we developed for data profiling. We introduce Europa in section

6 and describe the steps required for developing Europa custom operators in section 7. In the next

section we mention some possible future works. Finally we conclude in section 9.





2    ETL

ETL stands for extract, transform and load. The entire process is divided into three phases.



                                                   1

    �? Extract: The first phase of an ETL process is to extract data from source systems. Most data

      warehousing projects consolidate data from di�?erent source systems. Each separate system

      may use di�?erent data organization formats. Source data may be in relational databases, text

      files (standard and delimited), XML, COBOL Copybooks etc. Additionally the files might

      be having di�?erent data representation formats.



    �? Transform: After extraction, the data is transformed, or modified, depending on the specific

      business logic involved so that it can be sent to the target repository. There are a variety of

      ways to perform the transformation, and the work involved varies. Some examples are:



         �? Derive a new calculated value (f inalprice = originalprice �? (1 �? discount))

         �? Summarize multiple rows of data (Find total number of employees in that branch)

         �? Join data from multiple sources (Get names of those employees who work in the location

           where some particular product is developed)



       Before reformatting the data to be available for use by the target schema, most ETL projects

       perform data cleaning operation to remove inconsistencies present in the source data. This

       is done during the transformation phase.



    �? Load: The load phase transports and loads the data into to the data warehouse. Depending

      on the requirements of the organization, this process ranges widely. Some data warehouses

      simply overwrite old information with new data, some might do this in incremental fashion.

      There are more complex systems which maintain a history and audit trail of all changes to

      the data.



Several products are available in the market for preparing and executing ETL applications. Exam-

ples include IBM Websphere Datastage (IBM), PowerCenter (Informatica) etc.





3     Dirty data - some examples

A taxonomy of dirty data can be found in [KCH+ 03]. In this section we give some examples of

di�?erent classes of dirty data.



    1. Wrong data due to non-enforcement of enforceable integrity constants :



         �? Duplicate entries instead of unique entries

         �? Null values where null not expected



    2. Unwanted data, constraints can not be specified :



         �? Age is not updated, so Current age = Current date - Date of Birth

         �? Work experience < 0, which is out of possible range



    3. Spelling errors



    4. Entry into wrong fields



         �? address in place of name





                                                  2

    5. Use of abbreviations



         �? T. J. Watson and Thomas J. Watson both entries are used

         �? st used in address field instead of street



    6. Di�?erent ordering



         �? Name used in  order and  order.





4     Data Profiling

Whenever we say some data present in the source is dirty, it means that there is some rule which

the data is supposed to follow, is violated. In data profiling, we try to find out those “rules�?.

Hence profiling allows to analyze the data values to find areas that are incomplete, inaccurate

or ambiguous. It can also verify relationships across columns and tables. If during the profiling

stage we can find out the integrity constraints followed by the column, then first error listed in the

previous section can be eliminated. Similarly, if the profiling phase can find out the range of data

for the columns (where applicable), then the second error in the list can be eliminated. Removal of

other kind of errors (listed in the previous section) require more complex rules to be derived from

data.





5     Functionality of our tool

The tool that we developed for data profiling task can find out the following rules from the database.



    1. Does the column allow null value?



    2. Is it a unique key in its table?



    3. Is it a categorical field? If yes, what is the domain?



    4. Find the acceptable range for the column entries.



    5. Find primary key - foreign key relationship.



    The application allows the user to select a set of columns to analyze. It also allows the user

to select the rules he wants to check. Then it comes up with the rules by looking at the entries

present in those columns.

    For checking nullability it finds out the number of null entries in that column. If there is at

least one null entry, it means the column allows null values, since we assume the data source to be

clean. If there is no null value, then it reports the column under consideration as a non-nullable

column.

    For checking uniqueness of the field, the application finds out the number of distinct elements in

the column. If number of distinct elements is same as the number of entries present in the column,

it means that all the elements are distinct. In such cases, it declares the column as a unique key.

    For the third rule, it calculates the ratio of the number of the distinct elements and the total

number of elements in the table. If the ratio is very low, it means that average frequency of the

elements is very high and the column can be a categorical field. When this ratio falls below some



                                                  3

predefined threshold, the application declares it as a categorical field. The domain of acceptable

values is the set of the distinct elements in the column. We check this rule for the columns which

are of char or varchar type, and are of reasonably small length (length �? some predefined

threshold).

     Finding PK-FK relationship is done only if both the columns are non-null and of the type

integer or smallint. First it tries to find out if the range of the first column is totally contained

in the range of the second column. If it is not, then the second column can not be a foreign key.

Otherwise it checks for the inclusion dependency of the first column in the second column. If this

checking gives positive result, the application declares the second column as a foreign key of the

first column.

     The rules that the application outputs after analyzing the the columns may not hold in the

context in which the database is used. Basically the application provides the expert (user) a set

of possible (which is a narrowed down list of all the possible rules) rules which may exist in the

database. Like the user may say that the range is not exactly what is given by the application, but

it gives some idea about where the range lies. Hence it helps the user to find the rules those are

followed by the entries in the database.





6    Europa - a platform to design ETL applications

Europa is a platform for developing ETL applications [Sria]. It provides the user with a set of

operators, which can be used to create ETL jobs. Operators are of three types:



    �? Source operators: Source operators are sources of data.

      Example: database table, flat file, JDBC - ODBC sources etc.



    �? Transform operators: Transform operators accept the data from sources or other transform

      operators, process them in some fashion and send the output of the processing to other

      transform operator(s) or target operator(s).

      Example: sort, filter, union, intersection etc.



    �? Target operators: Target operators are the targets where data can be stored after process-

      ing. They generally consume data generated by transform operators.

      Example: database tables, flat files, JDBC - ODBC targets etc.



Operators accept input and provide output through ports. Number of input and output ports that

an operator will have can vary depending upon the use of the operator. Besides input-output ports,

operators have a set of Properties. These values are manipulated by the ETL project designer to

govern the behavior of this operator.

    Any Europa ETL project contains a control flow and one or more data flows. Data flow is a

directed graph of operator nodes interconnected by links used to indicate the ETL data transfor-

mation sequence. There can be multiple data flows in a single project. Control flows, on the other

hand, describe the sequence in which di�?erent data flows are executed. They also describe how

error recovery, notification and compensation activities are organized. Control flows do not deal

with how exactly data is transformed - that is described in individual data flows. The applica-

tion developer generates the data flows and control flows which gives high level description of the

project. Data flows are saved in the Europa data flow XML format, while control flow is in BPEL.





                                                4

These codes are automatically generated by Europa. Once code for the control flow is generated,

the application can be run on Websphere Application Server.





7     Developing custom operators for Europa

Europa is built using eclipse plug-in architecture. Its functionalities can be extended by using

custom plug-ins. Users can build their own custom operators and place them in an operator

library. Once the operator library is registered to the data flow system, the operators contained in

that library become available for use. Operators and operator libraries are in XML format. Below

we discuss how operator libraries and custom operators can be created [Sria], [Srib].



7.1   Add an operator library to the Plug-in

Create an operator library and place your operator there.

    Step 1: Create a new plug-in project. It will have a plugin.xml file.

    Step 2: Add dependencies to the plug-in. Dependencies vary according to the task that the

operator is supposed to perform. Each such dependency will add one import entry in the plugin.xml

file. For example, after adding the com.ibm.datatools.etl.dataflow.core and

com.ibm.datatools.etl.dataflow.base.oplib plug-ins as dependencies to your plug-in, the plu-

gin.xml file will contain the following lines.





  

  







    Step 3: Create an operator library. For doing this, in the plugin.xml file, choose the extensions

tab. Add a new extension. Choose the operatorLibraries extension. Add an operator library to

the operatorLibraries extension. Fill in the libFileName attribute with the name of the operator

library file that you are going to create. The file name’s path is relative to the plug-in’s home

directory. After adding the file name, the plugin.xml file will contain the following lines. (Suppose

the operator library file name is sampleOp.oplib).





    







    Step 4: Edit the oplib file. It must have a unique nsURI attribute. The following is an example

of an empty oplib file.




xmlns:opLib="http:///com/ibm/datatools/etl/operatorlibrary.ecore"

xmlns:coretypes="http:///com/ibm/datatools/etl/coretypes.ecore"

xmlns:SQLDataTypes="http:///org/eclipse/wst/rdb/models/sql/datatypes.ecore"

name="europa_sampleOp_lib"



                                                 5

nsURI="http:///org/europa/sampleOp/lib/sampleOp.oplib">









    Step 5: Create a category. Operators and their details are put in categories. An example is

given below. It creates an operator library named sampleCat.











     Step 6: Design the operator. Select the category where you want to put it. Place the details

of the operator inside the and tags of the category. The name and the

codeGeneratorClass of the operator are mandatory in its description. You will also have to mention

its input, output and properties and parameters, wherever applicable.



   �? name is the identity by which the platform and the ETL application developer will know

     and refer the operator.



   �? codeGeneratorClass’es className attribute denotes the name of the java class that will

     generate the code for this operator. This class is to be created by you.



     

         
         className="org.europa.sampleOp.codegen.codeGenForSampleOp"/>

     





     This code snippet means that we are designing a sampleOp custom operator, its code will be

     generated by the sampleOp class. The path of the sampleOp class is



     org\europa\sampleOp\codegen\sampleOp.



     (operator name and code generator class name can be di�?erent also).



   �? input and output attributes define the input and output ports of the operator. One input

     port can accept data from one operator only, whereas one output port can be connected

     to multiple operators. Number of input and output ports that an operator can have in a

     dataflow can be limited by setting the lowerBound and upperBound the input and output.

     upperBound = -1 means there is no limit on upper bound, and you can open as many ports

     as you want.



     

     

     








                                                6

�? property of an operator is analogous to parameters in function calls in any high level pro-

  gram. Designer can set the value of the property and that value can be used by the operator

  to perform its functionality. For example, File Export operator has a property called File

  Name. This value can be set according to the need of the user and can be used in the code

  generation phase. These properties may be of di�?erent ‘data types�?, for example, Integers,

  Lists, Strings, Expressions, Database Table, File etc. You can define custom property types

  also. For each property type, a GUI “editor�? can be associated. This editor class and other

  artifacts would be invoked in the Europa Data Flow Design editor, and whenever an oper-

  ator has a property of this type - one can enter the value of the property from that editor.

  Pre-defined property types already have property editors defined and similar editors can be

  defined by custom operator developers. This is how you can define a property of an operator.



  

  

  





�? Properties, inputs and outputs can be defined to be dependent on each other in the operator

  definition. In some cases, changes in one (the cause) can be used to cause changes in its

  dependent objects. Such dependencies may be specified using �?�? tags in the

  operator definition.



  For example, the Filter operator allows the user to select the input table and the ‘filterEx-

  pression�?. This ‘filterExpression�? is a property of type condition. It has a condition property

  editor associated with it, where you can enter the condition for filtering. The condition prop-

  erty editor has a list of the available columns from the input table. So the ‘filterExpression�?

  property depends on the input table. This is captured in the following lines, which are there

  in the description of the Filter operator.



  

  

      

  





  ‘name�? attribute of the param tag tells the name of the input, output or property on which

  this property depends, and ‘type�? denotes what is its type - input, output or property.



Step 7: Create the code generator class. Here is an example code generator class.





 import java.util.List; import

 java.util.Vector;



 import com.ibm.datatools.etl.codegen.IGenericCodeGenerator;

 import com.ibm.datatools.etl.codeunit.CodeType;



                                             7

    import   com.ibm.datatools.etl.codeunit.CodeUnit;

    import   com.ibm.datatools.etl.common.CodeGenOptions;

    import   com.ibm.datatools.etl.dataflow.Operator;

    import   com.ibm.datatools.etl.util.CUHelper;

    import   com.ibm.datatools.etl.codeunit.Action;

    import   com.ibm.datatools.etl.common.Phase;



    public class codeGenForSampleOp implements IGenericCodeGenerator {



         Operator opInst = null;

         CodeGenOptions options = null;

         CodeUnit cu = null;



    public boolean init(Operator op, CodeGenOptions opts) {

        opInst = op;

        options = opts;

        System.out.println("DEBUG:== operator name = " + op.getItemName() +

                            ", label = " + op.getItemLabel());

        return false;

    }



    public List getCodeUnits() {

        Vector cuList = new Vector();

        CodeUnit customOpCodeUnit=CUHelper.createCU(

                CodeType.SQLSCRIPT_LITERAL,

                Action.EXECUTION_LITERAL,

                Phase.RUNTIME_LITERAL);

        String fileName = (String)opInst.getPropertyValueRef("fileName");

        cuList.add(customOpCodeUnit);

        return cuList;

    }

}







    Here is an example of an operator library. The name of the library is sampleLib. It has a

custom operator defined in it. Name of the operator is sampleOp.




 xmlns:oplib="http:///com/ibm/datatools/etl/operatorlibrary.ecore"

 xmlns:coretypes="http:///com/ibm/datatools/etl/coretypes.ecore"

 xmlns:SQLDataTypes="http:///org/eclipse/wst/rdb/models/sql/datatypes.ecore"

 name="europa_customOp_lib" nsURI="http:///org/europa/sampleOp/lib/sampleOp.oplib">



    

        



                                             8

              
              className="org.europa.sampleOp.codeGenForSampleOp"/>

              

              

          

      









7.2    Define design time GUI artifacts

Once the backbone of the operator is defined, the next task is to make the custom operator appear

in the operator palette in the Europa runtime workbench.

    Step 1: Create another plug-in project. Add the plugins com.ibm.datatools.etl.dataflow.ui

and the com.ibm.datatools.etl.dataflow.base.oplib.ui as dependencies.

    Step 2: Add the com.ibm.datatools.etl.properties.ui.OperatorLibraryPresentation

extension. Enter the OpLibPresentation file name in the plugin.xml file. The file name must have

.prxmi extension. After this, the plugin.xml file will look something like this. (suppose the name

of the OpLibPresentation file name was sampleOp.prxmi.



 


   id="org.europa.sampleOp.lib.ui"

   name="Ui Plug-in"

   version="1.0.0"

   provider-name="">



   

      

         

      

   

   

      

      

   

   
         id="org.europa.sampleOp.lib.ui.Presentation"

         name="Sample Custom Operator Presentation"

         point="com.ibm.datatools.etl.properties.ui.OperatorLibraryPresentation">

         

   











                                               9

    Step 3: Create and edit the presentation file (the one with prxmi extension). Add header and

footer to that and an empty palette category to it.






  xmlns:pres="http://com.ibm.datatools.etl.dataflow.presentation">



    
        id="sampleCat"

        label="sampleOps_category_name"

        description="sampleOps_category_desc"

        appliesToEditorType="DataFlowEditor"

        smallIcon="/icons/sampleOpCategory.gif"

       />







   Step 4: Add an palette entry for the custom operator. Put it inside the scope of a category.

Here is an example.




  id="sampleCat.sampleOp"

  label="sampleOpName"

  description="sampleOp_desc"

  categoryID="sampleOpPaletteLib"

  widgetTypeID="http:///org/europa/sampleOp/lib/sampleOp.oplib/sampleCat/sampleOp"

  appliesToEditorType="DataFlowEditor"

  smallIcon="/icons/sampleOpIcon.gif"

/>





The icon files have to be placed in the icons subdirectory under the ui plugin directory. Be sure

about the icon names. The type of the icon file and the name should be exactly as you have

specified in the prxmi file. In the examples above, the entries “sampleOps category name�?, “sam-

pleOps category desc�?, “sampleOpName�?, “sampleOp desc�?, are analogous to variables in pro-

gramming language terminology. Their exact values can be defined in plugin.properties file. Edit

the file (create it first if it does not exist) and enter the values of the above mentioned variables.



sampleOps_category_name=My custom operators

sampleOps_category_desc=This category contains my own operators



sampleOpName=sample operator

sampleOp_desc=This is a sample operator





   Now the operator is ready for use. You can create a runtime workbench and execute it. You

can see the operator in the operator palette.





                                                10

8    Future Work

The rules that can be found out by our application can be used to filter some classes of dirty

data. However, there are many complex rules which are very common in databases. For example,

ordering relationship between two columns, algebraic relationship between two or more columns.

Sometimes the value in one column restrict the range or domain of some other column. Once these

rules are discovered from the source data, the power of the data cleaning phase will increase a lot.

Focus can be given on generating Europa operators having complex processing power. Currently

the ETL process designer has to draw the data flow and the control flow diagrams. His work can

be reduced if data flows can be generated automatically.





9    Conclusion

In this report we have discussed why data profiling is needed for ETL processes. We have seen

some classes of dirty data. We have described the functionality of our data profiling tool and

the technique to come up with the rules. These rules (or their modified versions) can be used to

eliminate some type of dirtiness. However, coming up with more sophisticated rules can ensure

more quality data to be sent to the target data repository. We have also described how to create

custom operators for Europa platform and make them available for creating data flows in Europa.



                                       Acknowledgement



    I am thankful to my mentor, Natwar Modani, for his help, support and guidance throughout

this work. I am indebted to Mr Mukesh Mohania, for many fruitful discussions. I have been

very lucky to interact with other employees in IBM India Research Lab, for their suggestions and

constant encouragement. Special thanks to Mr Dinkar Rao for explaining everything in minute

details whenever I sought help from him.





References

[KCH+ 03] Won Y. Kim, Byoung-Ju Choi, Eui Kyeong Hong, Soo-Kyung Kim, and Doheon Lee.

          A taxonomy of dirty data. Data Min. Knowl. Discov., 7(1):81�?99, 2003.



[Sria]     Sriram Srinivasan. Europa Developer note.



[Srib]     Sriram Srinivasan. Ibm dwe etl (europa).









                                                11