Building a Logical Enterprise Data Warehouse from Existing Data Warehouses

Building a Logical Enterprise Data Warehouse from Existing Data Warehouses Brian Beckman Procter & Gamble Sep 29, 2014

About Procter & Gamble • Countries of operation: ~70 • Countries where our brands are sold: ~180 • Consumers served by our brands: 4.8 billion (approximate) • Last FY net sales: $83.1 billion

Agenda 1 2 3 4 Starting Point The Challenge… …And Our Response Keys to Success

Data Warehousing History • Almost 20 years of global data warehousing • Conformed internal dimensions • Mature data warehouse environment delivering significant business value • Constant incremental improvements, tuning, adaptation to changing business needs

Data Warehouse Evolution • Evolved from multiple distributed Oracle DBs to consolidated multi-TB DBs on Exadata, SAP BW • Dramatic reliability, performance improvements • New tools being adopted (BI tools, Big Data, etc.)

However, we still have challenges • Too long, too expensive, “one-size fits all” mentality • Multi data reporting speed and cost • Agility gaps to enable self-serve analytics • Data distributedacross systems, internally and externally • VUCA world in technology and business As a result, our customers started to refer to our warehouses as “Roach Motels”

Our Response At a high level…there is no single silver bullet solution but rather we need a: Multi-pronged approach against an explicit architectural strategy …addressing the primary root causes …but built upon our successes …yet with revolutionary changes

The Heart of our Solution A logical enterprise data warehouse The Logical Data Warehouse (LDW) is a new data management architecture for analytics which combines the strengths of traditional repository warehouses with alternative data management and access strategy. LDW according to the Gartner Hype Cycle for Information Infrastructure, 2012

LDW Journey Not a direct leap but an evolutionary journey which requires: • A solid data foundation • Strategy to address critical business needs first • Treating data as an asset to be extended • Recognition that technology is only part of the solution andtechnology evolution reinforces the value of a LDW

First… Fix the “one-size-fits-all” mentality for projects and DW platforms that drove speed and cost issues • Establish a prototyping platform • Create rapid deployment project teams

Agile Approach to DW • “Prototype plant” separate from standard production DW lifecycles • Dedicated partner resources specialized in DW/BI development, ready in days • Iterate to solutions with business partners, focusing on 80/20

Next… Separate data from applications and address data harmonization (and thus speed of data integration) to create the logical DW foundation • Define BI Master Data to establish a common language of golden attributes for data • Map critical data sets to golden attributes to translate to the common language

United Nations Analogy

On-the-fly Translations Japanese Japanese Translate Dutch Dutch International English Spanish Spanish UK English UK English US English US English

One-way Translation UK English: “BMW’s are splendid cars” US English: “BMW’s are awesome cars” US Boston English: “BMW’s are wicked awesome cars” International English: “BMW’s are very good cars” The meaning of the translation stays intact, but the exact original sentence can not be recreated

BI Master Data Concepts • Good enough for business insights & decisions • Not good enough to literally translate data back to the source (not intended to close the books) • For external purposes we can translate our “International English” back to other languages, just like the UN does!

Powered by Golden Attributes • Valid business values for key attributes across dimensions (e.g., product brands and categories, customer details, trade channels, time definitions, etc.) • Independent of solutions and data sources • Treat as additional master data available to further describe existing sources

Golden Attributes In Action BI Tool Layer MM Ship BI Master Data & Mappings Application Data Mart Layer SH + MM App SH + MM App EDW Layer MM POS Ship

And then… Consolidate all the data into one place…but since money and time are finite, do it virtually rather than physically • Embrace a “play-it-where-it-lies” (PIWIL) mentality • Implement data virtualization

Data Virtualization in the BI Complex Metadata Security Governance Architecture Reporting Layer Impala, Parquet, new analytic tools, etc. Spotfire Business Objects OBIEE BI Tool Semantic Layer WebI Report Xcelsius MGRC Template CustSuff Template WebI Report Xcelsius Data Extractor Info Links Universe Data Virtualization BI Tool Agnostic Semantic Layer Demand/Consumer Views POS Ship + Share Share Finance Ship + Share MGRC POS Other Share Ship Common Model “Harmonized Layer” - Integrated ADW (Exadata) BW (->HANA) App Data Mart Layer Shipments Financials Product Shares Others Geo POS Mappings BI Master Data Conformance Layer Custom Hier EDW Layer (Enterprise Data Model) Essbase Master Data Big Data MM Cubeless FMR Data Mart MGRC Data Mart Standard Standard Standard Product Customer Standard Market Measures Shipments POS Financials Profit Center Geo Legal Entity Channel Structured data sources Unstructured data sources

Finally… Determine how to insulate the DW environment from change….but since time cannot be stopped, instead: • Pursue cloud concepts of elasticity as well as consumption based pricing for the DW • Previous steps required for a DW cloud to be a reality

Keys to Success • Stabilize the foundation • Embrace what has worked… …but realize radical re-invention needed! • Buy a sports car (fast team and environment) • Mandate language courses (harmonize the data) • Play golf (PIWIL) • Bring an umbrella (cloud concepts)

Questions?

Building a Logical Enterprise Data Warehouse from Existing Data Warehouses