Data Warehouse Why Data Warehouse and OLAP The

Data Warehouse

Why Data Warehouse and OLAP? The Intelligence Stage of Decision Making needs correct data Data also should be clean, organized with fast access Often the data should only be used for subsequent stages and should not be changed (in other words decision makers are users of data, not generators of data) The data for decision making comes from multiple sources OLTP databases, XML files, Flat files like CSV, PDF, etc. They need to be combined for one version of the truth These needs cannot be easily served by the traditional data sources, hence the concept of data warehouse/data marts

Business Intelligence Process Increasing potential to support business decisions Making Decisions Data Presentation Visualization Techniques Data Mining Information Discovery End User Business Analyst Data Exploration Statistical Analysis, Querying and Reporting Data Warehouses / Data Marts OLAP, MDA Data Sources Paper, Files, Information Providers, Database Systems, OLTP DBA

Data Warehouse: Definitions Data warehouse There is no single definition, as it can encompass many aspects of the data management. Some of the ones found in the literature are: A physical repository where relational data are specially organized to provide enterprise-wide, cleansed data in a standardized format Defined in many different ways, but not rigorously. A decision support database that is maintained separately from the organization’s operational database. A consistent database source that bring together information from multiple sources for decision support queries Support information processing by providing a solid platform of consolidated, historical data for analysis Data warehousing A process by which data from multiple sources are extracted, transformed and loaded into a data warehouse in a planned operation

Data Warehouse vs. Operational DBMS • OLTP (on-line transaction processing) Major task of traditional relational DBMS Day-to-day operations: purchasing, inventory, banking, manufacturing, payroll, registration, accounting, etc. Aims at reliable and efficient processing of a large number of transaction and ensuring data consistency • OLAP (on-line analytical processing) Major task of data warehouse system Data analysis and decision making Aims at efficient multidimensional processing of large data volumes Fast, interactive answers to large aggregate queries • Distinct features (OLTP vs. OLAP): User and system orientation: customer vs. market Data contents: current, detailed vs. historical, consolidated Database design: ER + application vs. star + subject View: current, local vs. evolutionary, integrated Access patterns: update vs. read-only but complex queries

OLTPvs. OLAP User Clerk, IT Professional Knowledge worker Function Day to day operations Decision support DB Design Application-oriented Subject-oriented Data Current, Isolated Historical, Consolidated View Detailed, Flat relational Summarized and Multidimensional Usage Structured, Repetitive Ad hoc Unit of work Short, Simple transaction Complex query Access Read/write Read Mostly Operations Index/hash on prim. Key Lots of Scans # Rec. accessed Tens Millions #Users Thousands Hundreds Db size 100 MB-GB 100 GB-TB Metric Trans. throughput Query throughput, response

Data Warehousing Characteristics Subject oriented Integrated Time variant (time series) Nonvolatile Web based Relational/multidimensional Client/server Real-time Include metadata

Data Warehouse Architecture Inmon (Bill Inmon) Model: EDW approach Kimball (Ralph Kimball) Model: Data mart approach Which model is best? There is no one-size-fits-all strategy to data warehousing One alternative is the hosted warehouse

Alternative DW Architectures

Teradata’s. DW Architecture

DW Architecture

Categories/Types of Data Warehouses Data Mart Operational Data Stores A departmental data A type of database often warehouse that stores only used as an interim area for relevant data a data warehouse, especially for customer Dependent Data Mart information files A subset that is created Operational Data Marts directly from a data An operational data mart. warehouse Independent Data Mart An operational data mart is a small-scale data mart A small data warehouse typically used by a single designed for a strategic department or functional business unit or a area in an organization department

Various. OLAPs ROLAP: Relational OLAP. Analysis cube supported by a Multi-Dimensional Data Warehouse (or Data Mart) based on Relational database. MOLAP: Multidimensional OLAP. Analysis cube supported by a multi-dimensional data warehouse based on another multi-dimensional data storage. HOLAP: Hybrid OLAP. Combination of the above two to optimize performance. Your task: Search the web to find the relative advantages and disadvantages of each.

Data Warehousing Architectures Issues to consider when deciding which architecture to use: Which database management system (DBMS) should be used? Will parallel processing and/or partitioning be used? Will data migration tools be used to load the data warehouse? What tools will be used to support data retrieval and analysis?

Data Warehouse Development and Implementation

