Data Warehousing and Business Intelligence

24 April 2011

Reviewing a Dimensional Model

Filed under: Data Warehousing — Vincent Rainardi @ 8:03 am
Tags:

Sometimes in a data warehousing or BI projects we need to review an existing dimensional model. In this post I will try to write down what to look for when reviewing.

FUNCTIONALITY/BUSINESS

The most important thing to look out when reviewing a dimensional model is the functionality. The technicality is the easy bit. You can read the Kimball book, read all Kimball Group articles, read my blog, and you might consider yourself an expert technically on dimensional modelling. But if you don’t understand insurance, you won’t be able review the dimensional model in a Lloyd’s underwriter. You could be asking lots of basic questions: what is a “line”, what does “to underwrite” mean? During the first month of the project, you will have to spend a lot of hours reviewing the business requirements of the source systems. You need to understand the source systems. That’s the key.

After you understand the source systems, you can ask yourself these questions: (I’m going to use Investment Bank for an example, and abbreviate Dimensional Model with DM)

  1. Pick a business area (e.g. Repo, Credit Risk, FX, Fixed Income). Don’t try to analyse the business in its entirety.
  2. In one area, say Repo, list all the sub areas that need analysis (for example position analysis, exposure analysis, collateral analysis, etc), and check if all of them are covered in the DM. It is possible that you have 6 sub areas for analysis and the dim model only cover 5.
  3. Then pick one sub area (say exposure analysis), ask yourself what are the business questions that need answering when analysing exposure? For example, daily re-pricing/marking-to-market, exposure by counterparty, rating, liquidity analysis, etc. For example: “what is the value of three days’ worth of the underlying security’s turnover?” To manage the liquidity the business needs to know this.
    A good business analyst has experience in Repo in other banks, hence he or she can guide the business user of what to do/expect in the analysis. But what I observe is that the data warehouse architect/designer often don’t understand the business hence he/she can’t design the DM.
  4. Now that you know all the questions, you can check if the DM can be used to answer those questions. Are the missing attributes/measures? Are they at the right grain? Are there a missing fact table? Are there a missing dimension? Is the history captured? Does the fact table contain the all the necessary data? (for example: we need 5 product types but the fact table only contains 4).
    I must emphasise that there is no point going straight to point 4 without doing point 1 to 3.

TECHNICAL

As I said this is the easy bit. Just read Kimball’s DW Toolkit book and you will be able to evaluate the design of the DM. If you are the business user (rather than the dimensional modeller), avoid going into the technical straight away, without discussing the functionalities. You need to sort out the Functionality first. There is no point talking about SCD, grain, etc if the functionality is wrong.

I’ll list down some technical things:

  1. Measured stored in dimensions
  2. Incorrect grain
  3. DD not captured
  4. SCD type 2 over used in all attributes
  5. Incorrect data type
  6. Some data is required but not available
  7. Daily snapshot causing performance issue
  8. Aggregate fact table not defined
  9. Multi Valued Attribute

As I said, Kimball Toolkit book will give you an exhaustive list. You can also refer to my Advanced Dimensional Modelling presentation here to get some ideas.

Advertisements

1 Comment »

  1. Hi Vincent,

    Good blog post…I regularly read your blogs… keep it up….

    Thanks,
    Pravin Patel.
    MS SQL Database Professional
    India.

    Comment by Pravin Patel — 11 May 2011 @ 10:22 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: