What is an Operational data store (ODS)?
It is a database which has integrated data from different business or sources with different rules. The data cleansing process applied also. It gets data from the transactional database directly or through staging area.
It will have a limited period of history data, hardly 30 to 90 days of data.
Why does ODS is required?
It will be used for operational monitoring and processing, and for creating simple analysis reports. The performance of processing will be faster since it has only less volume of data compare to data-mart.
Difference between data warehouse and ODS:
Factor
| ODS |
Data mart or data warehouse
|
| Period | It will have limited period of data (30 to 90 days) | It stores history of data |
| Purpose | Operational processing | Forecasting and decision-making |
| Query complexity | SQL query complexity will be less | Query complexity will be high |
| Stage in architecture | It will be built before data warehouse | ODS data will be moved into data warehouse |
Operational data store vs OLTP (Online Transactional Processing):
Factor
| ODS |
OLTP
|
| Period | It will have limited period of data (30 to 90 days) | It has only current data |
| Purpose | Operational processing | For regular online transactions |
| Data | Integrated from different business lines | Only for specific business |
| Normalization | Normalized or de-normalized | Normalized to avoid data redundancy |
Operational data store vs Staging database:
Factor
| ODS |
Staging
|
| Period | It will have limited period of data (30 to 90 days) | Based on type of load it stores incremental data or full volume of data |
| Purpose | Operational processing | Temporary data storage and for doing data cleansing and other calculations |
| Data | Integrated from different business lines | Based on business need, normally the each business line would have dedicated staging |
