Learn Data Warehousing in the Real World with Sam Anahory's PDF File: Tips and Tricks for Building Effective Decision Support Systems
Data Warehousing in the Real World: A Practical Guide for Building Decision Support Systems
Data warehouses are the primary means by which businesses can gain competitive advantage through analyzing and using the information stored in their computerized systems. However, the data warehousing market is inundated with confusing, often contradictory, technical information from suppliers of hardware, databases and tools. How can you plan, build, and manage a data warehouse that meets your business needs and delivers value to your organization?
Data Warehousing In The Real World Sam Anahory Pdf File
In this article, we will provide you with a practical guide for building decision support systems using open-systems data warehouses. We will cover the following topics:
What is data warehousing and why is it important?
How to plan and build a data warehouse that aligns with your business goals and requirements?
How to design and implement a data warehouse that supports your analytical needs and queries?
How to manage and maintain a data warehouse that ensures user access, security, and performance?
We will also answer some frequently asked questions about data warehousing at the end of the article. Let's get started!
What is Data Warehousing?
Data warehousing is the process of collecting, integrating, transforming, storing, and analyzing data from various sources for the purpose of providing decision support to business users. A data warehouse is a centralized repository that stores historical and current data from different operational systems such as ERP, CRM, POS, etc. A data warehouse enables business users to access and analyze data using various tools such as reports, dashboards, OLAP cubes, etc.
Data warehousing has many benefits for businesses such as:
Improving decision making by providing accurate, consistent, timely, and relevant information.
Enhancing business performance by identifying trends, patterns, opportunities, and threats.
Reducing costs by eliminating data redundancy, inconsistency, and errors.
Increasing customer satisfaction by understanding customer behavior, preferences, and needs.
Supporting strategic planning by enabling what-if analysis, forecasting, and scenario modeling.
How to Plan and Build a Data Warehouse?
Building a data warehouse is not a simple task. It requires careful planning and analysis of the business needs and technical requirements of the data warehouse. The following are the main steps involved in planning and building a data warehouse:
Project Planning
The first step is to define the scope, objectives, deliverables, and stakeholders of the data warehouse project. This involves answering questions such as:
What are the business problems or opportunities that the data warehouse will address?
What are the expected benefits and outcomes of the data warehouse?
What are the key performance indicators (KPIs) and metrics that will measure the success of the data warehouse?
What are the data sources, data types, data volumes, and data quality issues that the data warehouse will handle?
Who are the users, roles, and responsibilities of the data warehouse?
What are the budget, timeline, and resources available for the data warehouse project?
The output of this step is a project charter or a project plan that outlines the scope, objectives, deliverables, and stakeholders of the data warehouse project.
Requirements Analysis
The next step is to gather and analyze the business, functional, and technical requirements of the data warehouse. This involves conducting interviews, surveys, workshops, and document reviews with the business users, IT staff, and other stakeholders of the data warehouse. The purpose of this step is to understand:
What are the business processes, rules, and policies that the data warehouse will support?
What are the analytical needs, questions, and expectations of the data warehouse users?
What are the functional features, capabilities, and specifications of the data warehouse?
What are the technical standards, platforms, tools, and architectures of the data warehouse?
The output of this step is a requirements document or a business requirements specification (BRS) that details the business, functional, and technical requirements of the data warehouse.
How to Design and Implement a Data Warehouse?
After planning and analyzing the requirements of the data warehouse, the next step is to design and implement the data warehouse. This involves designing the architecture and the database of the data warehouse, as well as developing and testing the data integration and data access components of the data warehouse. The following are the main steps involved in designing and implementing a data warehouse:
Architecture
The first step is to design the architecture of the data warehouse. The architecture defines how the data warehouse components interact with each other and with other systems. The architecture consists of three layers: data sources, data integration, and data storage; data access; and data presentation.
Components
The following are the main components of a data warehouse architecture:
Data sources: These are the operational systems that provide raw data to the data warehouse. They can be internal or external, structured or unstructured, relational or non-relational.
Data integration: This is the process of extracting, transforming, loading (ETL), or streaming (ELT) data from various sources into a common format and structure in the data warehouse. It involves applying business rules, validations, transformations, cleansing, aggregation, etc. to ensure data quality and consistency.
Data storage: This is where the integrated and transformed data is stored in a relational database management system (RDBMS) or a columnar database management system (CDBMS). It consists of tables that store facts (measures) and dimensions (attributes) that represent the business entities and events.
Data access: This is where the business users can query and analyze the data stored in the data warehouse using various tools such as SQL queries, reports, dashboards, OLAP cubes, etc. It involves applying security policies, access controls, query optimization techniques, etc. to ensure user satisfaction and performance.
Data presentation: This is where the results of the queries and analyses are displayed to the business users using various formats such as charts, graphs, tables, etc. It involves applying visualization techniques, interactivity features, storytelling elements, etc. to ensure user engagement and understanding.
Models
The following are some common models for designing a data warehouse:
Star schema: This is a simple model that consists of one fact table that stores measures related to a specific business process or event (such as sales), and multiple dimension tables that store attributes related to each measure (such as product, customer, location). The fact table has foreign keys that reference primary keys in each dimension table. The star schema enables fast query performance by reducing joins.
Snowflake schema: This is an extension of star schema: This is a more complex model that consists of one or more fact tables that store measures related to different business processes or events (such as sales, inventory, etc.), and multiple dimension tables that store attributes related to each measure. However, some of the dimension tables are further normalized into multiple related tables, creating a hierarchical structure that resembles a snowflake. The snowflake schema enables better data integrity and flexibility by reducing data redundancy and allowing multiple levels of granularity.
Fact constellation: This is the most complex model that consists of multiple fact tables that share some common dimension tables. Each fact table can have its own dimensions or can share dimensions with other fact tables. The fact constellation enables more complex analysis and reporting by allowing multiple perspectives and dimensions of the data.
Types
The following are some common types of data warehouses based on the scope and purpose of the data:
Enterprise data warehouse (EDW): This is a large-scale data warehouse that stores data from all the operational systems and business functions of an organization. It provides a single source of truth and a comprehensive view of the entire organization.
Data mart: This is a small-scale data warehouse that stores data from a specific business function or department of an organization. It provides a focused view and analysis of a particular subject area or domain.
Operational data store (ODS): This is a temporary data warehouse that stores data from operational systems for near-real-time analysis and reporting. It provides a snapshot of the current state and activities of the organization.
Real-time data warehouse: This is a data warehouse that captures and stores data from operational systems as soon as they occur. It provides immediate access and analysis of the latest data.
Design
The next step is to design the database of the data warehouse. The database design involves creating the logical and physical models of the data warehouse tables and indexes.
Logical Design
The logical design involves creating the conceptual model, dimensional model, and star schema design of the data warehouse. The conceptual model defines the entities, attributes, and relationships of the data warehouse at a high level of abstraction. The dimensional model defines the facts, dimensions, measures, hierarchies, and levels of the data warehouse at a lower level of abstraction. The star schema design defines the structure, keys, constraints, and indexes of the data warehouse tables at the lowest level of abstraction.
The following are some steps for creating the logical design of the data warehouse:
Identify the facts and dimensions of the data warehouse based on the business requirements and analytical needs.
Identify the measures and attributes of each fact and dimension based on the KPIs and metrics.
Identify the hierarchies and levels of each dimension based on the granularity and drill-down needs.
Create a conceptual model diagram that shows the entities, attributes, and relationships of the data warehouse using an entity-relationship (ER) notation.
Create a dimensional model diagram that shows the facts, dimensions, measures, hierarchies, and levels of the data warehouse using a star schema notation.
Create a star schema design document that shows the structure, keys, constraints, and indexes of each table in the data warehouse using a SQL notation.
Physical Design
The physical design involves creating the database, table, index, and partition design of the data warehouse. The physical design involves creating the actual database objects such as tables, indexes, partitions, etc. in the data warehouse using SQL commands.
The following are some steps for creating the physical design of the data warehouse:
Create the database and schema for the data warehouse using the CREATE DATABASE and CREATE SCHEMA commands.
Create the tables for the facts and dimensions using the CREATE TABLE command. Specify the primary keys, foreign keys, constraints, and indexes for each table.
Create the partitions for the fact tables using the PARTITION BY clause. Specify the partitioning method (range or interval), partitioning key (event date/time column), and partitioning range (time interval) for each partition.
Create the indexes for the fact and dimension tables using the CREATE INDEX command. Specify the index type (bitmap or b-tree), index columns, and index options for each index.
Load the data into the tables using the INSERT, COPY, or ETL/ELT commands. Perform data validation and quality checks on the loaded data.
How to Manage and Maintain a Data Warehouse?
After designing and implementing the data warehouse, the next step is to manage and maintain the data warehouse. This involves ensuring user access, security, and performance of the data warehouse, as well as performing regular administrative tasks such as backup and recovery, monitoring, and optimization of the data warehouse. The following are the main steps involved in managing and maintaining a data warehouse:
User Access
The first step is to ensure user access to the data warehouse. This involves authenticating, authorizing, and auditing the users who access and query the data warehouse. The following are some steps for ensuring user access to the data warehouse:
Create user accounts and roles for the data warehouse users using the CREATE USER and CREATE ROLE commands. Assign passwords, privileges, and quotas to each user account and role.
Grant or revoke access to specific tables, views, or schemas in the data warehouse using the GRANT or REVOKE commands. Specify the type of access (select, insert, update, delete) and the scope of access (all or specific columns) for each grant or revoke.
Audit user activities on the data warehouse using the AUDIT command. Specify what actions to audit (select, insert, update, delete) and where to store the audit records (database table or operating system file).
Security
The next step is to ensure security of the data warehouse. This involves encrypting, firewalling, and VPNing the data warehouse data and connections. The following are some steps for ensuring security of the data warehouse:
Encrypt the data at rest and in transit using the encryption features of the data warehouse platform or the database management system. For example, you can use Transparent Data Encryption (TDE) or Always Encrypted to encrypt the data at rest and SSL/TLS or IPSec to encrypt the data in transit.
Firewall the data warehouse network and instances using the firewall features of the data warehouse platform or the operating system. For example, you can use VPC firewall rules or Windows Firewall to block or allow traffic based on IP addresses, ports, protocols, etc.
VPN the data warehouse connections using the VPN features of the data warehouse platform or a third-party VPN service. For example, you can use Cloud VPN or ExpressRoute to create a secure and private connection between your data warehouse and your on-premises or cloud network.
Performance
The next step is to ensure performance of the data warehouse. This involves tuning, monitoring, and optimizing the data warehouse queries and components. The following are some steps for ensuring performance of the data warehouse:
Tune the data warehouse queries by using the query optimizer features of the data warehouse platform or the database management system. For example, you can use query hints, statistics, execution plans, etc. to improve the query performance.
Monitor the data warehouse performance by using the performance monitoring features of the data warehouse platform or a third-party monitoring tool. For example, you can use performance counters, alerts, logs, etc. to measure and track the performance metrics of the data warehouse.
Optimize the data warehouse components by using the optimization features of the data warehouse platform or a third-party optimization tool. For example, you can use compression, partitioning, indexing, etc. to optimize the data storage and access of the data warehouse.
Conclusion
In this article, we have provided you with a practical guide for building decision support systems using open-systems data warehouses. We have covered the following topics:
What is data warehousing and why is it important?
How to plan and build a data warehouse that aligns with your business goals and requirements?
How to design and implement a data warehouse that supports your analytical needs and queries?
How to manage and maintain a data warehouse that ensures user access, security, and performance?
We hope that this article has helped you to understand the basics of data warehousing and how to apply them in your own projects. Data warehousing is a powerful technique for transforming raw data into valuable insights that can help you make better decisions and achieve your business objectives.
FAQs
Here are some frequently asked questions about data warehousing:
What are the differences between a data warehouse and a database?
A database is a collection of structured data that supports transactional processing and operational reporting. A data warehouse is a collection of integrated and transformed data that supports analytical processing and decision support.
What are the differences between a data warehouse and a data lake?
A data lake is a collection of raw and unstructured data that can store any type of data from any source. A data warehouse is a collection of integrated and transformed data that can store structured or semi-structured data from selected sources.
What are the differences between a data warehouse and a data mart?
A data mart is a subset of a data warehouse that stores data for a specific business function or department. A data warehouse is a superset of a data mart that stores data for the entire organization.
What are the differences between ETL and ELT?
ETL stands for extract-transform-load, which is a process of extracting data from various sources, transforming it into a common format and structure, and loading it into a data warehouse. ELT stands for extract-load-transform, which is a process of extracting data from various sources, loading it into a staging area or a data lake, and transforming it on demand when needed.
What are some best practices for designing a star schema?
Some best practices for designing a star schema are:
Use surrogate keys instead of natural keys for dimension tables.
Avoid many-to-many relationships between fact and dimension tables.
Avoid snowflaking unless necessary for performance or usability reasons.
Avoid redundant attributes across dimension tables.
Avoid null values in fact tables.
71b2f0854b