Slowly Changing Dimensions (SCD), dimension data that is slowly and unpredictably updated over time, instead of being updated regularly, are usually an important part of any data warehouse implementation. With SQL Server 2016, Microsoft gave us temporal tables, which lets us automatically keep a history of data changes in a table.

In this article, we are going to explain how to implement a type 2 SCD, with start and end dates in Power BI, using SQL Server temporal tables.

Step 1: Create the temporal table in SQL Server

First of all, we need the data that will be loaded into Power BI. For this example, we create two tables, a system versioned “Product” which is our dimension and a “Sales” table containing our facts. We added one record to the product table for product “Product 1” with a price of 35.45 and added three records to the “Sales” table for that product.

Later, we update the price for “Product 1” from 20.00 to 35.45, and add a new purchase to the “Sales” table.

If we join the two tables taking the dates into account, we would expect to see 7 purchases for the product at a price of 20.00 and one purchase at the price of 35.45. If we load the tables directly into Power BI and join them through the ProductId field, we see that we aren’t achieving the desired effect. We’re getting the data for the dimension from the temporal table, not the history table.

#temporal-tables #analytics #power-bi #power-bi-tutorials #slowly-changing-dimension #data analytic