Creating a Data Warehouse
Data warehouses are the latest buzz in the business world. Not only are they used to store data for reporting and forecasting, but they are part of a decision support system. There are many reasons for creating and using a data warehouse. The data warehouse will support the decisions a business needs to make, usually on a daily basis. The data warehouse collects data, consolidates the data for reporting purposes. Data warehouses are accompanied by analytical tools that accommodate forecasting as part of the decision support system. The purpose of this paper is to explore the creation of a data warehouse. Since the specifics of creating the data warehouse are determined by the database system, this paper will devote its discussion to the design or layout of the data warehouse. Before discussion of the layout of the data ware house proceeds, the basics about a data warehouse need to be discussed. Then the elements of the data ware house will be covered.
What is a Data Warehouse?
A data warehouse is a warehouse full of data, an electronic warehouse. In a manner of speaking this is true. Don Awalt describes it as follows, “A data warehouse is the cohesive data model that defines the central data repository for an organization. “ He also further stated that “we consider it a complete, integrated data model of the enterprise, regardless of how of where the data is stored.” Thus we can see that the data warehouse collects and stores the data for an organization in an organized manner that allows for analytical purposes. These purposes can be for forecasting, predictive analysis or for historical reporting. Many organizations utilize an online transaction processing system (OLTP), while the data warehouse can be used with a tool such as online analytical processing (OLAP) and data mining. Since the purpose of the OLAP differs from the OLTP, the design characteristics of a relational database that support a data warehouse differ from the design characteristics of an OLTP database.
Getting Started in Designing a Data Warehouse
Before starting on the design of a data warehouse, it is extremely important to know the types of data warehouses and the components of the data warehouse. Because the purpose of a data warehouse is to serve users, it is also critical to understand the various types of users, their needs, and how they will interact with the data warehouse.
Data Warehouse Goals
A data warehouse is developed and maintained to serve its users and management. According to the Microsoft SQL Server 200 Resource Kit manual, the typical data warehouse must be designed to satisfy the following requirements: •
“Deliver a great user experience — user acceptance is the measure of success” •
“Function without interfering with OLTP systems”
“Provide a central repository of consistent data” •
“Answer complex queries quickly”
“Provide a variety of powerful analytical tools such as OLAP and data mining” In addition, the manual expounds on these user requirements to observe that the successful data warehouses that satisfy the above requirements have these common characteristics: •
“Are based on a dimensional model”
“Contain historical data”
“Include both detailed and summarized data”
“Consolidate disparate data from multiple sources while retaining consistency” •
“Focus on a single subject such as sales, inventory, or finance”
Types of Data Warehouses
In the book “Decision Support and Business Intelligence Systems, Turban relates that there are three types of data warehouses, a data mart, an operational data store and an enterprise data warehouse. The data mart is usually a smaller data warehouse that is focused on a specific subject. There are two versions of the data mart, the dependent data mart and the independent data mart. This data warehouse is composed of a consistent data model and provides quality data and...
References: Awalt, Don, and Brian Lawton. "Data Warehousing: Back to Basics." SQL Server Magazine Feb. 2000. 1 Feb. 2008 http://www.sqlmag.com/Article/ArticleID/7833/sql_server_7833.html.
"Creating an Oracle Data Warehouse." Oracle. 1 Feb. 2008 .
"Data Warehouse Design Considerations for SQL Server 2000." Microsoft. 1 Feb. 2008 .
"DB2 Database for Linux, UNIX and Windows." IBM. 1 Feb. 2008 .
"How to Create a Data Warehouse Structure." Exforsys, Inc. 1 Feb. 2008 .
Marakas, George M. Decision Support Systems in the 21st Century. 2nd ed. Upper Saddle River, New Jersey: Prentice Hall, 2003.
Turban, Efraim, Jay E. Aronson, Ting-Peng Liang, and Ramesh Sharda. Decision Support and Business Intelligence Systems. 8th ed. Upper Saddle River, New Jersey: Prentice Hall, 2007.
Please join StudyMode to read the full document