You can optimize queries that aggregate data and perform multiple joins by permanently storing the aggregated and joined data. For example, you could create a new table with joined and aggregated data and then maintain that table during your ETL process.

However, creating additional tables for joined and aggregated data is not best practice because using these tables means you have to change queries used in your reports. Fortunately, there is another option for storing joined and aggregated tables. You can create a view with a query that joins and aggregates data. Then you can create a clustered index on the view to get an indexed view. With indexing, you are materializing a view; you are storing, physically, the data the view is returning when you query it. In the Enterprise or Developer Edition of SQL Server, SQL Server Query Optimizer uses an indexed view automatically, without changing the query. SQL Server also maintains indexed views automatically. However, to speed up data loads, you can drop or disable the index before the load and then recreate or rebuild it after the load.

For example, note the following query, which aggregates the data from the test fact table:

SET STATISTICS IO ON; 
SELECT StockItemKey,  
 SUM(TotalAmount) AS Sales, 
 COUNT_BIG(*) AS NumberOfRows 
FROM dbo.FactTest 
GROUP BY StockItemKey; 
SET STATISTICS IO OFF; 

In my case, this query used 6,685 logical IOs. It used the Clustered Index Scan operator on the fact table to retrieve the whole dataset. Now let's create a view with the same query used for the definition:

CREATE VIEW dbo.SalesByProduct 
WITH SCHEMABINDING AS 
SELECT StockItemKey,  
 SUM(TotalAmount) AS Sales, 
 COUNT_BIG(*) AS NumberOfRows 
FROM dbo.FactTest 
GROUP BY StockItemKey; 
GO 

Indexed views have many limitations. One of them is that they have to be created with the SCHEMABINDING option if you want to index them, as you can see in the previous code. Now let's index the view:

CREATE UNIQUE CLUSTERED INDEX CLU_SalesByProduct 
 ON dbo.SalesByProduct (StockItemKey); 
GO 

Try to execute the last query before creating the VIEW again. Make sure you notice that the query refers to the base table and not the view. This time, the query needed only four logical reads. If you check the execution plan, you should see that SQL Server used the Clustered Index Scan operator on the indexed view. If your indexed view was not used automatically, please check which edition of SQL Server you are using. When you finish testing, you can drop the view with the following code:

DROP VIEW dbo.SalesByProduct; 
GO