Bus Matrix… the foundation of your Data Warehouse

1. Bus Matrix…the foundation of your Data Warehouse Bill Anton Prime Data Intelligence

2. About Me • I Love Data! • …also, Microsoft DW/BI (MCTS/MCITP, MCSA/MCSE) • Independent Consultant @ Prime Data Intelligence, LLC • Atlanta BI SQL Server Users Group • Twitter: @SQLbyoBI • Blog: http://byoBI.com • Email: william.anton@gmail.com

3. What we will cover today  • Dimensional Modeling 101 • What, Why, How • Common Challenges • Bus Matrix • What is it? • How does it help? • Examples

4. What is Dimensional Modeling?

5. What is Dimensional Modeling? “Star Schema”

6. What is Dimensional Modeling? • Denormalization • “Repeating Values” • Opposite of “normalized” (e.g. 3rd Normal Form) • Optimized for reads (not writes)

7. Dimensional Modeling 101 Question: What are the most common types of Data Warehouse methodologies/architectures? • Kimball • Inmon • Data Vault

8. Dimensional Modeling 101 Question: For which of these DW methodologies should you include a dimensional model? Kimball, Inmon, Data Vault All of them 

9. Kimball Dimensional DW

10. Inmon 3NF EDW + Data Mart(s)

11. Data Vault + Data Mart(s)

12. Why Dimensional Modeling • Intuitive to Business Users • Simpler than OLTP/3NF • Rise of Self-Service (E.g. Power Pivot, Power View) • Iterative Development • “Agile” • Performance • Optimized for analytical queriese.g. sales amount by product in 2013 for top 10 all-time customers • And many more… See TeoLachev’s “WHY SEMANTIC LAYER” newsletter:http://www.prologika.com/Newsroom/Newsletter2013Fall.aspx

13. Intuitive to Business Users

14. How many bikes did we sell last year?

15. Do we sell more bikes to single or married females?

16. What was our most/least profitable product this year?

17. What was the Average Monthly Gross Margin Return on Inventory Investment (GMROII) by Product Category for the trailing 6 months? It’s Complicated

18. Star-Schema

19. 1 “Star” per Fact table Inventory Process Sales Process

20. Facts are related through dimensions… Inventory Process Sales Process

21. Facts are related through dimensions… “Conformed Dimensions”A conformed dimension is a set of data attributes that have been physically referenced by multiple fact tables using the same key value to refer to the same structure, attributes, domain values, definitions and concepts. Dimensions are conformed when they are either exactly the same(including keys) or one is a perfect subsetof the other. Dimension tables are NOTconformed if the attributes are labeled differently or contain different values.

22. Dimensions: Conformed vs Unconformed

23. Revisiting Average Monthly Gross Margin Return on Inventory Investment (GMROII) Sum of each month ending inventory cost Average Monthly GMROII Profit for total time period

24. What was the Average Monthly Gross Margin Return on Inventory Investment (GMROII) by Product Category for the trailing 6 months?

25. Where things start to get complex… • 1 Star per Fact table • Multiple Fact tables per business process • Multiple business processes in an enterprise

26. Dimensional Model becomes a “Galaxy of Stars” Finance Production Sales Distribution HR

27. ER Diagram: Adventure Works Sample DW

28. For bigger Data Warehouses… This ^^ Turns into this ^^

29. Variety of Problems to Overcome with Dimensional Modeling • Communication & Strategy • What’s the short term plan of attack? • What’s the long term plan of attack? • Documentation • What’s in our Data Warehouse? • Business Users can’t read ER diagrams • Business Users are typically only familiar with a 1 or 2 business processes • E.g. Sales User vs Inventory User; Warehouse Supervisor vs CEO • Conforming Dimensions is hard…REALLY hard • So are changes (E.g. Impact Analysis)

30. What’s the Solution? • Train business users to read ER Diagrams? • Simplify Data Model? • Ignore certain business processes? • Don’t use Conformed Dimensions? • Force business users to manually map data between processes? What about a Bus Matrix?

31. What is a Bus Matrix? 2-dimensional visualization showing the intersection of facts and dimensions

32. Variety of Use-Cases for a Bus Matrix • Documentation, Communication, Training • Facilitate User Adoption of BI tools • Communicate Expectations w/ Business • New users unfamiliar with new business process • Team Development • Agile • Prioritization of Tasks • Divide & Conquer • Road-Mapping • Prioritization of Business Processes in a Business Intelligence “Program”

33. Documentation For Business

34. Documentation for IT

35. Master Bus Matrix

36. Team Development Sprint 1Internet Sales Sprint 2Reseller Sales

37. Road-Mapping

38. When To Create a Bus Matrix • During Requirements Gathering • Before You Start Development! • Updated Over Time • Changes to Business Processes • New Source Systems (E.g. mergers/acquisitions)

39. How To Create a Bus Matrix Manual via Excel Automated via SSRS

40. Manual • Only option when starting out ;-) • Updates can be made quickly made as requirements come in • Adds development overhead, but the ROI is well worth it

41. Automated • Reporting pack with drill-through to data dictionary information • Can be based on Cube or Relational Database (*FK required) • Incorporate query statistics to visualize common usage patterns • Use MDS to allow SME’s to manage business definitions Based on example from Alex Whittleshttp://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/

42. QUESTIONS

43. References Twitter: @SQLbyoBI Blog: http://byoBI.com Email: william.anton@gmail.com http://byobi.com/blog/bus-matrix/