Insert Picture Here Best Practices for Data Warehousing

<Insert Picture Here> Best Practices for Data Warehousing

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

Best Practices in Data Modeling 3

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

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

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

Best Practices in ETL 7

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

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

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

Best Practices in Reporting 11

The Semantic Layer • Multi-layered Abstraction Dashboards by Role BI Presentation Services Logical Model / Subject Areas BI Server Physical Map Metadata Metrics / KPIs Data Warehouse / Data Model Direct Access to Source Data Load Process Staging Area ETL • Metrics / KPIs • Aggregate navigation • Prebuilt hierarchy drills and cross dimensional drills DAC Administration Reports, Analysis / Analytic Workflows • 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 Extraction Process Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other 12

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

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

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