General Ledger Account Number as Dimension Key

View previous topic View next topic Go down

General Ledger Account Number as Dimension Key

Post  VHF on 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

View user profile

Back to top Go down

Re: General Ledger Account Number as Dimension Key

Post  juz_b on 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

View user profile

Back to top Go down

Re: General Ledger Account Number as Dimension Key

Post  BoxesAndLines on Tue Jul 21, 2009 4:45 pm

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

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

View user profile

Back to top Go down

Re: General Ledger Account Number as Dimension Key

Post  ngalemmo on 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...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: General Ledger Account Number as Dimension Key

Post  VHF on 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

View user profile

Back to top Go down

Re: General Ledger Account Number as Dimension Key

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: General Ledger Account Number as Dimension Key

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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