Data warehouse: Difference between revisions
Jump to navigation
Jump to search
imported>Mikael Häggström Linked to the organizations, as the databases are summarized there |
imported>Wootery →Operational databases: 'PhD' is not a last name |
||
| Line 1: | Line 1: | ||
{{Short description|Centralized storage of knowledge}} | {{Short description|Centralized storage of knowledge}} | ||
{{more citations needed |date=February 2026}} | |||
[[File:Data Warehouse & Data-Marts overview.svg|400px|thumb|right|alt=Data warehouse and data marts overview|Data warehouse and [[data mart]] overview, with data marts shown in the top right.]] | [[File:Data Warehouse & Data-Marts overview.svg|400px|thumb|right|alt=Data warehouse and data marts overview|Data warehouse and [[data mart]] overview, with data marts shown in the top right.]] | ||
In [[computing]], a '''data warehouse''' ('''DW''' or '''DWH'''), also known as an '''enterprise data warehouse''' ('''EDW'''), is a system used for [[Business intelligence|reporting]] and [[data analysis]] and is a core component of [[business intelligence]].<ref>{{cite conference|last1=Dedić|first1=Nedim|last2=Stanier|first2=Clare|year=2016|editor1-last=Hammoudi|editor1-first=Slimane|editor2-last=Maciaszek|editor2-first=Leszek|editor3-last=Missikoff|editor3-first=Michele M. Missikoff|editor4-last=Camp|editor4-first=Olivier|editor5-last=Cordeiro|editor5-first=José | In [[computing]], a '''data warehouse''' ('''DW''' or '''DWH'''), also known as an '''enterprise data warehouse''' ('''EDW'''), is a system used for [[Business intelligence|reporting]] and [[data analysis]] and is a core component of [[business intelligence]].<ref>{{cite conference|last1=Dedić|first1=Nedim|last2=Stanier|first2=Clare|title=Proceedings of the 18th International Conference on Enterprise Information Systems |chapter=An Evaluation of the Challenges of Multilingualism in Data Warehouse Development |year=2016|editor1-last=Hammoudi|editor1-first=Slimane|editor2-last=Maciaszek|editor2-first=Leszek|editor3-last=Missikoff|editor3-first=Michele M. Missikoff|editor4-last=Camp|editor4-first=Olivier|editor5-last=Cordeiro|editor5-first=José |url=http://eprints.staffs.ac.uk/2770/ |publisher=SciTePress|volume=1|pages=196–206|conference=International Conference on Enterprise Information Systems, 25–28 April 2016, Rome, Italy|conference-url=https://eprints.staffs.ac.uk/2770/1/ICEIS_2016_Volume_1.pdf |archive-url=https://web.archive.org/web/20180522180940/https://eprints.staffs.ac.uk/2770/1/ICEIS_2016_Volume_1.pdf |archive-date=2018-05-22 |url-status=live|doi=10.5220/0005858401960206|isbn=978-989-758-187-8|doi-access=free}}</ref> Data warehouses are central [[Repository (version control)|repositories]] of data integrated from disparate sources. They store current and historical data organized in a way that is optimized for data analysis, generation of reports, and developing insights across the integrated data.<ref>{{Cite web |title=What is a Data Warehouse? {{!}} Key Concepts {{!}} Amazon Web Services |url=https://aws.amazon.com/data-warehouse/ |access-date=2023-02-13 |website=Amazon Web Services, Inc. |language=en-US}}</ref> They are intended to be used by analysts and managers to help make organizational decisions.<ref name="rainer2012">{{cite book |last1=Rainer |first1=R. Kelly |url=https://archive.org/details/introductiontoin00rain_274 |title=Introduction to Information Systems: Enabling and Transforming Business, 4th Edition |last2=Cegielski |first2=Casey G. |date=2012-05-01 |publisher=Wiley |isbn=978-1118129401 |edition=Kindle |pages=[https://archive.org/details/introductiontoin00rain_274/page/n138 127], 128, 130, 131, 133 |url-access=limited}}</ref> | ||
[[File:Data warehouse architecture.jpg|thumb|upright=1.5|The basic architecture of a data warehouse]] | [[File:Data warehouse architecture.jpg|thumb|upright=1.5|The basic architecture of a data warehouse]] | ||
| Line 21: | Line 22: | ||
=== Operational databases === | === Operational databases === | ||
Operational databases are optimized for the preservation of [[data integrity]] and speed of recording of business transactions through use of [[database normalization]] and an [[entity–relationship model]]. Operational system designers generally follow | Operational databases are optimized for the preservation of [[data integrity]] and speed of recording of business transactions through use of [[database normalization]] and an [[entity–relationship model]].<ref>{{Cite web |last=Halder |first=Nilimesh |date=2024-04-22 |title=Operational Database vs. Data Warehouse: Key Differences and Strategic Insights for Businesses |url=https://levelup.gitconnected.com/operational-database-vs-data-warehouse-key-differences-and-strategic-insights-for-businesses-85318be7ebd5 |access-date=2026-04-16 |website=Medium |language=en}}</ref> Operational system designers generally follow [[database normalization]] to ensure data integrity. Fully normalized database designs often result in information from a business transaction being stored in dozens to hundreds of tables. [[Relational database]]s are efficient at managing the relationships between these tables. The databases have very fast insert/update performance because only a small amount of data in those tables is affected by each transaction. To improve performance, older data are periodically purged. | ||
Data warehouses are optimized for analytic access patterns, which usually involve selecting specific fields rather than all fields as is common in operational databases. Because of these differences in access, operational databases (loosely, OLTP) benefit from the use of a row-oriented database management system (DBMS), whereas analytics databases (loosely, OLAP) benefit from the use of a [[column-oriented DBMS]]. Operational systems maintain a snapshot of the business, while warehouses maintain historic data through ETL processes that periodically migrate data from the operational systems to the warehouse. | Data warehouses are optimized for analytic access patterns, which usually involve selecting specific fields rather than all fields as is common in operational databases. Because of these differences in access, operational databases (loosely, OLTP) benefit from the use of a row-oriented database management system (DBMS), whereas analytics databases (loosely, OLAP) benefit from the use of a [[column-oriented DBMS]]. Operational systems maintain a snapshot of the business, while warehouses maintain historic data through ETL processes that periodically migrate data from the operational systems to the warehouse. | ||
[[Online analytical processing]] (OLAP) is characterized by a low rate of transactions and complex queries that involve aggregations. Response time is an effective performance measure of OLAP systems. OLAP applications are widely used for [[Data Mining|data mining]]. OLAP databases store aggregated, historical data in multi-dimensional schemas (usually [[star schema]]s). OLAP systems typically have a data latency of a few hours, while data mart latency is closer to one day. The OLAP approach is used to analyze multidimensional data from multiple sources and perspectives. The three basic operations in OLAP are roll-up (consolidation), drill-down, and slicing & dicing. | [[Online analytical processing]] (OLAP) is characterized by a low rate of transactions and complex queries that involve aggregations.<ref>{{Cite web |date=2020-01-27 |title=Online Transaction Processing (OLTP) and Online Analytic Processing (OLAP) |url=https://www.geeksforgeeks.org/dbms/online-transaction-processing-oltp-and-online-analytic-processing-olap/ |access-date=2026-04-16 |website=GeeksforGeeks |language=en}}</ref> Response time is an effective performance measure of OLAP systems. OLAP applications are widely used for [[Data Mining|data mining]]. OLAP databases store aggregated, historical data in multi-dimensional schemas (usually [[star schema]]s). OLAP systems typically have a data latency of a few hours, while data mart latency is closer to one day. The OLAP approach is used to analyze multidimensional data from multiple sources and perspectives. The three basic operations in OLAP are roll-up (consolidation), drill-down, and slicing & dicing. | ||
[[Online transaction processing]] (OLTP) is characterized by a large numbers of short online transactions (INSERT, UPDATE, DELETE). OLTP systems emphasize fast query processing and maintaining [[data integrity]] in multi-access environments. For OLTP systems, performance is the number of transactions per second. OLTP databases contain detailed and current data. The schema used to store transactional databases is the entity model (usually [[Third normal form|3NF]]).{{citation needed|date=November 2024}} Normalization is the norm for data modeling techniques in this system. | [[Online transaction processing]] (OLTP) is characterized by a large numbers of short online transactions (INSERT, UPDATE, DELETE). OLTP systems emphasize fast query processing and maintaining [[data integrity]] in multi-access environments. For OLTP systems, performance is the number of transactions per second. OLTP databases contain detailed and current data. The schema used to store transactional databases is the entity model (usually [[Third normal form|3NF]]).{{citation needed|date=November 2024}} Normalization is the norm for data modeling techniques in this system. | ||
| Line 32: | Line 33: | ||
=== Data marts === | === Data marts === | ||
A [[data mart]] is a simple data warehouse focused on a single subject or functional area. Hence it draws data from a limited number of sources such as sales, finance or marketing. Data marts are often built and controlled by a single department in an organization. The sources could be internal operational systems, a central data warehouse, or external data.<ref>{{cite web |year=2007 |title=Data Mart Concepts |url=http://docs.oracle.com/html/E10312_01/dm_concepts.htm |publisher=Oracle}}</ref> | A [[data mart]] is a simple data warehouse focused on a single subject or functional area. Hence it draws data from a limited number of sources such as sales, finance or marketing. Data marts are often built and controlled by a single department in an organization. The sources could be internal operational systems, a central data warehouse, or external data.<ref>{{cite web |year=2007 |title=Data Mart Concepts |url=http://docs.oracle.com/html/E10312_01/dm_concepts.htm |publisher=Oracle}}</ref> | ||
{| class="wikitable" | {| class="wikitable" | ||
| Line 53: | Line 54: | ||
| easy | | easy | ||
|- | |- | ||
! style="text-align: left" | | ! style="text-align: left" | Volume of data stored | ||
| larger | | larger | ||
| limited | | limited | ||
| Line 72: | Line 73: | ||
==Benefits== | ==Benefits== | ||
{{prose|section|date=February 2026}} | |||
A data warehouse maintains a copy of information from the source transaction systems. This architectural complexity provides the opportunity to: | A data warehouse maintains a copy of information from the source transaction systems. This architectural complexity provides the opportunity to: | ||
* Integrate data from multiple sources into a single database and data model. More congregation of data to single database so a single query engine can be used to present data in an [[Operational Data Store|operational data store]]. | * Integrate data from multiple sources into a single database and data model. More congregation of data to single database so a single query engine can be used to present data in an [[Operational Data Store|operational data store]]. | ||
| Line 157: | Line 159: | ||
===Bottom-up design=== | ===Bottom-up design=== | ||
In the ''bottom-up'' approach, [[data mart]]s are first created to provide reporting and analytical capabilities for specific [[business process]]es. These data marts can then be integrated to create a comprehensive data warehouse. The data warehouse bus architecture is primarily an implementation of "the bus", a collection of [[Dimension (data warehouse)#Types| | In the ''bottom-up'' approach, [[data mart]]s are first created to provide reporting and analytical capabilities for specific [[business process]]es. These data marts can then be integrated to create a comprehensive data warehouse. The data warehouse bus architecture is primarily an implementation of "the bus", a collection of [[Dimension (data warehouse)#Types|confirmed dimension]]s and [[Facts (data warehouse)#Types|confirmed fact]]s, which are dimensions that are shared (in a specific way) between facts in two or more data marts.<ref>{{Cite web|url=http://decisionworks.com/2003/09/the-bottom-up-misnomer/|title=The Bottom-Up Misnomer - DecisionWorks Consulting|website=DecisionWorks Consulting|date=17 September 2003|language=en-US|access-date=2016-03-06}}</ref> | ||
===Top-down design=== | ===Top-down design=== | ||
| Line 163: | Line 165: | ||
===Hybrid design=== | ===Hybrid design=== | ||
Data warehouses often | Data warehouses often use a [[spoke–hub distribution paradigm]]. [[Legacy system]]s feeding the warehouse often include [[customer relationship management]] and [[enterprise resource planning]], generating large amounts of data. To consolidate these various data models and facilitate the [[extract transform load]] process, data warehouses often make use of an [[operational data store]], the information from which is parsed into the actual data warehouse. To reduce data redundancy, larger systems often store the data in a normalized way. Data marts for specific reports can then be built on top of the data warehouse. | ||
A hybrid (also called ensemble) data warehouse database is kept on [[third normal form]] to eliminate [[data redundancy]]. A normal relational database, however, is not efficient for business intelligence reports where dimensional modelling is prevalent. Small data marts can shop for data from the consolidated warehouse and use the filtered, specific data for the fact tables and dimensions required. The data warehouse provides a single source of information from which the data marts can read, providing a wide range of business information. The hybrid architecture allows a data warehouse to be replaced with a [[master data management]] repository where operational (not static) information could reside. | A hybrid (also called ensemble) data warehouse database is kept on [[third normal form]] to eliminate [[data redundancy]]. A normal relational database, however, is not efficient for business intelligence reports where dimensional modelling is prevalent. Small data marts can shop for data from the consolidated warehouse and use the filtered, specific data for the fact tables and dimensions required. The data warehouse provides a single source of information from which the data marts can read, providing a wide range of business information. The hybrid architecture allows a data warehouse to be replaced with a [[master data management]] repository where operational (not static) information could reside. | ||
| Line 217: | Line 219: | ||
| '''[[Patient-Centered Outcomes Research Institute|PCOR]]net'''<ref name=Fayanju2025/> || [[Patient-Centered Outcomes Research Institute]] (PCORI) || United States || 140 million patients || Free for participating organizations | | '''[[Patient-Centered Outcomes Research Institute|PCOR]]net'''<ref name=Fayanju2025/> || [[Patient-Centered Outcomes Research Institute]] (PCORI) || United States || 140 million patients || Free for participating organizations | ||
|- | |- | ||
| '''OLDW''' (OptumLabs Data Warehouse) || [[Optum]] || United States || 160<ref>{{cite web|url=https://data.ucsf.edu/research/oldw|website=University of California San Francisco|title=OptumLabs Data Warehouse (OLDW)|accessdate=2025-04-13}}</ref> million patients || For a fee, or for free through certain academic institutions<ref>{{cite web|url=https://www.medschool.umaryland.edu/cibr/core/optumlabs/ | | '''OLDW''' (OptumLabs Data Warehouse) || [[Optum]] || United States || 160<ref>{{cite web|url=https://data.ucsf.edu/research/oldw|website=University of California San Francisco|title=OptumLabs Data Warehouse (OLDW)|accessdate=2025-04-13}}</ref> million patients || For a fee, or for free through certain academic institutions<ref>{{cite web|url=https://www.medschool.umaryland.edu/cibr/core/optumlabs/|title=Optum Labs|website=University of Maryland|accessdate=2025-04-13}}</ref> | ||
|- | |- | ||
| '''EHDEN'''<ref>{{cite journal |vauthors=Voss EA, Blacketer C, van Sandijk S, Moinat M, Kallfelz M, van Speybroeck M, Prieto-Alhambra D, Schuemie MJ, Rijnbeek PR |title=European Health Data & Evidence Network-learnings from building out a standardized international health data network |journal=J Am Med Inform Assoc |volume=31 |issue=1 |pages=209–219 |date=December 2023 |pmid=37952118 |pmc=10746315 |doi=10.1093/jamia/ocad214 |url=}}</ref> (European Health Data Evidence Network) || Innovative Health Initiative of the [[European Union]] || [[Europe]] || 133 million patients || Free for discovery. May have fees for secondary use.<ref>{{cite web|url=https://www.european-health-data-space.com/European_Health_Data_Space_Article_42_%28Proposal_3.5.2022%29.html|title=Articles of the European Health Data Space (EHDS), Article 42, Fees|website=The European Health Data Space (EHDS)|accessdate=2025-04-13}}</ref> | | '''EHDEN'''<ref>{{cite journal |vauthors=Voss EA, Blacketer C, van Sandijk S, Moinat M, Kallfelz M, van Speybroeck M, Prieto-Alhambra D, Schuemie MJ, Rijnbeek PR |title=European Health Data & Evidence Network-learnings from building out a standardized international health data network |journal=J Am Med Inform Assoc |volume=31 |issue=1 |pages=209–219 |date=December 2023 |pmid=37952118 |pmc=10746315 |doi=10.1093/jamia/ocad214 |url=}}</ref> (European Health Data Evidence Network) || Innovative Health Initiative of the [[European Union]] || [[Europe]] || 133 million patients<ref>{{cite journal | last1=Voss | first1=Erica A | last2=Blacketer | first2=Clair | last3=van Sandijk | first3=Sebastiaan | last4=Moinat | first4=Maxim | last5=Kallfelz | first5=Michael | last6=van Speybroeck | first6=Michel | last7=Prieto-Alhambra | first7=Daniel | last8=Schuemie | first8=Martijn J | last9=Rijnbeek | first9=Peter R | title=European Health Data & Evidence Network—learnings from building out a standardized international health data network | journal=Journal of the American Medical Informatics Association | volume=31 | issue=1 | date=2023-12-22 | issn=1067-5027 | pmid=37952118 | pmc=10746315 | doi=10.1093/jamia/ocad214 | pages=209–219 | url=https://academic.oup.com/jamia/article/31/1/209/7407971 | access-date=2026-01-18}}</ref> || Free for discovery. May have fees for secondary use.<ref>{{cite web|url=https://www.european-health-data-space.com/European_Health_Data_Space_Article_42_%28Proposal_3.5.2022%29.html|title=Articles of the European Health Data Space (EHDS), Article 42, Fees|website=The European Health Data Space (EHDS)|accessdate=2025-04-13}}</ref> | ||
|} | |} | ||
| Line 239: | Line 241: | ||
* Linstedt, Graziano, Hultgren. ''The Business of Data Vault Modeling'' Second Edition (2010) Dan linstedt, {{ISBN|978-1-4357-1914-9}} | * Linstedt, Graziano, Hultgren. ''The Business of Data Vault Modeling'' Second Edition (2010) Dan linstedt, {{ISBN|978-1-4357-1914-9}} | ||
* William Inmon. ''Building the Data Warehouse'' (2005) John Wiley and Sons, {{ISBN|978-81-265-0645-3}} | * William Inmon. ''Building the Data Warehouse'' (2005) John Wiley and Sons, {{ISBN|978-81-265-0645-3}} | ||
* Watson, H. (2002). Recent Developments in Data Warehousing. Communications of the Association for Information Systems, 8, pp-pp. https://doi.org/10.17705/1CAIS.00801 | |||
{{data}} | {{data}} | ||