[Link] OF ENGINEERING – TIRUCHENGODE 637 215.

(AUTONOMOUS)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

Course Name : 20CS765 - Business Intelligence

Class : IV CSE
UNIT – II
DATA INTEGRATION
Data Warehouse – Need and Goals of Data Warehouse – Data Integration – Need and
Advantages of Data Integration – Common Data Integration Approaches – Data Integration
Technologies – Data Quality – Data Profiling Concepts and Applications – Introduction to
ETL using SSIS.

DATA WAREHOUSE

A data warehouse is a subject-oriented, integrated, time variant and non-volatile collection of

data in support of management’s decision making process.

A data warehouse is a system used for reporting, data analysis, and decision support, and it
acts as a central database where integrated data from various sources like OLTP (Online
Transaction Processing) systems, flat files, and external data sources is stored.

Data Mart
A Data Mart is a subset of a data warehouse that is focused on a specific business line or
department, such as sales, marketing, or finance.

NEED FOR DATA WAREHOUSE

 Lack of Information Sharing
  Lack of information credibility
 Reports take a longer time to be prepared
 Little or no scope for ad hoc querying or queries that require historical data.

GOALS OF DATA WAREHOUSE

 Information accessibility
 Information credibility
 Flexible to change
 Support for more fact-based decision making
 Support for the data security
 Information consistency

Data Warehouse – Advantage and Limitations

• Advantages
• Integration at the lowest level, eliminating need for integration queries.
• Runtime schematic cleaning is not needed – performed at the data staging
environment
• Independent of original data source
• Query optimization is possible.
Limitations
• Process would take a considerable amount of time and effort
• Requires an understanding of the domain
• More scalable when accompanied with a metadata repository – increased load.
• Tightly coupled architecture
 DATA INTEGRATION
Process of coherent merging of data from various data sources and presenting a
cohesive/consolidated view to the user.
• Involves combining data residing at different sources and providing users with a
unified view of the data.
• Significant in a variety of situations
• Commercial
• Scientific

NEED AND ADVANTAGES OF DATA INTEGRATION

Need for Data Integration

 It is done for providing data in a specific view as requested by users, applications
 Increases with the need for data sharing

Advantages of Using Data Integration

 Benefit to decision-makers, who have access to important information from past
studies
 Reduces cost, overlaps and redundancies; reduces exposure to risks
 Helps to monitor key variables like trends and consumer behaviour, etc.
 COMMON DATA INTEGRATION APPROACHES
 Federated databases
 Memory-mapped data structure
 Data warehousing

Federated database (virtual database):

 Type of meta-database management system which transparently integrates multiple
autonomous databases into a single federated database
 The constituent databases are interconnected via a computer network, geographically
decentralized.
 The federated databases is the fully integrated, logical composite of all constituent
databases in a federated database management system.
 The federated database system was defined by McLeod and Heimbigner.

Memory-mapped data structure:

 Useful when needed to do in-memory data manipulation and data structure is large.
 It’s mainly used in the dot net platform and is always performed with C# or using
[Link]
 It’s is a much faster way of accessing the data than using Memory Stream.

Data warehousing
The various primary concepts used in data warehousing would be:
 ETL (Extract Transform Load)
 Component-based (Data Mart)
 Dimensional Models and Schemas
 Metadata driven

Comparison between federated database and data warehouse

Federated Data Warehouse
Preferred when the database are present Preferred when the source information can be
across various locations over a large area taken from one location
Data would be present in various servers The entire data warehouse would be present
in one server
Requires high speed network connection Requires no network connection
It is easier to create as compared to data Its creation is not as easy as that of the
warehouse federated database
Requires network expert to setup network Requires database experts
connection

DATA INTEGRATION TECHNOLOGIES

Integration is divided into two main approaches:

Schema integration
Multiple data sources may provide data on the same entity type. The main goal is to allow
applications to transparently view and query this data as one uniform data source, and this is
done using various mapping rules to handle structural differences.

―Schema integration is developing a unified representation of semantically similar

information, structured and stored differently in the individual databases‖.

Example:

Branch 1 : custid, transid, prodid, unit

C1 T1 P1 10

Branch 2: customerid, transid, prodid, unit

C2 T2 P2 20
Map two database use metadata information. Then output will be

