上QQ阅读APP看书，第一时间看更新

SQL view layer

Create a SQL View for each fact and dimension table to be represented in the data model
- The views should only select the columns required of the model and apply the model's column names

The layer of views protects the model from changes in the source system and provides visibility to administrators of the model's dependency. Additionally, the views can denormalize source tables via joins to conform to the structure of the model tables and potentially include derived columns not available in the source.

In the following SQL example, the product dimension view joins three tables and applies model column names:

, C.EnglishProductCategoryName AS 'Product Category'
, P.ProductAlternateKey AS 'Product Alternate Key'
FROM
DBO.DimProduct AS P
 LEFT JOIN DBO.DimProductSubcategory AS S ON P.ProductSubcategoryKey = S.ProductSubcategoryKey
 LEFT JOIN DBO.DimProductCategory AS C ON S.ProductCategoryKey = C.ProductCategoryKey

Defining the SQL views is especially important if supporting a DirectQuery model. For DirectQuery model views, evaluate the efficiency of the query plans and the referential integrity between the views to support inner join DirectQuery queries.