Module 3

Data Warehousing

Data Warehousing Definitions and Concepts

• Real-time Data Warehousing:
• Enhances business processes by integrating and analyzing large amounts of data from various
sources.
• Provides rapid results to support critical decision-making processes.
• Offers a single version of the truth, expanding innovative business processes.
• Allows quick identification and resolution of problems by viewing the current state of business
in real-time.
What is a Data Warehouse?
• Definition:
• A data warehouse (DW) is a centralized repository of data produced to support decision
making.
• It stores both current and historical data, structured to be available for analytical processing.
Characteristics of Data Warehousing:
• Subject-Oriented: Organized by detailed subjects like sales, products, customers to aid
decision support.
• Integrated: Combines data from various sources into a consistent format.
• Time-Variant: Maintains historical data for trend analysis and comparisons over time.
• Nonvolatile: Data, once entered, is not changed or deleted; changes are recorded as new
data.
Additional Characteristics:
• Web-Based: Designed for efficient Web-based application support.
• Relational/Multidimensional: Uses relational or multidimensional structures for data
storage.
• Client/Server: Employs client/server architecture for easy access.
• Real-Time: Supports real-time data access and analysis capabilities.
• Includes Metadata: Contains data about data, aiding in organization and usage.
Types of Data Warehouses:
• Data Marts: Subsets of data warehouses focused on specific subjects or departments.
o Dependent Data Mart: A subset created from the data warehouse.
o Independent Data Mart: A standalone system designed for a specific business unit.
• Operational Data Stores (ODS):
o Used as an interim area for a data warehouse.
o Provides a near-term view of current data for operational applications.
• Enterprise Data Warehouses (EDW):
o Large-scale warehouse used across the enterprise for decision support.
o Stores data in a standard format for BI and decision support applications.
Metadata:
• Definition:
• Metadata are data about data, providing structure and meaning.
• Includes different types: syntactic, structural, and semantic.
• Importance:
• Enhances data integration, consistency, and accessibility.
• Supports better decision making by providing context and meaning to the data.
• Management Levels:
• Ranges from ad hoc to automated, indicating the maturity level of metadata management
within an organization.
• Ethical Considerations:
• Ensures the ethical collection and ownership of data.
• Addresses privacy and intellectual property issues related to data use.
• Review Questions:
1. What is a data warehouse?
2. How does a data warehouse differ from a database?
3. What is an ODS?
4. Differentiate among a data mart, an ODS, and an EDW.
5. Explain the importance of metadata.
Data Warehousing Process Overview
• Overview:
• Organizations collect data at an accelerated rate, necessitating complex systems for
maintaining and using this data.
• Improved network access and the growing number of users accessing data increase the
complexity.
• Managing multiple databases requires significant expertise but provides immense
benefits.
• Major Components:
[Link] Sources: Sourced from independent systems and external providers.
[Link] Extraction: Using ETL (Extract, Transform, Load) tools.
[Link] Loading: Data transformed and cleansed before loading into the warehouse.
[Link] Database: Supports decision analysis by providing summarized data.
[Link]: Maintained for easy access and organization.
[Link] Tools: Facilitate data access, allowing users to write queries or use query
environments.
Data Warehousing Architectures
• Basic Architectures:
• Two-tier Architecture:
• Tier 1: Client workstation.
• Tier 2: Application and database server.
• Data warehouse and DSS engine on the same hardware platform.
• Three-tier Architecture:
• Tier 1: Client workstation.
• Tier 2: Application server.
• Tier 3: Database server.
• Separates data warehouse functions, improving scalability and
performance.
Two-Tier Architecture:
Tier 1: Client Workstation
• The client workstation is where end-users access the data warehouse.
• Users interact with the data warehouse through applications or tools
installed on their workstations.
Tier 2: Application and Database Server
• This tier combines both the application server and the database server
into a single system.
• The application server processes business logic and application-specific
operations.
• The database server handles data storage, retrieval, and management.
• This architecture is simpler but may face performance issues when
scaling up due to the combined load on a single server.
Three-Tier Architecture:
Tier 1: Client Workstation
• Similar to the two-tier architecture, the client workstation is used by end-users to access
the data warehouse.
• Users interface with the data warehouse through applications or tools on their
workstations.
Tier 2: Application Server
• This tier is dedicated to processing business logic and application-specific tasks.
• It serves as an intermediary between the client workstation and the database server.
• The application server offloads processing tasks from the database server, improving
overall performance.
Tier 3: Database Server
• The database server is solely responsible for data storage, retrieval, and management.
• It stores the data warehouse's data, ensuring efficient data access and management.
• This separation of concerns allows for better scalability and performance as each server
handles distinct tasks.
Web-Based Data Warehousing:
• Three-tier Architecture: PC client, Web server, application server.
• Advantages: Access, platform independence, lower cost.
• Examples: Vanguard Group, Hilton migrating to Web-based systems
for enhanced scalability and cost savings.
• This diagram illustrates the architecture of a web-based data warehousing system,
which leverages web technologies to facilitate access to and interaction with the
data warehouse. The architecture is divided into several key components, each
playing a specific role in the system.
1. Client (Web Browser):
• The client represents the end-user's device, typically accessing the data warehouse
through a web browser.
• Users interact with the data warehouse via web pages, providing an intuitive and
accessible interface.
• Connectivity is achieved through the Internet, intranet, or extranet.
2. Web Server:
• The web server acts as the intermediary between the client and the application
server/data warehouse.
• It serves web pages to the client and manages the flow of information between
the client and the server-side components.
• Ensures that requests from the client are properly routed to the appropriate
resources.
3. Web Pages:
• Web pages provide the user interface that clients interact with.
• These pages are delivered by the web server and can include forms, reports,
dashboards, and other interactive elements.
• They facilitate user interactions such as querying the data warehouse and viewing
results.
4. Application Server:
• The application server handles the business logic and application-specific operations.
• It processes user requests received from the web server, performs necessary
computations or data manipulations, and interacts with the data warehouse.
• This server ensures that business rules are applied and that data processing tasks are
executed efficiently.
5. Data Warehouse:
• The data warehouse stores integrated and cleansed data from various sources.
• It serves as the central repository for all data accessed and analyzed by the users.
• The data warehouse is queried by the application server to retrieve and store data as
required.
Design Considerations:
• DBMS Choice: Most use relational databases (Oracle, SQL Server, DB2).
• Parallel Processing/Partitioning: Ensures scalability by handling queries
simultaneously.
• Data Migration Tools: Essential for transferring data into the warehouse.
• Retrieval and Analysis Tools: Specialized tools for locating, accessing, and
analyzing data periodically.
Alternative Architectures
• Enterprise-Wide Data Warehouse Design:
• Divided into four architectural structures: basic, Evolved EDW, and new
architectures (hub-and-spoke, federated).
• Hub-and-Spoke: Facilitates data integration and sharing across the enterprise.
• Federated: Combines multiple data warehouses and marts, maintaining
autonomy while supporting shared access.
Metadata
Definition:
• Metadata are data about data, providing structure and meaning.
• Types: Syntactic, structural, semantic.
Importance:
• Supports data integration, consistency, and accessibility.
• Enhances decision making by providing context and meaning to data.
Management Levels:
• Ad hoc, Discovered, Managed, Optimized, Automated: Levels of metadata management
maturity.
• Ethical Considerations: Ensures the ethical collection and ownership of data, addressing
privacy and intellectual property issues.
• Review Questions
[Link] is a data warehouse?
[Link] does a data warehouse differ from a database?
[Link] is an ODS?
[Link] among a data mart, an ODS, and an EDW.
[Link] the importance of metadata.
 Data Integration and the Extraction, Transformation, and
