Data WarehouseData Mart Components Concepts Characteristics Overview Operational

  • Slides: 24
Download presentation
Data Warehouse/Data Mart Components Concepts Characteristics

Data Warehouse/Data Mart Components Concepts Characteristics

Overview • Operational vs Informational Systems • Data Warehouse components • Data Marts

Overview • Operational vs Informational Systems • Data Warehouse components • Data Marts

Basic Data Warehouse Architecture Source OLTP Systems One Version of the Truth Subset Data

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 Order Entry Manf. Operational Systems Information Access Today

Operational vs. Informational Systems Operational Systems Information Access Today

Operational vs. Informational Systems Operational Systems Information Access Today

Operational vs. Informational Systems • Most of the advances in end-user programming have run

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 Information Delivery System Informational Systems

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

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

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

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

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

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

Operational vs. Informational Systems Notice that one of the big impacts of Operational Data

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

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

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

Data Marts vs Data Warehouses Internet/Intranet Layer 11 direct queries virtual queries ad hoc

Data Marts vs Data Warehouses Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual DW Coarse DW Operational Data Layer 2 a Central DW Distributed DW Core DW Layer 3 External Data Layer 2 b 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 2 c

Central Data Warehouse Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Tracking

Central Data Warehouse Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Tracking DB Lawson DB Operational Data Layer 2 a Central DW External Data Layer Core DW Layer 3 2 b 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 2 c

Virtual Date Warehouse • A Virtual Data Warehouse approach is often chosen when there

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

• 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

Central Data Warehouse Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual DW Coarse DW Operational Data Layer 2 a Central DW Distributed DW Core DW Layer 3 External Data Layer 2 b 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 2 c

Data Marts Only Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual

Data Marts Only Internet/Intranet Layer 11 direct queries virtual queries ad hoc queries Virtual DW Coarse DW Operational Data Layer 2 a Central DW Distributed DW Core DW Layer 3 External Data Layer 2 b 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 2 c

Heterogeneity - The Reality i 2 Supply Chain Packaged I 2 Supply Chain Non-

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

Federated BI Architecture i 2 Supply Chain Oracle Financials Siebel CRM Common Staging Area

Federated BI Architecture i 2 Supply Chain Oracle Financials Siebel CRM Common Staging Area Federated Financial Data Warehouse Federated Packaged I 2 Supply Chain Data Marts 3 rd 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

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

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

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