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