As you know, in many BI reports, what's missing is sometimes more interesting than what in the report. Therefore, in many cases I need to use OUTER JOINS to retrieve all data from the "dimension" table, even if there is no matching row.
I noticed not many are familiar with GROUP BY ALL which might be helpful.
As you can see in this simple example, the product 344 has only one color (silver). But I want to see all other colors in the table (red, yellow …).
SELECT P.Color, sum(unitprice) as SumUnitPrice
FROM dbo.FactInternetSales as F
JOIN dbo.DimProduct as P
ON P.ProductKey = F.ProductKey
WHERE P.ProductKey = 353
GROUP BY ALL P.Color