Load (ETL) Processes
Overview:
• Global pressures for ROI, management, and regulatory
demands push businesses to integrate and manage their
data more effectively.
• Business analysis requirements, like mergers, regulatory
changes, and new channels, increase data complexity.
• Integration of data across various sources, both structured
and unstructured, is necessary.
Data Integration:
• Involves three major processes: accessing, integrating, and delivering data
for analysis.
• Data warehousing environments facilitate data access for analysis across
multiple data sources, including CRM, ERP, and supply chain projects.
Integration Technologies:
• Enterprise Application Integration (EAI): Pushes data from source systems
into the warehouse.
• Service-Oriented Architecture (SOA): Uses services to integrate
applications.
• Enterprise Information Integration (EII): Creates integrated views from
various data sources.
• ETL: Extracts, transforms, and loads data into the warehouse.
Extraction, Transformation, and Load (ETL)
• Overview:
• Core of data warehousing; crucial for data-centric projects.
• Consists of extracting data from sources, transforming it, and
loading it into the warehouse.
ETL Process:
[Link]: Reading data from various sources.
[Link]: Converting data to the desired format using
business rules.
[Link]: Inserting data into the warehouse.
• The ETL process consists of three primary stages: Extraction,
Transformation, and Loading. This block diagram illustrates how data flows
through these stages, starting from various data sources and ending in the
data warehouse and data marts.
1. Data Sources:
• Packaged Application: Pre-built software applications used by the
organization.
• Legacy System: Older, often outdated, systems still in use.
• Other Internal Applications: Various internal systems generating data.
2. Transient Data Source:
• An intermediary storage area where data from different sources is
collected temporarily.
• Acts as a staging area before the data undergoes transformation and
cleansing.
3. Extraction:
• The process of reading data from various data sources.
• Data is pulled from packaged applications, legacy systems, and other
internal applications.
• The extracted data is moved to the transient data source for further
processing.
4. Transformation:
• Involves converting the extracted data into a suitable format for the data
warehouse.
• Transform: Data is changed to fit the required format, which might include:
• Data type conversion.
• Standardization.
• Application of business rules.
• Cleanse: Ensuring data quality by removing inconsistencies, duplicates, and
errors.
5. Load:
• The final step of the ETL process where transformed and cleansed data is
loaded into the target data warehouse.
• Data can also be loaded into data marts, which are subsets of the data
warehouse tailored for specific business needs or departments.
6. Data Warehouse:
• A centralized repository where integrated data is stored for analysis and
reporting.
• Supports decision-making processes by providing a unified view of the
organization's data.
7. Data Mart:
• Smaller, focused data repositories derived from the data warehouse.
• Designed to meet the specific needs of different business units or
functions, such as marketing, sales, or finance.
Challenges:
• Data transformation tools can be expensive and complex to learn.
• High maintenance due to evolving business rules and data sources.
Benefits:
• Effective ETL processes improve data quality and integration.
• Tools like SAS, Oracle, IBM, etc., offer comprehensive ETL capabilities.
• Review Questions:
[Link] data integration.
[Link] the three steps of the ETL process.
[Link] is the ETL process so important for data warehousing efforts?