Data Warehouse Concepts

What is Data Warehouse?

A data warehouse is collection of historical data; it is subject oriented data, integrated, time variant and non volatile data for decision making process. Following are key points of data warehouse:
· Data warehouse is a relational and multinational database.
· Designed for query and analysis rather than transaction process.
· Data warehouse usually historical data and derived from online transaction data.
· Data Stored in Demoralized form
· Data warehouse environment often consist an ETL solution, OLAP design.
· The data warehouse is nothing more than the union of all the constituent data marts.

Elements of the data warehouse:

Source System: Multiple Sources, Excel, People Soft, RDBMS, Flat File and SAP etc.
Data Staging Area: ETL- Extraction, Transformation and Loading.
Presentation Server: Physical Data Warehouse.
Dimensional Modeling: Alternative of E/R Modeling, designed based on Dimension and Fact table.
Business Process: A business process is usually a set of activities like "order processing" or "customer pipeline management," but business processes can overlap, and certainly the definition of an individual business process will evolve over time.
Data Marts: Designed for particular line of business activity like Sales, Marketing and Finance etc. A data mart is a complete “pie-wedge” of the overall data warehouse pie.
Operation Data Store: ODS is more than the real enterprise data warehouse, data is refreshed in near time and used for routine business activity.
On-Line Analytic Process: Online Analytical Processing is an approach that helps organization to take advantages of DATA. Technically, OLAP cube allows one to analyze data across multiple dimensions by providing multidimensional view of aggregated, grouped data.
Relation OLAP: ROLAP stands for Relational Online Analytical Process that provides multidimensional analysis of data, stored in a Relational database.
Multidimensional OLAP: MOLAP provides the analysis of data stored in a multi-dimensional data cube.
Hybrid OLAP: HOLAP a combination of both ROLAP and MOLAP can provide multidimensional analysis simultaneously of data stored in a multidimensional database and in a relational database
Desktop or Database OLAP: DOLAP provide multidimensional analysis locally in the client machine on the data collected from relational or multidimensional database servers.
End User Application: Application which provides decision making information in targeted format. Like Excel sheet and Graphical etc.
End User Data Access Tool: Client of the data ware house to access and review the data warehouse information.
Metadata: All of the information in the data warehouse environment that is not the actual data itself.