SQL views
As described in the Dataset planning section of Chapter 7, Planning Power BI Projects, a set of SQL views should be created within the data source and these objects, rather than the database tables, should be accessed by the Power BI dataset. 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 the following screenshot:
Views assigned to BI schema in SQL ServerÂ
Note
A common practice is to create a database schema specific to the given dataset being created or to the specific set of reports and dashboards required for a project. However, as suggested in the Data Warehouse Bus Matrix section of
Note
Chapter 7, Planning Power BI Projects there shouldn't be multiple versions of dimensions and facts across separate datasets—version control is a...
