A Data Warehouse is a relational/multidimensional database that is designed for query and analysis rather than transaction processing. A data warehouse usually contains historical data that is derived from transaction data. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources.
In addition to a relational/multidimensional database, a data warehouse environment often consists of an ETL solution, an OLAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.
There are three types of data warehouses:
1. Enterprise Data Warehouse – An enterprise data warehouse provides a central database for decision support throughout the enterprise.
2. ODS (Operational Data Store) – This has a broad enterprise wide scope, but unlike the real Enterprise data warehouse, data is refreshed in near real time and used for routine business activity.
3. Data Mart – Datamart is a subset of data warehouse and it supports a particular region, business unit or business function.
Data warehouses and data marts are built on dimensional data modeling where
fact tables are connected with dimension tables. This is most useful for users to access
data since a database can be visualized as a cube of several dimensions. A data
warehouse provides an opportunity for slicing and dicing that cube along each of
its dimensions.
Data Mart:
A data mart is a subset of data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, data can be derived from an enterprise-wide data warehouse. In an independent data mart, data can be collected directly from sources.
Data Warehouse and Datamarts – Sample Diagram:
