select concat(c.FirstName,' ',c.LastName)
CustomerName,subtotal.TotalAmount
from
DimCustomer c left outer join
(
select p.EnglishProductName,
s.CustomerKey,sum(s.SalesAmount)
TotalAmount
from
DimProduct p join FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
)
subtotal
on c.CustomerKey=subtotal.CustomerKey
order by CustomerName
OPTION (MAXDOP 1);
(58922 row(s)
affected)
Table 'Worktable'.
Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'.
Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCustomer'.
Scan count 1, logical reads 975, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table
'FactInternetSales'. Scan count 1, logical reads 1234, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimProduct'.
Scan count 1, logical
reads 251, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
--Set the
options to support indexed views.
--These
are default SQL Server settings as well
-- But
not default for ODBC/OLEDB
--
https://msdn.microsoft.com/en-sg/library/ms191432.aspx
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
IF OBJECT_ID ('vCustProdSales', 'view') IS NOT NULL
drop view vCustProdSales
GO
CREATE VIEW vCustProdSales
WITH SCHEMABINDING
AS
select p.EnglishProductName,
s.CustomerKey,sum(s.SalesAmount)
TotalAmount, count_big(*) Sales_Count
from dbo.DimProduct p join
dbo.FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
GO
--Create
an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_vCustProdSales
ON
vCustProdSales (EnglishProductName, CustomerKey);
GO
select concat(c.FirstName,' ',c.LastName)
CustomerName,subtotal.TotalAmount
from
DimCustomer c left outer join
(
select p.EnglishProductName,
s.CustomerKey,sum(s.SalesAmount)
TotalAmount
from
DimProduct p join FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
)
subtotal
on c.CustomerKey=subtotal.CustomerKey
order by CustomerName
OPTION (MAXDOP 1);
(58922 row(s)
affected)
Table 'Worktable'.
Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'.
Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCustomer'.
Scan count 1, logical reads 975, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table
'vCustProdSales'. Scan count 1, logical reads 530, physical reads 0, read-ahead reads 3, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
