bus matrix the foundation of your data warehouse n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Bus Matrix… the foundation of your Data Warehouse PowerPoint Presentation
Download Presentation
Bus Matrix… the foundation of your Data Warehouse

Loading in 2 Seconds...

play fullscreen
1 / 43

Bus Matrix… the foundation of your Data Warehouse - PowerPoint PPT Presentation


  • 153 Views
  • Uploaded on

Bus Matrix… the foundation of your Data Warehouse. Bill Anton Prime Data Intelligence. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Bus Matrix… the foundation of your Data Warehouse' - netis


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
bus matrix the foundation of your data warehouse

Bus Matrix…the foundation of your Data Warehouse

Bill Anton

Prime Data Intelligence

about me
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
what we will cover today
What we will cover today 
  • Dimensional Modeling 101
    • What, Why, How
    • Common Challenges
  • Bus Matrix
    • What is it?
    • How does it help?
    • Examples
what is dimensional modeling2
What is Dimensional Modeling?
  • Denormalization
    • “Repeating Values”
    • Opposite of “normalized” (e.g. 3rd Normal Form)
    • Optimized for reads (not writes)
dimensional modeling 101
Dimensional Modeling 101

Question: What are the most common types of Data Warehouse methodologies/architectures?

  • Kimball
  • Inmon
  • Data Vault
dimensional modeling 1011
Dimensional Modeling 101

Question: For which of these DW methodologies should you include a dimensional model? Kimball, Inmon, Data Vault

All of them 

why dimensional modeling
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

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

It’s Complicated

1 star per fact table
1 “Star” per Fact table

Inventory Process

Sales Process

facts are related through dimensions
Facts are related through dimensions…

Inventory Process

Sales Process

facts are related through dimensions1
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.

revisiting average monthly gross margin return on inventory investment gmroii
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

slide24
What was the Average Monthly Gross Margin Return on Inventory Investment (GMROII) by Product Category for the trailing 6 months?
where things start to get complex
Where things start to get complex…
  • 1 Star per Fact table
  • Multiple Fact tables per business process
  • Multiple business processes in an enterprise
dimensional model becomes a galaxy of stars
Dimensional Model becomes a “Galaxy of Stars”

Finance

Production

Sales

Distribution

HR

for bigger data warehouses
For bigger Data Warehouses…

This ^^

Turns into this ^^

variety of problems to overcome with dimensional modeling
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)
what s the solution
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?

what is a bus matrix
What is a Bus Matrix?

2-dimensional visualization showing the intersection of facts and dimensions

variety of use cases for a bus matrix
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”
team development
Team Development

Sprint 1Internet Sales

Sprint 2Reseller Sales

when to create a bus matrix
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)
how to create a bus matrix
How To Create a Bus Matrix

Manual via Excel

Automated via SSRS

manual
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
automated
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/

references
References

Twitter: @SQLbyoBI

Blog: http://byoBI.com

Email: william.anton@gmail.com

http://byobi.com/blog/bus-matrix/