Branch1,2: custid, transid, prodid, unit

C1 T1 P1 10
C2 T2 P2 20

Instance integration
Data integration from multiple heterogeneous data sources has become a high-priority task in
many large enterprises. Hence to obtain the accurate semantic information on the data
content, the information is being retrieved directly from the data. It identifies and integrates
all the instance of the data items that represents the real-world entity, distinct from the
schema integration.

Example:
Database 1 :
Empno empname noofleave
E1 John kumar 4

Database 2 :
 Empno empname noofpresent
E1 J. kumar 25

Database 3 :
Empno empname salary
E1 John. k 25000

Solution: use Empno common attribute and replace the value in the empname with one
consistent value such as John kumar in all places.

Database 1 :
Empno empname noofleave
E1 John kumar 4

Database 2 :
Empno empname noofpresent
E1 John kumar 25

Database 3 :
Empno empname salary
E1 John kumar 25000

Data Integration Technologies

(i) Electronic Data Interchange (EDI) :

• It refers to the structured transmission of data between organizations by electronic means.
It is used to transfer electronic documents from one computer system to another (ie) from one
trading partner to another trading partner.
• It is more than mere E-mail.

(ii)Object Brokering/Object Request Broker (ORB):

• An ORB is a piece of middleware software that allows programmers to make programs
calls from one computer to another via a network.
• It handles the transformation of in-process data structure to and from the byte sequence.

(iii)Modeling techniques
 Entity-Relational Modeling
 Dimensional Modeling

Entity-Relational Modeling

The Entity-Relationship Model (ER Model) is a conceptual model for designing a databases.
This model represents the logical structure of a database, including entities, their attributes
and relationships between them.
Steps to drawing an ER model:
 Identify entities
 Identify relationships between various entities
 Identify the key attribute
 Identify the other relevant attributes for the entities
 Draw the ER diagram
  Review the ER diagram with business users.

Dimensional Modeling
Its a logical design techniques. The main focus is to present data in a standard format for end
user consumption.
 Star schema
 Snowflake scheme
Every dimensional model is composed of one fact table and a number of dimensional tables.

Difference between ER modelling and Dimensional modelling

ER Modelling Dimensional Modelling

Eliminate redundant data Does not eliminate redundant data
Highly normalized It aggregates most of the attributes and
hierarchies of a dimension into a single
entity

Useful for transactional systems Useful for analytical systems

It is split as per the entities It is split as per the dimensions and facts
Relationship between ER model and Dimensional model
Steps to convert ER diagram into set of dimensional models
1. Separate out the various business processes and represent each as a separate
dimensional model.
2. Identify all many-to-many relationships in ER diagram and construct them into fact
table.
3. De-normalize all the remaining tables into single part key tables.

DATA QUALITY
Data Quality refers to the accuracy, completeness, reliability, and relevance of data for its
intended use. High-quality data is essential for effective decision-making, analytics, and
operations in any data-driven environment.

Key dimensions of data quality

 Correctness
 Consistency
 Completeness
 Timeliness
 Uniqueness
 Integrity

Why Data Quality is Important:

 Improves decision-making
 Enhances customer satisfaction
 Reduces operational costs
 Supports compliance and reporting
 Strengthens analytics and business intelligence

How to Ensure/maintain Data Quality:

 Data profiling and auditing

 Validation rules and constraints
 Regular cleansing and deduplication
 Master Data Management (MDM)
 Data governance policies

Data integrity
Data Integrity refers to the accuracy, consistency, and reliability of data throughout its
lifecycle — from creation to storage, processing, and retrieval.
It ensures that data remains unchanged, valid, and trustworthy unless deliberately
modified through authorized processes.

Comparison between data integrity and data quality

 Aspect Data Integrity Data Quality

Definition Ensures data is accurate, consistent, Measures how well data is fit for its
and secure over its entire lifecycle intended purpose
Focus Correct structure, relationships, and Content accuracy, completeness,
protection from corruption timeliness, and relevance
Scope More technical and database- Broader business and analytical context
focused
Maintained By Database administrators Data stewards
System architects Business analysts
Data engineers

DATA PROFILING CONCEPTS AND APPLICATIONS

