Data Profiling in Datawarehouse

Data Profiling    

A process whereby one examines the data available in an existing database and collects statistics and information about that data. The purpose of these statistics may be to:

• Find out whether existing data can easily be used for other purposes 
• Give metrics on data quality including whether the data conforms to company standards
•  Assess the risk involved in integrating data for new applications, including the challenges of joins
•  Track data quality
•  Assess whether metadata accurately describes the actual values in the source database
•   Understanding data challenges early in any data intensive project, so that late project surprises are avoided. Finding data problems late in the project can incur time delays and project cost overruns.

Data Profiling categories    :

The overall process is in three steps, which must be executed in order:

•  Column Profiling Column profiling provides critical metadata which is required in order to perform dependency profiling, and as such, must be executed before dependency profiling.

•  Dependency Profiling, which identifies intra-table dependencies. Dependency profiling is related to the normalization of a data source, and addresses whether or not there are non-key attributes that determine or are dependent on other non-key attributes. The existence of transitive dependencies here may be evidence of second-normal form.

•  Redundancy Profiling, which identifies overlapping values between tables. This is typically used to identify candidate foreign keys within tables, to validate attributes that should be foreign keys (but that may not have constraints to enforce integrity), and to identify other areas of data redundancy. Example: redundancy analysis could provide the analyst with the fact that the ZIP field in table A contained the same values as the ZIP_CODE field in table B, 80% of the time.

Benefits of Data Profiling:

• The benefits of data profiling is to improve data quality, shorten the implementation cycle of major projects, and improve understanding of data for the users^.

•  Discovering business knowledge embedded in data itself is one of the significant benefits derived from data profiling^..         

• Data profiling is one of the most effective technologies for improving data accuracy in corporate databases.      

• Although data profiling is effective, it can be challenging not slip into analysis paralysis.

Data Profiling

Change data capture System in Datawerhouse

Change Data Capture (CDC) is a generic term for techniques that monitor operational data sources with the objective of detecting and capturing data changes of interest . CDC is of particular importance for data warehouse maintenance.

With CDC techniques in place, the data warehouse can be maintained by propagating changes captured at the sources. CDC techniques applied in practice roughly follow three main approaches,

namely

1.log-based CDC.

2.utilization of audit columns

3.calculation of snapshot differentials

1.Log-based CDC techniques parse system logs and retrieve changes of interest. These techniques are typically employed in conjunction with database systems. Virtually all database systems record changes in transaction logs. This information can be leveraged for CDC. Alternatively, changes may be explicitly recorded using database triggers or application logic for instance.

2.utilization of audit columns :

 Operational data sources often employ so called audit columns. Audit columns are appended to each tuple and indicate the time at which the tuple was modified for the last time. Usually timestamps or version numbers are used. Audit columns serve as the selection criteria to extract changes that occurred since the last incremental load process. Note that deletions remain undetected.

3.calculation of snapshot differentials

The snapshot differential technique is most appropriate for data that resides in unsophisticated data sources such as flat files or legacy applications. The latter typically offer mechanisms for dumping data into files but lack advanced query capabilities. In this case, changes can be inferred by comparing a current source snapshot with a snapshot taken at a previous point in time. A major drawback of the snapshot differential approach is the need for frequent extractions of large data volumes. However, it is applicable to virtually any type of data source.

• The above mentioned CDC approaches differ not only in their technical realization but also in their ability to detect changes. We refer to the inability to detect certain types of changes as CDC limitation. 
• As mentioned before deletions cannot be detected by means of audit columns. Often a single audit column is used to record the time of both, record creation and modification. In this case insertions and updates are indistinguishable with respect to CDC. Another limitation of the audit columns approach is the inability to retrieve the initial state of records that have been updated. 
• Interestingly, existing snapshot differential implementations usually have the same limitation. They do not provide the initial state of updated records while this would be feasible in principle. 
• since the required data is available in the snapshot taken during the previous run. 
• Log-based CDC approaches in practice typically capture all types of changes, i.e. insertions, deletions, and the initial and current state of updated records.

Defined different methods for Change data capture in datawearhouse.

1. Timestamp on rows.
2. Use in Optimistic locking.
3. Status Indicator on rows.
4. Time/Version/Status on Rows.
5. Trigger on Tables
6. Event Programming.
7. Log Scanners on databases.

Facebook Commentbox

What is ETL (Extract, Transform, Load)

