Different types of data warehouses
In addition to the relational database, an enterprise data warehouse environment often consists of an Extract Transform and Load (ETL) solution, an OLAP engine (hooray Essbase), client analysis tools, and other web or desktop applications that manage the gathering of data and delivering it to business users.
There are three types of data warehouses:
- Enterprise Data Warehouse: An enterprise data warehouse provides a central database for decision support throughout the enterprise. It is recommended that there is only one data warehouse across the enterprise.
- 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. One of the typical applications of the Operational Data Store (ODS) is to hold the recent data before migration to the data warehouse. Typically, the ODS are not conceptually equivalent to the data warehouse albeit do store the data that have a deeper level of the history than that of the OLTP data.
- Data Mart: The data mart is a subset of the data warehouse and it supports a particular region, business unit, or business function. The data mart receives its source data from the data warehouse. There can be many data marts sourcing data from the one data warehouse.
In case you're wondering, here are a few words about an OLAP solution and an OLTP solution. An OLAP solution stands for On-Line Analytical Processing, which in a nutshell means that the data you are using for your analysis is mainly considered reporting or presentation data and any updates or write-backs are solely for analytical purposes. The source data is rarely updated in this method.
The OLTP solution stands for On-Line Transactional Processing which means that the base or source data is directly updated with factual and historical data as an output of the analysis or data entry processes. Conventional straight line reporting can be performed and there is very little, if any, slice-and-dice analysis or what-if scenarios.
Data warehouses and data marts are usually 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 containing many dimensions. A data warehouse and its smaller, more specific data mart provide an opportunity for slicing and dicing that visualize cube along any one of its dimensions.