Dimensions are not just for data warehouses

October 3rd, 2006 by Peter Scott

Conventional wisdom has OLTP people thinking in the third-normal form with master - detail relationships and the like popping up all over the place, and us DWers (does the expression data warehomemaker exist?) working with long tables of fact linked to many denormalised tables representing the dimensions. These dimension tables represent hierarchical knowledge about an entity for example: Customer -> City -> State -> Country. Typical data warehouse dimensions include customers, products, and dates.

But what if we moved the idea of dimensions to something such as the receiving accounts in an ERP system. We could develop a hierarchy that has levels such

  1. company total
  2. expense category (income or expense)
  3. expense type (shipping, labour costs etc)
  4. account

And then if we have a time dimension that aligned to the fiscal calendar and perhaps mix in customers and possibly product then we have the beginnings of a system to provide some pretty flexible management (and perhaps financial) reporting. And if this runs over some OLAP style aggregates or a OLAP cube the performance should not be too bad at all

Posted in BI (General), Dimensional Modelling |

Comments

  1. Sorin Says:

    October 4th, 2006 at 7:55 pm

    That’s the idea of UDM(Unified Dimensional Model I supose) in SQL 2005.

  2. Naresh Says:

    October 7th, 2006 at 8:43 am

    Wouldn’t that be just moving the DW functionality into the Operational DB? Potentially everything in the DW could be moved to the Operational DB ? (Strictly questions - I am not a DW expert).

  3. Pete_S Says:

    October 7th, 2006 at 1:36 pm

    Sorin - indeed yes - but the concept of financal anlyitics has been arround for a long while - Oracle used to market a product called Fiancial Analyzer based on the old Express technology that preceeded the embed OLAP that came out in 9i

    Naresh - good question! Using dimesnional methods to report operational infromation does not always imply that the operational system is the DW - sometimes we need to query across multiple sources, and sometimes we need a longer history than can be held within an OLTP sysem - and if we choose to use an OLAP engine within Oracle or MS SQL server we may well be better off puting the olap cube in another database