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

ODS Data Architecture

2 posters

Go down

ODS Data Architecture Empty ODS Data Architecture

Post  rk1234 Tue Jul 22, 2014 9:21 am

If we have data that has lots of lookup tables, do we have to maintain Referential Integrity (RI) in ODS as well?.  What are the best practices in ODS  Data Architecture in designing data that have lots of lookup tables (Forigenkey tables).


Posts : 5
Join date : 2014-07-22

Back to top Go down

ODS Data Architecture Empty Re: ODS Data Architecture

Post  ngalemmo Tue Jul 22, 2014 3:22 pm

It seems to me to be a personal choice.  Technically, you should.  And if there isn't a noticeable impact on loads, why not?

On the other hand, do you really need to?  If you data source is clean and reliable (such as coming from an operational database that has already enforced RI on the data), why bother?  If you are assigning surrogate keys to rows in your ODS you are already ensuring RI and do not need to do the same in the database itself.

The thing is, the data warehouse has the advantage that the data being received is in a controlled manner, usually from a controlled source. Transactional environments, on the other hand, need to deal with multiple concurrent transactions with questionable data. Database RI is important in such situations to ensure data quality.

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

Back to top Go down

Back to top

- Similar topics

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