Business Key and Source Id naming conventions

View previous topic View next topic Go down

Business Key and Source Id naming conventions

Post  mru22 on Mon Sep 30, 2013 10:45 am

I am working on a project that has both and ODS and DW that will be fed from the ODS.

The tables in the ODS will have their own identity columns for the primary key.  Most if not all the ODS tables carry the source primary key which is generally named sometablename + id

So when creating the ODS tables is it best to rename the Source PrimaryKey if its name would collide with  the ODS primary key or is it better to leave the sourceId name unchanged? And if so I was wondering what others have generally used ?  Also what happens then if we are moving data then from the ODS to DW?

I was considering the following for the three environments:

OLTP Source    
SampleId                                                                                                                          
                           
ODS destination  
SampleId              int identity
SampleSourceId    (SampleId from OLTP)              



DW destination
SampleId              int Identity
SourceSampleId     (Sample Id from ODS)
                   

This would mean that the source "Whatever" id is always the identity(i.e pk) from the source table.  And if for some reason I needed to trace back from DW to OLTP then I would
have to join sampleSourceId from DW to SampleId from ODS and then SampleSourceId from ODS to SampleId from OLTP.

Is there a more elegant or better way to name the source Id's and PK's  ?

Thanks,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Business Key and Source Id naming conventions

Post  ngalemmo on Mon Sep 30, 2013 1:59 pm

Do what you feel makes sense. However, it's not a good idea to use source surrogate keys as a means of identifying rows in the DW. You should rely on business keys.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Business Key and Source Id naming conventions

Post  mru22 on Mon Sep 30, 2013 2:01 pm



I agree I will rely on business keys I just wanted to know if there was some "best convention" for giving them names vs identity columns.

Thanks,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Business Key and Source Id naming conventions

Post  Mike Honey on Tue Oct 01, 2013 12:45 am

I've been following the convention from the Kimball Group's Dimensional Modeling Workbook, which for your example would look something like:

OLTP Source
SampleId

DW destination (Dim_Sample)
Dim_Sample_SKey int Identity
Dim_Sample_ID (Sample Id from ODS)

The guiding principle (as I understand it) is that the DW defines the names, not the source system. So if the DW designer decides that Dim_Sample would be better named as Dim_Samples, that would result in columns Dim_Samples_SKey and Dim_Samples_ID.

From a technical perspective, I find the underscore word separators more readable and work well with most downstream reporting/cube tools (they automatically present to users with spaces in place of the underscores).

I found your renaming of columns quite confusing (e.g. OLTP SampleId = ODS SampleSourceId = DW SourceSampleId).

Sorry this doesnt provide any specific help on ODS naming. I tend to follow the DW naming where DW attributes exist, and follow the Source system naming where they do not.

Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Business Key and Source Id naming conventions

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