The logical dimensional model should be developed jointly by representatives from all interested groups: business users, reporting teams, and the DW/BI project team. It is important that the appropriate individuals are represented on the dimensional data model design team as described in Design Tip #103 in order to achieve an effective design. The best dimensional models result from a collaborative team effort. No single individual is likely to have the detailed knowledge of the business requirements and the idiosyncrasies of all the source systems to effectively create the model themselves.
However, involving more people in the design process increases the risk of slowing down the process. With so many individuals involved, it’s important that the lead designer/facilitator keeps the group on track. The team may find itself spiraling into deep, complex discussions of data elements only to determine that the data element in question isn’t within the design scope or perhaps isn’t a reasonable candidate to be included in the design.
A helpful strategy for limiting long resource-draining discussions is to establish a “design charter” early in the design process. The goal of the design charter is to keep the team focused on the key issues and avoid runaway discussion on ancillary topics. The design charter is simply the mantra the design team looks to when in doubt to help guide the process. As data elements are discussed and it is not readily apparent whether the item is in scope or out of scope, the design team can run the data element through the project’s design charter. While each design team should develop its own specific charter, the following are examples of charters we’ve used in the past:
1 – Does the data element help support the business requirements that are in scope?
- For example, one insurance client’s core business requirements included supporting the organizations’ financial, management and regulatory reporting requirements. All proposed data elements were passed through that filter.
- Another client’s goal was to build an analytic platform enabling the business to support world class analytics. In that case the filter became: Is the data element analytically interesting? Will it be used to support analytic requests? Or does it exist only to support a need of the operational system?
2 – What does the data element describe? Is it a dimension attribute used for slicing / dicing / grouping / constraining? Or is it a metric? If the data element is an amount, is it a metric or dimensional attribute? If it is an amount that behaves like an attribute, can it be range valued?
3 – If it is a dimension attribute, can it change? If it can change, will analysis based on the value at the time of the measurement be required? If it can change, will analysis based on the current value be required? Or both?
It is easy for the design team to get bogged down trying to incorporate the seemingly endless data elements available. To stay on track, keep the design charter in mind at all times during the design process. The first bullet is critical: does the data element support the business requirements? Always keep in mind that the goal is not to “model the data.” The goal is to create a data model that will support the business need. It’s okay to leave data elements behind. Be especially leery of data elements that primarily support operational capabilities. Constantly ask “How will the data element be used analytically? What value does it provide?” in an effort to fully understand the data in question. Often it’s not enough to merely include the data element in the design; it may need to be tweaked, enriched, or ranged to make it as valuable as possible to the business community.