Business Intelligence and Data Warehouses
The differences between the structure of data warehouse optimized for processing versus relational database optimized for online transactions and summarizing large amounts of data include the following. Data warehouses use partially denormalized schemas so as to optimize performance, but relational databases have fully normalized schemas used to insert, update and guaranteeing data consistency. Data warehouse is updated regularly using techniques like bulk data modification, but relational database is modified by the end user. It is always up-to date and so it reflects the current state of every transaction. A data warehouse query scans millions of rows, for example, finding total sales for all customers in a month, but relational database allows access of only a handful of records, and an excellent example is when retrieving current order of one customer.
There are differences between database requirements for decision support data, and that for operational data. In decision support, data is the snapshot of operational data at a given point in time but operational data represents the transactions in real time as they happen. Decision support data are stored in few tables that stores data derived from operational data, but operational data are stored in many tables. The stored data represent information about only a given transaction. Operational data is characterized by update transactions but for decision support data is characterized by query transactions, it also requires regular updates to load new data summarized from operational data. Also, in operational data concurrent transaction volume tend to be extraordinarily high compared to decision support data where there is low to medium levels.
Databases can be used to support decision making in large organizations, the following are some of the examples. It can be used, in credit bureaus, to store credit information of all the customers. An outstanding example is the Credit Information Bureau of India Limited. Databases help them in the lending process and credit decision making. Members share credit information of their customers with CIBIL to be updated in CIBIL’s database. This information is used in credit decisions by credit underwriters. Another example is the bank databases which are used in the operations and management of banks. Information available in the banks databases helps the management to make decisions depending on the indicators from their financial records. Another example is the use of databases in National Agricultural Systems. In these cases, there are unusually many employees working under it. The extension managers, at different levels, need to make effective decisions. This is possible by using relevant information in their databases. It will help them in analyzing situations and making effective decisions.
Data mining and data warehousing can be used to support data processing and trend analysis in large organizations. There are some examples which depict this scenario. One example include a situation where a diversified transportation company, which has large direct sales force being able to use data mining in identifying best prospects for the services it offers. Using data mining in analyzing its customer experience, it will help build a unique segmentation thereby identifying attributes of high value prospects, this segmentation yields prioritized prospects by region when applied to a general business database.
Another example is that a large consumer package company can use data mining to improve sales process to its retailers. The data from competitor’s activity, consumer panels and shipments can be used to understand reasons for store and brand switching. Manufacturer can choose promotional strategies through this analysis that can best reach the target customer segments. A third example is that a pharmaceutical company can analyze recent sales activity and their results which will improve targeting of the value physicians and to determine marketing activities that will have the greatest impact in next few months.
References
Cardon D. (2014). Database vs Data Warehouse: A Comparative Review. Retrieved 11th June 11, 2014 from < http://www.healthcatalyst.com/database-vs-data-warehouse-a-comparative-review. >
Golfarelli (2009). An introduction to data warehousing and decision support systems. retrieved 11th June 11, 2014 from <http://searchdatamanagement.techtarget.com/generic/0, 295582, sid91_gci1358911, 00.html >
Rob P. (2009) Database Systems Design, Implementation and Management. Massachusetts Course Technology