Data Warehouse and BI Life Cycle Structured data Business Intelligence Reporting, Ad-hoc Query Analysis OLTP Data Warehouse (Enterprise) Unstructured data

Data Warehouse and BI Life Cycle Phase I Extract -Transform - Load Data Integration Data Warehouse (Enterprise) Source Data ODS Reporting Tools Develop Discover Deliver

Data Warehouse and BI Life Cycle Phase II Data Marts (Departments) Data Warehouse (Enterprise) Multi-Dimensional Cubes (Subject wise) Develop Discover Business Intelligence Deliver

Data Warehouse and BI Life Cycle

Data Warehouse OLAP Cube Total Sales for Customer 3, Product C, for July. From: http: //oraclezine. blogspot. com/2009/01/data-warehousingand-olap-cube. html, accessed January 20, 2011

Data Integration and the Extraction, Transformation and Load (ETL) Process Extraction reading data from a database Transformation Converting the extracted data from its previous form into the form in which it needs to be so that it can be placed into a data warehouse or simply another database Load Putting the data into the data warehouse

Data Warehouse Schema Dimensional Modeling The Star Schema Dimension Tables that contain the Dimension for Analysis Example: Time, Region, Salesperson, etc. Fact Tables that contains the measures and aggregates Example: Average sales, total commission, total sales, etc. The Snowflake Schema Very similar to Star-schema with a central fact table, but the dimensions are in hierarchical fashion. Example: Listing agent is a part of the listing company, one city can have multiple zip code etc. Reduces the redundant data but can be inefficient for queries that do not follow patterns.

Steps for DW OLP Design Decide on your Information Needs I want to know the Total Sales by month, by region and by salesperson Decide the sources of data for each information need Total Sales is not available, must be calculated from unit and quantity sold Most of the unit and qty sold are in OLTP, but some are also in CSV files for some of the stores not connected to the database The Monthly information can be obtained from the date and is in OLTP The region information is also in OLTP transaction records vi a region code The Salesperson information is maintained in an Excel file and is coded by region Declare the grain of the fact table (preferably at the most atomic level) A grain is the lowest level of information you are interested in. The finer is the grain (tending towards atomic), the more are the dimensions. Atomic grain is preferred as it can allow easy roll-ups. However, it can take a lot of space and processing. Often the grains in the fact tables are much coarser with ability to drill down Example of Grains Sales of Each Customer, for each day for each store. Monthly total sales for a particular region for all female customers Add dimensions for "everything you know" about this grain Add numeric measured facts true to the grain.

Creating a Star* Schema Identify the dimensions (typically this is the analysis by). In our real estate listing example, it can be the city name, bedrooms, listing agent, etc. Identify the measures. In our case, it can be the average price/sq ft, total number of houses in a city, the average price, etc. Identify the attributes of each dimension. Attributes are the properties of a dimension. For example, if the listing agent is a dimension, then the first name, last name, phone number etc, of a listing agent will be the attributes. * Snowflake schema design is very similar with hierarchy of the dimensions separated in another table.

Star Schema continued. Create the dimension tables with a surrogate Primary Key (PK). Include all the necessary attributes. Decide on the measures and calculations. Those will be in the fact table. For each PK in the dimension tables, create foreign keys (FK) in the star table.

Example of Star Schema

Example of Snowflake Schema

Implementing the Star Schema 1. Extract Data From Multiple Sources 2. Integrate, Transform, and Restructure Data 3. Load Data Into Dimension Tables and Fact Tables

The Star Schema Data Load Heterogeneous Data Sources Data Warehouse Staging Area Northwind OLTP Financial External Internal Files Sales Star DTS External Files Extracting Data From DTS Transforming DTS Heterogeneous Sources Data Inventory Star Loading the Star Schema

Verifying the Dimension Source Data Verifying Accuracy of Source Data Integrating data from multiple sources Applying business rules Checking structural requirements Correcting Invalid Data Managing Invalid Data l Transforming data l Rejecting invalid data l Reassigning data values l Saving invalid data to a log

Dimension Data Load Examples: buyer_name reg_id Barr, Adam 2 Chai, Sean 4 O’Melia, Erin 6. . . buyer_code buyer_last reg_id Barr 2 A 123 Chai 4 B 456 O’Melia 6. . buyer_name Barr, Adam Chai, Sean reg_id II IV buyer_name Smith, Jane Paper, Anne reg_id 2 4 DTS DTS buyer_first Adam Sean Erin. . . buyer_code U 999 A 123 B 456. . . buyer_last reg_id Barr 2 Chai 4 O’Melia 6. . . buyer_name Barr, Adam Chai, Sean Smith, Jane Paper, Anne reg_id 2 4

