Choosing which dimension tables to create

  • I have an Account dimension table which contains all the accout numbers.  I also have an AccountType dimension table.

    Question is:  should I instead have an AccountType field in my Account dimension?

    Are there any rules or guidelines around this type of decision?

    Thanks.

  • Yes, typically you would have the account type as an attribute in the Account dimension.

    The main consideration would be if other fact tables need to be associated with the account type directly. In that case it would be better to have a separate dimension. Think about a scenario where you have budgets that are maintained at the account type level and not the individual account level.

    Also, if you find that you have the same attribute in multiple dimensions which could lead to confusion for end users...then it may be a good idea to consider a separate dimension. An example of this would be something like geography (country, state, etc.) which could be part of a customer, employee and store dimensions. In some cases it may make sense to split this into a separate role-playing dimension.

    Hope this helps.

  • Thanks Martin....yes, this does help a lot.

    But an account has other attributes....e.g. Account Status (plus half a dozen others.)

    Assuming I add the AccountType attribute to my Account dimension, should I also add the other Account attributes to the same dimension?  It feels like I've just moved my entire (OLTP) accounts table to the Account dimension, with the exception of a couple of fields in my factAccount.

  • David McKinney wrote:

    Assuming I add the AccountType attribute to my Account dimension, should I also add the other Account attributes to the same dimension?  It feels like I've just moved my entire (OLTP) accounts table to the Account dimension, with the exception of a couple of fields in my factAccount.

    Don't just blindly add everything, only the attributes required for reporting. If that means everything in your current OLTP table, then so be it.

    Also think about derived attributes that could assist with reporting, or tracking changes over time so you can report a status for instance at a specific point in time.

  • Thanks Martin.  Sound advice.  Still feeling my way around OLAP via trial and error, but I'll get there in the end.

    I appreciate your help.

  • We've all been there...keep asking questions 🙂

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply