Dimension Modelling – Design Tip of the day - Focus on Business process not business departments

Introduction

This article is about how to approach a dimensional modelling and the significance of approaching from a business process perspective rather than from business department perspective.

The Problem

One of the most prevalent practices still in our industry is that data marts are defined by business department. We’ve seen countless data warehouse architecture diagrams with boxes labelled “Marketing Data Mart,” “Sales Data Mart,” and “Finance Data Mart.”

After reviewing business requirements from these departments, we can easily observe the three organizations want the same core information, the orders data.

The Solution

Rather than constructing a Marketing data mart that includes orders and a Sales data mart with orders, etc., we should build a single detailed Orders data mart which multiple departments access.

Focusing on business processes, rather than business departments allows us to more economically deliver consistent information throughout the organization.

If we establish departmentally bound marts, these problems will creep in

•       Duplication of data.
•        Multiple data flows into the marts
•        Data inconsistencies.

The best way to ensure consistency is to publish the data once. A single publishing run also reduces the extract transform-load development effort, on-going data management burden, and disk storage requirements.

How to identify Business Process

So how do we go about identifying the key business processes in our organization? The first step is to listen to our business users. The performance metrics that they clamor to analyze are collected or generated by a business process. As we’re gathering requirements, we should also investigate key operational source systems. In fact, it’s easiest to begin by defining data marts in terms of source systems.

After you’ve identified the data marts based on individual business processes and source systems, then you can focus on marts that integrate data across processes, such as a vendor supply chain, or all the inputs to customer profitability or customer satisfaction. We recommend that you tackle these more complex (albeit highly useful) multi-process marts as a secondary phase.

The Important Tip

Of course, it will come as no surprise to hear that you must use conformed dimensions across the data marts. Likewise, we strongly suggest drafting a Data Warehouse Bus matrix up-front to establish and communicate your overall mart strategy. Just don’t let the rows of your matrix read “Marketing,” “Sales,” and “Finance.”

Conclusion

We understand that it can be tricky to build a process-centric data mart given the usual departmental funding. You can promote the process concept by scrutinizing the unnecessary expense associated with implementing and maintaining the same (or nearly the same) large fact tables in multiple data marts. Even if organizational walls exist, management typically responds to savings opportunities.