Aggregate measures by dimension attributes

In most data analytics scenarios involving a data warehouse, the process typically revolves around aggregating numeric measures from fact tables based on attributes in dimension tables. Due to the structure of a star or snowflake schema, such aggregation queries depend on JOIN clauses to link fact tables with dimension tables. Also, they use aggregate functions and GROUP BY clauses to define the aggregation hierarchies.

The following SQL query aggregates sales amounts by year and quarter from the FactSales and DimDate tables in a hypothetical data warehouse:

SQLCopy

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;