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. Margy Ross on October 3, 2013.
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 (See it above). 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.
Design Tip #69 Identifying Business Processes
Margy Ross on July 5, 2005
Readers who follow the Kimball approach can often recite the 4 key decisions when designing a dimensional model: identify the business process, grain, dimensions and facts. While this sounds straightforward, teams often stumble on the first step. They struggle to articulate the business process as it’s a term that seems to take on different meaning depending on the context. Since the business process declaration is the first stake in the ground when designing a dimensional model, we want to eliminate confusion in our context.
First, let’s begin by discussing what a business process is not. When designing a dimensional model, the business process does not refer to a business department, organization or function. Likewise, it shouldn’t refer to a single report or specific analysis.
For a dimensional modeler, the business process is an event or activity which generates or collects metrics. These metrics are performance measurements for the organization. Business analysts inevitably want to scrutinize and evaluate these metrics by a seemingly limitless combination of filters and constraints. As dimensional modelers, it’s our job to present these metrics in an easy-to understand structure that responds quickly to unpredictable inquiries.
When identifying the business process for dimensional modeling, some common characteristics and patterns often emerge.
- Business processes are typically supported by an operational system. For example, the billing business process is supported by a billing system; likewise for the purchasing, ordering, or receiving business processes.
- Business processes generate or collect unique measurements with unique granularity and dimensionality used to gauge organizational performance. Sometimes the metrics are a direct result from the business process. Other times, the measurements are derivations. Regardless, the business processes deliver the performance metrics used by various analytic processes. For example, the sales ordering business process supports numerous reports and analytics, such as customer analysis, sales rep performance, and so on.
- Business processes are frequently expressed as action verbs with the associated dimensions as nouns describing the who, what, where, when, why and how related to the process. For example, the billing business process results will be sliced-and-diced and analyzed by date, customer, service/product, and so on.
- Business processes are usually triggered by an input and result in output that needs to be monitored. For example, an accepted proposal is input to the ordering process which results in a sales order and its associated metrics. In this scenario, the business process is sales ordering; you’ll have an orders fact table with the sales order as a potential degenerate dimension and the order amounts and counts as facts. Try to envision the general flow from input into a business process, resulting in output metrics. In most organizations, there’s a series of business processes where outputs from one process become inputs to the next. In the parlance of a dimensional modeler, these business processes will result in a series of fact tables.
- Analysts sometimes want to drill across business processes, looking at the result of one process alongside the results of another. Drilling across processes is certainly viable if the dimensions common to both processes are conformed.
Determining your organization’s core business processes is critical to establishing your overall framework of dimensional models. The easiest way to determine these processes is by listening to the business users. Which processes generate the performance metrics they’re most interested in monitoring? At the same time, the data warehouse team should be assessing the realities of the source environment to deliver the data coveted by the business.
One final comment… It should go without saying that the ever-popular dashboard is NOT a business process; it presents the performance results of numerous individual business processes.