on target

« ROLAP: Starring your... | Main | Three Online KPI... »

Thursday Oct 22, 2009

Agile Business Intelligence? Take a Bus!

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.

Interested in partnership?

Would like to try or coach this technique with your clients, do you need any additional or technical detail? Please let me know! Component Bases Solutions has great interest in partnership with consultants. We can help you automate your proposed solutions in a very short time. We can also help to increase your visibility through links from many management tools we make freely available on the Web. Please contact for more detail.

Did you like this post?

Please, spend a few seconds and click on button below to socially bookmark it or tweet it. It will help to promote this blog and improve its content, Thank you!