data warehousing brief notes, Slides of Data Warehousing

Download data warehousing brief notes and more Slides Data Warehousing in PDF only on Docsity!

Data Warehouse

2

What is a Data

Warehouse?

 (^) Defined in many different ways, but not rigorously. ◦ (^) A decision support database that is maintained separately from the organization’s operational database ◦ (^) Support information processing by providing a solid platform of consolidated, historical data for analysis.  (^) “A data warehouse is a subject-oriented, integrated, time- variant, and nonvolatile collection of data in support of management’s decision-making process.”—W. H. Inmon  (^) Data warehousing: ◦ (^) The process of constructing and using data warehouses

4

Data Warehouse—

Integrated

 (^) Constructed by integrating multiple, heterogeneous data sources ◦ (^) relational databases, flat files, on-line transaction records  (^) Data cleaning and data integration techniques are applied. ◦ (^) Ensure consistency in naming conventions, encoding structures, attribute measures, etc. among different data sources  (^) E.g., Hotel price: currency, tax, breakfast covered, etc. ◦ (^) When data is moved to the warehouse, it is converted.

5

Data Warehouse—Time

Variant

 (^) The time horizon for the data warehouse is significantly longer than that of operational systems ◦ (^) Operational database: current value data ◦ (^) Data warehouse data: provide information from a historical perspective (e.g., past 5-10 years)  (^) Every key structure in the data warehouse ◦ (^) Contains an element of time, explicitly or implicitly ◦ (^) But the key of operational data may or may not contain “time element”

Data Warehouse



An operational database

undergoes frequent changes on a

daily basis on account of the

transactions that take place.



Suppose a business executive

wants to analyze previous

feedback on any data such as a

product, a supplier, or any

consumer data, then the executive

will have no data available to

analyze because the previous data

Data Warehouse

 A data warehouses provides us generalized and consolidated data in multidimensional view.  Along with generalized and consolidated view of data, a data warehouses also provides us Online Analytical Processing (OLAP) tools.  These tools help us in interactive and effective analysis of data in a multidimensional space.

Understanding the Data Warehouse  (^) A data warehouse is a database, which is kept separate from the organization's operational database.  (^) There is no frequent updating done in a data warehouse.  (^) It possesses consolidated historical data, which helps the organization to analyze its business.  (^) A data warehouse helps executives to organize, understand, and use their data to take strategic decisions.  (^) Data warehouse systems help in the integration of diversity of application systems.

Why a Data Warehouse is Separated from Operational Databases  (^) An operational database is constructed for well-known tasks and workloads such as searching particular records, indexing, etc.  (^) Data warehouse queries are often complex and they present a general form of data.  (^) Operational databases support concurrent processing of multiple transactions. Concurrency control and recovery mechanisms are required for operational databases to ensure robustness and consistency of the database  (^) An operational database query allows to read and modify operations, while an OLAP query needs only read only access of stored data.  (^) An operational database maintains current data. On the other hand, a data warehouse

How OLAP works?  Data is collected from multiple sources such as a spreadsheet, video, and online databases.  Data warehouse stores the collected data and cleanses it.  (^) The data is then organized into OLAP cubes.  Each data cube consists of some dimensions.

How OLAP works?  There are three main types of OLAP servers: MOLAP, HOLAP, and ROLAP.  These categories are mainly distinguished by the data storage mode.  MOLAP is a multi-dimensional storage mode, while ROLAP is a relational mode of storage.  HOLAP is a combination of multi- dimensional and relational elements.

MOLAP  The MOLAP engine in the application layer collects data from the databases in the data layer.  It then loads data cubes into the multi- dimensional databases.  (^) When the user makes a query, data will move in a propriety format from the MDDBs to the client desktop in the presentation layer.  (^) This enables users to view data in

ROLAP  In this type of analytical processing, data storage is done in a relational database.  The data is stored as rows and columns.  Data is presented to end-users in a multi-dimensional form  When a user makes a query , the ROLAP server will fetch data from the RDBMS server.  The ROLAP engine will then create data cubes dynamically.  The user will view data from a multi- dimensional point.

Differences between OLAP and OLTP OLTP OLAP Customer Oriented and is used for data transaction Market Oriented used for data analysis It is an online transactional system and manages database modification. It is an online data retrieving and data analysis system Insert, Update, Delete information from the database. Extract data for analyzing that helps in decision making. Real-time transactions of organizations. Data is consolidated from various OLTP databases The processing time of a transaction is comparatively less in OLTP The processing time of a transaction is comparatively more in OLAP

Features of Data Warehouse  Subject Oriented− A data warehouse is subject oriented because it provides information around a subject rather than the organization's ongoing operations. These subjects can be product, customers, suppliers, sales, revenue, etc. A data warehouse does not focus on the ongoing operations, rather it focuses on modelling and analysis of data for decision making  Integrated− A data warehouse is constructed by integrating data from heterogeneous sources such as