Our website and books are loaded with guidance about designing dimensional models for the data warehouse/business intelligence (DW/BI) presentation area. But dimensional modeling concepts go beyond the design of databases that are simple and fast. You should think dimensionally at other critical junctures of a DW/BI project.
When gathering requirements for a DW/BI initiative, you need to listen for and then synthesize the findings around business processes, as described in Design Tip #69. Sometimes teams get lulled into focusing on a set of required reports or dashboard gauges. Instead you should constantly ask yourself about the business process measurement events producing the report or dashboard metrics. As you are planning the project’s scope, you should remain steadfast about focusing on a single business process per project and not sign up to deploy a dashboard that includes a handful of them in a single iteration. Attempting to design dimension models to deliver multiple loosely-related metrics is a classic “failure to declare the grain” mistake.
Although it’s critical that the DW/BI team concentrates on business processes, it’s equally important to get IT and business management on the same process-centric wavelength. Due to historical IT funding policies, the business may be more familiar with departmental data deployments. You need to shift their mindset about the DW/BI rollout to a process perspective, not a department or report perspective. When prioritizing opportunities and developing the DW/BI roadmap, business processes are the unit of work. Fortunately, business management typically embraces this approach since it mirrors their thinking about key performance indicators, and can nudge IT in the right direction. Plus, the business has lived with the inconsistencies, incessant debates, and never ending reconciliations caused by the departmental approach, so they’re ready for a fresh tactic. Working with senior business partners, you should rank each business process on business value and feasibility, then tackle processes with the highest impact and feasibility scores first. Although prioritization is a joint activity with the business, your underlying understanding of the organization’s business processes is essential to its effectiveness and subsequent actionability.
If tasked with drafting the DW/BI system’s data architecture, you need to wrap your head around the organization’s processes, along with the associated master descriptive dimension data. The prime deliverable for this activity, the enterprise data warehouse bus matrix, is described in our article “The Matrix: Revisited.” The matrix also serves as a useful tool for touting the potential benefits of more rigorous master data management.
Data stewardship or governance programs should focus first on the business’s major dimensions. Depending on the industry, the list might include date, customer, product, employee, facility, provider, patient, student, faculty, account, and so on. Thinking about the central nouns used to describe the business translates into a list of data governance efforts to be led by subject matter experts from the business community. Establishing data governance responsibilities for these nouns is the key to eventually deploying dimensions that deliver consistency and address the business’s needs for analytic filtering, grouping, and labeling. Robust dimensions translate into robust DW/BI systems.
As you can see, the fundamental motivation for dimensional modeling is front and center long before you descend into the technical world of star schemas or OLAP cubes. Dimensional concepts link the business and technical communities together as they collaboratively specify the DW/BI deliverables.