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

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

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