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.
