1 / 16

Tips and Tricks for Dimensional Modeling

Tips and Tricks for Dimensional Modeling. By Shawn Jackson. Overview. Set of techniques and concepts used in data warehouse design Intended to support end-user queries and is oriented around understandability and performance Uses the concepts of facts (measures) and dimensions (context)

armine
Download Presentation

Tips and Tricks for Dimensional Modeling

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tips and Tricks for Dimensional Modeling By Shawn Jackson

  2. Overview • Set of techniques and concepts used in data warehouse design • Intended to support end-user queries and is oriented around understandability and performance • Uses the concepts of facts (measures) and dimensions (context) • Facts are typically (but not always) numerical values that can be aggregated • Dimensions are groups of hierarchies and descriptors that define the facts

  3. Star Schema

  4. Snowflake Schema

  5. Kimball University:10 Essential Rules of Dimensional Modeling (#1-5) • Load detailed atomic data into dimensional structures • Store data at the lowest grain • Use summary tables/views to improve performance as necessary • Structure dimensional models around business processes • Fact tables should be based on a business event • Complement single process fact tables with consolidated fact tables that combine metrics from multiple processes at the same level of detail • Ensure that every fact table has an associated date dimension table • Ensure that all facts in a single fact table are at the same grain or level of detail • Resolve many-to-many relationships in fact tables

  6. Kimball University:10 Essential Rules of Dimensional Modeling (#6-10) • Resolve many-to-one relationships in dimension tables • Store report labels and filter domain values in dimension tables • Don’t store codes and descriptions in the fact table • Make sure the full description of the code is in the dimension table • Make certain that dimension tables use a surrogate key • Create conformed dimensions to integrate data across the enterprise • Date dimension is a common example • Single version of the truth • Continuously balance requirements and realities to deliver a DW/BI solution that's accepted by business users and that supports their decision-making

  7. Slowly Changing Dimensions • Type 0 • Type 1 • Type 2 • Type 3 • Type 4 • Type 6

  8. SCD Type 0 • Rows are added but never changed • Missing true business / natural key • Typically are only used in derived dimensions • Type 0 attributes are more common

  9. SCD Type 1 • Rows can be updated or added based upon business key • Historical information is not tracked

  10. SCD Type 2 • Rows are only added • A version number or effective dates are used to keep track of history

  11. SCD Type 3 • Rows are updated but not added • Historical information is preserved through extra columns

  12. SCD Type 4 • Combination of type 1 and type 2 dimensions • Rows are updated in the type 1 table and added in the type 2 table

  13. SCD Type 6 / hybrid • Combines type 1, 2 and 3 in one table

  14. Roleplaying Dimensions • Recycled for multiple applications within the same database • Date dimension is commonly used (sale date, delivery date) • Can be used to get different views of data

  15. Roleplaying Example

  16. Factless Fact Tables • Tracking events • Many to many joins

More Related