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.
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.
