Skip to content

Instantly share code, notes, and snippets.

@mwpastore

mwpastore/ExampleLoader.sql

Last active December 3, 2024 22:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mwpastore/f4af94d08e50f90a13e8d821d8b17904 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mwpastore/f4af94d08e50f90a13e8d821d8b17904 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mwpastore revised this gist Dec 3, 2024. 15 changed files with 704 additions and 711 deletions.
    149 changes: 149 additions & 0 deletions ExampleLoader.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,149 @@
    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;
    43 changes: 43 additions & 0 deletions ExampleReportQuery.sql.patch
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,43 @@
    --- 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',
    77 changes: 77 additions & 0 deletions ExampleReportQuery_BaseFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,77 @@
    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;
    74 changes: 74 additions & 0 deletions ExampleReportQuery_ScopedFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@
    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;
    109 changes: 0 additions & 109 deletions Item.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,109 +0,0 @@
    CREATE TABLE [dimension].[Item]
    (
    [ItemKey] BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [SequenceNumber] BIGINT NOT NULL,
    [ItemId] UNIQUEIDENTIFIER NOT NULL,
    [HistoricalUpc] CHAR(13) NOT NULL,
    [HistoricalMfgCode] NVARCHAR(5) NOT NULL,
    [HistoricalType] INT NOT NULL,
    [HistoricalIsWeighed] BIT NOT NULL,
    [HistoricalDescription] NVARCHAR(50) NOT NULL,
    [HistoricalLongDescription] NVARCHAR(50) NOT NULL,
    [HistoricalPack] DECIMAL(8,2) NOT NULL,
    [HistoricalItemCode] NVARCHAR(50) NOT NULL,
    [HistoricalSize] DECIMAL(8,2) NOT NULL,
    [HistoricalUnitOfMeasure] NVARCHAR(2) NOT NULL,
    [HistoricalDateAuthorized] DATE NOT NULL,
    [HistoricalCutDate] DATE NOT NULL,
    [HistoricalBrand] NVARCHAR(MAX) NOT NULL,
    [VendorId] UNIQUEIDENTIFIER NULL,
    [HistoricalVendorName] NVARCHAR(50) NOT NULL,
    [HistoricalVendorShortCode] NVARCHAR(10) NOT NULL,
    [CategoryId] UNIQUEIDENTIFIER NULL,
    [HistoricalCategoryName] NVARCHAR(50) NOT NULL,
    [HistoricalCategoryShortCode] NVARCHAR(10) NOT NULL,
    [HistoricalLeftPaddedCategoryShortCode] NVARCHAR(7) NOT NULL,
    [DepartmentGroupId] UNIQUEIDENTIFIER NULL,
    [HistoricalDepartmentGroupName] NVARCHAR(50) NOT NULL,
    [HistoricalDepartmentGroupShortCode] NVARCHAR(50) NOT NULL,
    [FamilyGroupId] UNIQUEIDENTIFIER NULL,
    [HistoricalFamilyGroupName] NVARCHAR(50) NOT NULL,
    [GlAccountId] UNIQUEIDENTIFIER NULL,
    [HistoricalGlAccountName] NVARCHAR(50) NOT NULL,
    [HistoricalGlAccountShortCode] NVARCHAR(10) NOT NULL,
    [HistoricalHashKey] VARBINARY(20) NOT NULL,
    [CurrentUpc] CHAR(13) NOT NULL,
    [CurrentMfgCode] NVARCHAR(5) NOT NULL,
    [CurrentType] INT NOT NULL,
    [CurrentIsWeighed] BIT NOT NULL,
    [CurrentDescription] NVARCHAR(50) NOT NULL,
    [CurrentLongDescription] NVARCHAR(50) NOT NULL,
    [CurrentPack] DECIMAL(8,2) NOT NULL,
    [CurrentItemCode] NVARCHAR(50) NOT NULL,
    [CurrentSize] DECIMAL(8,2) NOT NULL,
    [CurrentUnitOfMeasure] NVARCHAR(2) NOT NULL,
    [CurrentDateAuthorized] DATE NOT NULL,
    [CurrentCutDate] DATE NOT NULL,
    [CurrentBrand] NVARCHAR(MAX) NOT NULL,
    [CurrentVendorName] NVARCHAR(50) NOT NULL,
    [CurrentVendorShortCode] NVARCHAR(50) NOT NULL,
    [CurrentCategoryName] NVARCHAR(50) NOT NULL,
    [CurrentCategoryShortCode] NVARCHAR(10) NOT NULL,
    [CurrentLeftPaddedCategoryShortCode] NVARCHAR(7) NOT NULL,
    [CurrentDepartmentGroupName] NVARCHAR(50) NOT NULL,
    [CurrentDepartmentGroupShortCode] NVARCHAR(50) NOT NULL,
    [CurrentFamilyGroupName] NVARCHAR(50) NOT NULL,
    [CurrentGlAccountName] NVARCHAR(50) NOT NULL,
    [CurrentGlAccountShortCode] NVARCHAR(10) NOT NULL,
    [IsRowCurrent] BIT NULL,
    )
    GO

    CREATE UNIQUE NONCLUSTERED INDEX [CK_Item_Finder]
    ON [dimension].[Item]
    (
    [HistoricalHashKey],
    [ItemId]
    )
    INCLUDE
    (
    [ItemKey],
    [IsRowCurrent],
    [SequenceNumber]
    )
    GO

    -- For Category Analysis report
    CREATE NONCLUSTERED INDEX [IX_Item_Filter_Upc]
    ON [dimension].[Item]
    (
    [CurrentUpc],
    [CurrentMfgCode]
    )
    -- N.B. We basically need to fetch the whole row after this lookup, so there's
    -- no need to INCLUDE any specific columns in the index
    GO

    CREATE NONCLUSTERED INDEX [IX_Item_Filter_Category]
    ON [dimension].[Item]
    (
    [CurrentCategoryName],
    [CurrentCategoryShortCode]
    )
    GO

    CREATE NONCLUSTERED INDEX [IX_Item_Filter_DepartmentGroup]
    ON [dimension].[Item]
    (
    [CurrentDepartmentGroupName],
    [CurrentDepartmentGroupShortCode]
    )
    GO

    CREATE NONCLUSTERED INDEX [IX_Item_Filter_Vendor]
    ON [dimension].[Item]
    (
    [CurrentVendorName],
    [CurrentVendorShortCode]
    )
    GO
    65 changes: 41 additions & 24 deletions ItemAisledFactlessFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,24 +1,41 @@
    --
    -- any changes here *MUST* be replicated to switch_staging/tables/ItemAisledFactlessFacts
    --
    CREATE TABLE [fact].[ItemAisledFactlessFacts]
    (
    INDEX [CCI_ItemAisledFactlessFacts] CLUSTERED COLUMNSTORE
    ON [ItemAisledFactlessFactsPartitionScheme]([BusinessDay]),

    [BusinessDay] 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
    (
    [BusinessDay],
    [LocationKey],
    [ItemKey]
    ),
    )
    ON [ItemAisledFactlessFactsPartitionScheme]([BusinessDay])
    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
    47 changes: 47 additions & 0 deletions ItemAisledFactlessFactsPeriod.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    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
    47 changes: 47 additions & 0 deletions ItemAisledFactlessFactsWeek.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    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
    75 changes: 46 additions & 29 deletions MovementFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,29 +1,46 @@
    --
    -- any changes here *MUST* be replicated to switch_staging/tables/MovementFacts
    --
    CREATE TABLE [fact].[MovementFacts]
    (
    INDEX [CCI_MovementFacts] CLUSTERED COLUMNSTORE
    ON [MovementFactsPartitionScheme]([BusinessDay]),

    [BusinessDay] 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
    (
    [BusinessDay],
    [ItemKey],
    [LocationKey]
    ),
    )
    ON [MovementFactsPartitionScheme]([BusinessDay])
    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
    52 changes: 52 additions & 0 deletions MovementFactsPeriod.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,52 @@
    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
    52 changes: 52 additions & 0 deletions MovementFactsWeek.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,52 @@
    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
    136 changes: 0 additions & 136 deletions MovementItemAggregateView.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,136 +0,0 @@
    CREATE VIEW [etl].[MovementItemAggregateView] AS
    WITH Base AS (
    SELECT
    mia.[BusinessDay],
    mia.[IntId],

    mia.[Item_id] AS ItemId,
    mia.[Upc],
    COALESCE(mia.[ItemType], -1) AS ItemType,
    COALESCE(i.[Description], 'N/A') AS [Description],
    COALESCE(i.[LongDescription], 'N/A') AS LongDescription,
    COALESCE(mia.[Pack], -1) AS Pack,
    COALESCE(mia.[VItem], 'QQ') AS ItemCode,
    COALESCE(mia.[Size], -1) AS [Size],
    COALESCE(mia.[UnitOfMeasureShortCode], 'QQ') AS UnitOfMeasure,
    COALESCE(mia.[DateAuthorized], i.[DateAuthorized], '1900-01-01') AS DateAuthorized,
    COALESCE(mia.[CutDate], i.[CutDate], '9999-12-31') AS CutDate,
    COALESCE(i.[Brand], 'N/A') AS Brand,

    mia.[VendorId],
    COALESCE(mia.[VendorName], 'N/A') AS VendorName,
    COALESCE(v.[ShortCode], 'N/A') AS VendorShortCode,

    mia.[CategoryId],
    COALESCE(mia.[CategoryName], 'N/A') AS CategoryName,
    COALESCE(mia.[CategoryShortCode], 'N/A') AS CategoryShortCode,

    mia.[DepartmentGroupId],
    COALESCE(dg.[Name], 'N/A') AS DepartmentGroupName,
    COALESCE(dg.[ShortCode], 'N/A') AS DepartmentGroupShortCode,

    mia.[FamilyGroupId],
    COALESCE(fg.[Name], 'N/A') AS FamilyGroupName,

    i.[GlAccount_id] AS GlAccountId,
    COALESCE(gl.[Name], 'N/A') AS GlAccountName,
    COALESCE(gl.[ShortCode], 'N/A') AS GlAccountShortCode,

    mia.[Location_id] AS LocationId,
    COALESCE(mia.[LocationName], 'N/A') AS LocationName,
    COALESCE(mia.[LocationShortCode], 'N/A') AS LocationShortCode,

    mia.[IsAuthorized],

    mia.[TotalQuantity],
    mia.[TotalWeight],
    mia.[TotalCost],
    mia.[TotalRetail],
    mia.[WeekMargin]
    FROM [Host].[dbo].[MovementItemAggregate] AS mia
    --INNER JOIN [Host].[dbo].[Location] AS l
    -- ON l.[Id] = mia.[Location_id]
    INNER JOIN [Host].[dbo].[Item] AS i
    ON i.[Id] = mia.[Item_id]
    --LEFT JOIN [Host].[dbo].[Category] AS c
    -- ON c.[Id] = mia.[CategoryId]
    LEFT JOIN [Host].[dbo].[DepartmentGroup] AS dg
    ON dg.[Id] = mia.[DepartmentGroupId]
    LEFT JOIN [Host].[dbo].[FamilyGroup] AS fg
    ON fg.[Id] = mia.[FamilyGroupId]
    LEFT JOIN [Host].[dbo].[GlAccount] AS gl
    ON gl.[Id] = i.[GlAccount_id]
    LEFT JOIN [Host].[dbo].[Vendor] AS v
    ON v.[Id] = mia.[VendorId]
    )
    SELECT
    [BusinessDay],
    [IntId],
    [ItemId],
    [Upc],
    [ItemType],
    [Description],
    [LongDescription],
    [Pack],
    [ItemCode],
    [Size],
    [UnitOfMeasure],
    [DateAuthorized],
    [CutDate],
    [Brand],
    [VendorId],
    [VendorName],
    [VendorShortCode],
    [CategoryId],
    [CategoryName],
    [CategoryShortCode],
    [DepartmentGroupId],
    [DepartmentGroupName],
    [DepartmentGroupShortCode],
    [FamilyGroupId],
    [FamilyGroupName],
    [GlAccountId],
    [GlAccountName],
    [GlAccountShortCode],
    [LocationId],
    [LocationName],
    [LocationShortCode],
    [IsAuthorized],
    [TotalQuantity],
    [TotalWeight],
    [TotalCost],
    [TotalRetail],
    [WeekMargin],
    CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
    [ItemId], '|',
    [VendorId], '|',
    [CategoryId], '|',
    [DepartmentGroupId], '|',
    [FamilyGroupId], '|',
    [GlAccountId], '|',
    [Upc], '|',
    [ItemType], '|',
    [Description], '|',
    [LongDescription], '|',
    [Pack], '|',
    [ItemCode], '|',
    [Size], '|',
    [UnitOfMeasure], '|',
    [DateAuthorized], '|',
    [CutDate], '|',
    [Brand], '|',
    [VendorName], '|',
    [VendorShortCode], '|',
    [CategoryName], '|',
    [CategoryShortCode], '|',
    [DepartmentGroupName], '|',
    [DepartmentGroupShortCode], '|',
    [FamilyGroupName], '|',
    [GlAccountName], '|',
    [GlAccountShortCode]
    ))) AS ItemHashKey,
    CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
    [LocationName], '|',
    [LocationShortCode]
    ))) AS LocationHashKey
    FROM Base
    162 changes: 0 additions & 162 deletions PopulateCategoryAnalysisFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,162 +0,0 @@
    CREATE PROCEDURE [etl].[PopulateCategoryAnalysisFacts]
    @fromBusinessDay DATE,
    @toBusinessDay DATE = @fromBusinessDay,
    @factSchemaName NVARCHAR(128),
    @appendOnly BIT = 0
    AS
    BEGIN
    -- Build Fact Source
    BEGIN
    -- Load a temp table instead of using a CTE so predicate(s) push
    -- down to the view
    SELECT
    BusinessDay,
    ItemHashKey,
    LocationHashKey,

    CAST(MAX(CAST(IsAuthorized AS INT)) AS BIT) AS IsAuthorized,

    SUM(TotalQuantity) AS TotalQuantity,
    SUM(TotalWeight) AS TotalWeight,
    SUM(TotalCost) AS TotalCost,
    SUM(TotalRetail) AS TotalRetail,
    SUM(WeekMargin) AS WeekMargin
    INTO #IntermediateFactsSource
    FROM [etl].[MovementItemAggregateView]
    WHERE BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay
    GROUP BY BusinessDay, ItemHashKey, LocationHashKey;

    SELECT
    BusinessDay,
    id.ItemKey,
    l.LocationKey,

    CAST(MAX(CAST(IsAuthorized AS INT)) AS BIT) AS IsAuthorized,

    SUM(TotalQuantity) AS TotalQuantity,
    SUM(TotalWeight) AS TotalWeight,
    SUM(TotalCost) AS TotalCost,
    SUM(TotalRetail) AS TotalRetail,
    SUM(WeekMargin) AS WeekMargin,

    CAST(CASE
    WHEN SUM(TotalQuantity) <> 0
    OR SUM(TotalWeight) <> 0
    OR SUM(TotalCost) <> 0
    OR SUM(TotalRetail) <> 0
    OR SUM(WeekMargin) <> 0
    THEN 1
    ELSE 0
    END AS BIT) AS HasMovement
    INTO #FactsSource
    FROM #IntermediateFactsSource
    INNER JOIN [dimension].[Item] AS id
    ON id.[HistoricalHashKey] = ItemHashKey
    INNER JOIN (
    SELECT MAX([LocationKey]) AS LocationKey, [HistoricalHashKey]
    FROM [dimension].[Location]
    GROUP BY [HistoricalHashKey]
    ) AS l
    ON l.[HistoricalHashKey] = LocationHashKey
    GROUP BY
    BusinessDay,
    id.ItemKey,
    l.LocationKey;
    END

    BEGIN -- Add new Movement Facts
    DECLARE @movementFactsSql NVARCHAR(MAX);

    SET @movementFactsSql = N'
    MERGE ' + QUOTENAME(@factSchemaName) + N'.[MovementFacts] WITH (TABLOCK) AS target
    USING (
    SELECT *
    FROM #FactsSource
    WHERE HasMovement = 1
    ) AS source
    ON target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    WHEN MATCHED AND @appendOnly <> 1 THEN
    UPDATE SET
    target.TotalQuantity = source.TotalQuantity,
    target.TotalWeight = source.TotalWeight,
    target.TotalCost = source.TotalCost,
    target.TotalRetail = source.TotalRetail,
    target.WeekMargin = source.WeekMargin
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (BusinessDay, ItemKey, LocationKey,
    TotalQuantity, TotalWeight, TotalCost, TotalRetail, WeekMargin)
    VALUES (source.BusinessDay, source.ItemKey, source.LocationKey,
    source.TotalQuantity, source.TotalWeight, source.TotalCost, source.TotalRetail, source.WeekMargin);
    ';

    EXEC sp_executesql @movementFactsSql, N'@appendOnly BIT', @appendOnly;

    IF @appendOnly <> 1
    BEGIN
    SET @movementFactsSql = N'
    DELETE target WITH (TABLOCK)
    FROM ' + QUOTENAME(@factSchemaName) + N'.[MovementFacts] AS target
    WHERE target.BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay AND NOT EXISTS (
    SELECT 1
    FROM #FactsSource AS source
    WHERE target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    AND source.HasMovement = 1
    );
    ';

    EXEC sp_executesql @movementFactsSql, N'@fromBusinessDay DATE, @toBusinessDay DATE', @fromBusinessDay, @toBusinessDay;
    END
    END

    BEGIN -- Add Factless Facts for the day
    DECLARE @factlessFactsSql NVARCHAR(MAX);

    SET @factlessFactsSql = N'
    INSERT INTO ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] WITH (TABLOCK)
    (
    BusinessDay,
    ItemKey,
    LocationKey
    )
    SELECT
    source.BusinessDay,
    source.ItemKey,
    source.LocationKey
    FROM #FactsSource AS source
    WHERE (IsAuthorized = 1 OR HasMovement = 1)
    AND NOT EXISTS (
    SELECT 1
    FROM ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] AS target
    WHERE target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    );
    ';

    EXEC (@factlessFactsSql);

    IF @appendOnly <> 1
    BEGIN
    SET @factlessFactsSql = N'
    DELETE target WITH (TABLOCK)
    FROM ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] AS target
    WHERE target.BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay AND NOT EXISTS (
    SELECT 1
    FROM #FactsSource AS source
    WHERE target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    AND (source.IsAuthorized = 1 OR source.HasMovement = 1)
    );
    ';

    EXEC sp_executesql @factlessFactsSql, N'@fromBusinessDay DATE, @toBusinessDay DATE', @fromBusinessDay, @toBusinessDay;
    END
    END
    END
    251 changes: 0 additions & 251 deletions PopulateItem.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,251 +0,0 @@
    CREATE PROCEDURE [etl].[PopulateItem]
    @fromBusinessDay DATE,
    @toBusinessDay DATE = @fromBusinessDay,
    @updateCurrent BIT
    AS
    BEGIN
    -- Load a temp table instead of using a CTE so predicate(s) push
    -- down to the view
    SELECT
    [BusinessDay],
    [IntId] AS [SequenceNumber],

    [ItemId],
    [Upc],
    SUBSTRING([Upc], 4, 5) AS [MfgCode],
    [ItemType] AS [Type],
    CASE WHEN [ItemType] IN (2, 3) THEN 1 ELSE 0 END AS [IsWeighed],
    [Description],
    [LongDescription],
    [Pack],
    [ItemCode],
    [Size],
    [UnitOfMeasure],
    [DateAuthorized],
    [CutDate],
    [Brand],

    [VendorId],
    [VendorName],
    [VendorShortCode],

    [CategoryId],
    [CategoryName],
    [CategoryShortCode],
    RIGHT('00000000' + [CategoryShortCode], 7) AS [LeftPaddedCategoryShortCode],

    [DepartmentGroupId],
    [DepartmentGroupName],
    [DepartmentGroupShortCode],

    [FamilyGroupId],
    [FamilyGroupName],

    [GlAccountId],
    [GlAccountName],
    [GlAccountShortCode],

    [ItemHashKey] AS HashKey,
    ROW_NUMBER() OVER (PARTITION BY [ItemHashKey] ORDER BY [IntId] DESC) AS Rank
    INTO #ItemsSource
    FROM [etl].[MovementItemAggregateView]
    WHERE [BusinessDay] BETWEEN @fromBusinessDay AND @toBusinessDay;

    INSERT INTO [dimension].[Item] WITH (TABLOCK)
    (
    [SequenceNumber],
    [HistoricalHashKey],
    [IsRowCurrent],

    [CategoryId],
    [DepartmentGroupId],
    [FamilyGroupId],
    [GlAccountId],
    [ItemId],
    [VendorId],

    [HistoricalBrand],
    [HistoricalCategoryName],
    [HistoricalCategoryShortCode],
    [HistoricalCutDate],
    [HistoricalDateAuthorized],
    [HistoricalDepartmentGroupName],
    [HistoricalDepartmentGroupShortCode],
    [HistoricalDescription],
    [HistoricalFamilyGroupName],
    [HistoricalGlAccountName],
    [HistoricalGlAccountShortCode],
    [HistoricalIsWeighed],
    [HistoricalItemCode],
    [HistoricalLeftPaddedCategoryShortCode],
    [HistoricalLongDescription],
    [HistoricalMfgCode],
    [HistoricalPack],
    [HistoricalSize],
    [HistoricalType],
    [HistoricalUnitOfMeasure],
    [HistoricalUpc],
    [HistoricalVendorName],
    [HistoricalVendorShortCode],

    [CurrentBrand],
    [CurrentCategoryName],
    [CurrentCategoryShortCode],
    [CurrentCutDate],
    [CurrentDateAuthorized],
    [CurrentDepartmentGroupName],
    [CurrentDepartmentGroupShortCode],
    [CurrentDescription],
    [CurrentFamilyGroupName],
    [CurrentGlAccountName],
    [CurrentGlAccountShortCode],
    [CurrentIsWeighed],
    [CurrentItemCode],
    [CurrentLeftPaddedCategoryShortCode],
    [CurrentLongDescription],
    [CurrentMfgCode],
    [CurrentPack],
    [CurrentSize],
    [CurrentType],
    [CurrentUnitOfMeasure],
    [CurrentUpc],
    [CurrentVendorName],
    [CurrentVendorShortCode]
    )
    SELECT
    li.[SequenceNumber],
    li.[HashKey],
    NULL, -- IsRowCurrent

    li.[CategoryId],
    li.[DepartmentGroupId],
    li.[FamilyGroupId],
    li.[GlAccountId],
    li.[ItemId],
    li.[VendorId],

    li.[Brand],
    li.[CategoryName],
    li.[CategoryShortCode],
    li.[CutDate],
    li.[DateAuthorized],
    li.[DepartmentGroupName],
    li.[DepartmentGroupShortCode],
    li.[Description],
    li.[FamilyGroupName],
    li.[GlAccountName],
    li.[GlAccountShortCode],
    li.[IsWeighed],
    li.[ItemCode],
    li.[LeftPaddedCategoryShortCode],
    li.[LongDescription],
    li.[MfgCode],
    li.[Pack],
    li.[Size],
    li.[Type],
    li.[UnitOfMeasure],
    li.[Upc],
    li.[VendorName],
    li.[VendorShortCode],

    li.[Brand],
    li.[CategoryName],
    li.[CategoryShortCode],
    li.[CutDate],
    li.[DateAuthorized],
    li.[DepartmentGroupName],
    li.[DepartmentGroupShortCode],
    li.[Description],
    li.[FamilyGroupName],
    li.[GlAccountName],
    li.[GlAccountShortCode],
    li.[IsWeighed],
    li.[ItemCode],
    li.[LeftPaddedCategoryShortCode],
    li.[LongDescription],
    li.[MfgCode],
    li.[Pack],
    li.[Size],
    li.[Type],
    li.[UnitOfMeasure],
    li.[Upc],
    li.[VendorName],
    li.[VendorShortCode]
    FROM #ItemsSource li
    WHERE li.[Rank] = 1
    AND NOT EXISTS (
    SELECT 1
    FROM [dimension].[Item] di
    WHERE di.[HistoricalHashKey] = li.[HashKey]
    );

    UPDATE di WITH (TABLOCK)
    SET di.[SequenceNumber] = li.[SequenceNumber]
    FROM [dimension].[Item] di
    INNER JOIN #ItemsSource li ON li.[HashKey] = di.[HistoricalHashKey]
    WHERE li.[Rank] = 1 AND li.[SequenceNumber] > di.[SequenceNumber];

    IF @updateCurrent <> 1
    RETURN;

    SELECT
    [ItemKey],
    [ItemId],
    [HistoricalUpc] AS [CurrentUpc],
    [HistoricalMfgCode] AS [CurrentMfgCode],
    [HistoricalType] AS [CurrentType],
    [HistoricalIsWeighed] AS [CurrentIsWeighed],
    [HistoricalDescription] AS [CurrentDescription],
    [HistoricalLongDescription] AS [CurrentLongDescription],
    [HistoricalPack] AS [CurrentPack],
    [HistoricalItemCode] AS [CurrentItemCode],
    [HistoricalSize] AS [CurrentSize],
    [HistoricalUnitOfMeasure] AS [CurrentUnitOfMeasure],
    [HistoricalDateAuthorized] AS [CurrentDateAuthorized],
    [HistoricalCutDate] AS [CurrentCutDate],
    [HistoricalBrand] AS [CurrentBrand],
    [HistoricalVendorName] AS [CurrentVendorName],
    [HistoricalVendorShortCode] AS [CurrentVendorShortCode],
    [HistoricalCategoryName] AS [CurrentCategoryName],
    [HistoricalCategoryShortCode] AS [CurrentCategoryShortCode],
    [HistoricalLeftPaddedCategoryShortCode] AS [CurrentLeftPaddedCategoryShortCode],
    [HistoricalDepartmentGroupName] AS [CurrentDepartmentGroupName],
    [HistoricalDepartmentGroupShortCode] AS [CurrentDepartmentGroupShortCode],
    [HistoricalFamilyGroupName] AS [CurrentFamilyGroupName],
    [HistoricalGlAccountName] AS [CurrentGlAccountName],
    [HistoricalGlAccountShortCode] AS [CurrentGlAccountShortCode]
    INTO #CurrentItems
    FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [ItemId] ORDER BY [SequenceNumber] DESC) AS Rank
    FROM [dimension].[Item]
    ) AS RankedCurrentItems
    WHERE [Rank] = 1;

    UPDATE di WITH (TABLOCK)
    SET
    di.CurrentUpc = ci.CurrentUpc,
    di.CurrentMfgCode = ci.CurrentMfgCode,
    di.CurrentType = ci.CurrentType,
    di.CurrentIsWeighed = ci.CurrentIsWeighed,
    di.CurrentDescription = ci.CurrentDescription,
    di.CurrentLongDescription = ci.CurrentLongDescription,
    di.CurrentPack = ci.CurrentPack,
    di.CurrentItemCode = ci.CurrentItemCode,
    di.CurrentSize = ci.CurrentSize,
    di.CurrentUnitOfMeasure = ci.CurrentUnitOfMeasure,
    di.CurrentDateAuthorized = ci.CurrentDateAuthorized,
    di.CurrentCutDate = ci.CurrentCutDate,
    di.CurrentBrand = ci.CurrentBrand,
    di.CurrentCategoryName = ci.CurrentCategoryName,
    di.CurrentCategoryShortCode = ci.CurrentCategoryShortCode,
    di.CurrentLeftPaddedCategoryShortCode = ci.CurrentLeftPaddedCategoryShortCode,
    di.CurrentDepartmentGroupName = ci.CurrentDepartmentGroupName,
    di.CurrentDepartmentGroupShortCode = ci.CurrentDepartmentGroupShortCode,
    di.CurrentFamilyGroupName = ci.CurrentFamilyGroupName,
    di.CurrentGlAccountName = ci.CurrentGlAccountName,
    di.CurrentGlAccountShortCode = ci.CurrentGlAccountShortCode,
    di.IsRowCurrent = (CASE WHEN ci.[ItemKey] = di.[ItemKey] THEN 1 ELSE 0 END)
    FROM [dimension].[Item] di
    INNER JOIN #CurrentItems ci ON ci.[ItemId] = di.[ItemId];
    END
    76 changes: 76 additions & 0 deletions ScopedFactsView.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,76 @@
    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
  2. mwpastore revised this gist Nov 18, 2024. 1 changed file with 38 additions and 1 deletion.
    39 changes: 38 additions & 1 deletion MovementItemAggregateView.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -63,7 +63,44 @@ WITH Base AS (
    LEFT JOIN [Host].[dbo].[Vendor] AS v
    ON v.[Id] = mia.[VendorId]
    )
    SELECT *,
    SELECT
    [BusinessDay],
    [IntId],
    [ItemId],
    [Upc],
    [ItemType],
    [Description],
    [LongDescription],
    [Pack],
    [ItemCode],
    [Size],
    [UnitOfMeasure],
    [DateAuthorized],
    [CutDate],
    [Brand],
    [VendorId],
    [VendorName],
    [VendorShortCode],
    [CategoryId],
    [CategoryName],
    [CategoryShortCode],
    [DepartmentGroupId],
    [DepartmentGroupName],
    [DepartmentGroupShortCode],
    [FamilyGroupId],
    [FamilyGroupName],
    [GlAccountId],
    [GlAccountName],
    [GlAccountShortCode],
    [LocationId],
    [LocationName],
    [LocationShortCode],
    [IsAuthorized],
    [TotalQuantity],
    [TotalWeight],
    [TotalCost],
    [TotalRetail],
    [WeekMargin],
    CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
    [ItemId], '|',
    [VendorId], '|',
  3. mwpastore created this gist Nov 18, 2024.
    109 changes: 109 additions & 0 deletions Item.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,109 @@
    CREATE TABLE [dimension].[Item]
    (
    [ItemKey] BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [SequenceNumber] BIGINT NOT NULL,
    [ItemId] UNIQUEIDENTIFIER NOT NULL,
    [HistoricalUpc] CHAR(13) NOT NULL,
    [HistoricalMfgCode] NVARCHAR(5) NOT NULL,
    [HistoricalType] INT NOT NULL,
    [HistoricalIsWeighed] BIT NOT NULL,
    [HistoricalDescription] NVARCHAR(50) NOT NULL,
    [HistoricalLongDescription] NVARCHAR(50) NOT NULL,
    [HistoricalPack] DECIMAL(8,2) NOT NULL,
    [HistoricalItemCode] NVARCHAR(50) NOT NULL,
    [HistoricalSize] DECIMAL(8,2) NOT NULL,
    [HistoricalUnitOfMeasure] NVARCHAR(2) NOT NULL,
    [HistoricalDateAuthorized] DATE NOT NULL,
    [HistoricalCutDate] DATE NOT NULL,
    [HistoricalBrand] NVARCHAR(MAX) NOT NULL,
    [VendorId] UNIQUEIDENTIFIER NULL,
    [HistoricalVendorName] NVARCHAR(50) NOT NULL,
    [HistoricalVendorShortCode] NVARCHAR(10) NOT NULL,
    [CategoryId] UNIQUEIDENTIFIER NULL,
    [HistoricalCategoryName] NVARCHAR(50) NOT NULL,
    [HistoricalCategoryShortCode] NVARCHAR(10) NOT NULL,
    [HistoricalLeftPaddedCategoryShortCode] NVARCHAR(7) NOT NULL,
    [DepartmentGroupId] UNIQUEIDENTIFIER NULL,
    [HistoricalDepartmentGroupName] NVARCHAR(50) NOT NULL,
    [HistoricalDepartmentGroupShortCode] NVARCHAR(50) NOT NULL,
    [FamilyGroupId] UNIQUEIDENTIFIER NULL,
    [HistoricalFamilyGroupName] NVARCHAR(50) NOT NULL,
    [GlAccountId] UNIQUEIDENTIFIER NULL,
    [HistoricalGlAccountName] NVARCHAR(50) NOT NULL,
    [HistoricalGlAccountShortCode] NVARCHAR(10) NOT NULL,
    [HistoricalHashKey] VARBINARY(20) NOT NULL,
    [CurrentUpc] CHAR(13) NOT NULL,
    [CurrentMfgCode] NVARCHAR(5) NOT NULL,
    [CurrentType] INT NOT NULL,
    [CurrentIsWeighed] BIT NOT NULL,
    [CurrentDescription] NVARCHAR(50) NOT NULL,
    [CurrentLongDescription] NVARCHAR(50) NOT NULL,
    [CurrentPack] DECIMAL(8,2) NOT NULL,
    [CurrentItemCode] NVARCHAR(50) NOT NULL,
    [CurrentSize] DECIMAL(8,2) NOT NULL,
    [CurrentUnitOfMeasure] NVARCHAR(2) NOT NULL,
    [CurrentDateAuthorized] DATE NOT NULL,
    [CurrentCutDate] DATE NOT NULL,
    [CurrentBrand] NVARCHAR(MAX) NOT NULL,
    [CurrentVendorName] NVARCHAR(50) NOT NULL,
    [CurrentVendorShortCode] NVARCHAR(50) NOT NULL,
    [CurrentCategoryName] NVARCHAR(50) NOT NULL,
    [CurrentCategoryShortCode] NVARCHAR(10) NOT NULL,
    [CurrentLeftPaddedCategoryShortCode] NVARCHAR(7) NOT NULL,
    [CurrentDepartmentGroupName] NVARCHAR(50) NOT NULL,
    [CurrentDepartmentGroupShortCode] NVARCHAR(50) NOT NULL,
    [CurrentFamilyGroupName] NVARCHAR(50) NOT NULL,
    [CurrentGlAccountName] NVARCHAR(50) NOT NULL,
    [CurrentGlAccountShortCode] NVARCHAR(10) NOT NULL,
    [IsRowCurrent] BIT NULL,
    )
    GO

    CREATE UNIQUE NONCLUSTERED INDEX [CK_Item_Finder]
    ON [dimension].[Item]
    (
    [HistoricalHashKey],
    [ItemId]
    )
    INCLUDE
    (
    [ItemKey],
    [IsRowCurrent],
    [SequenceNumber]
    )
    GO

    -- For Category Analysis report
    CREATE NONCLUSTERED INDEX [IX_Item_Filter_Upc]
    ON [dimension].[Item]
    (
    [CurrentUpc],
    [CurrentMfgCode]
    )
    -- N.B. We basically need to fetch the whole row after this lookup, so there's
    -- no need to INCLUDE any specific columns in the index
    GO

    CREATE NONCLUSTERED INDEX [IX_Item_Filter_Category]
    ON [dimension].[Item]
    (
    [CurrentCategoryName],
    [CurrentCategoryShortCode]
    )
    GO

    CREATE NONCLUSTERED INDEX [IX_Item_Filter_DepartmentGroup]
    ON [dimension].[Item]
    (
    [CurrentDepartmentGroupName],
    [CurrentDepartmentGroupShortCode]
    )
    GO

    CREATE NONCLUSTERED INDEX [IX_Item_Filter_Vendor]
    ON [dimension].[Item]
    (
    [CurrentVendorName],
    [CurrentVendorShortCode]
    )
    GO
    24 changes: 24 additions & 0 deletions ItemAisledFactlessFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@
    --
    -- any changes here *MUST* be replicated to switch_staging/tables/ItemAisledFactlessFacts
    --
    CREATE TABLE [fact].[ItemAisledFactlessFacts]
    (
    INDEX [CCI_ItemAisledFactlessFacts] CLUSTERED COLUMNSTORE
    ON [ItemAisledFactlessFactsPartitionScheme]([BusinessDay]),

    [BusinessDay] 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
    (
    [BusinessDay],
    [LocationKey],
    [ItemKey]
    ),
    )
    ON [ItemAisledFactlessFactsPartitionScheme]([BusinessDay])
    29 changes: 29 additions & 0 deletions MovementFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,29 @@
    --
    -- any changes here *MUST* be replicated to switch_staging/tables/MovementFacts
    --
    CREATE TABLE [fact].[MovementFacts]
    (
    INDEX [CCI_MovementFacts] CLUSTERED COLUMNSTORE
    ON [MovementFactsPartitionScheme]([BusinessDay]),

    [BusinessDay] 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
    (
    [BusinessDay],
    [ItemKey],
    [LocationKey]
    ),
    )
    ON [MovementFactsPartitionScheme]([BusinessDay])
    99 changes: 99 additions & 0 deletions MovementItemAggregateView.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,99 @@
    CREATE VIEW [etl].[MovementItemAggregateView] AS
    WITH Base AS (
    SELECT
    mia.[BusinessDay],
    mia.[IntId],

    mia.[Item_id] AS ItemId,
    mia.[Upc],
    COALESCE(mia.[ItemType], -1) AS ItemType,
    COALESCE(i.[Description], 'N/A') AS [Description],
    COALESCE(i.[LongDescription], 'N/A') AS LongDescription,
    COALESCE(mia.[Pack], -1) AS Pack,
    COALESCE(mia.[VItem], 'QQ') AS ItemCode,
    COALESCE(mia.[Size], -1) AS [Size],
    COALESCE(mia.[UnitOfMeasureShortCode], 'QQ') AS UnitOfMeasure,
    COALESCE(mia.[DateAuthorized], i.[DateAuthorized], '1900-01-01') AS DateAuthorized,
    COALESCE(mia.[CutDate], i.[CutDate], '9999-12-31') AS CutDate,
    COALESCE(i.[Brand], 'N/A') AS Brand,

    mia.[VendorId],
    COALESCE(mia.[VendorName], 'N/A') AS VendorName,
    COALESCE(v.[ShortCode], 'N/A') AS VendorShortCode,

    mia.[CategoryId],
    COALESCE(mia.[CategoryName], 'N/A') AS CategoryName,
    COALESCE(mia.[CategoryShortCode], 'N/A') AS CategoryShortCode,

    mia.[DepartmentGroupId],
    COALESCE(dg.[Name], 'N/A') AS DepartmentGroupName,
    COALESCE(dg.[ShortCode], 'N/A') AS DepartmentGroupShortCode,

    mia.[FamilyGroupId],
    COALESCE(fg.[Name], 'N/A') AS FamilyGroupName,

    i.[GlAccount_id] AS GlAccountId,
    COALESCE(gl.[Name], 'N/A') AS GlAccountName,
    COALESCE(gl.[ShortCode], 'N/A') AS GlAccountShortCode,

    mia.[Location_id] AS LocationId,
    COALESCE(mia.[LocationName], 'N/A') AS LocationName,
    COALESCE(mia.[LocationShortCode], 'N/A') AS LocationShortCode,

    mia.[IsAuthorized],

    mia.[TotalQuantity],
    mia.[TotalWeight],
    mia.[TotalCost],
    mia.[TotalRetail],
    mia.[WeekMargin]
    FROM [Host].[dbo].[MovementItemAggregate] AS mia
    --INNER JOIN [Host].[dbo].[Location] AS l
    -- ON l.[Id] = mia.[Location_id]
    INNER JOIN [Host].[dbo].[Item] AS i
    ON i.[Id] = mia.[Item_id]
    --LEFT JOIN [Host].[dbo].[Category] AS c
    -- ON c.[Id] = mia.[CategoryId]
    LEFT JOIN [Host].[dbo].[DepartmentGroup] AS dg
    ON dg.[Id] = mia.[DepartmentGroupId]
    LEFT JOIN [Host].[dbo].[FamilyGroup] AS fg
    ON fg.[Id] = mia.[FamilyGroupId]
    LEFT JOIN [Host].[dbo].[GlAccount] AS gl
    ON gl.[Id] = i.[GlAccount_id]
    LEFT JOIN [Host].[dbo].[Vendor] AS v
    ON v.[Id] = mia.[VendorId]
    )
    SELECT *,
    CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
    [ItemId], '|',
    [VendorId], '|',
    [CategoryId], '|',
    [DepartmentGroupId], '|',
    [FamilyGroupId], '|',
    [GlAccountId], '|',
    [Upc], '|',
    [ItemType], '|',
    [Description], '|',
    [LongDescription], '|',
    [Pack], '|',
    [ItemCode], '|',
    [Size], '|',
    [UnitOfMeasure], '|',
    [DateAuthorized], '|',
    [CutDate], '|',
    [Brand], '|',
    [VendorName], '|',
    [VendorShortCode], '|',
    [CategoryName], '|',
    [CategoryShortCode], '|',
    [DepartmentGroupName], '|',
    [DepartmentGroupShortCode], '|',
    [FamilyGroupName], '|',
    [GlAccountName], '|',
    [GlAccountShortCode]
    ))) AS ItemHashKey,
    CONVERT(VARBINARY(20), HASHBYTES('SHA1', CONCAT(
    [LocationName], '|',
    [LocationShortCode]
    ))) AS LocationHashKey
    FROM Base
    162 changes: 162 additions & 0 deletions PopulateCategoryAnalysisFacts.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,162 @@
    CREATE PROCEDURE [etl].[PopulateCategoryAnalysisFacts]
    @fromBusinessDay DATE,
    @toBusinessDay DATE = @fromBusinessDay,
    @factSchemaName NVARCHAR(128),
    @appendOnly BIT = 0
    AS
    BEGIN
    -- Build Fact Source
    BEGIN
    -- Load a temp table instead of using a CTE so predicate(s) push
    -- down to the view
    SELECT
    BusinessDay,
    ItemHashKey,
    LocationHashKey,

    CAST(MAX(CAST(IsAuthorized AS INT)) AS BIT) AS IsAuthorized,

    SUM(TotalQuantity) AS TotalQuantity,
    SUM(TotalWeight) AS TotalWeight,
    SUM(TotalCost) AS TotalCost,
    SUM(TotalRetail) AS TotalRetail,
    SUM(WeekMargin) AS WeekMargin
    INTO #IntermediateFactsSource
    FROM [etl].[MovementItemAggregateView]
    WHERE BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay
    GROUP BY BusinessDay, ItemHashKey, LocationHashKey;

    SELECT
    BusinessDay,
    id.ItemKey,
    l.LocationKey,

    CAST(MAX(CAST(IsAuthorized AS INT)) AS BIT) AS IsAuthorized,

    SUM(TotalQuantity) AS TotalQuantity,
    SUM(TotalWeight) AS TotalWeight,
    SUM(TotalCost) AS TotalCost,
    SUM(TotalRetail) AS TotalRetail,
    SUM(WeekMargin) AS WeekMargin,

    CAST(CASE
    WHEN SUM(TotalQuantity) <> 0
    OR SUM(TotalWeight) <> 0
    OR SUM(TotalCost) <> 0
    OR SUM(TotalRetail) <> 0
    OR SUM(WeekMargin) <> 0
    THEN 1
    ELSE 0
    END AS BIT) AS HasMovement
    INTO #FactsSource
    FROM #IntermediateFactsSource
    INNER JOIN [dimension].[Item] AS id
    ON id.[HistoricalHashKey] = ItemHashKey
    INNER JOIN (
    SELECT MAX([LocationKey]) AS LocationKey, [HistoricalHashKey]
    FROM [dimension].[Location]
    GROUP BY [HistoricalHashKey]
    ) AS l
    ON l.[HistoricalHashKey] = LocationHashKey
    GROUP BY
    BusinessDay,
    id.ItemKey,
    l.LocationKey;
    END

    BEGIN -- Add new Movement Facts
    DECLARE @movementFactsSql NVARCHAR(MAX);

    SET @movementFactsSql = N'
    MERGE ' + QUOTENAME(@factSchemaName) + N'.[MovementFacts] WITH (TABLOCK) AS target
    USING (
    SELECT *
    FROM #FactsSource
    WHERE HasMovement = 1
    ) AS source
    ON target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    WHEN MATCHED AND @appendOnly <> 1 THEN
    UPDATE SET
    target.TotalQuantity = source.TotalQuantity,
    target.TotalWeight = source.TotalWeight,
    target.TotalCost = source.TotalCost,
    target.TotalRetail = source.TotalRetail,
    target.WeekMargin = source.WeekMargin
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (BusinessDay, ItemKey, LocationKey,
    TotalQuantity, TotalWeight, TotalCost, TotalRetail, WeekMargin)
    VALUES (source.BusinessDay, source.ItemKey, source.LocationKey,
    source.TotalQuantity, source.TotalWeight, source.TotalCost, source.TotalRetail, source.WeekMargin);
    ';

    EXEC sp_executesql @movementFactsSql, N'@appendOnly BIT', @appendOnly;

    IF @appendOnly <> 1
    BEGIN
    SET @movementFactsSql = N'
    DELETE target WITH (TABLOCK)
    FROM ' + QUOTENAME(@factSchemaName) + N'.[MovementFacts] AS target
    WHERE target.BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay AND NOT EXISTS (
    SELECT 1
    FROM #FactsSource AS source
    WHERE target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    AND source.HasMovement = 1
    );
    ';

    EXEC sp_executesql @movementFactsSql, N'@fromBusinessDay DATE, @toBusinessDay DATE', @fromBusinessDay, @toBusinessDay;
    END
    END

    BEGIN -- Add Factless Facts for the day
    DECLARE @factlessFactsSql NVARCHAR(MAX);

    SET @factlessFactsSql = N'
    INSERT INTO ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] WITH (TABLOCK)
    (
    BusinessDay,
    ItemKey,
    LocationKey
    )
    SELECT
    source.BusinessDay,
    source.ItemKey,
    source.LocationKey
    FROM #FactsSource AS source
    WHERE (IsAuthorized = 1 OR HasMovement = 1)
    AND NOT EXISTS (
    SELECT 1
    FROM ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] AS target
    WHERE target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    );
    ';

    EXEC (@factlessFactsSql);

    IF @appendOnly <> 1
    BEGIN
    SET @factlessFactsSql = N'
    DELETE target WITH (TABLOCK)
    FROM ' + QUOTENAME(@factSchemaName) + N'.[ItemAisledFactlessFacts] AS target
    WHERE target.BusinessDay BETWEEN @fromBusinessDay AND @toBusinessDay AND NOT EXISTS (
    SELECT 1
    FROM #FactsSource AS source
    WHERE target.BusinessDay = source.BusinessDay
    AND target.ItemKey = source.ItemKey
    AND target.LocationKey = source.LocationKey
    AND (source.IsAuthorized = 1 OR source.HasMovement = 1)
    );
    ';

    EXEC sp_executesql @factlessFactsSql, N'@fromBusinessDay DATE, @toBusinessDay DATE', @fromBusinessDay, @toBusinessDay;
    END
    END
    END
    251 changes: 251 additions & 0 deletions PopulateItem.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,251 @@
    CREATE PROCEDURE [etl].[PopulateItem]
    @fromBusinessDay DATE,
    @toBusinessDay DATE = @fromBusinessDay,
    @updateCurrent BIT
    AS
    BEGIN
    -- Load a temp table instead of using a CTE so predicate(s) push
    -- down to the view
    SELECT
    [BusinessDay],
    [IntId] AS [SequenceNumber],

    [ItemId],
    [Upc],
    SUBSTRING([Upc], 4, 5) AS [MfgCode],
    [ItemType] AS [Type],
    CASE WHEN [ItemType] IN (2, 3) THEN 1 ELSE 0 END AS [IsWeighed],
    [Description],
    [LongDescription],
    [Pack],
    [ItemCode],
    [Size],
    [UnitOfMeasure],
    [DateAuthorized],
    [CutDate],
    [Brand],

    [VendorId],
    [VendorName],
    [VendorShortCode],

    [CategoryId],
    [CategoryName],
    [CategoryShortCode],
    RIGHT('00000000' + [CategoryShortCode], 7) AS [LeftPaddedCategoryShortCode],

    [DepartmentGroupId],
    [DepartmentGroupName],
    [DepartmentGroupShortCode],

    [FamilyGroupId],
    [FamilyGroupName],

    [GlAccountId],
    [GlAccountName],
    [GlAccountShortCode],

    [ItemHashKey] AS HashKey,
    ROW_NUMBER() OVER (PARTITION BY [ItemHashKey] ORDER BY [IntId] DESC) AS Rank
    INTO #ItemsSource
    FROM [etl].[MovementItemAggregateView]
    WHERE [BusinessDay] BETWEEN @fromBusinessDay AND @toBusinessDay;

    INSERT INTO [dimension].[Item] WITH (TABLOCK)
    (
    [SequenceNumber],
    [HistoricalHashKey],
    [IsRowCurrent],

    [CategoryId],
    [DepartmentGroupId],
    [FamilyGroupId],
    [GlAccountId],
    [ItemId],
    [VendorId],

    [HistoricalBrand],
    [HistoricalCategoryName],
    [HistoricalCategoryShortCode],
    [HistoricalCutDate],
    [HistoricalDateAuthorized],
    [HistoricalDepartmentGroupName],
    [HistoricalDepartmentGroupShortCode],
    [HistoricalDescription],
    [HistoricalFamilyGroupName],
    [HistoricalGlAccountName],
    [HistoricalGlAccountShortCode],
    [HistoricalIsWeighed],
    [HistoricalItemCode],
    [HistoricalLeftPaddedCategoryShortCode],
    [HistoricalLongDescription],
    [HistoricalMfgCode],
    [HistoricalPack],
    [HistoricalSize],
    [HistoricalType],
    [HistoricalUnitOfMeasure],
    [HistoricalUpc],
    [HistoricalVendorName],
    [HistoricalVendorShortCode],

    [CurrentBrand],
    [CurrentCategoryName],
    [CurrentCategoryShortCode],
    [CurrentCutDate],
    [CurrentDateAuthorized],
    [CurrentDepartmentGroupName],
    [CurrentDepartmentGroupShortCode],
    [CurrentDescription],
    [CurrentFamilyGroupName],
    [CurrentGlAccountName],
    [CurrentGlAccountShortCode],
    [CurrentIsWeighed],
    [CurrentItemCode],
    [CurrentLeftPaddedCategoryShortCode],
    [CurrentLongDescription],
    [CurrentMfgCode],
    [CurrentPack],
    [CurrentSize],
    [CurrentType],
    [CurrentUnitOfMeasure],
    [CurrentUpc],
    [CurrentVendorName],
    [CurrentVendorShortCode]
    )
    SELECT
    li.[SequenceNumber],
    li.[HashKey],
    NULL, -- IsRowCurrent

    li.[CategoryId],
    li.[DepartmentGroupId],
    li.[FamilyGroupId],
    li.[GlAccountId],
    li.[ItemId],
    li.[VendorId],

    li.[Brand],
    li.[CategoryName],
    li.[CategoryShortCode],
    li.[CutDate],
    li.[DateAuthorized],
    li.[DepartmentGroupName],
    li.[DepartmentGroupShortCode],
    li.[Description],
    li.[FamilyGroupName],
    li.[GlAccountName],
    li.[GlAccountShortCode],
    li.[IsWeighed],
    li.[ItemCode],
    li.[LeftPaddedCategoryShortCode],
    li.[LongDescription],
    li.[MfgCode],
    li.[Pack],
    li.[Size],
    li.[Type],
    li.[UnitOfMeasure],
    li.[Upc],
    li.[VendorName],
    li.[VendorShortCode],

    li.[Brand],
    li.[CategoryName],
    li.[CategoryShortCode],
    li.[CutDate],
    li.[DateAuthorized],
    li.[DepartmentGroupName],
    li.[DepartmentGroupShortCode],
    li.[Description],
    li.[FamilyGroupName],
    li.[GlAccountName],
    li.[GlAccountShortCode],
    li.[IsWeighed],
    li.[ItemCode],
    li.[LeftPaddedCategoryShortCode],
    li.[LongDescription],
    li.[MfgCode],
    li.[Pack],
    li.[Size],
    li.[Type],
    li.[UnitOfMeasure],
    li.[Upc],
    li.[VendorName],
    li.[VendorShortCode]
    FROM #ItemsSource li
    WHERE li.[Rank] = 1
    AND NOT EXISTS (
    SELECT 1
    FROM [dimension].[Item] di
    WHERE di.[HistoricalHashKey] = li.[HashKey]
    );

    UPDATE di WITH (TABLOCK)
    SET di.[SequenceNumber] = li.[SequenceNumber]
    FROM [dimension].[Item] di
    INNER JOIN #ItemsSource li ON li.[HashKey] = di.[HistoricalHashKey]
    WHERE li.[Rank] = 1 AND li.[SequenceNumber] > di.[SequenceNumber];

    IF @updateCurrent <> 1
    RETURN;

    SELECT
    [ItemKey],
    [ItemId],
    [HistoricalUpc] AS [CurrentUpc],
    [HistoricalMfgCode] AS [CurrentMfgCode],
    [HistoricalType] AS [CurrentType],
    [HistoricalIsWeighed] AS [CurrentIsWeighed],
    [HistoricalDescription] AS [CurrentDescription],
    [HistoricalLongDescription] AS [CurrentLongDescription],
    [HistoricalPack] AS [CurrentPack],
    [HistoricalItemCode] AS [CurrentItemCode],
    [HistoricalSize] AS [CurrentSize],
    [HistoricalUnitOfMeasure] AS [CurrentUnitOfMeasure],
    [HistoricalDateAuthorized] AS [CurrentDateAuthorized],
    [HistoricalCutDate] AS [CurrentCutDate],
    [HistoricalBrand] AS [CurrentBrand],
    [HistoricalVendorName] AS [CurrentVendorName],
    [HistoricalVendorShortCode] AS [CurrentVendorShortCode],
    [HistoricalCategoryName] AS [CurrentCategoryName],
    [HistoricalCategoryShortCode] AS [CurrentCategoryShortCode],
    [HistoricalLeftPaddedCategoryShortCode] AS [CurrentLeftPaddedCategoryShortCode],
    [HistoricalDepartmentGroupName] AS [CurrentDepartmentGroupName],
    [HistoricalDepartmentGroupShortCode] AS [CurrentDepartmentGroupShortCode],
    [HistoricalFamilyGroupName] AS [CurrentFamilyGroupName],
    [HistoricalGlAccountName] AS [CurrentGlAccountName],
    [HistoricalGlAccountShortCode] AS [CurrentGlAccountShortCode]
    INTO #CurrentItems
    FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [ItemId] ORDER BY [SequenceNumber] DESC) AS Rank
    FROM [dimension].[Item]
    ) AS RankedCurrentItems
    WHERE [Rank] = 1;

    UPDATE di WITH (TABLOCK)
    SET
    di.CurrentUpc = ci.CurrentUpc,
    di.CurrentMfgCode = ci.CurrentMfgCode,
    di.CurrentType = ci.CurrentType,
    di.CurrentIsWeighed = ci.CurrentIsWeighed,
    di.CurrentDescription = ci.CurrentDescription,
    di.CurrentLongDescription = ci.CurrentLongDescription,
    di.CurrentPack = ci.CurrentPack,
    di.CurrentItemCode = ci.CurrentItemCode,
    di.CurrentSize = ci.CurrentSize,
    di.CurrentUnitOfMeasure = ci.CurrentUnitOfMeasure,
    di.CurrentDateAuthorized = ci.CurrentDateAuthorized,
    di.CurrentCutDate = ci.CurrentCutDate,
    di.CurrentBrand = ci.CurrentBrand,
    di.CurrentCategoryName = ci.CurrentCategoryName,
    di.CurrentCategoryShortCode = ci.CurrentCategoryShortCode,
    di.CurrentLeftPaddedCategoryShortCode = ci.CurrentLeftPaddedCategoryShortCode,
    di.CurrentDepartmentGroupName = ci.CurrentDepartmentGroupName,
    di.CurrentDepartmentGroupShortCode = ci.CurrentDepartmentGroupShortCode,
    di.CurrentFamilyGroupName = ci.CurrentFamilyGroupName,
    di.CurrentGlAccountName = ci.CurrentGlAccountName,
    di.CurrentGlAccountShortCode = ci.CurrentGlAccountShortCode,
    di.IsRowCurrent = (CASE WHEN ci.[ItemKey] = di.[ItemKey] THEN 1 ELSE 0 END)
    FROM [dimension].[Item] di
    INNER JOIN #CurrentItems ci ON ci.[ItemId] = di.[ItemId];
    END