In previous post you have seen how a star schema and ROLAP approach can produce a flexible and scalable data mart to support business analytics. You are now able to produce in few days or even few hours a data mart to satisfy specific user needs.

But what will happen when you need a second data mart? And a third one? The number of data marts tend to grow as you add new analytic aspects of your business. How can you guarantee integration? The Data Warehouse Bus Architecture (DWBA) approach solves this problem.

The basic principle is: “Plan a Data Warehouse, build a Data Mart!” The strategy is to adopt a set of conformed dimensions for the whole business. Whenever a new fact table (thus data mart) is built, it shall refer to those dimensions.

Initial model

An example will make things clear. Let's suppose that we have completed the data mart of previous post. Our Data Warehouse Bus would look like this:

We have two measures (quantity and amount) for a single data mart (sales) available on three dimensions. Remember that we only use aggregate and semi aggregate measures, so average price is not stored into data mart but always available as quotient of sales amount by sales quantity.

Dimensions (on diagram top) show available levels and how they roll-up: clients roll-up into corporations and account reps roll-up into branch offices. Thus we the following drill-down sequences will be available:

• All Clients » Branch Office » Sales Rep » Client

• All Clients » Corporation Name » Client

• Any Time » Year » Quarter » Month » Day

• Any Time » Fiscal Year » Day

• Any Product » Brand » Product

Notice that in client dimension we have two parallel hierarchies because climbing from bottom level can be done through two distinct paths, one leading to corporation and the other to branch office. The same occurs in time dimension (year and fiscal year) where we could have a third parallel hierarchy (by week).

Now take a look at the cells on diagram body. They indicate the lower level (smallest granularity) at which each measure is available and whether that measure is additive on that dimension (sigma suffix). In this case all measures are available at lower level and all measures are additive on all dimensions, with exception of amount on product dimension that is not additive (remember amount is a semi-additive measure?)

Now, that notation is clear, let's see how DWBA works on a typical enhancement situation.

Expanding initial model

Our sales manager need a new measure: expected sales cost that is obtained by dividing all direct sales budget by amount sold. If, for example, amount sold is 120,000,000 and sales budget is 20,000 expected sales cost would be 1.66%. Accounting can supply monthly sales budget grouped by sales rep and expense category, for example payroll, travel expenses, finder fees, …

Here is new model reflecting new request:

Some notes:

• Budget amount × Client is labeled with "3" sigma. It means that will be available at level 3 (account rep) and not 1 (client) as sales figures.

• For the same reason Budget amount × Time is at level 2 (month).

• There is no cell Budget amount × Product because that dimension will not be used on Budget data mart.
  

Using shared conformed dimensions, you can integrate different data marts with extreme ease since most OLAP technology supports virtual cubes that are composed of other cubes by sharing one or more dimensions.

A spreadsheet is all what you need to design and document DWBA. You can even automate, with minimum effort, data base schema generation and ROLAP configuration (ROLAP schema is used by multidimensional server to map relational model to dimensions, hierarchies and levels).

As usual a good reference book on this subject: Ralph Kimball – The Data Warehouse Lifecycle Toolkit, always available form Amazon where DWBA is described in great detail.