Data Modeling with Microsoft Power BI

Chapter 2. Building a Data Model

Traditionally, we speak of Online Transactional Processing (OLTP) databases on one hand and Online Analytical Processing (OLAP) databases on the other. The term online isn’t related to the internet here; it means that you query a database directly instead of triggering and waiting for an asynchronous batch job, which runs in the background—something you might only have seen if you’re my age (or older); asynchronous queries were commonly used until the 1990s (and might still exist in mainframe computers). Transactional means that the purpose of the database is to store real-world events (transactions). This is typical for databases behind any application you can think of, such as the software your bank uses to track the movement of money or the retailer that keeps track of your orders and delivery. Databases for such use cases should avoid redundancy under all circumstances. A change of your name should not require updating only in a single place, rather than necessitating a complicated query to persist the new name through several tables in the database.

This book concentrates on analytical queries in general and on Power BI and Analysis Services in particular. Therefore, when I speak of a data model, I mean data models built for analytical purposes, OLAP databases. For Power BI and Analysis Services, the optimal shape of the data model is the dimensional model. Such databases hold data for the sole purpose of making analytical queries and reports ...