Blog

OLTP vs OLAP Modeling: Dimensional Models, Star Schema and Slowly Changing Dimensions

A normalized OLTP schema is designed for writing, a dimensional model for reading: the grain is declared first, measures go into the fact table and context into denormalized dimensions. In SQL Server we build a star schema from Bookstore sales and show why the same report loses sales on the OLTP schema, how Type 1, 2 and 3 slowly changing dimensions affect past reports when a category changes, double counting from mixed grains, and columnstore.