Choosing which dimension tables to create

Question

Choosing which dimension tables to create

David McKinney

SSChampion

Points: 10472
More actions
August 24, 2021 at 1:03 pm

#3923469

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.

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

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

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

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.

David McKinney SSChampion Points: 10472 More actions · Answer 2

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.

Martin Schoombee SSCoach Points: 19116 More actions · Answer 3

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.

David McKinney SSChampion Points: 10472 More actions · Answer 4

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.

Martin Schoombee SSCoach Points: 19116 More actions · Answer 5

Martin Schoombee

SSCoach

Points: 19116

August 27, 2021 at 12:36 pm

#3924485

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

MMartin1 One Orange Chip Points: 27771 More actions · Answer 6

With OLAP your dimension tables are typically wide and short compared to your fact tables (tall and narrow). Typically we add everything to the dimension if the attribute is a property of that attribute key. Account type is a property of an account. If the same account type applied to more than one dimension (as Martin explained with his example on geography), then you can split out the account type to its own dimension. This is referred to as snowflaking.

----------------------------------------------------