Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

General Ledger Account Number as Dimension Key

4 posters

Go down

General Ledger Account Number as Dimension Key Empty General Ledger Account Number as Dimension Key

Post  VHF Tue Jul 21, 2009 3:47 pm

I am adding a General Ledger Account dimension to my DW. It will be populated from the chart of accounts table in our ERP system. Now, all the dimensions (product, customer, etc.) that I have previously designed have a proper surrogate key (SK), and I have never before been inclined to use a business/natural key as a DW dimension key.

However, for the General Ledger Account dimension I am sorely tempted to use the 5-digit account number (which can be stored as a 4-byte integer) as the key. The accounting folks assure me that account numbers never change, and there are no SCD2 attributes for an account number.

Should I use the account number as my dimension key? Is there a benefit to creating a proper SK for a Type1 dimension if the business key is truly unique and immutable?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

General Ledger Account Number as Dimension Key Empty Re: General Ledger Account Number as Dimension Key

Post  juz_b Tue Jul 21, 2009 4:42 pm

I would definitely implement a surrogate key because you'll never know when it'll come back and bite you. Another reason is if your company acquires another company, chances are they will have a whole different set of GL account numbers.

juz_b

Posts : 17
Join date : 2009-02-07

Back to top Go down

General Ledger Account Number as Dimension Key Empty Re: General Ledger Account Number as Dimension Key

Post  BoxesAndLines Tue Jul 21, 2009 4:45 pm

Why risk it? Create a surrogate key and move on.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

General Ledger Account Number as Dimension Key Empty Re: General Ledger Account Number as Dimension Key

Post  ngalemmo Wed Jul 22, 2009 4:34 pm

VHF wrote:The accounting folks assure me that account numbers never change, and there are no SCD2 attributes for an account number.

Should I use the account number as my dimension key? Is there a benefit to creating a proper SK for a Type1 dimension if the business key is truly unique and immutable?

If I could have a dollar every time a business user said 'always' or 'never' which really meant 'usually' or 'rarely'....

Always use surrogate keys... don't be seduced by the dark side...
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

General Ledger Account Number as Dimension Key Empty Re: General Ledger Account Number as Dimension Key

Post  VHF Wed Jul 22, 2009 4:48 pm

In the past when business users have told me that something will "never" happen I have often replied that "'never' is a very long time"!

But in this case I was letting myself be tempted by those nice shinny, red, delicious, unique (at least for now) 4-byte integer business keys!

Don't worry, I'll create proper SKs... thanks everybody for keeping me on the straight and narrow!

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

General Ledger Account Number as Dimension Key Empty Re: General Ledger Account Number as Dimension Key

Post  ngalemmo Wed Jul 22, 2009 10:02 pm

Oh, and by the way, most accounting systems allow alphanumerics for account segments. They may be using numbers now, but it won't be long until they start using letters for things like memo accounts. Always store business keys in character fields in the data warehouse, no matter what the business tells you.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

General Ledger Account Number as Dimension Key Empty Re: General Ledger Account Number as Dimension Key

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum