You are here:

A Realistic Data Warehouse Project: An Integration of Microsoft Access[R] and Microsoft Excel[R] Advanced Features and Skills
ARTICLE

Journal of Information Technology Education Volume 8, ISSN 1547-9714

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. (Contains 3 tables and 10 figures.)

Citation

King, M.A. (2009). A Realistic Data Warehouse Project: An Integration of Microsoft Access[R] and Microsoft Excel[R] Advanced Features and Skills. Journal of Information Technology Education, 8,. Retrieved June 4, 2020 from .

This record was imported from ERIC on April 19, 2013. [Original Record]

ERIC is sponsored by the Institute of Education Sciences (IES) of the U.S. Department of Education.

Copyright for this record is held by the content creator. For more details see ERIC's copyright policy.

Keywords