{"id":19159,"date":"2023-10-27T20:07:15","date_gmt":"2023-10-28T02:07:15","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=19159"},"modified":"2025-05-18T12:12:31","modified_gmt":"2025-05-18T18:12:31","slug":"steve-hobermans-data-modelling-questions-made-simple","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/steve-hobermans-data-modelling-questions-made-simple\/","title":{"rendered":"Steve Hoberman’s Data Modelling Questions Made Simple"},"content":{"rendered":"\n

Steve Hoberman’s 10 Questions to use in your Data Model Scorecard <\/h1>\n\n\n\n
  1. How do the characteristics of the model support the type of model\u00a0e.g., OLTP or OLAP<\/li>
  2. How well does the model capture the requirements?<\/li>
  3. How complete is the model? \u00a0<\/li>
  4. How structurally sound is the model? Have you checked primary and Alternate Keys, no null values, referential integrity, check constraints, Indexes, Partitions, etc \u00a0<\/li>
  5. How well does the model leverage generic structures?<\/li>
  6. How well does the model follow naming standards?<\/li>
  7. How well is the model arranged for readability?<\/li>
  8. How good are the definitions? Document the entities and each attribute, and give examples of values.<\/li>
  9. How consistent is the model with the enterprise standards? Did you refer to the enterprise glossary for physical and logical definitions reuse?<\/li>
  10. How well does the metadata match the data? \u00a0<\/li><\/ol>\n\n\n\n

    Implementing Dimensional Models <\/h2>\n\n\n\n

    Implementing dimensional models effectively requires both strategic thinking and attention to detail. Here are key best practices<\/strong> to follow when designing and implementing dimensional models:<\/p>\n\n\n\n

    1. Understand Business Processes First<\/strong><\/h3>\n\n\n\n
    • \nModel the business, not just the data.<\/strong>\n<\/li>
    • \nBegin by identifying key business processes<\/em> (e.g., sales, inventory, shipping).\n<\/li>
    • \nFocus on the metrics<\/em> (facts) business users care about (e.g., revenue, quantity sold).\n<\/li><\/ul>\n\n\n\n

      2. Use Clear and Consistent Naming Conventions<\/strong><\/h3>\n\n\n\n
      • \nUse business-friendly, readable names<\/strong> for tables and columns.\n<\/li>
      • \nAvoid technical jargon or abbreviations that aren\u2019t widely understood.\n<\/li><\/ul>\n\n\n\n

        3. Design Star Schemas, Not Snowflakes<\/strong><\/h3>\n\n\n\n
        • \nFavor denormalized<\/strong> dimension tables to simplify querying.\n<\/li>
        • \nOnly use snowflaking (normalizing dimensions) if it significantly reduces redundancy or simplifies updates.\n<\/li><\/ul>\n\n\n\n

          4. Create Conformed Dimensions<\/strong><\/h3>\n\n\n\n
          • \nReuse dimensions like Date<\/code>, Customer<\/code>, Product<\/code> across different fact tables.\n<\/li>
          • \nEnsures consistency in reporting and enables cross-domain analysis.\n<\/li><\/ul>\n\n\n\n

            5. Handle Slowly Changing Dimensions (SCDs) Thoughtfully<\/strong><\/h3>\n\n\n\n
            • \nDecide on the appropriate method:\n
              • \nType 1<\/strong>: Overwrite with new values (simple, loses history)\n<\/li>
              • \nType 2<\/strong>: Create new records for changes (retains history)\n<\/li>
              • \nType 3<\/strong>: Keep limited history in additional columns\n<\/li><\/ul>\n<\/li>
              • \nApply SCD types based on reporting requirements.\n<\/li><\/ul>\n\n\n\n

                6. Include Surrogate Keys<\/strong><\/h3>\n\n\n\n
                • \nUse surrogate keys<\/strong> (e.g., integer IDs) in dimensions instead of natural keys.\n<\/li>
                • \nImproves performance, handles data changes gracefully, and avoids key collisions.\n<\/li><\/ul>\n\n\n\n

                  7. Design Fact Tables Carefully<\/strong><\/h3>\n\n\n\n
                  • \nUse additive<\/strong> or semi-additive<\/strong> measures where possible.\n<\/li>
                  • \nEnsure grain (level of detail) is well defined and consistent.\n<\/li>
                  • \nInclude foreign keys to all related dimensions.\n<\/li><\/ul>\n\n\n\n

                    8. Add a Date Dimension (Always)<\/strong><\/h3>\n\n\n\n
                    • \nEven if timestamps exist, use a well-structured Date<\/code> dimension.\n<\/li>
                    • \nSupport fiscal periods, holidays, and relative time filters (e.g., YTD, QTD).\n<\/li><\/ul>\n\n\n\n

                      9. Build Coverage and Bridge Tables Where Needed<\/strong><\/h3>\n\n\n\n
                      • \nUse coverage tables<\/strong> to model what should<\/em> be there versus what is<\/em> there (useful in sales quotas or expected delivery).\n<\/li>
                      • \nUse bridge tables<\/strong> to manage many-to-many relationships (e.g., students enrolled in multiple classes).\n<\/li><\/ul>\n\n\n\n

                        10. Test with Real-World Queries<\/strong><\/h3>\n\n\n\n
                        • \nValidate model design with actual reporting scenarios.\n<\/li>
                        • \nMake sure performance and usability meet business needs before scaling.\n<\/li><\/ul>\n\n\n\n

                          11. Document the Model<\/strong><\/h3>\n\n\n\n
                          • \nMaintain documentation for:\n
                            • \nDimensions and their attributes\n<\/li>
                            • \nFact tables and grain\n<\/li>
                            • \nBusiness definitions of metrics\n<\/li><\/ul>\n<\/li>
                            • \nEnables data literacy and reduces onboarding time for analysts.\n<\/li><\/ul>\n\n\n\n

                              Adhering to these practices will make your dimensional models more scalable, user-friendly, and aligned with business goals<\/strong>, making them a powerful foundation for analytics and decision-making.<\/p>\n","protected":false},"excerpt":{"rendered":"

                              Steve Hoberman’s 10 Questions to use in your Data Model Scorecard  How do the characteristics of the model support the type of model\u00a0e.g., OLTP or OLAP How well does the model capture the requirements? How complete is the model? \u00a0 How structurally sound is the model? Have you checked primary and Alternate Keys, no null […]<\/p>\n","protected":false},"author":2,"featured_media":19162,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-19159","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-data-warehouse"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2023\/10\/DeZign-for-Databases.png","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/19159","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=19159"}],"version-history":[{"count":5,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/19159\/revisions"}],"predecessor-version":[{"id":23678,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/19159\/revisions\/23678"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/19162"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=19159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=19159"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=19159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}