Update n2-reporting ETL process

ExampleLoader.sql

DECLARE @fiscalYearQuarterName NVARCHAR(10) = N'2022Q4';
DECLARE @fromBusinessDay DATE;
DECLARE @toBusinessDay DATE;

--
-- prepare switch_staging fact tables for bulk loading
-- N.B. this truncates whole tables so we don't waste time rebuilding indexes, etc. over junk data later
--

EXEC [etl].[DematerializeAggs]
    @factSchemaName = N'switch_staging',
    @factTableName = N'ItemAisledFactlessFacts';

EXEC [etl].[DisableFactConstraints]
    @factSchemaName = N'switch_staging',
    @factTableName = N'ItemAisledFactlessFacts';

EXEC [etl].[TruncateFiscalQuarterFactPartition]
    @fiscalYearQuarterName = NULL,
    @factSchemaName = N'switch_staging',
    @factTableName = N'ItemAisledFactlessFacts';

EXEC [etl].[DematerializeAggs]
    @factSchemaName = N'switch_staging',
    @factTableName = N'MovementFacts';

EXEC [etl].[DisableFactConstraints]
    @factSchemaName = N'switch_staging',
    @factTableName = N'MovementFacts';

EXEC [etl].[TruncateFiscalQuarterFactPartition]
    @fiscalYearQuarterName = NULL,
    @factSchemaName = N'switch_staging',
    @factTableName = N'MovementFacts';

--
-- run the load
--

-- get a cursor over the date range to load
DECLARE myCursor CURSOR FOR
SELECT
    [BusinessDay] AS [FromBusinessDay],
    [BusinessDay] AS [ToBusinessDay]
FROM [dimension].[Date]
WHERE [FiscalYearQuarterName] = @fiscalYearQuarterName
ORDER BY 1, 2;

OPEN myCursor;

