Data warehousing and OLAP
Research Scholar, JJT University, Rajasthan.
Data warehousing and on-line analytical processing (OLAP) are essential elements of decision support, which has increasingly become a focus of the database industry. Many commercial products and services are now available, and all of the principal database management system vendors now have offerings in these areas. Decision support places some rather different requirements on database technology compared to traditional on-line transaction processing applications. This paper provides an overview of data warehousing and OLAP technologies, with an emphasis on their new requirements. We describe back end tools for extracting, cleaning and loading data into a data warehouse; multidimensional data models typical of OLAP; front end client tools for querying and data analysis; server extensions for efficient query processing; and tools for metadata management and for managing the warehouse. 1. Introduction
In the 1990s, as businesses grew more complex, corporation spread globally, and competition became fiercer, business executives became desperate for information to stay competitive and improve the bottom line. Data warehousing technologies have been successfully deployed in many industries: manufacturing (for order shipment and customer support), retail (for user profiling and inventory management), financial services (for claims analysis, risk analysis, credit card analysis, and fraud detection), transportation (for fleet management), telecommunications (for call analysis and fraud detection), utilities (for power usage analysis), and healthcare (for outcomes analysis). This paper presents a roadmap of data warehousing technologies, focusing on the special requirements that data warehouses place on database management systems (DBMSs). A data warehouse is a “subject-oriented, integrated, time varying, non-volatile collection of data that is used primarily in organizational decision making.” To facilitate complex analyses and visualization, the data in a warehouse is typically modeled multidimensional. For example, in a sales data warehouse, time of sale, sales district, salesperson, and product might be some of the dimensions of interest. Often, these dimensions are hierarchical; time of sale may be organized as a day-month-quarter-year hierarchy, product as a product-category-industry hierarchy. Typical OLAP operations include rollup (increasing the level of aggregation) and drill-down (decreasing the level of aggregation or increasing detail) along one or more dimension hierarchies, slice and dice (selection and projection), and pivot (re-orienting the multidimensional view of data). 2. Architecture and End-to-End Process
Figure 1 shows a typical data warehousing architecture.
Figure :Data Warehousing Architecture
It includes tools for extracting data from multiple operational databases and external sources; for cleaning, transforming and integrating this data; for loading data into the data warehouse; and for periodically refreshing the warehouse to reflect updates at the sources and to purge data from the warehouse, perhaps onto slower archival storage. In addition to the main warehouse, there may be several departmental data marts. Data in the warehouse and data marts is stored and managed by one or more warehouse servers, which present multidimensional views of data to a variety of front end tools: query tools, report writers, analysis tools, and data mining tools. Finally, there is a repository for storing and managing metadata, and tools for monitoring and administering the warehousing system. The warehouse may be distributed for load balancing, scalability, and higher availability.
Designing and rolling out a data warehouse is a complex process, consisting of the following activities.
• Define the architecture, do capacity planning, and select the storage servers, database and OLAP servers, and...
References: 3 Codd, E.F., S.B. Codd, C.T. Salley, “Providing OLAP (On-Line
Analytical Processing) to User Analyst: An IT Mandate.”
Available from Arbor Software’s web site
5 Kimball, R. The Data Warehouse Toolkit. John Wiley, 1996.
6 Wu, M-C., A.P. Buchmann. “Research Issues in Data
Warehousing.” Submitted for publication7 Blakeley, J.A., N. Coburn, P. Larson. “Updating
Please join StudyMode to read the full document