UNIT II

Data warehouse Architecture and its

components
Data warehouse development life
cycle,
► Data warehousing is the process of developing, managing, and
securing the electronic storage of data by a business or organization
in a digital warehouse.
► The main goal of data warehousing is to create a treasure of
historical data that can be used at any time for analysis and to
discover useful insights to make a better decision for the
organization.
Data warehouse development life cycle
► Data Warehousing is a flow process used to gather and handle
structured and unstructured data from multiple sources into a
centralized repository to operate actionable business decisions.
Requirement Specification
► It is the first step in the development of the Data Warehouse and is
done by business analysts.
► In this step, Business Analysts prepare business requirement
specification documents. More than 50% of requirements are
collected from the client side and it takes 3-4 months to collect all the
requirements.
► After the requirements are gathered, the data modeler starts
recognizing the dimensions, facts & combinations based on the
requirements.
► This phase is more about determining business needs and placing
them in the data warehouse.
Data Modelling:
► Data Modelling is the process of visualizing data distribution and designing
databases by fulfilling the requirements to transform the data into a format that
can be stored in the data warehouse.
► For example, whenever we start building a house, we put all the things in the
correct position as specified in the blueprint. That’s what data modeling is for data
warehouses.
► Data modelling helps to organize data, creates connections between data sets,
and it’s useful for establishing data compliance and its security that line up with
data warehousing goals.
► It is the most complex phase of data warehouse development. And, there are
many data modelling techniques that businesses use for warehouse design.
► Data modelling typically takes place at the data mart level and branches out in a
data warehouse. It’s the logic of how the data is stored concerning other data.
There are three data models for data warehouses:
► Star Schema
► Snowflake Schema
► Galaxy Schema.
ELT Design and Development:

► ETL or Extract, Transfer, Load tool may extract data from various
source systems and store it in a data lake.
► An ETL process can extract the data from the lake, after that
transform it and load it into a data warehouse for reporting.
► For optimal speeds, good visualization, and the ability to build easy,
replicable, and consistent data pipelines between all of the existing
architecture and the new data warehouse, we need ELT tools.
► ETL tools like SAS Data Management, IBM Information Server, Hive,
etc. come into the picture. A good ETL process can be helpful in
constructing a simple yet functional data warehouse that’s valuable
throughout every layer of the organization.
OLAP Cubes:
► An OLAP cube, also known as a multidimensional cube or hypercube, is a
data structure that allows fast analysis of data according to the multiple
dimensions that define a business problem.
► A data warehouse would extract information from multiple data sources
and formats like text files, excel sheets, multimedia files, etc. The
extracted data is cleaned and transformed and is loaded into an OLAP
server (or OLAP cube) where information is pre-processed in advance for
further analysis.
► Usually, data operations and analysis are performed using a simple
spreadsheet, where data values are arranged in row and column format.
► This is ideal for two-dimensional data. However, OLAP contains
multidimensional data, with data typically obtained from different and
unrelated sources. Employing a spreadsheet isn’t an optimum choice.
► The cube will store and analyze multidimensional data in a logical and
orderly manner. Now, data warehouses are now offered as a fully built
product that is configurable and capable of staging multiple types of
data.
UI Development:

► This is the fifth step in the development of the Data Warehouse.

► There is a need for a user interface for how the user and a computer
system interact, in particular the use of input devices and software,
to immediately access the data warehouse for analysis and
generating reports.
► The main aim of a UI is to enable a user to effectively manage a
device or machine they’re interacting with.
► There are plenty of tools in the market that helps with UI
development. BI tools like Tableau or PowerBI for those using
BigQuery are great choices.
Maintenance:

► In this phase, we can update or make changes to the schema and

data warehouse’s application domain or requirements.
► Data warehouse maintenance systems must provide means to keep
track of schema modifications as well, for instance, modifications. At
the schema level, we can perform operations for the Insertion, and
change dimensions and categories. Changes are, for example, adding
or deleting user-defined attributes.
Test and Deployment

► This is often the ultimate step in the Data Warehouse development cycle.
Businesses and organizations test data warehouses to ensure whether the
required business problems are implemented successfully or not.
► The warehouse testing involves the scrutiny of enormous volumes of data.
Data that has to be compared comes from heterogeneous data sources like
relational databases, flat files, operational data, etc.
► The overall data warehouse project testing phases include: Data
completeness, Data Transformation, Data is loaded by means of ETL tools,
Data integrity, etc.
► After testing the data warehouse, we deployed it so that users could
immediately access the data and perform analysis.
► At the time of data warehouse deployment, most of its functions are
implemented. The data warehouses can be deployed at their own data
center or on the cloud.
Data Warehouse’s Components
[Link] Data Component
[Link] Staging Component
3. Data Storage Component
[Link] Delivery Component
5. Metadata Component
[Link] and Control
Component
Data Warehouse Architecture

