Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 263

an attribute of integer type, which represents the values of the surrogate keys
for the Time dimension:
CREATE PARTITION FUNCTION [PartByYear] (INT)
AS RANGE LEFT FOR VALUES (184, 549, 730);
Here, 184, 549, and 730 are, respectively, the surrogate keys representing
the dates 31/12/1996 , 31/12/1997 ,and 31/12/1998 . These dates are the
boundaries of the partition intervals. RANGE LEFT means that the records
with values less or equal than 184 will belong to the first partition, the ones
greater than 184 and less or equal than 549 to the second, and the records
with values greater than 730 to the third partition.
Once the partition function has been defined, the partition scheme is
created as follows:
CREATE PARTITION SCHEME [SalesPartScheme]
AS PARTITION [PartByYear] ALL to ( [PRIMARY] );
Here, PRIMARY means that the partitions will be stored in the primary
filegroup, that is, the group that contains the startup database information.
Filegroup names can be used instead (can be more than one). ALL indicates
that all partitions will be stored in the primary filegroup.
The Sales fact table is created as a partitioned table as follows:
CREATE TABLE Sales (CustomerKey INT, EmployeeKey INT,
OrderDateKey INT, . . . ) ON SalesPartScheme(OrderDateKey)
The statement ON SalesPartScheme(OrderDateKey) tells that the table will
be partitioned following the SalesPartScheme and the partition function will
have OrderDateKey as argument.
Now we create an indexed view over the Sales table, as explained in
Sect. 7.8.1 . We first create the view:
CREATE VIEW SalesByDateProdEmp WITH SCHEMABINDING AS (
SELECT OrderDateKey, ProductKey, EmployeeKey, COUNT(*) AS Cnt,
SUM(SalesAmount) AS SalesAmount
FROM Sales
GROUP BY OrderDateKey, ProductKey, EmployeeKey )
Finally, we materialize the view:
CREATE UNIQUE CLUSTERED INDEX UCI SalesByDateProdEmp
ON SalesByDateProdEmp (OrderDateKey, ProductKey, EmployeeKey)
ON SalesPartScheme(OrderDateKey)
Since the clustered index was created using the same partition scheme, this
is a partition-aligned indexed view.