1 / 15

Best Practices for Data Warehousing

Best Practices for Data Warehousing. Agenda – Best Practices for DW-BI. <Insert Picture Here>. Best Practices in Data Modeling Best Practices in ETL Best Practices in Reporting. Best Practices in Data Modeling. Dim Table. Dim Table. Dimension Tables.

quincy-landry
Download Presentation

Best Practices for Data Warehousing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Best Practices for Data Warehousing

  2. Agenda – Best Practices for DW-BI <Insert Picture Here> • Best Practices in Data Modeling • Best Practices in ETL • Best Practices in Reporting 2

  3. Best Practices in Data Modeling 3

  4. Dim Table Dim Table Dimension Tables Support for Cross-Application Analysis Dim Table Dim Table Products Dimension Dimension Tables Sales Orders Fact Table Purchase Orders Fact Table Time Dimension Oracle Order Management & Fulfillment Analytics Supply Chain Analytics Q. How many of my top customers bought products from my worst suppliers? Fundamental requirement that dimensions be common (conformed) 4

  5. Customers Suppliers Customers Suppliers HR / Workforce Sales Operations Service Marketing Distribution Finance Procurement Customers Suppliers Integrated Enterprise Analytics Data Model Features: • Conformed dimensions • Transaction data stored in most granular fashion • Tracks full history of changes • Prebuilt and extensible • Built for speed • Benefits: • Enterprise-wide business analysis (across entire value chain) • Access summary metrics or drill to lowest level of detail • Accurate historical representations 5

  6. The Result From this : To this : • Fewer, larger database tables rather than many smaller ones • Same piece of data appearing in several locations • Reduces need for join paths • Structure is denormalized for performance 6

  7. Best Practices in ETL 7

  8. Dashboards by Role Metadata Presentation Reports, Analysis / Analytic Workflows Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process App Layer App Layer ETL Architecture – Best Practice Administration Data Warehouse Load Source Independent Layer Metrics / KPIs Logical Model / Subject Areas BI Server Staging Tables Transform Physical Map Direct Access to Source Data DAC Source Dependent Extract SQL SQL SQL Special Connect Special Connect ABAP SQL Extract Siebel OLTP Oracle Other Oracle SAP R/3 Siebel PSFT EDW PeopleSoft SAP Federated Data Sources Other 8

  9. Use of a ETL platform • Limited programming – GUI interface • Re-usable components • Easy data lineage tracking (where did data come from?) • Pseudo-documentation – fast ramp-up for new resources • Can build, test & implement the data flows more quickly 9

  10. ETL Framework – Best Practices • Generates surrogate key • Does lookups for descriptions of code fields • Does data driven updates – inserts for new rows, updates for old rows • Reject Capture • Keep track of effective dates and maintain history as required • Handles Deletes 10

  11. Best Practices in Reporting 11

  12. Multi-layered Abstraction Separation of physical, logical and presentation layers Logical modeling builds upon complex physical data structures Logical model independent of physical data sources, i.e. same logical model can be remapped quickly to another data source Metrics / KPIs Aggregate navigation Prebuilt hierarchy drills and cross dimensional drills Dashboards by Role Metadata BI Presentation Services Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas BI Server Physical Map Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process The Semantic Layer Administration Direct Access to Source Data Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other 12

  13. Presentation Layer Semantic Object Layer Object Security Physical Layer Object SecurityWhat parts of the application can you see? • Business Logic Object Security • Controls access to Subject Areas, Tables and Columns in Presentation Layer • Web Object Security • Limits access to Dashboards, Reports and Web Folders

  14. Additional dashboards and reports, guided and conditional navigations, iBots, etc. Dashboards & Reports Easy Additional derived metrics, custom drill paths, exposing extensions in physical, logical and presentation layer, etc. Semantic Metadata Layer Moderate Extension of DW Schema for extension columns, additional tables, external sources, aggregates, indices, etc. DW Schema Intermediate Extension of ETL for extension columns, descriptive flexfields, additional tables, external sources, etc. ETL Involved Level of Effort Degree of Customization DW -BI Architecture – Best Practice 14

  15. For any sales queries, please contact Oracleindia_in@oracle.com 15

More Related