As shown in the database diagram schema referenced in Chapter 1, Planning Power BI Projects, it's recommended to provide a consolidated or de-normalized dimension for datasets. In the following view (BI.vDim_Product), three product dimension tables are joined and a logical column, Product Category Group, is created to support a common reporting and analysis need:

SELECT
  P.ProductKey as 'Product Key'
, P.ProductAlternateKey as 'Product Alternate Key'
, P.EnglishProductName AS 'Product Name'
, ISNULL(S.EnglishProductSubcategoryName, 'Undefined') 'Product Subcategory'
, ISNULL(C.EnglishProductCategoryName, 'Undefined') AS 'Product Category'
, CASE 
  WHEN C.EnglishProductCategoryName = 'Bikes' THEN 'Bikes' 
  WHEN C.EnglishProductCategoryName IS NULL THEN 'Undefined'
  ELSE 'Non-Bikes'
  END AS 'Product Category Group'
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

In this example, it's necessary to use LEFT JOIN since the product dimension table in the data warehouse allows for null values in the foreign key column (ProductSubcategoryKey). Retrieving the product rows that haven't yet been assigned a subcategory or category is necessary for certain reports that highlight future products. For these products, an ISNULL() function is used to replace null values with an undefined value. Additionally, similar to the Date view, a CASE expression is used to generate a column that groups the product rows into two categories (Bikes and Non-Bikes).