► Three common architectures are:

1. Data Warehouse Architecture: Basic

2. Data Warehouse Architecture: With Staging Area
3. Data Warehouse Architecture: With Staging Area and Data Marts
Data Warehouse Architecture: Basic
⮚ Operational System:-
An operational system is a method to process the day-to-day
transactions
of an organization.
► Flat Files
A Flat file system is a system of files in which transactional data is
stored,
and every file in the system must have a different name.
► Meta Data
-A set of data that defines and gives information about other data.
-Meta Data used in Data Warehouse for a variety of purpose, including:
Meta Data summarizes necessary information about data, which can
make
finding and work with particular instances of data more accessible.
-For example, author, data build, and data changed, and file size are
examples of very basic document metadata.
-Metadata is used to direct a query to the most appropriate data source.
► Lightly and highly summarized data
The area of the data warehouse saves all the predefined lightly and
highly summarized (aggregated) data generated by the warehouse
manager.
The goals of the summarized information are to speed up query
performance. The summarized record is updated continuously as new
information is loaded into the warehouse.
► End-User access Tools
The principal purpose of a data warehouse is to provide information to
the business managers for strategic decision-making. These customers
interact with the warehouse using end-client access tools.
Data Warehouse Architecture: With Staging
Area
► We must clean and process your operational information before put it
into the warehouse.
► A staging area simplifies data cleansing and consolidation for
operational method coming from multiple source systems, especially
for enterprise data warehouses where all relevant data of an
enterprise is consolidated.
Data Warehouse Staging Area is a temporary location where
a record from source systems is copied.
Data Warehouse Architecture: With Staging Area and Data Marts

► We may want to customize our warehouse's architecture for multiple

groups within our organization.
► We can do this by adding data marts. A data mart is a segment of a
data warehouses that can provided information for reporting and
analysis on a section, unit, department or operation in the company,
e.g., sales, payroll, production, etc.
► The figure illustrates an example where purchasing, sales, and stocks
are separated. In this example, a financial analyst wants to analyze
historical data for purchases and sales or mine historical information
to make predictions about customer behavior.
Three-Tier Architecture of Warehouse Architectures
► The three-tier architecture consists of the
o source layer (containing multiple source system),
o reconciled layer and
o Data warehouse layer (containing both data warehouses and data
marts). The reconciled layer sits between the source data and data
warehouse.
► The main advantage of the reconciled layer is that it creates a
standard reference data model for a whole enterprise.
► In some cases, the reconciled layer is used to accomplish better
some operational tasks, such as producing daily reports
► This architecture is especially useful for the extensive, enterprise-wide
systems. A disadvantage of this structure is the extra file storage space
used through the extra redundant reconciled layer.
❑ Three-Tier Data Warehouse Architecture:
Data Warehouses usually have a three-level (tier) architecture that
includes:
► Bottom Tier (Data Warehouse Server)
► Middle Tier (OLAP Server)
► Top Tier (Front end Tools).
► A bottom-tier that consists of the Data Warehouse server, which
is almost always an RDBMS. It may include several specialized data
marts and a metadata repository.
► Data from operational databases and external sources (such as user
profile data provided by external consultants) are extracted using
application program interfaces called a gateway. A gateway is
provided by the underlying DBMS and allows customer programs to
generate SQL code to be executed at a server.
► Examples of gateways contain ODBC (Open Database Connection)
and OLE-DB (Open-Linking and Embedding for Databases),
by Microsoft, and JDBC (Java Database Connection).
► A middle-tier which consists of an OLAP server for fast querying of
the data warehouse.
► The OLAP server is implemented using either
► (1) A Relational OLAP (ROLAP) model, i.e., an extended relational
DBMS that maps functions on multidimensional data to standard
relational operations.
► (2) A Multidimensional OLAP (MOLAP) model, i.e., a particular
purpose server that directly implements multidimensional information
and operations.
► A top-tier that contains front-end tools for displaying results
provided by OLAP, as well as additional tools for data mining of the
OLAP-generated data.
The overall Data Warehouse Architecture is shown in fig:
► The metadata repository stores information that defines DW
objects. It includes the following parameters and information for the
middle and the top-tier applications:
► A description of the DW structure, including the warehouse schema,
dimension, hierarchies, data mart locations, and contents, etc.
► Operational metadata, which usually describes the currency level of
the stored data, i.e., active, archived or purged, and warehouse
monitoring information, i.e., usage statistics, error reports, audit, etc.
► System performance data, which includes indices, used to improve
data access and retrieval performance.
► Information about the mapping from operational databases, which
provides source RDBMSs and their contents, cleaning and
transformation rules, etc.
► Summarization algorithms, predefined queries, and reports business
data, which include business terms and definitions, ownership
information, etc.
Reasons for Creating a Data Mart:
► Faster Decision Making:
► Data marts are designed to meet the specific needs of business units or
departments, allowing faster access to relevant data for decision-making
without having to sift through the entire data warehouse.
► Improved Performance:
► By narrowing the scope of data to a specific subject area (e.g., finance,
sales, marketing), the system can perform more efficiently, leading to
quicker query responses.
► Cost-Effective:
► For smaller organizations or teams, creating a data mart is more cost-
effective compared to building a full-fledged data warehouse. It is a more
manageable solution for specific business needs.
► Tailored Data for Specific Users:
► Data marts provide a subset of the data warehouse, focusing on the
information needed by a particular department or business function,
allowing users to work with only relevant data.
► Data Accessibility:
► A data mart can be optimized for easier access, simplifying data for non-
technical users. This enhances the overall usability for a specific
department without technical overhead.
► Ease of Implementation:
► Data marts are often easier and faster to implement, allowing a business
to quickly get value from their data and refine their data strategy over
time.
Issues with Data Marts:

