Intelligent Enterprise

Kimball Group has written more than 150 Intelligent Enterprise columns and one hundred Kimball Design Tips about dimensional modeling techniques, but we haven't written much about the dimensional modeling process. What are the tasks and deliverables required to create a robust design? This article presents best practices for designing a dimensional model.

Before embarking on a dimensional design project, you need a solid understanding of the business's requirements along with a reasonable assessment of the underlying source data. It's tempting to skip the requirements review, but resist this urge as doing so increases the risk of developing a source-driven model that falls short of business needs in numerous small but significant ways. Optimally, the requirements have been thoroughly researched and documented in a user-approved requirements finding document with the top priority needs clearly identified. This deliverable often also includes a preliminary data warehouse bus matrix. (More details about the bus matrix, a critical data architecture planning tool, are available in an Intelligent Enterprise column, "The Matrix: Revisited.")

In addition to the business requirements and bus matrix, you'll also want to review any source data profiling insights uncovered by the project team to date. The final item on your required-reading list is your organization's naming convention standards document. If you don't already have naming conventions to adapt for data warehousing and business intelligence, you'll need to establish them as you develop the dimensional model. For example, you may opt for a three-part column naming standard consisting of a prime word, zero or more qualifiers, and class word to yield a column name such as sales_dollar_amount.