A Realistic Data Warehouse Project: An Integration of Microsoft Access® and Microsoft Excel® Advanced Features and Skills

Abstract

Business intelligence derived from data warehousing and data mining has become one of the most strategic management tools today, providing organizations with long-term competitive advantages. Business school curriculums and popular database textbooks cover data warehousing, but the examples and problem sets typically are small and unrealistic. The purpose of this paper is to provide an overview of how to construct a realistic data warehouse using numerous advanced features available in Microsoft Access and Microsoft Excel. Large fact table creation is demonstrated, which subsequently allows for the development of meaningful queries and cross tab analysis utilizing pivot tables. Fact table sizes of one million records can be iteratively developed and quickly imported into databases such as Microsoft Access or MySQL. A short discussion on the benefits of using Microsoft Access Query by Example and completely bypassing the complexities of advanced SQL is included. With the resulting fact table, students can experiment with several indexing techniques, usually only conceptually discussed in textbooks, and measure a series of index effectiveness. This paper includes a brief discussion of enterprise-level data requirements, the differences between dimensional and relational modeling, data warehouse schemas, and enterprise data flow concepts, along with a demonstration of business modeling concepts, such as random variable generation and probability distributions. As a case example, this data warehouse project utilizes a public retail corporation with an excellent online presence to provide the student with a real data extract, transform and load hands on experience. General financial data and colorful background information about the corporation is provided. [ABSTRACT FROM AUTHOR]