Data profiling is the process of examining, analyzing, and summarizing data to understand its
structure, content, and quality.
It helps to assess the data's accuracy, completeness, consistency, and uniqueness, which are
essential for tasks like data cleaning, data integration, and data quality improvement.

Example : Phone no (9727)483491

Objectives of Data Profiling:

• Understand the Data:

• Assess Data Quality:
• Support Data Management Tasks:

Data profiling can be either data quality profiling or database profiling

Data quality profiling – Analyzing the data from data source against certain specified rules
or requirements.

Database profiling – Analyze of database with respect to its scheme, relationship between
tables, columns used , data type of column, keys of the table.

Types of Data Profiling Techniques:

Type Description
Structure Discovery Analyzes data types, lengths, formats, and schema adherence.
Examines actual data values (e.g., min, max, mean, frequency
Content Discovery
distribution).
Relationship Detects relationships across columns/tables (e.g., foreign key
Discovery candidates).

When to conduct data profiling?

• At the requirement gathering phase
 • Just before the dimensional modelling process
• During ETL package design

How to conduct data profiling?

• Data quality – analyze quality of data at data source
• Null values – look out the null values in an attribute
• Candidate keys – analyze the candidate keys
• Primary key selection
• Empty string values
• String length
• Numeric length and type
• Identification of cardinality
• Data format
Applications / Data profiling software / Tools

• Trillium Enterprise Data quality

o It scan all data systems
o Remove duplicate records
o Generae statistical reports about the data

• Datiris Profiler
o Compatible with other applications
o Domain validation
o Command line interface
o Pattern analysis
o Real time data viewing

• Talend Data Profiler

o Free open source software
o Good for small business and non-profit organizations

• IBM InfoSphere information analyzer

o Perform deep scan of the system
o Scanning scheduler
o Reports
o Rules analysis
o Source system profiling and analysis

• Oracle warehouse builder

o It is not strictly a data profiling software tool
o Provide necessary functionalities to clean data

• SSIS data profiling task (Microsoft SQL Server Data Tools)

o It is not independent software tool
o It is integrated into ERL software SQL Server Integration Service (SSIS)
provided by Microsoft.

• Pandas (in Python)

• OpenRefine
 INTRODUCTION TO ETL USING SSIS

ETL
Extract - Retrieve data from source systems (databases, files, APIs, etc.)
Transform - Clean, format, and convert data (e.g., remove duplicates, change data types)
Load - Store the transformed data into the target (e.g., SQL Server, data warehouse)

SQL Server Integration Services (SSIS) is a Microsoft tool used for ETL operations. It is
part of the Microsoft SQL Server Data Tools (SSDT) suite.

SSIS enables:

 Data migration and transformation

 Workflow creation and automation
 Integration from multiple sources (SQL, Excel, XML, etc.)

Implementing ETL using SSIS Components

The ETL acronym stands for Extraction, Transformation, and Loading. This process involves
extracting, transforming, and loading data into the final repository.
ETL is the process of loading data into the warehouse from the source system.
ETL process step-by-step.
 o Extraction (E): Collection of data from different sources.
o Transformation (T): A different form of data obtained from different sources and
converted according to business needs.
o Loading (L): The Data warehouse contains the loaded data.

ETL is facilitated by SSIS components, which are listed below:

• Control Flow (for storing containers and tasks)
• Data Flow (Source, destination, and transformations)
• Event Handler (for managing messages and e-mails)
• Package Explorer (for offering an all-in-one view)
• Parameters (for fostering user interaction)

ETL Process in SSIS:

1. Create a New SSIS Project in SQL Server Data Tools (Visual Studio).
2. Add Data Flow Task in Control Flow.
3. Inside Data Flow:
o Use a Source (like OLE DB Source) to extract data.
o Apply Transformations (like Data Conversion, Conditional Split).
o Use a Destination (like SQL Server, Flat File, Excel) to load data.
4. Run or Schedule the Package using SQL Server Agent or command-line tools.

SSIS Package
An SSIS Package is a collection of control flow and data flow tasks saved as a .dtsx file. It
can be deployed to the SSIS catalog for reuse, monitoring, and scheduling.

Benefits of SSIS for ETL

 High performance for large data volumes

 Drag-and-drop GUI for rapid development
 Built-in connectors for various sources and destinations
 Error handling and logging support
 Easy integration with other SQL Server services