Maintaining Integrity of the Dimension Assigning a Surrogate Key to Each Record Defines the dimension’s primary key Relates to the foreign key fields of the fact table Loading One Record Per Application Key Maintains uniqueness in the dimension Depends on how you manage changing dimension data Maintains integrity of the fact table

Managing Changing Dimension Data One of the problem in DW is to deal with the changing data on the reload and refresh. OLTP is operational and thus does not face this problem. These change areas are often called SCD or Slow Changing Dimensions with Changing Column Values Inserts of new data Updates of existing data Slowly-Changing Dimension Design Solutions Type 1: Overwrite the dimension record Type 2: Write another dimension record Type 3: Add attributes to the dimension record

Examining the Star Schema Sales Star Schema Fact Table Dimension Tables Dimension Table

Type 1: Overwriting the Dimension Slide Product Dimension product key product name product size product package product dept product cat product subcat. . . Before 001 Rice Puffs 10 oz. Bag Grocery Dry Goods Snacks. . . After 001 Rice Puffs 12 Oz oz. Bag Grocery Dry Goods Snacks. . . Existing record is changed

Type 2: Writing Another Dimension Record Product Dimension product key product name product size product package product dept product cat product subcat effective_date … Before 001 Rice Puffs 10 oz. Bag Grocery Dry Goods Snacks 05 -01 -1995. . . 001 Rice Puffs 10 Oz oz. Bag Grocery Dry Goods Snacks 05 -01 -1995. . . After 731 Rice Puffs 12 Oz oz. Bag Grocery Dry Goods Snacks 10 -15 -1998. . . Adds a new record

Type 3: Adding Attributes in the Dimension Record Product Dimension product key product name productsize product package product dept product cat product subcat current product size date previous product size date previous product 2 nd previous product size date. . . Before 001 Rice Puffs 10 10 Oz oz. Bag Grocery Dry Goods Snacks 05 -01 -1995 11 11 Oz oz. 03 -20 -1994 (null). . . After 001 Rice Puffs 12 12 oz. oz Bag Grocery Dry Goods Snacks 10 -15 -1998 10 oz. 05 -01 -1995 11 11 Oz oz. 03 -20 -1994. . . Additional information is stored in an existing record

Verifying the Fact Table Source Data Verifying Accuracy of Source Data n Integrating data from multiple sources n Applying business rules n Checking structural requirements n Creating calculated fields Correcting Invalid Data Managing Invalid Data l Transforming data l Rejecting invalid data l Reassigning data values l Saving invalid data to a log

Assigning Foreign Keys Dimension Tables customer_dim 201 ALFI Alfreds Source Data customer id ALFI product id order date quantity_sales amount_sales 123 1/1/2000 400 10, 789 product_dim 25 123 Chai Sales Fact Data cust_key time_dim 134 1/1/2000 201 prod_key time_key 123 25 1/1/2000 134 quantity_sales amount_sales 400 10, 789

Defining Measures Loading Measures from the Source System Calculating Additional Measures customer_id VINET ALFI HANAR. . . product_id 9 GZ 1 KJ 0 ZA. . . price. 55 1. 10. 98. . . qty 32 48 9. . . Source System Data customer_key 100 238 437. . . product_key 512 207 338. . . qty 32 48 9. . . Fact Table Data total_sales 17. 60 52. 80 8. 82. . .

Maintaining Data Integrity Adhering to the Fact Table Grain A fact table can only have one grain You must load a fact table with data at the same level of detail as defined by the grain Enforcing Column Constraints NOT NULL constraints FOREIGN KEY constraints

Implementing Staging Tables Centralize and Integrate Source Data Break Up Complex Data Transformations Facilitate Error Recovery market_stage shipments_stage Staging Area sales_stage inventory_stage

DTS Functionality Accessing Heterogeneous Data Sources Importing, Exporting, and Transforming Data Creating Reusable Transformations and Functions Automating Data Loads Managing Metadata Customizing and Extending Functionality

Defining DTS Packages Identifies Data Sources and Destinations Defines Tasks or Actions Implements Transformation Logic Defines Order of Operations

