In Design Tip # 69, Identifying Business Processes, Margy discussed the importance of recognizing your organization’s business processes and provided guidelines to spot them. We dive into more details here.
Focusing on business processes is absolutely critical to successfully implement a DW/BI solution using the Kimball Method. Business processes are the fundamental building block of a dimensional data warehouse. We suggest you build your data warehouse iteratively, business process at a time. You may wonder, “What’s so magical about business processes? How does identifying the business process help in our dimensional modeling activities?“. The answer is that correctly identifying the business processes launches the entire dimensional design. The lightly-held secret is that each business process will result in at least one fact table. Essentially, identifying the business processes identifies the fact tables to be built.
It’s not unusual for a single business process to result in more than one fact table. This occurs most frequently when the process involves heterogeneous products, also know as super types and sub types. In this case, several similar but separate fact tables will be spawned. Health care provides a good example. The Paid Claims business process may result in three fact tables: professional claims (e.g., doctors’ office visits), institutional claims (e.g., hospital stays) and drug claims.
The consequences of incorrectly identifying the business process are designs that never come to fruition or (worse) a fact table designed at an inappropriate level of detail. Given the volumes we’ve written on the topic, it should go without saying (although you wouldn’t know it by reading some of the recent analyst reports that have crossed our desks) that we advocate implementing data at its most atomic level of detail in your dimensional data warehouse.
A good test of whether you have accurately identified a business process is to state the fact table’s grain. If you can succinctly state the grain, you are in good shape. On the other hand, if there is confusion regarding the level of detail of a single row in the fact table, you’re not there yet. Most likely you are mixing more than a single business process. You need to step back and look carefully for additional business processes that may be involved.
Listening to your business users’ requirements is the best way to develop an understanding of the business processes. Unfortunately, as the business requirements unfold, they don’t take the shape of business processes as we describe them. The business users typically describe analytic requirements: the types of analysis and decisions they want to make using data in their terms. For example, an actuarial analyst in a health care organization may describe their needs for ratings analysis, utilization trend reporting, and the claim triangles they rely on. But none of these analytic requirements seem to describe a business process. The key is to decode the requirements, decomposing them into the appropriate business processes. This means digging a bit deeper to understand the data and operational systems that support the analytic requirements. Further analysis in our example ultimately shows that all three analytic requirements are served by data from the business process: paid claims.
Sometimes the analytic requirements are more challenging to decode. Often the most valuable analytic requirements described by business users cross multiple business processes. Unfortunately, it’s not always obvious that multiple business processes are involved. The decoding process is more difficult because it requires decomposing the analytic requirements into all of the unique types and sources of data required. In our health care example, underwriting requires a loss ratio analysis to support renewal decisions. Digging further into the details, we can determine the loss ratio analysis compares clients’ premium revenue against their claim expenses to determine the ratio between revenues and expenses. In this case, two business processes are required to support the analytic requirement: paid claims and premium billing.
After reflecting on this design tip you should be able to discern the challenges of creating a corporate dashboard. A dashboard is not a single business process; rather it is a display mechanism for presenting the results of many or most of the business processes in the organization. Unfortunately, the dashboard is usually presented (and accepted) by the DW/BI team as a single analytic requirement.
