Logical Data Models for Agile BI David D

Logical Data Models for Agile BI David D. Schoeff Teradata - EDW Data Architect & Principal Consultant

Not Designing a Data Architecture is a … 2 > 3/18/2018

Why do we need an LDM? Data Warehouse with LDM Data Warehouse Without LDM 3 > 3/18/2018

What is the Purpose of a Data Model? • A visual business representation of how data is organized in the enterprise • It provides discipline and structure to the complexities inherent in data management • Can you imagine building a house without a blueprint? • Or driving across the country without a map? • It facilitates communication within the business (e. g. within IT and between IT and the business) • It facilitates arriving at a common understanding of important business concepts (e. g what is a customer? ) 4 > 3/18/2018

Logical Data Model Components … • LDM graphically represents the data requirements and data organization of the business > Identifies those things about which it is important to track information (entities) > Facts about those things (attributes) > Associations between those things (relationships) • Subject-oriented, designed in Third Normal Form – one fact in one place in the right place 5 > 3/18/2018

Reference Models Lots of Detail / Expertise Behind Models 6 > 3/18/2018

Reference Model Sources • Data Warehousing Vendors > IBM > Oracle > Teradata > … • Tool Vendors > Embarcadero > … • Service Vendors > EWSolutions > … • Industry/Standards Associations > ARTS (Association for Retail Technology Standards) > … 7 > 3/18/2018

Teradata Industry Logical Data Models - i. LDMs Financial Services - Banking, Investments Financial Services Communications - Wireline, Wireless, Cable, Satellite Travel - Insurance - Travel, Hospitality, Gaming Retail Transportation - Retail Store, Food Service - 3 PL, 4 PL, Air, Truck, Rail, Sea Manufacturing Healthcare - CPG, High Tech Automotive 8 > 3/18/2018 - Payor, HIPAA

Data Management Context Three Layer Structure Source Core (Enterprise) Semantic (Usage/Presentation) i. LDM Analyze & Design (Logical) Implement (Physical) Used for customization Views Data Integration Source Operational Images 9 > 3/18/2018 EDW-LDM Semantic Layer Models EDW-PDM Load Once Marts Use Many BIOs & User Types drive requirements

Enterprise Information Management Requires A Shared VOCABULARY Experts estimate that the 500 most commonly used words in the English language have an average of 28 definitions each. 10 > 3/18/2018

Enterprise Data Management Objectives that are enabled by Enterprise Logical Data Modeling : > Build a Common Business Vocabulary for the enterprise. > Develop an EDW Data Structure that is Neutral from All the Sources that populate it. > Develop an EDW Data Structure that will Support All Business Requirements While Not Being Constrained by any specific requirement. – i. e. Neutral from use by multiple functional areas – Supports operational and analytical uses 11 > 3/18/2018

Data Modeling Structure Data Modeling SUBJECT Model CONCEPTUAL Model KEY-BASED Model A model of the high level data concepts that define the scope of the Data Architecture. An entity-relationship model that identifies the elements of the Business Vocabulary and Business Rules. A refinement of the Conceptual Model that identifies the natural and surrogate keys for all entitles and relationships. This the foundation of the Enterprise Business Vocabulary. ATTRIBUTED Model PHYSICAL Model 12 > 3/18/2018 A detailed model that identifies the non-key attributes for the entitles. Attribution also leads to refining the Key-Based Model A model that is the design for a database. The Attributed Model is transformed for Sourcing and Accessing performance.

Data Modeling Structure Purposes Architecture Data Modeling SUBJECT Model Information Requirements CONCEPTUAL Model Reference Model KEY-BASED Model ATTRIBUTED Model Implementation PHYSICAL Model 13 > 3/18/2018 • Business Improvement Opportunities • Business Questions • Key Performance Indicators • Legacy Reporting/Analysis

Data/Information Management Data Modeling Data Warehousing APPLICATION Layer SUBJECT Model SEMANTIC Layer CONCEPTUAL Model Master Data CORE Layer ATTRIBUTED Model STAGING Layer 14 > 3/18/2018 DD L KEY-BASED Model PHYSICAL Model Access Layer Transaction Data Sources Data Source Layer Teradata Enabled Source Layer

Data Management Context Agile Development Environment User External Data 15 > 3/18/2018 Sandbox

Data Management Context Perceived Value from Medium to Large Scale Projects 80 -95% 0 -1% User External Data 16 > 3/18/2018 0 -5% Sandbox 5 -15%

Data Management Context Development Time for Medium to Large Scale Projects 4 -8 weeks 2 -4 Months 3 -6 months User External Data 17 > 3/18/2018 1 -5 days Sandbox

Data Integration 1 st Sandbox Application Local 2 nd Sandbox Application Shared Local Common Shared 3 rd Sandbox Application 18 > 3/18/2018 Local

Data Management Context Integration in an Agile Development Environment Conceptual Data Architecture Governance-driven Integration User External Data 19 > 3/18/2018 Sandbox

Pros and Cons of Using a Vendor Provided Analytical Data Model in Your BI Implementation Let’s Boris Evelson, Information Management Blogs, January 29, 2010 discuss. Pros: • Leverage vendor knowledge from prior experience and other customers • May fill in the gaps in enterprise domain knowledge • Best if your IT dept does not have experienced data modelers • May sometimes serve as a project, initiative, solution accelerator • May sometimes break through a stalemate between stakeholders failing to agree on metrics, definitions Cons: • May sometimes require more customization effort, than building a model from scratch • May create difference of opinion arguments and potential road blocks from your own experienced data modelers • May reduce competitive advantage of business intelligence and analytics (since competitors may be using the same model) • Goes against “agile” BI principles that call for small, quick, tangible deliverables • Goes against top down performance management design and modeling best practices, where one does not start with a logical data model but rather > Defines departmental, line of business strategies > Links goals and objectives needed to fulfill these strategies > Defines metrics needed to measure the progress against goals and objectives > Defines strategic, tactical and operational decisions that need to be made based on metrics > Then, and only then defines logical model needed to support the metrics and decisions 20 > 3/18/2018

Cooking Something New. . . “Change without a recipe is a recipe for chaos. ” “The transformation model must describe not only the steps in the process, but also the enabling context that is critical to its success. ” If Only We Knew What We Know Carla O’Dell & C. Jackson Grayson The Free Press, 1998 21 > 3/18/2018