FETCH NEXT FROM myCursor INTO @fromBusinessDay, @toBusinessDay;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Create new Location dims as necessary (but don't update IsRowCurrent)
    EXEC [etl].[PopulateLocation]
        @fromBusinessDay = @fromBusinessDay,
        @toBusinessDay = @toBusinessDay,
        @updateCurrent = 0;

    -- Create new Item dims as necessary (but don't update IsRowCurrent)
    EXEC [etl].[PopulateItem]
        @fromBusinessDay = @fromBusinessDay,
        @toBusinessDay = @toBusinessDay,
        @updateCurrent = 0;

    -- Load switch_staging fact tables in append-only mode
    EXEC [etl].[PopulateCategoryAnalysisFacts]
        @fromBusinessDay = @fromBusinessDay,
        @toBusinessDay = @toBusinessDay,
        @factSchemaName = N'switch_staging',
        @appendOnly = 1;

    FETCH NEXT FROM myCursor INTO @fromBusinessDay, @toBusinessDay;
END

CLOSE myCursor;
DEALLOCATE myCursor;

--
-- rebuild switch_staging fact tables and indexed views to match fact schema
--

EXEC [etl].[EnableFactConstraints]
    @factSchemaName = N'switch_staging',
    @factTableName = N'ItemAisledFactlessFacts';

EXEC [etl].[RematerializeAggs]
    @factSchemaName = N'switch_staging',
    @factTableName = N'ItemAisledFactlessFacts';

EXEC [etl].[EnableFactConstraints]
    @factSchemaName = N'switch_staging',
    @factTableName = N'MovementFacts';

EXEC [etl].[RematerializeAggs]
    @factSchemaName = N'switch_staging',
    @factTableName = N'MovementFacts';

--
-- prepare fact_archive fact tables to receive stale partitions
-- N.B. we can truncate these tables because they have no indexed views in this schema
--

EXEC [etl].[TruncateFiscalQuarterFactPartition]
    @factSchemaName = N'fact_archive',
    @factTableName = N'ItemAisledFactlessFacts',
    @fiscalYearQuarterName = @fiscalYearQuarterName;

EXEC [etl].[TruncateFiscalQuarterFactPartition]
    @factSchemaName = N'fact_archive',
    @factTableName = N'MovementFacts',
    @fiscalYearQuarterName = @fiscalYearQuarterName;

--
-- execute three-way partition swaps!
--

-- step 1. switch stale fact partitions out

EXEC [etl].[SwitchFiscalQuarterFactPartition]
    @fromFactSchemaName = N'fact',
    @fromFactTableName = N'ItemAisledFactlessFacts',
    @toFactSchemaName = N'fact_archive',
    @toFactTableName = N'ItemAisledFactlessFacts',
    @fiscalYearQuarterName = @fiscalYearQuarterName;

EXEC [etl].[SwitchFiscalQuarterFactPartition]
    @fromFactSchemaName = N'fact',
    @fromFactTableName = N'MovementFacts',
    @toFactSchemaName = N'fact_archive',
    @toFactTableName = N'MovementFacts',
    @fiscalYearQuarterName = @fiscalYearQuarterName;

-- step 2. switch fresh fact partitions in

EXEC [etl].[SwitchFiscalQuarterFactPartition]
    @fromFactSchemaName = N'switch_staging',
    @fromFactTableName = N'ItemAisledFactlessFacts',
    @toFactSchemaName = N'fact',
    @toFactTableName = N'ItemAisledFactlessFacts',
    @fiscalYearQuarterName = @fiscalYearQuarterName;

EXEC [etl].[SwitchFiscalQuarterFactPartition]
    @fromFactSchemaName = N'switch_staging',
    @fromFactTableName = N'MovementFacts',
    @toFactSchemaName = N'fact',
    @toFactTableName = N'MovementFacts',
    @fiscalYearQuarterName = @fiscalYearQuarterName;

ExampleReportQuery.sql.patch

--- ExampleReportQuery_BaseFacts.sql	2024-12-03 16:36:41
+++ ExampleReportQuery_ScopedFacts.sql	2024-12-03 16:34:19
@@ -15,26 +15,23 @@
     i.CurrentBrand,
     i.CurrentUnitOfMeasure,
     i.CurrentCategoryName,
-    SUM(COALESCE(mf.TotalQuantity, 0)) AS TotalQuantity,
-    SUM(COALESCE(mf.TotalWeight, 0)) AS TotalWeight,
-    SUM(COALESCE(mf.TotalCost, 0)) AS TotalCost,
-    SUM(COALESCE(mf.TotalRetail, 0)) AS TotalRetail,
-    SUM(COALESCE(mf.WeekMargin, 0)) AS WeekMargin
-FROM [fact].[ItemAisledFactlessFacts] AS iaff
-INNER JOIN [dimension].[Date] AS d
-    ON iaff.BusinessDay = d.BusinessDay
-        AND iaff.FiscalQuarterStartDate = d.FiscalQuarterStartDate
-LEFT JOIN [fact].[MovementFacts] AS mf
-    ON iaff.BusinessDay = mf.BusinessDay
-        AND iaff.FiscalQuarterStartDate = mf.FiscalQuarterStartDate
-        AND iaff.ItemKey = mf.ItemKey
-        AND iaff.LocationKey = mf.LocationKey
+    SUM(sf.TotalQuantity) AS TotalQuantity,
+    SUM(sf.TotalWeight) AS TotalWeight,
+    SUM(sf.TotalCost) AS TotalCost,
+    SUM(sf.TotalRetail) AS TotalRetail,
+    SUM(sf.WeekMargin) AS WeekMargin
+FROM [fact].[ScopedFactsView] AS sf
 INNER JOIN [dimension].[Item] AS i
-    ON mf.ItemKey = i.ItemKey
+    ON sf.ItemKey = i.ItemKey
 INNER JOIN [dimension].[Location] AS l
-    ON mf.LocationKey = l.LocationKey
+    ON sf.LocationKey = l.LocationKey
 WHERE 1 = 1
-    AND d.BusinessDay >= @rangeStart AND d.BusinessDay <= @rangeEnd
+    AND sf.Scope = CASE
+        WHEN sf.FiscalPeriodStartDate >= @rangeStart AND sf.FiscalPeriodEndDate <= @rangeEnd THEN 'P'
+        WHEN sf.FiscalWeekStartDate >= @rangeStart AND sf.FiscalWeekEndDate <= @rangeEnd THEN 'W'
+        ELSE 'D'
+    END
+    AND sf.BusinessDay >= @rangeStart AND sf.BusinessDay <= @rangeEnd
     AND l.CurrentName IN (
         'Nugget Market #1',
         'Nugget Market #2',

ExampleReportQuery_BaseFacts.sql

DECLARE @rangeStart DATE = '2023-04-24';
DECLARE @rangeEnd DATE = '2024-04-21';

SELECT
    i.CurrentUpc,
    i.CurrentDescription,
    i.CurrentLongDescription,
    i.CurrentVendorName,
    i.CurrentItemCode,
    i.CurrentPack,
    i.CurrentSize,
    i.CurrentDateAuthorized,
    i.CurrentCutDate,
    i.CurrentFamilyGroupName,
    i.CurrentBrand,
    i.CurrentUnitOfMeasure,
    i.CurrentCategoryName,
    SUM(COALESCE(mf.TotalQuantity, 0)) AS TotalQuantity,
    SUM(COALESCE(mf.TotalWeight, 0)) AS TotalWeight,
    SUM(COALESCE(mf.TotalCost, 0)) AS TotalCost,
    SUM(COALESCE(mf.TotalRetail, 0)) AS TotalRetail,
    SUM(COALESCE(mf.WeekMargin, 0)) AS WeekMargin
FROM [fact].[ItemAisledFactlessFacts] AS iaff
INNER JOIN [dimension].[Date] AS d
    ON iaff.BusinessDay = d.BusinessDay
        AND iaff.FiscalQuarterStartDate = d.FiscalQuarterStartDate
LEFT JOIN [fact].[MovementFacts] AS mf
    ON iaff.BusinessDay = mf.BusinessDay
        AND iaff.FiscalQuarterStartDate = mf.FiscalQuarterStartDate
        AND iaff.ItemKey = mf.ItemKey
        AND iaff.LocationKey = mf.LocationKey
INNER JOIN [dimension].[Item] AS i
    ON mf.ItemKey = i.ItemKey
INNER JOIN [dimension].[Location] AS l
    ON mf.LocationKey = l.LocationKey
WHERE 1 = 1
    AND d.BusinessDay >= @rangeStart AND d.BusinessDay <= @rangeEnd
    AND l.CurrentName IN (
        'Nugget Market #1',
        'Nugget Market #2',
        'Nugget Market #5',
        'Nugget Market #6',
        'Nugget Market #7',
        'Nugget Market #8',
        'Nugget Market #9',
        'Nugget Market #10',
        'Nugget Market #11',
        'Nugget Market #12',
        'Nugget Market #14',
        'Nugget Market #15',
        'Nugget Market #16',
        'Sonoma Market #17',
        'Fork Lift #21',
        'Food 4 Less #30'
    )
    AND i.CurrentDepartmentGroupName IN (
        'GRO TX SPC',
        'GROC SPCL',
        'GROC TAX',
        'GROCERY',
        'GEN MDSE',
        'HBC'
    )
GROUP BY
    i.CurrentUpc,
    i.CurrentDescription,
    i.CurrentLongDescription,
    i.CurrentVendorName,
    i.CurrentItemCode,
    i.CurrentPack,
    i.CurrentSize,
    i.CurrentDateAuthorized,
    i.CurrentCutDate,
    i.CurrentFamilyGroupName,
    i.CurrentBrand,
    i.CurrentUnitOfMeasure,
    i.CurrentCategoryName;

ExampleReportQuery_ScopedFacts.sql

DECLARE @rangeStart DATE = '2023-04-24';
DECLARE @rangeEnd DATE = '2024-04-21';

SELECT
    i.CurrentUpc,
    i.CurrentDescription,
    i.CurrentLongDescription,
    i.CurrentVendorName,
    i.CurrentItemCode,
    i.CurrentPack,
    i.CurrentSize,
    i.CurrentDateAuthorized,
    i.CurrentCutDate,
    i.CurrentFamilyGroupName,
    i.CurrentBrand,
    i.CurrentUnitOfMeasure,
    i.CurrentCategoryName,
    SUM(sf.TotalQuantity) AS TotalQuantity,
    SUM(sf.TotalWeight) AS TotalWeight,
    SUM(sf.TotalCost) AS TotalCost,
    SUM(sf.TotalRetail) AS TotalRetail,
    SUM(sf.WeekMargin) AS WeekMargin
FROM [fact].[ScopedFactsView] AS sf
INNER JOIN [dimension].[Item] AS i
    ON sf.ItemKey = i.ItemKey
INNER JOIN [dimension].[Location] AS l
    ON sf.LocationKey = l.LocationKey
WHERE 1 = 1
    AND sf.Scope = CASE
        WHEN sf.FiscalPeriodStartDate >= @rangeStart AND sf.FiscalPeriodEndDate <= @rangeEnd THEN 'P'
        WHEN sf.FiscalWeekStartDate >= @rangeStart AND sf.FiscalWeekEndDate <= @rangeEnd THEN 'W'
        ELSE 'D'
    END
    AND sf.BusinessDay >= @rangeStart AND sf.BusinessDay <= @rangeEnd
    AND l.CurrentName IN (
        'Nugget Market #1',
        'Nugget Market #2',
        'Nugget Market #5',
        'Nugget Market #6',
        'Nugget Market #7',
        'Nugget Market #8',
        'Nugget Market #9',
        'Nugget Market #10',
        'Nugget Market #11',
        'Nugget Market #12',
        'Nugget Market #14',
        'Nugget Market #15',
        'Nugget Market #16',
        'Sonoma Market #17',
        'Fork Lift #21',
        'Food 4 Less #30'
    )
    AND i.CurrentDepartmentGroupName IN (
        'GRO TX SPC',
        'GROC SPCL',
        'GROC TAX',
        'GROCERY',
        'GEN MDSE',
        'HBC'
    )
GROUP BY
    i.CurrentUpc,
    i.CurrentDescription,
    i.CurrentLongDescription,
    i.CurrentVendorName,
    i.CurrentItemCode,
    i.CurrentPack,
    i.CurrentSize,
    i.CurrentDateAuthorized,
    i.CurrentCutDate,
    i.CurrentFamilyGroupName,
    i.CurrentBrand,
    i.CurrentUnitOfMeasure,
    i.CurrentCategoryName;

ItemAisledFactlessFacts.sql

CREATE TABLE [fact].[ItemAisledFactlessFacts]
(
  [BusinessDay] DATE NOT NULL,
  [FiscalQuarterStartDate] DATE NOT NULL,
  [LocationKey] BIGINT NOT NULL,
  [ItemKey] BIGINT NOT NULL,

  CONSTRAINT [FK_ItemAisledFactlessFacts_BusinessDay] FOREIGN KEY ([BusinessDay]) REFERENCES [dimension].[Date]([BusinessDay]),
  CONSTRAINT [FK_ItemAisledFactlessFacts_LocationKey] FOREIGN KEY ([LocationKey]) REFERENCES [dimension].[Location]([LocationKey]),
  CONSTRAINT [FK_ItemAisledFactlessFacts_ItemKey] FOREIGN KEY ([ItemKey]) REFERENCES [dimension].[Item]([ItemKey]),

  CONSTRAINT [CK_ItemAisledFactlessFacts] UNIQUE NONCLUSTERED
  (
    [ItemKey],
    [LocationKey],
    [BusinessDay],
    [FiscalQuarterStartDate]
  )
  WITH (STATISTICS_NORECOMPUTE = ON),
)
ON [ItemAisledFactlessFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_ItemAisledFactlessFacts]
ON [fact].[ItemAisledFactlessFacts]
ON [ItemAisledFactlessFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_ItemAisledFactlessFacts_BusinessDay]
ON [fact].[ItemAisledFactlessFacts]
(
  [BusinessDay],
  [FiscalQuarterStartDate]
)
INCLUDE
(
  [ItemKey],
  [LocationKey]
)
WITH (STATISTICS_INCREMENTAL = ON, STATISTICS_NORECOMPUTE = ON)
GO

ItemAisledFactlessFactsPeriod.sql

CREATE VIEW [fact].[ItemAisledFactlessFactsPeriod]
    WITH SCHEMABINDING
AS
SELECT
    d.[FiscalPeriodStartDate],
    d.[FiscalPeriodEndDate],
    iaff.[FiscalQuarterStartDate],
    iaff.[LocationKey],
    iaff.[ItemKey],
    COUNT_BIG(*) AS [RowCount]
FROM
    [fact].[ItemAisledFactlessFacts] iaff
    INNER JOIN [dimension].[Date] d
        ON iaff.[BusinessDay] = d.[BusinessDay]
GROUP BY
    iaff.[ItemKey],
    iaff.[LocationKey],
    d.[FiscalPeriodStartDate],
    d.[FiscalPeriodEndDate],
    iaff.[FiscalQuarterStartDate]
GO

CREATE UNIQUE CLUSTERED INDEX [CI_ItemAisledFactlessFactsPeriod]
ON [fact].[ItemAisledFactlessFactsPeriod]
(
    [ItemKey],
    [LocationKey],
    [FiscalPeriodStartDate],
    [FiscalPeriodEndDate],
    [FiscalQuarterStartDate]
)
ON [ItemAisledFactlessFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_ItemAisledFactlessFactsPeriod_FiscalPeriod]
ON [fact].[ItemAisledFactlessFactsPeriod]
(
    [FiscalPeriodStartDate],
    [FiscalPeriodEndDate],
    [FiscalQuarterStartDate]
)
INCLUDE
(
    [ItemKey],
    [LocationKey]
)
GO

ItemAisledFactlessFactsWeek.sql

CREATE VIEW [fact].[ItemAisledFactlessFactsWeek]
    WITH SCHEMABINDING
AS
SELECT
    d.[FiscalWeekStartDate],
    d.[FiscalWeekEndDate],
    iaff.[FiscalQuarterStartDate],
    iaff.[LocationKey],
    iaff.[ItemKey],
    COUNT_BIG(*) AS [RowCount]
FROM
    [fact].[ItemAisledFactlessFacts] iaff
    INNER JOIN [dimension].[Date] d
        ON iaff.[BusinessDay] = d.[BusinessDay]
GROUP BY
    iaff.[ItemKey],
    iaff.[LocationKey],
    d.[FiscalWeekStartDate],
    d.[FiscalWeekEndDate],
    iaff.[FiscalQuarterStartDate]
GO

CREATE UNIQUE CLUSTERED INDEX [CI_ItemAisledFactlessFactsWeek]
ON [fact].[ItemAisledFactlessFactsWeek]
(
    [ItemKey],
    [LocationKey],
    [FiscalWeekStartDate],
    [FiscalWeekEndDate],
    [FiscalQuarterStartDate]
)
ON [ItemAisledFactlessFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_ItemAisledFactlessFactsWeek_FiscalWeek]
ON [fact].[ItemAisledFactlessFactsWeek]
(
    [FiscalWeekStartDate],
    [FiscalWeekEndDate],
    [FiscalQuarterStartDate]
)
INCLUDE
(
    [ItemKey],
    [LocationKey]
)
GO

MovementFacts.sql

CREATE TABLE [fact].[MovementFacts]
(
  [BusinessDay] DATE NOT NULL,
  [FiscalQuarterStartDate] DATE NOT NULL,
  [ItemKey] BIGINT NOT NULL,
  [LocationKey] BIGINT NOT NULL,
  [TotalQuantity] INT NOT NULL,
  [TotalWeight] DECIMAL(12,4) NOT NULL,
  [TotalCost] DECIMAL(12,4) NOT NULL,
  [TotalRetail] DECIMAL(8,2) NOT NULL,
  [WeekMargin] DECIMAL(6,2) NOT NULL, -- Can these actually be sum'd/avg'd/etc?

  CONSTRAINT [FK_MovementFacts_BusinessDay] FOREIGN KEY ([BusinessDay]) REFERENCES [dimension].[Date]([BusinessDay]),
  CONSTRAINT [FK_MovementFacts_ItemKey] FOREIGN KEY ([ItemKey]) REFERENCES [dimension].[Item]([ItemKey]),
  CONSTRAINT [FK_MovementFacts_LocationKey] FOREIGN KEY ([LocationKey]) REFERENCES [dimension].[Location]([LocationKey]),

  CONSTRAINT [CK_MovementFacts] UNIQUE NONCLUSTERED
  (
    [ItemKey],
    [LocationKey],
    [BusinessDay],
    [FiscalQuarterStartDate]
  )
  WITH (STATISTICS_NORECOMPUTE = ON),
)
ON [MovementFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_MovementFacts]
ON [fact].[MovementFacts]
ON [MovementFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_MovementFacts_BusinessDay]
ON [fact].[MovementFacts]
(
  [BusinessDay],
  [FiscalQuarterStartDate]
)
INCLUDE
(
  [ItemKey],
  [LocationKey]
)
WITH (STATISTICS_INCREMENTAL = ON, STATISTICS_NORECOMPUTE = ON)
GO

MovementFactsPeriod.sql

CREATE VIEW [fact].[MovementFactsPeriod]
    WITH SCHEMABINDING
AS
SELECT
    d.[FiscalPeriodStartDate],
    d.[FiscalPeriodEndDate],
    mf.[FiscalQuarterStartDate],
    mf.[ItemKey],
    mf.[LocationKey],
    SUM(mf.[TotalQuantity]) AS [TotalQuantity],
    SUM(mf.[TotalWeight]) AS [TotalWeight],
    SUM(mf.[TotalCost]) AS [TotalCost],
    SUM(mf.[TotalRetail]) AS [TotalRetail],
    SUM(mf.[WeekMargin]) AS [WeekMargin],
    COUNT_BIG(*) AS [RowCount]
FROM
    [fact].[MovementFacts] mf
    INNER JOIN [dimension].[Date] d
        ON mf.[BusinessDay] = d.[BusinessDay]
GROUP BY
    mf.[ItemKey],
    mf.[LocationKey],
    d.[FiscalPeriodStartDate],
    d.[FiscalPeriodEndDate],
    mf.[FiscalQuarterStartDate]
GO

CREATE UNIQUE CLUSTERED INDEX [CI_MovementFactsPeriod]
ON [fact].[MovementFactsPeriod]
(
    [ItemKey],
    [LocationKey],
    [FiscalPeriodStartDate],
    [FiscalPeriodEndDate],
    [FiscalQuarterStartDate]
)
ON [MovementFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_MovementFactsPeriod_FiscalPeriod]
ON [fact].[MovementFactsPeriod]
(
    [FiscalPeriodStartDate],
    [FiscalPeriodEndDate],
    [FiscalQuarterStartDate]
)
INCLUDE
(
    [ItemKey],
    [LocationKey]
)
GO

MovementFactsWeek.sql

CREATE VIEW [fact].[MovementFactsWeek]
    WITH SCHEMABINDING
AS
SELECT
    d.[FiscalWeekStartDate],
    d.[FiscalWeekEndDate],
    mf.[FiscalQuarterStartDate],
    mf.[ItemKey],
    mf.[LocationKey],
    SUM(mf.[TotalQuantity]) AS [TotalQuantity],
    SUM(mf.[TotalWeight]) AS [TotalWeight],
    SUM(mf.[TotalCost]) AS [TotalCost],
    SUM(mf.[TotalRetail]) AS [TotalRetail],
    SUM(mf.[WeekMargin]) AS [WeekMargin],
    COUNT_BIG(*) AS [RowCount]
FROM
    [fact].[MovementFacts] mf
    INNER JOIN [dimension].[Date] d
        ON mf.[BusinessDay] = d.[BusinessDay]
GROUP BY
    mf.[ItemKey],
    mf.[LocationKey],
    d.[FiscalWeekStartDate],
    d.[FiscalWeekEndDate],
    mf.[FiscalQuarterStartDate]
GO

CREATE UNIQUE CLUSTERED INDEX [CI_MovementFactsWeek]
ON [fact].[MovementFactsWeek]
(
    [ItemKey],
    [LocationKey],
    [FiscalWeekStartDate],
    [FiscalWeekEndDate],
    [FiscalQuarterStartDate]
)
ON [MovementFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_MovementFactsWeek_FiscalWeek]
ON [fact].[MovementFactsWeek]
(
    [FiscalWeekStartDate],
    [FiscalWeekEndDate],
    [FiscalQuarterStartDate]
)
INCLUDE
(
    [ItemKey],
    [LocationKey]
)
GO

ScopedFactsView.sql

CREATE VIEW [fact].[ScopedFactsView] AS
SELECT
    'P' AS Scope,
    d.FiscalPeriodStartDate,
    d.FiscalPeriodEndDate,
    d.FiscalWeekStartDate,
    d.FiscalWeekEndDate,
    d.BusinessDay,
    iaff.ItemKey,
    iaff.LocationKey,
    COALESCE(mf.TotalQuantity, 0) AS TotalQuantity,
    COALESCE(mf.TotalWeight, 0) AS TotalWeight,
    COALESCE(mf.TotalCost, 0) AS TotalCost,
    COALESCE(mf.TotalRetail, 0) AS TotalRetail,
    COALESCE(mf.WeekMargin, 0) AS WeekMargin
FROM [fact].[ItemAisledFactlessFactsPeriod] AS iaff WITH (NOEXPAND)
INNER JOIN [dimension].[Date] AS d
    ON iaff.FiscalPeriodStartDate = d.BusinessDay
        AND iaff.FiscalQuarterStartDate = d.FiscalQuarterStartDate
LEFT JOIN [fact].[MovementFactsPeriod] AS mf WITH (NOEXPAND)
    ON iaff.FiscalPeriodStartDate = mf.FiscalPeriodStartDate
        AND iaff.FiscalQuarterStartDate = mf.FiscalQuarterStartDate
        AND iaff.ItemKey = mf.ItemKey
        AND iaff.LocationKey = mf.LocationKey

UNION ALL

SELECT
    'W' AS Scope,
    d.FiscalPeriodStartDate,
    d.FiscalPeriodEndDate,
    d.FiscalWeekStartDate,
    d.FiscalWeekEndDate,
    d.BusinessDay,
    iaff.ItemKey,
    iaff.LocationKey,
    COALESCE(mf.TotalQuantity, 0) AS TotalQuantity,
    COALESCE(mf.TotalWeight, 0) AS TotalWeight,
    COALESCE(mf.TotalCost, 0) AS TotalCost,
    COALESCE(mf.TotalRetail, 0) AS TotalRetail,
    COALESCE(mf.WeekMargin, 0) AS WeekMargin
FROM [fact].[ItemAisledFactlessFactsWeek] AS iaff WITH (NOEXPAND)
INNER JOIN [dimension].[Date] AS d
    ON iaff.FiscalWeekStartDate = d.BusinessDay
        AND iaff.FiscalQuarterStartDate = d.FiscalQuarterStartDate
LEFT JOIN [fact].[MovementFactsWeek] AS mf WITH (NOEXPAND)
    ON iaff.FiscalWeekStartDate = mf.FiscalWeekStartDate
        AND iaff.FiscalQuarterStartDate = mf.FiscalQuarterStartDate
        AND iaff.ItemKey = mf.ItemKey
        AND iaff.LocationKey = mf.LocationKey

UNION ALL

SELECT
    'D' AS Scope,
    d.FiscalPeriodStartDate,
    d.FiscalPeriodEndDate,
    d.FiscalWeekStartDate,
    d.FiscalWeekEndDate,
    d.BusinessDay,
    iaff.ItemKey,
    iaff.LocationKey,
    COALESCE(mf.TotalQuantity, 0) AS TotalQuantity,
    COALESCE(mf.TotalWeight, 0) AS TotalWeight,
    COALESCE(mf.TotalCost, 0) AS TotalCost,
    COALESCE(mf.TotalRetail, 0) AS TotalRetail,
    COALESCE(mf.WeekMargin, 0) AS WeekMargin
FROM [fact].[ItemAisledFactlessFacts] AS iaff
INNER JOIN [dimension].[Date] AS d
    ON iaff.BusinessDay = d.BusinessDay
        AND iaff.FiscalQuarterStartDate = d.FiscalQuarterStartDate
LEFT JOIN [fact].[MovementFacts] AS mf
    ON iaff.BusinessDay = mf.BusinessDay
        AND iaff.FiscalQuarterStartDate = mf.FiscalQuarterStartDate
        AND iaff.ItemKey = mf.ItemKey
        AND iaff.LocationKey = mf.LocationKey