One of the most prevalent fallacies in our industry is that data marts are defined by business department. We’ve seen countless data warehouse architecture diagrams with boxes labeled “Marketing Data Mart,” “Sales Data Mart,” and “Finance Data Mart.” After reviewing business requirements from these departments, you’d inevitably learn that all three organizations want the same core information, such as orders data. Rather than constructing a Marketing data mart that includes orders and a Sales data mart with orders, etc., you should build a single detailed Orders data mart which multiple departments access.
Focusing on business processes, rather than business departments allows you to more economically deliver consistent information throughout the organization. If you establish departmentally-bound marts, you’ll duplicate data. Regardless if the source is an operational system or centralized data warehouse, multiple data flows into the marts invariably result in 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.
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.
So how do you go about identifying the key business processes in your organization? The first step is to listen to your business users. The performance metrics that they clamor to analyze are collected or generated by a business process. As you’re gathering requirements, you 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.
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.”