► Data Silos:
► If not properly integrated with the larger data warehouse or with other
data marts, a data mart can create data silos. This leads to inconsistency,
as different departments may be using different versions of the same data.
► Redundancy:
► Data marts can result in redundant data storage, as the same data may be
copied from the main data warehouse into each individual data mart. This
increases storage costs and can lead to data inconsistencies.
► Data Integration Challenges:
► Data marts may struggle with integrating data from various sources,
especially when the data warehouse itself is not well integrated. This
can cause problems with data consistency and accuracy
► Maintenance Overhead:
► While the initial creation of a data mart may be simple, maintaining them
can be complex, especially if there are many marts across different
departments. Keeping data updated, consistent, and synchronized can
become time-consuming.
► Lack of Standardization:
► If data marts are created independently by different departments, there
may be a lack of standardization in terms of data models, naming
conventions, and definitions, leading to confusion and errors.
Importance of Metadata in Data
Warehousing:
► Data Discovery:
► Metadata provides crucial information about data (e.g., origin, definitions,
transformations, and relationships) that helps users understand where the
data came from and how it was processed. This makes it easier for users to
discover and trust the data.
► Data Lineage:
► Metadata tracks the flow of data throughout the system, ensuring that
organizations know how data was transformed, where it came from, and
how it moves through different systems. This is critical for maintaining
data integrity and troubleshooting data quality issues.
► Data Quality:
► By defining rules and standards in the metadata, organizations can
monitor data quality more effectively. Metadata helps ensure that
data is consistent, accurate, and adheres to business rules
► Security and Compliance:
► Metadata allows organizations to maintain detailed access control and audit trails for
sensitive data. This is crucial for compliance with data governance regulations (such
as GDPR, HIPAA) and helps in ensuring that only authorized users can access specific
data.
► Improved Data Management:
► Metadata facilitates better data management by allowing users to understand the
structure and relationships of data across the data warehouse. This can significantly
improve the efficiency of managing large and complex data sets.
► Query Optimization:
► Metadata helps database administrators optimize queries by providing information
about data structure, indexes, and access patterns. This leads to faster and more
efficient query execution.
► Documentation and Collaboration:
► Metadata acts as the documentation for the entire data warehouse, providing
business users and IT teams with clear insights into the data structure. It enhances
collaboration between teams and aids in onboarding new users.
► Facilitates Reporting and Analysis:
► When users are familiar with the metadata, they can generate reports and analyses
more effectively because they understand what data is available and how to use it.
 Difference between metadata and data marts in table format
with key characteristics
Feature Metadata Data Mart
Definition Data that describes other data, providing A subset of a data warehouse focused on a
information about its structure, format, and specific business function or department.
meaning.
Purpose Helps in understanding, organizing, and managing Enhances decision-making by providing relevant
data. data to a specific user group.

Schema definitions, data types, relationships, Sales data mart, HR data mart, Finance data mart.
Examples source details.
Scope Broad, applies to various data sources and Narrow, focused on a specific domain or
systems. department.
Users Data analysts, database administrators, Business analysts, department managers,
developers. decision-makers.
Storage Stored in metadata repositories, catalogs, or Stored as structured databases optimized for
dictionaries. queries and reporting.

Independent but essential for managing Depends on the data warehouse or operational
Dependenc databases and data warehouses. databases for data extraction.
y

Usage Used for data governance, documentation, and Used for faster and more efficient querying and
data lineage tracking. reporting.