Essay on Data Warehouse and Data

Submitted By Phense
Words: 998
Pages: 4

A relational database optimized for online transactions is designed to automate transaction processing, record keeping, and simple business report transactions (Power, 2000, pg. 8). The data in a relational transaction system usually require a normalized structure with many tables, each containing a minimum of attributes and emphasizing data integrity. Query activity in the relational database tends to be low for additional processing cycles. A data warehouse focuses on querying capabilities and tends to be non-normalized, including few tables which contain large numbers of attributes. The queries in the data warehouse are usually broad in scope and complex. A data warehouse is also inclined to very large amounts of data, which is stored in non-normalized tables housing many redundancies (Coronel, Morris, and Rob, 2013, pg. 552).
A decision support database is a specialized database management system tailored to provide fast answers to complex queries. There are three main requirements for a decision support database, which are the database schema, data extraction and filtering, and the size of the database. The decision support database schema must support complex data representations and must be able to extract multidimensional time slices (Coronel, Morris, and Rob, 2013, pg. 553). The schema must also be optimized for query retrievals. Decision support data is created mainly from extracting data from an operational database and using additional data from external sources, which means the database management system needs to support advanced data extraction and data-filtering tools, the decision support database’s filtering capabilities must include the ability to check for inconsistent data or validation rules. A decision support database is usually very large in size; therefore, the database management system must be capable of supporting very large databases (Coronel, Morris, and Rob, 2013, pg. 554). An operational database consists of system-specific reference data and event data that belongs to a transaction-update system. It is the source of data for the decision support data and data warehouse, containing detailed data used to run day to day operations. The data is continuously changing and being updated (What is Operational Database, 2007). Requirements for operational data include integrated subject oriented data, volatile data, current data, and detailed data. Operational data will usually contain several weeks or months worth of data while decision support data will contain large historical data (Bowman). Operational data is usually stored in a relational database and tends to be heavily normalized and optimized to support daily operations. Operational data represents individual transactions within many tables while decision support data encompasses transactions over time in very few tables (Coronel, Morris, and Rob, 2013, pg. 550).
The U.S. Air force used databases to analyze the impact of various base closure scenarios. The software used a multi-layer, hierarchical filtering process to evaluate the impact of closing the bases. Those that were of minimum strategic, operational, social, and economic impact were placed at the top of the list. This information focused on elements that impacted operational effectiveness, such as alternate airfield availability, weather, and facility capacity. In this example, the committee for closing the bases could easily bring up the information in an orderly manner and make a decision based on the data provided for closing a particular base (Power, 2000, pg. 20). Another example is ShopKo, which created a data warehouse to collect daily statistics on every stock unit in every store. The data collected helps ShopKo in determining the right merchandise to sell in a specific area during a specific time while remaining current with changing demands due to seasons, trends, and many other factors (Power, 2000, pg. 20-21). The last example is of Federal Express, which created a…