Home
Kimball Group Home
Search
Register
Log inODS Data Architecture
2 posters
Page 1 of 1
ODS Data Architecture
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).
rk1234- Posts : 5
Join date : 2014-07-22
Re: ODS Data Architecture
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.
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.
ngalemmo- Posts : 3000
Join date : 2009-05-15
Location : Los Angeles -
Similar topics» data model architecture for economic forecast data
» LA, L0, L1, L2 Data Warehouse Architecture
» steps to design Data Architecture
» Architecture questions : implement historical data view without SCD?
» Data Architecture for Single Source System (Normalised).
» LA, L0, L1, L2 Data Warehouse Architecture
» steps to design Data Architecture
» Architecture questions : implement historical data view without SCD?
» Data Architecture for Single Source System (Normalised).
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum