Data Warehouse/Data Mart Components Concepts Characteristics

Data Warehouse/Data Mart Components Concepts Characteristics

Overview Operational vs Informational Systems Data Warehouse components Data Marts

Basic Data Warehouse Architecture Source OLTP Systems One Version of the Truth Subset Data Marts Enterprise Data Warehouse Copyright 1997, Enterprise Group, Ltd.

Operational vs. Informational Systems Order Entry Manf. Operational Systems Information Access Today

Operational vs. Informational Systems Operational Systems Informational Systems Information Access Today

Operational vs. Informational Systems Most of the advances in end-user programming have run into difficulty in actually accessing data that exists in backbone, operational data bases. Operational data bases have a very, very long life. Large operational systems are converted from one technology to a more advanced one very infrequently (typically every eight to twenty years). Therefore, why not create specific DBs whose role was to make large scale end user access easy to isolate the operational DBs, i.e. a Data Warehouse

Operational vs. Informational Systems Operational Systems Information Delivery System Informational Systems

Operational vs. Informational Systems Operational Systems Data Information Warehouse Delivery System Informational Systems

Operational vs. Informational Systems Operational Systems Data Information Warehouse Delivery System Informational Systems

Operational vs. Informational Systems Operational Systems Data Information Warehouse Delivery System Informational Systems

Operational vs. Informational Systems Notice that one of the big impacts of Operational Data Warehousing is toSystems eliminate large numbers of existing DSS systems! Data Information Y2000 will make this essential!!! Warehouse Delivery System Informational Systems

Operational vs. Informational Systems Operational Systems Data Marts Data Information Warehouse Delivery System Informational Systems

Data Marts vs Data Warehouses Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual DW Coarse DW Operational Data Layer 2a Central DW Distributed DW Core DW Layer 3 North America External Data Layer 2b United States 11,000 Sales United States b y Sal es 1 0,3 40to 10 ,350 (1 ) 8,730to 10 ,340 (2 ) 4,320to 8 ,730 (2 ) 1,100to 4 ,320 (1 ) 730to 1 ,100 (3 ) Presentation/ Desktop Access Layer 1 Data Mart Layer 4 Data Feed/ Data Mining/ Indexing Layer 6 Data Staging and Quality Layer Meta-data Repository Layer 8 Warehouse Management Layer 9 Application Messaging (Transport) Layer 10 5 Data Access Layer 7 Non-operational Data Layer 2c

Central Data Warehouse Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Tracking DB Lawson DB Operational Data Layer 2a Central DW External Data Layer Core DW Layer 3 North America 2b United States 11,000 Sales United States b y Sal es 1 0,3 40to 10 ,350 (1 ) 8,730to 10 ,340 (2 ) 4,320to 8 ,730 (2 ) 1,100to 4 ,320 (1 ) 730to 1 ,100 (3 ) Presentation/ Desktop Access Layer 1 Data Mart Layer 4 Data Feed/ Data Mining/ Indexing Layer 6 Data Staging and Quality Layer Meta-data Repository Layer 8 Warehouse Management Layer 9 Application Messaging (Transport) Layer 10 5 Data Access Layer 7 Non-operational Data Layer 2c

Virtual Date Warehouse A Virtual Data Warehouse approach is often chosen when there are infrequent demands for data and management wants to determine if/how users will use operational data. One of the weaknesses of a Virtual Data Warehouse approach is that user queries a made against operational DBs. One way to minimize this problem is to build a “Query Monitor” to check the performance characteristics of a query before executing it.

A Coarse Data Warehouse is often chosen when the organization has a relatively clean/new operational system and management wants to make the operational data more easily available for just that system. A Central Data Warehouse is often chosen when the organization has a clear understanding about it Information Access needs and wants to provide “quality”, “integrated” , information to its knowledge workers A Distributed Data Warehouse is similar in most respects to a Central Data Warehouse, except that the data is distributed to separate mini-Data Warehouses (Data Marts )on local or specialized servers

Central Data Warehouse Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual DW Coarse DW Operational Data Layer 2a Central DW Distributed DW Core DW Layer 3 North America External Data Layer 2b United States 11,000 Sales United States b y Sal es 1 0,3 40to 10 ,350 (1 ) 8,730to 10 ,340 (2 ) 4,320to 8 ,730 (2 ) 1,100to 4 ,320 (1 ) 730to 1 ,100 (3 ) Presentation/ Desktop Access Layer 1 Data Mart Layer 4 Data Feed/ Data Mining/ Indexing Layer 6 Data Staging and Quality Layer Meta-data Repository Layer 8 Warehouse Management Layer 9 Application Messaging (Transport) Layer 10 5 Data Access Layer 7 Non-operational Data Layer 2c

Data Marts Only Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual DW Coarse DW Operational Data Layer 2a Central DW Distributed DW Core DW Layer 3 North America External Data Layer 2b United States 11,000 Sales United States b y Sal es 1 0,3 40to 10 ,350 (1 ) 8,730to 10 ,340 (2 ) 4,320to 8 ,730 (2 ) 1,100to 4 ,320 (1 ) 730to 1 ,100 (3 ) Presentation/ Desktop Access Layer 1 Data Mart Layer 4 Data Feed/ Data Mining/ Indexing Layer 6 Data Staging and Quality Layer Meta-data Repository Layer 8 Warehouse Management Layer 9 Application Messaging (Transport) Layer 10 5 Data Access Layer 7 Non-operational Data Layer 2c

Heterogeneity - The Reality i2 Supply Chain Packaged I2 Supply Chain Non- Architected Data Mart Oracle Financials Packaged Oracle Financial Data Warehouse Subset Data Marts Siebel CRM 3rd Party Data Custom Marketing Data Warehouse

Federated BI Architecture i2 Supply Chain Oracle Financials Siebel CRM Common Staging Area Federated Financial Data Warehouse Federated Packaged I2 Supply Chain Data Marts 3rd Party e-commerce Real Time ODS Federated Marketing Data Warehouse Subset Data Marts Analytical Applications Real Time Data Mining and Analytics Real Time Segmentation, Classification, Qualification, Offerings, etc.

Benefits of Data Warehouse Architecture Provides organizing framework Gives flexibility for changes and allows simplified maintenance Speeds up future development by aiding understanding of dw Communication tool for roles and requirements Coordinate data marts

Primary Technical Challenge Axis Dirty Data Large Co. Parallel Near ERP DW Real Custom VLDB Time ERP DW Slow Monthly Freq Turnkey ERP DW Mid-Size Co. Small DB Fast Easy Finance Multi-Source Marketing Single Source Clean Data Hard

Prerequisites for Success Pain driven Sponsorship at the highest levels Sustainable political will Iterative methodology Manageable scope User driven design Service business mindset Sustainability