Data transformation using T-SQL

With the data now loaded, it is time to transform the data into an aggregate reporting table. In this section, you will create and execute a stored procedure that will create an aggregate table that will be used later in this chapter as the base for a Power BI report.

Let’s look at transforming data using T-SQL:

1. While still in the query editor of the WideWorldImportersDW data warehouse, click on New SQL query from the ribbon.
2. In the query window, enter the following code:

Note

The following code should not be used as an example of best practices. In a real-world scenario, loading a data warehouse will involve generating a surrogate key (warehouse key), updating dimension data based on type 1 and type 2 attributes, handling NULL dimension attributes, looking up surrogate key values when loading a fact table, and more. The following code represents a simplified approach to loading dimension and fact tables to show the general...