Identifying Package Components Connections: Access Data Sources and Destinations Tasks: Describe Data Transformations or Functions Steps: Define the Order of Task Operations or Workflow Global Variables: Store Data that Can Be Shared Across Tasks

Creating Packages Using the DTS Import / Export Wizard Perform ad-hoc table and data transfers Develop a prototype package Using DTS Package Designer Edit packages created with the DTS Import/Export Wizard Create packages with a wide range of functionality Programming DTS Applications Directly access the functionality of the DTS Object Model Requires Microsoft Visual Basic or Microsoft Visual C++

Using DTS to Populate the Sales Star Populating the Sales Star Dimensions Populating the Sales Star Fact Table

Populating the Sales Star Dimensions Product Tab Delimited Files product_dim DTS customer_dim Northwind OLTP SQL Server Stored Procedure DTS time_dim DTS

Populating the Sales Star Fact Table Sales Data File product_dim DTS sales_stage DTS customer_dim sales_stage time_dim sales_fact

Why not Just Excel Pivot Tables? For small project, Pivot Tables are excellent However, if you insert/delete records, you have to refresh the pivot table and may have to change the table reference (though Table features make it easy) Calculation cannot be done for one field that will be reflected for all records. You have to copy it for all the records! No pre-processing can be done. The pivot table is refreshed and recalculated every time. Can lead to inefficiencies No built-in integration mechanism with a data source (can be done by connecting to cube servers and ODBC compliant databases, but again an extra step and work) Selective drill-down is difficult, mainly all or none (again grouping can help, but you have to be skilled in Pivot Tables)

Data Warehouse Implementation issues Implementing a data warehouse is generally a massive effort that must be planned and executed according to established methods There are many facets to the project lifecycle, and no single person can be an expert in each area

Major Tasks for Successful Implementation of a DW 1. Establishment of 2. 3. 4. 5. service-level agreements and datarefresh requirements Identification of data sources and their governance policies Data quality planning Data model design ETL tool selection 6. Relational database software and platform selection 7. Data transport 8. Data conversion 9. Reconciliation process 10. Purge and archive planning 11. End-user support From Solomon, 2005

Data Warehouse Implementation factors that can be categorized into three criteria Organizational issues Project issues Technical issues User participation in the development of data and access modeling is a critical success factor in data warehouse development

Data Warehouse Management Data warehouse administrator (DWA) A person responsible for the administration and management of a data warehouse Security Concerns “All eggs in one basket” Unauthorized access Usage Concerns Users need to understand how to use the data correctly Why was data collected, how was it stored, how do these affect the current use of the data

Data Warehouse Management Effective security in a data warehouse should focus on four main areas: Establishing effective corporate and security policies and procedures Implementing logical security procedures and techniques to restrict access Limiting physical access to the data center environment Establishing an effective internal control review process with an emphasis on security and privacy

Data Warehouse Benefits Direct benefits of a data warehouse Allows end users to perform extensive analysis Allows a consolidated view of corporate data Better and more timely information Enhanced system performance Simplification of data access

Data Warehouse Benefits Indirect benefits result from end users using these direct benefits Enhance business knowledge Present competitive advantage Enhance customer service and satisfaction Facilitate decision making Help in reforming business processes

Data Warehouse Vendor Selection Important guidelines for selecting a DW vendor Financial strength ERP linkages Qualified consultants Market share Industry experience Established partnerships

DW Development Best Practices Project must fit with The project must be corporate strategy and business objectives There must be complete buy-in to the project by executives, managers, and users It is important to manage user expectations about the completed project The data warehouse must be built incrementally Build in adaptability managed by both IT and business professionals Develop a business/supplier relationship Only load data that have been cleansed and are of a quality understood by the organization Do not overlook training requirements Be politically aware From Weir, 2002

DW Development: What NOT to do Cultural issues being ignored Inappropriate architecture Unclear business objectives Missing information Unrealistic expectations Low levels of data summarization Low data quality Believing promises of performance, capacity, and scalability Believing that your problems are over when the data warehouse is up and running Believing that DW database design is the same as transactional database design Choosing a DW manager who is technology oriented rather than user oriented Focusing on traditional internal record-oriented data and ignoring the value of external data and of text, images, and, perhaps, sound and video Focusing on ad hoc data mining and periodic reporting instead of alerts Delivering data with overlapping and confusing definitions