The Kimball Group has always stressed the importance of keeping a keen eye on the business requirements when designing dimensional data models for the data warehouse/business intelligence (DW/BI) environment. Gathering business requirements is typically undertaken just prior to beginning the dimensional data model design process. Design Tip #110 is a reminder of requirements gathering do’s and don’ts. We also believe it’s extremely important to include key business representatives in the design process itself; Design Tip #103 discusses the recommended makeup of the design team. Unfortunately, many organizations balk at including business representatives in the design activities. They view dimensional modeling as a technical exercise focused on modeling data elements and fail to appreciate the value of involving business subject matter experts.
My manta regarding the dimensional data modeling process is “Remember – data warehousing is not about the data. It’s about serving the business requirements!” Okay, I’ll admit that data warehousing is about the data to a significant extent, but when it comes to the design process, I’ll stand by my mantra. Worrying primarily about the data and failing to focus on the business requirements is a critical mistake. Including business representatives in the design process and keeping them engaged will result in a significantly better design. Often it’s hard to appreciate the value of business involvement until you are deep into the design process. But at the end of the day, the resultant data model must support the business requirements or the DW/BI initiative will fail. Design Tip #123 describes how to leverage the dimensional data model to validate and capture business requirements.
To effectively leverage the participation of business users, it is important to constantly keep them engaged. If discussions turn away from modeling the requirements and turn to detailed ETL design topics or other technical issues, the business users will tune out and ultimately stop participating. To keep them involved, maintain the focus on the design and how it supports the business requirements. The technical discussions can wait for another time.
Business users will actively participate in the design process if given half a chance. At the beginning they won’t be sure about how to participate as this is new to them. Fortunately, there are countless opportunities during a design session to engage the business. Every key dimensional attribute or metric is ripe for discussion. Key questions include: “Why is this data element important? How do you use it? What will you do with it in a report or analysis? What other attributes or metrics do you combine it with? Why?” Typically, these discussions lead to a deeper understanding of the business requirements that may not have surfaced earlier.
For example, many business processes (such as tracking call segments in a call center) result in transaction fact tables that include Begin Time and End Time dimensions. An inexperienced data-oriented design team that does not include business representation will be satisfied with a fact table with both these dimensions. A more experienced design team might query their business partners regarding the use of these time dimensions and discover they are used to calculate the call’s duration. With this newly understood requirement, the design team will enhance the fact table to include call duration as a metric. This enhancement results in a more compelling design for the business users who can now measure average call durations by any of the associated dimension attributes, enabling them to understand service levels without performing the duration calculations at query time. From a business perspective, this represents a significant usage improvement over the initial design.
Continued discussions with the business users regarding this metric might uncover the existence of standard service levels that have been defined by the business. For example, you may discover the business has recently established a new baseline for classifying call durations: a call duration of 2 to 5 minutes is considered “Normal,” 1 to 2 minutes is considered “Normal – Short,” less than 1 minute is considered “Abnormal – Short,” and likewise for “Normal – Long” and “Abnormal – Long.” The users explain that this classification will serve as the basis for several planned dashboards and scorecards based on the new data warehouse environment. Aha! Here is another opportunity to embellish the design to better support the business requirements. Clearly, you will want to create a Duration Type dimension that includes this classification, plus a rollup attribute to aggregate all “Normal” durations and “Abnormal” durations.
Due to the active participation of your business partners, the resulting design will better support their underlying business requirements. While the initial design would have included all the data elements required to support the requirements, it would have fallen short on ease of use and richness from a business perspective. Thus, the mantra that data warehousing is not just about the data; it’s about serving the business requirements. The active, engaged participation of your business partners in the design process will result in a design that is far more effective than solely focusing on modeling the data.