SQL views

As described in the Dataset planning section of Chapter 1, Planning Power BI Projects, a set of SQL views should be created within the data source and these SQL views, rather than the database tables, should be accessed by the Power BI dataset. SQL views are essentially virtual tables that provide an abstraction layer from the underlying database tables. SQL views can be used to merge database tables and to limit the number of columns, thus preventing such transformations from occurring within Power Query queries.

Each fact and dimension table required by the Power BI dataset should have its own SQL view and its own M query within the dataset that references this view. The SQL views should preferably be assigned to a dedicated database schema and identify the dimension or fact table represented as shown in Figure 2.11:

Figure 2.11: Views assigned to BI schema in SQL Server

A common practice is to create a database schema specific to the given dataset being...