The future of any successful enterprise will hinge on its ability to harness and use massive amounts of data currently available, to improve productivity, decrease costs, and increase profits. How organizations store, data is just as relevant. This blog post will define and contrast transactional databases and data warehouses.
A database contains information organized in columns, rows, and tables that is periodically indexed to make accessing relevant information more accessible. Most databases provide aggregated information on customers, product inventory, and sales transactions. Updates and deletions occur when new data is loaded.
While most read, write, and report generation is usually managed by a Database Administrator, some transactional databases provide atomicity, consistency, isolation, and durability (ACID) compliance to ensure that the information contains in the database is consistent and any transactions that take place, are complete.
Touted as the father for data warehousing, William H. Inmon is credited with coining the term data warehouse. According to him, a data warehouse is an integrated collection of data that support organizational decision making.
In more comprehensive terms, a data warehouse is a consolidated view of either a physical or logical data repository collected from various systems. The primary focus of a data warehouse is to provide a correlation between data from existing systems, i.e., product inventory stored in one system purchase orders for a specific customer, stored in another system.
Key Differences
With databases, there is a one-to-one relationship with a single application as its source. A credit card processing application is an excellent example of a single data source that can run on an OLTP database. This type of database contains highly detailed data as well as a detailed relational views. Tables are normalized to achieve efficient storage, concurrent transaction processing, as well as return quick query results.
Data warehouses store summarized historical data from many different applications. There is a one to many relationships between a data warehouses and the applications that serve as data sources. Examples of data sources include but are not limited to customer relationship management (CRM), enterprise resource management (ERP), or even social media data.
Databases use Online Transactional Processing (OLTP) to delete, insert, replace, and update large numbers of short online transactions. Other features include fast query processing, multi-access data integrity, and a number of processed transactions per second. Databases performing OLTP transactions contain and maintain current, and detailed data from a single source. However, due to the number of table joins, performing analytical queries is difficult and requires an experienced database administrator or developer familiar with the application, to write queries that result in any meaningful analysis.
Data warehouses use Online Analytical Processing (OLAP) that handles a low number of complex queries on aggregated historical data. Tables are denormalized and transformed to yield summarized data, multidimensional views, and faster query response times. Additionally, query response times are used to measure an OLAP system’s effectiveness. As a function of business intelligence, OLAP allows managers and analysts to select, extract, view, and analyze corporate data to identify and obtain insights on corporate trends as well as identify potential issues.
Most SLAs for OLTP databases state that they must meet 99.99% uptime. Any system failure may result in lost revenue and lawsuits. Because the database is directly linked to the front end application, real-time data is always available.
SLAs for data warehouses have downtime built in to accommodate periodic uploads of new data. To support scalability, data warehouses are separated from frontend applications.
Databases are transaction oriented and data warehouses are built for analysis. Both are useful and depend on your organization’s needs. If your company processes digital transactions or collects data, you will need a database. However, if or when you need to perform data analysis to discover trends, improve productivity, or uncover issues, a data warehouse becomes an invaluable resource.