Home > Published Issues > 2015 > Volume 6, No. 4, November 2015 >

Data Warehouse Snowflake Design and Performance Considerations in Business Analytics

Jiangping Wang and Janet L. Kourik
Walker School of Business and Technology, Webster University, St. Louis, Missouri, USA

Abstract—Snowflake is a data warehouse schema design where dimension tables are normalized on top of a star schema design. Snowflake schema is generally not recommended due to its performance overhead in joining the normalized dimension tables. However, the Snowflake schema can be extended in a way to improve performance for business analysis activities. In business analytics paradigm, two distinct environments are complementary and work together to provide effective business analytics. Firstly, the data warehouse environment transforms operational data into information. Secondly, the analytical environment delivers information to end users for further data analysis and decision making. The snowflake schema bridges the gap between the two environments. Snowflake schema facilitates the mapping of wide dimension structures with many dimension attributes to analytical processing hierarchies. The snowflake schema makes navigation along hierarchies easier and supports flexible analysis such as drilldown and rollup. This paper examines the two complementary business intelligence environments, roles played by the snowflake design in mapping from data warehouse to analytics, and performance considerations in snowflake design with case studies.

Index Terms—data warehouse, snowflake design, business intelligence, business analytics

Cite: Jiangping Wang and Janet L. Kourik, "Data Warehouse Snowflake Design and Performance Considerations in Business Analytics," Vol. 6, No. 4, pp. 212-216, November, 2015. doi: 10.12720/jait.6.4.212-216