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 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

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 Chapter 1, Planning Power BI Projects there shouldn't be multiple versions of dimensions and facts across separate datasets—version control is a top long-term deliverable for the BI team. Therefore, a single database schema with a generic name (BI in this example) is recommended.

The existence of SQL view objects declares a dependency to source tables that are visible to the data source owner. In the event that a change to the source tables of a view is needed or planned, the SQL view can be adjusted, thus avoiding any impact to the Power BI dataset, such as a refresh failure or an unexpected change in the data retrieved. As shown in the following SQL Server dialog, a view (BI.vDim_Promotion) is identified as a dependent object of the DimPromotion dimension table:

SQL Server Object Dependencies

For mature data warehouse sources, the simple query logic contained in each SQL view is sufficient to support the needs of the dataset. However, with Power BI (and SSAS Tabular 2017), BI teams can also leverage M functions to further enhance the value of this data.

Table of Contents for SQL views

Create new playlist

Sign In

Sign Up

Table of Contents for
SQL views