Kimball University: Think Critically When Applying Best Practices
Best practices are precision tools that should be wielded precisely and skillfully. This article describes five best practices drawn from the Kimball Method that often are described incorrectly.
Dimensional modeling is a design discipline focused on optimizing the business intelligence platform for business users' ease of use and query performance. To achieve the goals of being simple and fast we describe a set of very specific design recommendations:
Conformed master dimensions form the bedrock of the enterprise BI/DW system and by themselves address the central issues of integration
Fact tables are derived directly from measurement processes found in familiar transaction-based applications. A fact table should never be departmentally or functionally bound but rather depends only on the "physics" of the original measurement process.
Fact tables should always be populated at the most atomic level possible for maximum flexibility. Atomic data lets business users ask constantly changing, far-ranging and very precise questions. It also assures the extensibility of additional attributes, metrics or dimensions without disrupting existing reports and queries.
Exposing snowflaked or normalized dimension tables directly to end users is strongly discouraged. We have shown repeatedly that properly designed denormalized (flat) dimension tables contain precisely the same information content as normalized schemas. The only difference is complexity, as experienced by the end users. We embrace (and teach) normalized designs in the extract/transformation/load (ETL) phases; however, we avoid normalization in the user-accessible presentation area.
Data integration and consistency are key goals of any enterprise business intelligence effort. Data integration requires organizational consensus to establish and administer common labels and measures enterprisewide. In the Kimball Method, these labels and measures reside in conformed dimensions and conformed facts, respectively. Conformed dimensions are typically built and maintained as centralized, persistent master data during ETL, then reused across dimensional models to enable data integration and ensure consistency.
We enthusiastically support the recent Master Data Management (MDM) and Customer Data Integration (CDI) trends, as they are very consistent with the "conformed approach." For more insight, read The Matrix: Revisited and Integration for Real People articles at Intelligence Enterprise.
The Agile ArchiveWhen it comes to managing data, donít look at backup and archiving systems as burdens and cost centers. A well-designed archive can enhance data protection and restores, ease search and e-discovery efforts, and save money by intelligently moving data from expensive primary storage systems.
2014 Analytics, BI, and Information Management SurveyITís tried for years to simplify data analytics and business intelligence efforts. Have visual analysis tools and Hadoop and NoSQL databases helped? Respondents to our 2014 InformationWeek Analytics, Business Intelligence, and Information Management Survey have a mixed outlook.