Data modeling for status-oriented data

First, a discussion is needed on different ways to model data for which status is the determining factor. In the real world, how data is modeled is typically a trade-off between what is best for the Power BI model and what is achievable from a data preparation point of view. Here, we focus on what would be best for the model; if you apply this for your organization, you may find that data preparation efforts weigh in more heavily.

In deciding what the facts in the model will be, you have two basic options:

1. Store the status for each unit of time, usually each day.
2. Store the changes in the status.

The first option may seem the most straightforward. We just store one data point per day, per product:

Figure 2.7.1: Storing the status of inventory

There are, however, a number of issues with this option that you need to take care of if you choose to model this way:

• Most source systems store inventory...