Difference bewteen OLTP and Data warehousing OLAP part-3 - ETL- iNFORMATICA DEVELOPER

Sunday, May 19, 2019

Difference bewteen OLTP and Data warehousing OLAP part-3


  • A DBMS built for online transaction processing (OLTP) is generally regarded as unsuitable for data warehousing because each system is designed with a differing set of requirements in mind.
  • Example: OLTP systems are design to maximize the transaction processing capacity, while data warehouses are designed to support ad hoc query processing.

Comparision of OLTP systems and DWH- OLAP

OLTP systems
Data warehousing OLAP systems
Hold current data
Stores detailed data
Data is dynamic
High level of transaction throughput
Supports day-to-day decisions
Serves large number of clerical/operation users
Holds historical data
Stores detailed, lightly, and highly summarized data
Data is largely static
Medium to how level of transaction throughput
Analysis driven
supports strategic decisions
Serves relatively large number of managerial users

Data warehouse architecture

Staging Area

  • Staging Area: Any data store that is designed primarily to receive data into a warehousing environment.                                                                                                                                                    
  • The staging area is a landing pad for data to be ultimately updated into the ODS or appended into a data warehouse. The staging area is usually a temporary holding area for data that needs to be consolidated, synchronized, examined for errors or inconsistencies, or requires further processing before it is accepted into the ODS or data warehouse.                                                        
  • Staging is the "programmer’s playground" used for decoding, integration, and consolidation of data before it becomes usable in either the ODS or the data warehouse.

Operational Data Store

  • Operational Data Store - ODS comes between staging area & Data Warehouse. It is also a small DWH which will help analyst to analysis the business. It will have data for less number of days. generally it will be around 30-45 days and is available on Line.                                                             
  • The ODS is a volatile store of integrated and consolidated data that is subject oriented and used for tactical day-to-day business. The ODS has no history, at least not history as we see it in the data warehouse. Activity dates are used in the ODS and should be used to understand the tracking of an object over time. Time, however, is usually not part of the primary key in the ODS—each record is current. The ODS can be UPDATED multiple times a day and may even be synchronous with the operational systems, if the technology and integration/consolidation processes permit.                                                                                                                                               
  • The ODS is a reportable and auditable store of data created to meet tactical business requirements.                                                                                                                                                                   
Data Mart  

  • Data Mart : Data Warehouse is at an enterprise level repository, which is having a combination of various data marts. Data mart is a limited set of dimensions and measures used for specific business theme. They are populated out of the Data-Warehouse Data Sets.                                        
  • Typically an organization's business intelligence agenda starts with few data marts, before           maturing to a full-blown data warehouse. However, most of the design & development
     concept apply equally to a Data-mart 

No comments:

Post a Comment