ODS Data Architecture

View previous topic View next topic Go down

ODS Data Architecture

Post  rk1234 on 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).

rk1234

Posts : 5
Join date : 2014-07-22

View user profile

Back to top Go down

Re: ODS Data Architecture

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

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

View user profile http://aginity.com

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