ETL refers to the methods involved in accessing and   manipulating source   data and loading it into target database. During the ETL process, more   often, data is extracted from an OLTP database, transformed to   match the data warehouse schema, and loaded into the data warehouse   database.

(Extract, Transform, Load) –

ETL Process

 EXTRACTION :

• Data Capturing: The ETL extraction element is responsible for extracting data from the source system. During extraction, data may be removed from the source system or a copy made and the original data retained in the source system.

Data Capturing

• Data Transmission: Legacy systems may require too much effort to implement such offload processes, so legacy data is often copied into the data warehouse, leaving the original data in place. Extracted data is loaded into the data warehouse staging area (a relational database usually separate from the data warehouse database), for manipulation by the remaining ETL processes. 

Data Transmission

• Cleansing Process: 

1. Data extraction is generally performed within the source system itself.
2. Data extraction processes can be implemented using Transact-SQL stored procedures, Data Transformation Services (DTS) tasks, or custom applications developed in programming or scripting languages. 

Transformation:

         The ETL transformation element is responsible for data validation, data accuracy, data type conversion, and business rule application. An ETL system that uses inline transformations during extraction is less robust and flexible than one that confines transformations to the reformatting element. Transformations performed in the OLTP system impose a performance burden on the OLTP database.

• Data Validation: Check that all rows in the fact table match rows in dimension tables to enforce data integrity.

• DataAccuracy : Ensure that fields contain appropriate values, such as only "off" or "on" in a status field.

• Data Type Conversion : Ensure that all values for a specified field are stored the same way in the data warehouse regardless of how they were stored in the source system. For example, if one source system stores "off" or "on" in its status field and another source system stores "0" or "1" in its status field, then a data type conversion transformation converts the content of one or both of the fields to a specified common value such as "off" or "on".

Transformation

• Business Rule Application: Ensure that the rules of the business are enforced on the data stored in the warehouse. For example, check that all customer records contain values for both FirstName and LastName fields. 

Loading :

• The ETL loading element is responsible for loading transformed data into the data warehouse database.
• Data warehouses are usually updated periodically rather than continuously, and large numbers of records are often loaded to multiple tables in a single data load.
• The data warehouse is often taken offline during update operations so that data can be loaded faster and SQL Server 2000 Analysis Services can update OLAP cubes to incorporate the new data. BULK INSERT, bcp, and the Bulk Copy API are the best tools for data loading operations.
• The design of the loading element should focus on efficiency and performance to minimize the data warehouse offline time.

Facebook Commentbox

    

Logical Design In Datawearhouse

Logical Data Model (LDM) -

• A logical design is conceptual and abstract. The process of logical design involves arranging data into a series of logical relationships called entities and attributes.
• Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.
• An entity represents a chunk of information. In relational databases, an entity often maps to a table. An attribute is a component of an entity and helps define the uniqueness of the entity. In relational databases, an attribute maps to a column.

Logical design  in Data wearhouse

Important Terms:

Entity

•      Entity: Are the principal data object about which information is to be collected. A class of persons, places, objects, events, or concepts about which we need to capture and store data.

1.      Persons:  agency, contractor, customer, department, division, employee,       instructor, student, supplier.
2.       Places:  sales region, building, room, branch office, campus. 
3.       Objects:  book, machine, part, product, raw material, software license,  software package, tool, vehicle model, vehicle.  
4.       Events:  application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip.
5.       Concepts:  account, block of time, bond, course, fund, qualification, stock.
6. 
   

•      Relationship:A natural business association that exists between one or more entities. The relationship may represent an event that links the entities or merely a logical affinity that exists between the entities

Relationship

         An example of a relationship would be:

1. Employees are assigned to projects.
2. Student enrolling in a curriculum.
3. Projects have subtasks.
4. Departments manage one or more projects 

• 
  
•      Cardinality:The cardinality of a relationship is the actual number of related occurrences for each of the two entities. The basic types of connectivity for relations are: one-to-one, one-to-many, and many-to-many. The minimum and maximum number of occurrences of one entity that may be related to a single occurrence of the other entity. Because all relationships are bidirectional, cardinality must be defined in both directions for every relationship.

Cardinality

•     Cardinality Notations:

       

    

Facebook Commentbox

Physical design in datawearhouse

Physical design is the creation of the database with SQL statements. During the physical design process, you convert the data gathered during the logical design phase into a description of the physical database structure.

A complete physical data model will include all the database artifacts required to create relationships between tables or achieve performance goals, such as indexes, constraint definitions, linking tables, partitioned tables or clusters.

Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables.

