How to handle same code, different definitions depending upon the source system

View previous topic View next topic Go down

How to handle same code, different definitions depending upon the source system

Post  Jeff Smith on Thu Dec 22, 2011 12:48 pm

We have 3 source systems with the exact same design - needed for legal reasons. There are instances in which a The same code value can mean different things depending upon the Source System. Condition Code "A" can mean "Good" in Source 1, and "BAD" in Source 2. I want to put both codes in the same dimension with the Source System ID.

The issue I am having is that I don't like the idea of have Condition Code A mean Good and BAD. I was thinking I should concatenate the Condition Code with the Source System or do something to differentiate the Code A when it means Good and when it means Bad. Technically, there is a hierarchy of Code + Source System up to the Description of the Code.

Do I put the Codes into different columns of the dimension based on Source System? Source 1 Condition Code, Source 2 Condition Code, Sourcwe 3 Condition Code and a single Column for the Description? If I use this method, then I need to make a model change to accommodate the a new source system.

I almost want to hide the Codes and make everyone use the Descriptions.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: How to handle same code, different definitions depending upon the source system

Post  LAndrews on Thu Dec 22, 2011 1:20 pm


My preference is to keep it simple.

Like you said, Code+Source defines the dimension row, the Description is the attribute.

Keep all 3 in the data model, but only expose the description column to the BI Tool.

This should avoid the A=Good and A=Bad situation.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: How to handle same code, different definitions depending upon the source system

Post  ngalemmo on Thu Dec 22, 2011 3:14 pm

As LAndrews suggests, you most definately want to maintain a dimension with source + code as the natural key.

You could also consider adding some additional columns for a standardized code and desciption that could be used at an enterpise level. Somebody would need to sit down and map out a cross reference and you would need to implement some form of governance structure, but if it is important to the business, it may be an effort worth undertaking. You then have the option to expose 'local' and 'enterprise' codes to different audiences as necessary, it could also serve as an enabler to standardize coding across the enterprise.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How to handle same code, different definitions depending upon the source system

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