Features of a physical data model include:

• Specification all tables and columns.
• Foreign keys are used to identify relationships between tables.
• Denormalization may occur based on user requirements.
• Physical considerations may cause the physical data model to be quite different from the logical data model.
• Physical data model will be different for different RDBMS. For example, data type for a column may be different between MySQL and SQL Server.

The steps for physical data model design are as follows:

1. Convert entities into tables.
2. Convert relationships into foreign keys.
3. Convert attributes into columns.
4. Modify the physical data model based on physical constraints / requirements.

Physical Design Structures:

• Table spaces : A tablespace consists of one or more data files, which are physical structures within the operating system you are using. A data file is associated with only one tablespace. From a design perspective, table spaces are containers for physical design structures.

• Tables and Partitioned Tables : Tables are the basic unit of data storage. They are the container for the expected amount of raw data in your data warehouse. Using partitioned tables instead of non-partitioned ones addresses the key problem of supporting very large data volumes by allowing you to decompose them into smaller and more manageable pieces.

• Views : A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Views do not require any space in the database.

• Integrity Constraints : Integrity constraints are used to enforce business rules associated with your database and to prevent having invalid information in the tables. Integrity constraints in data warehousing differ from constraints in OLTP environments. In OLTP environments, they primarily prevent the insertion of invalid data into a record, which is not a big problem in data warehousing environments because accuracy has already been guaranteed.

• Indexes : Indexes are optional structures associated with tables or clusters. In addition to the classical B-tree indexes, bitmap indexes are very common in data warehousing environments.  

                             

Facebook Commentbox

Surrogate key in Datawearhouse with example

What is Surrogate Key?

• Surrogate Keys are integers that are assigned sequentially in the dimension table which can be used as PK.
• Surrogate key is a unique identification key, it is like an artificial or alternative key to production key, because the production key may be alphanumeric or composite key but the surrogate key is always single numeric key. 
• Assume the production key is an alphanumeric field if you create an index for this fields it will occupy more space, so it is not advisable to join/index, because generally all theDatawearhouse fact table are having historical data. 
• These fact Table are linked with so many dimension table. if it's a numerical fields the performance is high
• Surrogate key is the primary key for the Dimensional table.
• It’s a substitution for the natural primary key.
• It is just a unique identifier or number for each row that can be used for the primary key to the table. 
• The only requirement for a surrogate primary key is that it is unique for each row in the table.
• Data warehouses typically use a surrogate, (also known as artificial or identity key)  key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.
• It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

Example:

          Let us consider an scenarios where you have designed a very good Data Warehouse , its catering all your reporting need and its in production. After two years organization decides to reuse there business keys of products

e.g.

         There was product called Baby Powder after two years organization decides to stop selling at its cost is high and sell is low and instead Baby Powder they launched a Talcum Powder for Men and want to give same key to the product say 336.

         Now while designing a Data Warehouse you have used Business Keys as Primary Key in dimension table with this new change you will have to update the dimension table to replace Baby Powder with talcum Powder and organization does want to remove all data of Baby Powder, what will you do now?

        So to avoid such situation its always better to use Surrogate Keys as Primary Key in dimension table along with Business Key.

e.g.

SK_Prouduct	Product_ID	Product Name	Cost	Acive
1	336	Baby Powder	444	N
2	345	Cream	34	Y
3	336	talkem Powder	44	Y

Now with above table you can use same code for another Product, mark the product as active and inactive and maintain all the product in Data Warehouse

Advantages of Surrogate Key:

• Surrogate Key allow to cater all your data need that arises from the Business or operational changes and recycling.

• Surrogate Keys allow the data warehouse to integrate data from all sources if they lack natural Business keys.

• Surrogate keys are very helpful for ETL transformations.

Facebook Commentbox

Datawearhouse concepts

Hello all.........

Welcome to Datawearhousing and BusinessIntelligence Tutorials world.

Enjoy the following things in this blog.

1.   Datawerhousing and Business Intelligence Concepts.
2.   Sql Practicle Query Interview Questions asked in technicle Interview.
3.   Datawearhousing and BI job Posting Specially for Mumbai and pune people (Others city    also)
4.   Download DWH and BI study Materials like DWH books,Offline Tutorials,Scenario      Interviews Questions.
5. Study Materials for Other BI tools like Informatica,Cognos,Business Objects,SSIS,
6. Database concepts and other database tools,Interview questions regarding other tools.

Tired of checking for new posts ?

Get all posts directly in your mail.

Facebook Commentbox