WideWorldImportersDW – Fattening Up – fact.Sale

Background

In an earlier post we spoke about restoring Microsoft’s new sample database, WideWorldImportersDW.

The end game is to have a database to try out Columnar Indexes.

Lineage

It is just one post so far, but let us claim space for additional journaling along the same thoughts:

  1. World Wide Importers – Using On SQL Server 2014
    Published On :- 2017-August-23rd
    Link

 

Metadata – Microsoft

Number of Records

Code


use [WideWorldImportersDW]
go

select
		
          [table]
            =  tblSS.[name]
                + '.'
                + tblSO.[name]
 
        , [indexName]
            = tblSI.[name]
                     
        , [indexType]
          = tblSI.[type_desc]
 
        , [numberofRecords]
            = sum(tblSP.[rows])
 
        , numberofPagesData
            = sum(tblSPS.in_row_data_page_count)
 
        , numberofPagesLOB
            = sum(tblSPS.lob_used_page_count)
 
        , usedPageCount
            = sum(used_page_count)
 
from   sys.schemas tblSS
 
inner join sys.objects tblSO
 
    on tblSS.schema_id = tblSO.schema_id
 
inner join sys.indexes tblSI
 
    on tblSO.object_id = tblSI.object_id
 
 
inner join sys.partitions tblSP
 
    on tblSI.object_id = tblSP.object_id
    and tblSI.index_id = tblSP.index_id
 
 
inner join sys.dm_db_partition_stats tblSPS
 
    on tblSP.object_id = tblSPS.object_id
    and tblSP.index_id = tblSPS.index_id
    and tblSP.partition_id = tblSPS.partition_id
 
 
where tblSI.index_id in (0,1) 

and   tblSS.[name] = 'Fact'
 
group by
          tblSS.[name]
        , tblSO.[name]
        , tblSI.index_id
        , tblSI.[name]
        , tblSI.[type_desc]
 
order by
          tblSS.[name]
        , tblSO.[name]
        , tblSI.index_id



Image

Explanation

Our three biggest Fact tables ( Fact.Movement, Fact.Order, Fact.Sale ) are about 2.3 million records.

 

Make Bigger

fact.Sale

We chose fact.Sale out of our largest Fact tables.

The reason been the presence of a Stored Procedure (SP), [Application].[Configuration_PopulateLargeSaleTable].

Here are the other tooling available SPs in the same schema, Application.

Objective

We will leave the original sales table, dbo.Fact, as is.

And, create new ones.

 

List of Tables

Here are the tables.

Usage Table
Sales Table – Original Fact.Sale
Sales Table – Row Store – Clustered Index Fact.SaleLargeRowStore
Sales Table – Column Store – Clustered Index Fact.SaleLargeColumnStoreClustered
Sales Table – Column Store – NonClustered Index Fact.SaleLargeColumnStoreNonClustered

Create Tables

Code

Fact.SaleLargeRowStore


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [WideWorldImportersDW]
GO

/*
    DROP TABLE [Fact].[SaleLargeRowStore]
*/

if object_id('[Fact].[SaleLargeRowStore]') is null
begin

    CREATE TABLE [Fact].[SaleLargeRowStore]
    (
        [Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
        [City Key] [int] NOT NULL,
        [Customer Key] [int] NOT NULL,
        [Bill To Customer Key] [int] NOT NULL,
        [Stock Item Key] [int] NOT NULL,
        
        [Invoice Date Key] [date] NOT NULL,

        [InvoiceDateKeyYear] [int] NOT NULL,
        
        [Delivery Date Key] [date] NULL,
        
        [DeliveryDateKeyYear] [int] NULL,
        
        [Salesperson Key] [int] NOT NULL,
        [WWI Invoice ID] [int] NOT NULL,
        [Description] [nvarchar](100) NOT NULL,
        [Package] [nvarchar](50) NOT NULL,
        [Quantity] [int] NOT NULL,
        [Unit Price] [decimal](18, 2) NOT NULL,
        [Tax Rate] [decimal](18, 3) NOT NULL,
        [Total Excluding Tax] [decimal](18, 2) NOT NULL,
        [Tax Amount] [decimal](18, 2) NOT NULL,
        [Profit] [decimal](18, 2) NOT NULL,
        [Total Including Tax] [decimal](18, 2) NOT NULL,
        [Total Dry Items] [int] NOT NULL,
        [Total Chiller Items] [int] NOT NULL,
        [Lineage Key] [int] NOT NULL
    
            , CONSTRAINT [PK_Fact_SaleLargeRowStore] PRIMARY KEY CLUSTERED 
            (
                [Sale Key] ASC
            )
            WITH 
            (
                  PAD_INDEX = OFF
                , STATISTICS_NORECOMPUTE = OFF
                , IGNORE_DUP_KEY = OFF
                , ALLOW_ROW_LOCKS = ON
                , ALLOW_PAGE_LOCKS = ON
            ) ON [USERDATA]

        ) ON [USERDATA]

end
go

Fact.SaleLargeColumnStoreClustered

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [WideWorldImportersDW]
GO

if object_id('[Fact].[SaleLargeColumnStoreClustered]') is null
begin

	CREATE TABLE [Fact].[SaleLargeColumnStoreClustered]
	(
		[Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
		[City Key] [int] NOT NULL,
		[Customer Key] [int] NOT NULL,
		[Bill To Customer Key] [int] NOT NULL,
		[Stock Item Key] [int] NOT NULL,
	
		[Invoice Date Key] [date] NOT NULL,
		[InvoiceDateKeyYear] [int] NOT NULL,
			
		[Delivery Date Key] [date] NULL,
		[DeliveryDateKeyYear] [int] NULL,
			
		[Salesperson Key] [int] NOT NULL,
		[WWI Invoice ID] [int] NOT NULL,
		[Description] [nvarchar](100) NOT NULL,
		[Package] [nvarchar](50) NOT NULL,
		[Quantity] [int] NOT NULL,
		[Unit Price] [decimal](18, 2) NOT NULL,
		[Tax Rate] [decimal](18, 3) NOT NULL,
		[Total Excluding Tax] [decimal](18, 2) NOT NULL,
		[Tax Amount] [decimal](18, 2) NOT NULL,
		[Profit] [decimal](18, 2) NOT NULL,
		[Total Including Tax] [decimal](18, 2) NOT NULL,
		[Total Dry Items] [int] NOT NULL,
		[Total Chiller Items] [int] NOT NULL,
		[Lineage Key] [int] NOT NULL
	
			

		) ON [USERDATA]

		CREATE CLUSTERED COLUMNSTORE INDEX [INDX_FACT_SaleLargeColumnStoreClustered]
			ON [Fact].[SaleLargeColumnStoreClustered]
			ON [USERDATA]
			;  
	)

end
go
	
Fact.SaleLargeColumnStoreNonClustered

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


USE [WideWorldImportersDW]
GO

if object_id('[Fact].[SaleLargeColumnStoreNonClustered]') is null
begin

	CREATE TABLE [Fact].[SaleLargeColumnStoreNonClustered]
	(
		[Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
		[City Key] [int] NOT NULL,
		[Customer Key] [int] NOT NULL,
		[Bill To Customer Key] [int] NOT NULL,
		[Stock Item Key] [int] NOT NULL,

		[Invoice Date Key] [date] NOT NULL,
		[InvoiceDateKeyYear] [int] NOT NULL,

		[Delivery Date Key] [date] NULL,
		[DeliveryDateKeyYear] [int] NULL,
				
		[Salesperson Key] [int] NOT NULL,
		[WWI Invoice ID] [int] NOT NULL,
		[Description] [nvarchar](100) NOT NULL,
		[Package] [nvarchar](50) NOT NULL,
		[Quantity] [int] NOT NULL,
		[Unit Price] [decimal](18, 2) NOT NULL,
		[Tax Rate] [decimal](18, 3) NOT NULL,
		[Total Excluding Tax] [decimal](18, 2) NOT NULL,
		[Tax Amount] [decimal](18, 2) NOT NULL,
		[Profit] [decimal](18, 2) NOT NULL,
		[Total Including Tax] [decimal](18, 2) NOT NULL,
		[Total Dry Items] [int] NOT NULL,
		[Total Chiller Items] [int] NOT NULL,
		[Lineage Key] [int] NOT NULL
	
			

		) ON [USERDATA]

end

declare @tableName sysname
declare @objectID  int

set @tableName = '[Fact].[SaleLargeColumnStoreNonClustered]'
set @objectID = object_id(@tableName)


/*
	Msg 35339, Level 16, State 1, Line 162
	Multiple nonclustered columnstore indexes are not supported.
*/
if @objectID is not null
begin

	if not exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = @objectID
		and    tblSI.[name] = 'INDX_InvoiceDateKeyYear_DeliveryDateKeyYear'

	)
	begin

		CREATE NONCLUSTERED COLUMNSTORE INDEX [INDX_InvoiceDateKeyYear_DeliveryDateKeyYear]
			ON [Fact].[SaleLargeColumnStoreNonClustered]
			(
				  [InvoiceDateKeyYear]
				, [DeliveryDateKeyYear]
			)
			ON [USERDATA]
			;  

	end


end
go


Populate Tables

Approach

We take a uniform approach in populating each of our tables.

And, that approach is to:

  1. Decide on our target year
    • In our case always 2012
  2. Does Index need to be disabled to be able to manage data?
    • If Clustered Row Store or Clustered Column Store, then  “No, Indexes does not need to be frozen
    • If Non-Clustered Column Store exists, then “Yes, Indexes need to be frozen
  3. Remove any existing records for our target year
  4. If base data does not exist, add it
    • Remember base data is data after Jan 1st 2013
  5. Add data for Jan 1st 2012 to Dec 31st 2012
    • For each data add data @desiredTargetRecordCount / 365
  6. Defragment data
    • If Re-org requested
      • Reorg data
      • Update Statistics
    • If rebuild requested
      • Rebuild data

 

Code

Fact.usp_SaleLargeRowStorePopulateTable

use [WideWorldImportersDW]
go

if object_id('[Fact].[usp_SaleLargeRowStorePopulateTable]') is null
begin

    exec('create procedure [Fact].[usp_SaleLargeRowStorePopulateTable] as ')

end
go

alter procedure [Fact].[usp_SaleLargeRowStorePopulateTable]
(
      @EstimatedRowsFor2012 bigint = 12000000
    , @defrag bit = 0
)
as

begin


    DECLARE @OrderCounter bigint = 0;
    DECLARE @NumberOfSalesPerDay bigint
    DECLARE @DateCounter date;
    DECLARE @DateCounterBegin date
    DECLARE @DateCounterEnd date
    DECLARE @StartingSaleKey bigint;
    DECLARE @MaximumSaleKey bigint = (SELECT MAX([Sale Key]) FROM Fact.Sale);
    DECLARE @OutputCounter varchar(20);

    DECLARE @LineageKey int

    DECLARE @iCycle int
    DECLARE @lNumberofRecordsDeleted bigint
    DECLARE @lNumberofRecordsDeletedTotal bigint

    DECLARE @lNumberofRecordsAdded   bigint

    DECLARE @CHAR_TAB  varchar(10)

    DECLARE @tableDestination sysname

    set @CHAR_TAB = char(9);
    set @DateCounter = '20120101';
    set @DateCounterEnd = '20121231'

    set @DateCounterBegin = @DateCounter;

    set @MaximumSaleKey 
            = (
                SELECT MAX([Sale Key]) 
                FROM   Fact.Sale
              );

    set @LineageKey =1

    set @NumberOfSalesPerDay   = @EstimatedRowsFor2012 / 365;


    BEGIN


        SET NOCOUNT ON;
        SET XACT_ABORT ON;

        SET @tableDestination = '[Fact].[SaleLargeRowStore]';
        set @DateCounterEnd = '20121231'

        PRINT 'EstimatedRowsFor2012 Total '
                + cast(@EstimatedRowsFor2012 as varchar(30));

        PRINT 'Targeting ' 
                + CAST(@NumberOfSalesPerDay AS varchar(20)) 
                + ' sales per day.'
                ;

        --added by dadeniji 2017-08-23 7:01 AM
        PRINT 'DateCounter ' + convert(varchar(30), @DateCounter, 100)
        PRINT 'DateCounterMax ' + convert(varchar(30), '20121231', 100)

        IF @NumberOfSalesPerDay > 50000
        BEGIN
            PRINT 'WARNING: Limiting sales to 40000 per day';
            SET @NumberOfSalesPerDay = 50000;
        END;

        set @lNumberofRecordsDeleted = 1
        set @iCycle = 0
        set @lNumberofRecordsDeletedTotal = 0

        while (@lNumberofRecordsDeleted != 0)
        begin

            set @iCycle = @iCycle + 1

            delete top ( 10000)
            from    [Fact].[SaleLargeRowStore]
            where  [Invoice Date Key] <= @DateCounterEnd set @lNumberofRecordsDeleted = @@ROWCOUNT set @lNumberofRecordsDeletedTotal = @lNumberofRecordsDeletedTotal + @lNumberofRecordsDeleted PRINT @CHAR_TAB + cast(@iCycle as varchar(10)) + ') ' + 'Pruning records prior to ' + convert(varchar(30), @DateCounterEnd, 100) + ' Number of record(s) removed is ' + cast(@lNumberofRecordsDeleted as varchar(10)) + ' :: Total thus far ' + cast(@lNumberofRecordsDeletedTotal as varchar(10)) + '- TS is ' + convert(varchar(30), getdate(), 100) end if not exists ( select * from [Fact].[SaleLargeRowStore] where [Invoice Date Key] > @DateCounterEnd
        )
        begin

    
            PRINT 'Adding base data Fact.Sale - those greater than '  
                    + convert(varchar(30), @DateCounterEnd, 100)

            set identity_insert [Fact].[SaleLargeRowStore] on 

            insert into [Fact].[SaleLargeRowStore]
                (
                      [Sale Key]
                    , [City Key]
                    , [Customer Key]
                    , [Bill To Customer Key]
                    , [Stock Item Key]

                    , [Invoice Date Key]
                    , [InvoiceDateKeyYear]
                                        
                    , [Delivery Date Key]
                    , [DeliveryDateKeyYear]
                    
                    , [Salesperson Key]
                    , [WWI Invoice ID]
                    , [Description]
                    , Package
                    , Quantity
                    , [Unit Price]
                    , [Tax Rate]
                    , [Total Excluding Tax]
                    , [Tax Amount]
                    , Profit
                    , [Total Including Tax]
                    , [Total Dry Items]
                    , [Total Chiller Items]
                    , [Lineage Key]
                )

            select 
                      [Sale Key]
                    , [City Key]
                    , [Customer Key]
                    , [Bill To Customer Key]
                    , [Stock Item Key]

                    , [Invoice Date Key]
                    , [InvoiceDateKeyYear]
                        = datepart(year, [Invoice Date Key])

                    , [Delivery Date Key]
                    , [DeliveryDateKeyYear]
                        = datepart(year, [Delivery Date Key])

                    , [Salesperson Key]
                    , [WWI Invoice ID]
                    , [Description]
                    , Package
                    , Quantity
                    , [Unit Price]
                    , [Tax Rate]
                    , [Total Excluding Tax]
                    , [Tax Amount]
                    , Profit
                    , [Total Including Tax]
                    , [Total Dry Items]
                    , [Total Chiller Items]
                    , [Lineage Key]

            from   [Fact].[Sale]
    
            where  [Invoice Date Key] > @DateCounterEnd

            set @lNumberofRecordsAdded = @@ROWCOUNT


            set identity_insert [Fact].[SaleLargeRowStore] off
                
            PRINT 'Added base data Fact.Sale - ' 
                    + ' those greater than '  
                    + convert(varchar(30), @DateCounterEnd, 100)
                    + ' - Number of record(s) inserted during Initial loading is ' 
                    + cast(@lNumberofRecordsAdded as varchar(10))
                    + '- TS is '
                    + convert(varchar(30), getdate(), 100)

        end

    
        set @iCycle = 0;

        set identity_insert [Fact].[SaleLargeRowStore] off
    
        WHILE @DateCounter < '20121231' BEGIN SET @OutputCounter = CONVERT(varchar(20), @DateCounter, 112); --RAISERROR(@OutputCounter, 0, 1) WITH NOWAIT; -- PRINT 'Processing entry for ' + @OutputCounter SET @StartingSaleKey = @MaximumSaleKey - @NumberOfSalesPerDay - FLOOR(RAND() * 20000); SET @OrderCounter = 0; set @iCycle = @iCycle + 1 INSERT [Fact].[SaleLargeRowStore] WITH (TABLOCK) ( [City Key] , [Customer Key] , [Bill To Customer Key] , [Stock Item Key] , [Invoice Date Key] , [InvoiceDateKeyYear] , [Delivery Date Key] , [DeliveryDateKeyYear] , [Salesperson Key] , [WWI Invoice ID] , [Description] , Package , Quantity , [Unit Price] , [Tax Rate] , [Total Excluding Tax] , [Tax Amount] , Profit , [Total Including Tax] , [Total Dry Items] , [Total Chiller Items] , [Lineage Key] ) SELECT TOP(@NumberOfSalesPerDay) [City Key] , [Customer Key] , [Bill To Customer Key] , [Stock Item Key] , @DateCounter as [Invoice Date Key] , [InvoiceDateKeyYer] = datepart(year, @DateCounter) , [Delivery Date Key] = DATEADD(day, 1, @DateCounter) , [DeliveryDateKeyYer] = datepart ( year , DATEADD ( day , 1 , @DateCounter ) ) , [Salesperson Key] , [WWI Invoice ID] , [Description] , Package , Quantity , [Unit Price] , [Tax Rate] , [Total Excluding Tax] , [Tax Amount] , Profit = tblFS.[Profit] + ( tblFS.[Profit] * rand ( tblFS.[City Key] * tblFS.[Unit Price] ) ) , [Total Including Tax] , [Total Dry Items] , [Total Chiller Items] , @LineageKey FROM Fact.Sale tblFS WHERE tblFS.[Sale Key] > @StartingSaleKey
        
            and   tblFS.[Invoice Date Key] >='2013-01-01'
        
            ORDER BY 
                    [Sale Key];

            set @lNumberofRecordsAdded = @@ROWCOUNT
        
            PRINT @CHAR_TAB
                    + cast(@iCycle as varchar(10)) 
                    + ' - '
                    + 'Day : '
                    + convert(varchar(30), @DateCounter, 101) 
                    + ' - Number of record(s) added to [Fact].[SaleLargeRowStore] is ' 
                    + ' ' + cast(@lNumberofRecordsAdded as varchar(10))
                    + '- TS is '
                    + convert(varchar(30), getdate(), 100)

            SET @DateCounter = DATEADD(day, 1, @DateCounter);

        END;
    
    
        set identity_insert [Fact].[SaleLargeRowStore] off


        if (@defrag = 0)
        begin


            print @CHAR_TAB
                 + 'Reorg Index [Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
                 + convert(varchar(30), getdate(), 100)
                 +  '... '

            ALTER INDEX [PK_Fact_SaleLargeRowStore]
                on [Fact].[SaleLargeRowStore] REORGANIZE
                ;

            print @CHAR_TAB
                 + 'Re-orged Index [Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
                 + convert(varchar(30), getdate(), 100)

            print @CHAR_TAB
                 + 'Update Statistics on Index [Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
                 + convert(varchar(30), getdate(), 100)
                 + ' ....'

            update statistics [Fact].[SaleLargeRowStore] [PK_Fact_SaleLargeRowStore]
                with FULLSCAN;

            print @CHAR_TAB
                 + 'Updated Statistics on Index [Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
                 + convert(varchar(30), getdate(), 100)
                 + ' ....'


        end
        else if (@defrag = 1)
        begin
        

            print @CHAR_TAB
                 + 'Rebuilding Index [Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
                 + convert(varchar(30), getdate(), 100)
                 +  '... '

            ALTER INDEX [PK_Fact_SaleLargeRowStore]
                on [Fact].[SaleLargeRowStore] REBUILD
                ;

            print @CHAR_TAB
                 + 'Rebuilt Index [Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
                 + convert(varchar(30), getdate(), 100)

        end     

    END

    dbcc shrinkfile('WWI_Log') with no_infomsgs;

END

go


Fact.usp_SaleLargeColumnStoreClusteredPopulateTable

use [WideWorldImportersDW]
go

if object_id('[Fact].[usp_SaleLargeColumnStoreClusteredPopulateTable]') is null
begin

	exec('create procedure [Fact].[usp_SaleLargeColumnStoreClusteredPopulateTable] as ')

end
go

alter procedure [Fact].[usp_SaleLargeColumnStoreClusteredPopulateTable]
(
	  @EstimatedRowsFor2012 bigint = 12000000
	, @defrag bit = 0
)
as

begin


	DECLARE @OrderCounter bigint = 0;
	DECLARE @NumberOfSalesPerDay bigint;
	DECLARE @DateCounter date ;
	DECLARE @DateCounterBegin date
	DECLARE @DateCounterEnd date
	DECLARE @StartingSaleKey bigint;
	DECLARE @MaximumSaleKey bigint;
	DECLARE @OutputCounter varchar(20);

	DECLARE @LineageKey	int

	DECLARE @iCycle int
	DECLARE @lNumberofRecordsDeleted bigint
	DECLARE @lNumberofRecordsDeletedTotal bigint

	DECLARE @lNumberofRecordsAdded	 bigint

	DECLARE @CHAR_TAB  varchar(10)

	DECLARE @tableDestination sysname

	set @CHAR_TAB = char(9);
	set @DateCounter = '20120101';
	set @DateCounterEnd = '20121231'

	set @DateCounterBegin = @DateCounter;

	set @MaximumSaleKey 
			= (
				SELECT MAX([Sale Key]) 
				FROM   Fact.Sale
			  );

	set @LineageKey	=1

	set @NumberOfSalesPerDay   = @EstimatedRowsFor2012 / 365;


	BEGIN


		SET NOCOUNT ON;
		SET XACT_ABORT ON;

		SET @tableDestination = '[Fact].[SaleLargeColumnStoreClustered]';
		set @DateCounterEnd = '20121231'

		PRINT 'EstimatedRowsFor2012 Total '
				+ cast(@EstimatedRowsFor2012 as varchar(30));

		PRINT 'Targeting ' 
				+ CAST(@NumberOfSalesPerDay AS varchar(20)) 
				+ ' sales per day.'
				;

		--added by dadeniji 2017-08-23 7:01 AM
		PRINT 'DateCounter ' + convert(varchar(30), @DateCounter, 100)
		PRINT 'DateCounterMax ' + convert(varchar(30), '20121231', 100)

		IF @NumberOfSalesPerDay > 50000
		BEGIN
			PRINT 'WARNING: Limiting sales to 40000 per day';
			SET @NumberOfSalesPerDay = 50000;
		END;

		set @lNumberofRecordsDeleted = 1
		set @iCycle = 0
		set @lNumberofRecordsDeletedTotal = 0

		print @CHAR_TAB
			+ 'Pruning data '
			+ ' Begin Date ' + convert ( varchar(30), @DateCounter, 100)
			+ ' End Date '+   convert ( varchar(30), @DateCounterEnd, 100)
			+ ' from ' + @tableDestination
			+ convert(varchar(30), getdate(), 100)
			+  '... '

		while (@lNumberofRecordsDeleted != 0)
		begin

			set @iCycle = @iCycle + 1

			delete top ( 10000)
			from   [Fact].[SaleLargeColumnStoreClustered] 
			where  [Invoice Date Key] <= @DateCounterEnd set @lNumberofRecordsDeleted = @@ROWCOUNT set @lNumberofRecordsDeletedTotal = @lNumberofRecordsDeletedTotal + @lNumberofRecordsDeleted PRINT @CHAR_TAB + cast(@iCycle as varchar(10)) + ') ' + 'Pruning records prior to ' + convert(varchar(30), @DateCounterEnd, 100) + ' Number of record(s) removed is ' + cast(@lNumberofRecordsDeleted as varchar(10)) + ' :: Total thus far ' + cast(@lNumberofRecordsDeletedTotal as varchar(10)) end print @CHAR_TAB + 'Pruned data ' + ' Begin Date ' + convert ( varchar(30), @DateCounter, 100) + ' End Date '+ convert ( varchar(30), @DateCounterEnd, 100) + ' from ' + @tableDestination + '. TS is ' + convert(varchar(30), getdate(), 100) if not exists ( select * from [Fact].[SaleLargeColumnStoreClustered] where [Invoice Date Key] > @DateCounterEnd
		)
		begin

	
			PRINT 'Adding base data Fact.Sale - those greater than '  
					+ convert(varchar(30), @DateCounterEnd, 100)
					+ '. TS is '
					+ convert(varchar(30), getdate(), 100)

			set identity_insert [Fact].[SaleLargeColumnStoreClustered]  on 

			insert into [Fact].[SaleLargeColumnStoreClustered] 
				(
					  [Sale Key]
					, [City Key]
					, [Customer Key]
					, [Bill To Customer Key]
					, [Stock Item Key]

					, [Invoice Date Key]
					, [InvoiceDateKeyYear]

					, [Delivery Date Key]
					, [DeliveryDateKeyYear]

					, [Salesperson Key]
					, [WWI Invoice ID]
					, [Description]
					, Package
					, Quantity
					, [Unit Price]
					, [Tax Rate]
					, [Total Excluding Tax]
					, [Tax Amount]
					, Profit
					, [Total Including Tax]
					, [Total Dry Items]
					, [Total Chiller Items]
					, [Lineage Key]
				)

			select 
					  [Sale Key]
					, [City Key]
					, [Customer Key]
					, [Bill To Customer Key]
					, [Stock Item Key]

					, [Invoice Date Key]
					, [InvoiceDateKeyYer]
						= datepart(year, [Invoice Date Key])

					, [Delivery Date Key]
					, [DeliveryDateKeyYear]
						= datepart(year, [Delivery Date Key])

					, [Salesperson Key]
					, [WWI Invoice ID]
					, [Description]
					, Package
					, Quantity
					, [Unit Price]
					, [Tax Rate]
					, [Total Excluding Tax]
					, [Tax Amount]
					, Profit
					, [Total Including Tax]
					, [Total Dry Items]
					, [Total Chiller Items]
					, [Lineage Key]

			from   [Fact].[Sale]
	
			where  [Invoice Date Key] > @DateCounterEnd

			set @lNumberofRecordsAdded = @@ROWCOUNT


			set identity_insert [Fact].[SaleLargeColumnStoreClustered]  off
				
			PRINT 'Added base data Fact.Sale - ' 
					+ ' those greater than '  
					+ convert(varchar(30), @DateCounterEnd, 100)
					+ ' - Number of record(s) inserted during Initial loading is ' 
					+ cast(@lNumberofRecordsAdded as varchar(10))
					+ '- TS is '
					+ convert(varchar(30), getdate(), 100)

		end

	
		set @iCycle = 0;

		set identity_insert [Fact].[SaleLargeColumnStoreClustered]  off

		print @CHAR_TAB
				 + 'Adding data '
				 + ' Begin Date ' + convert ( varchar(30), @DateCounter, 100)
				 + ' End Date '+   convert ( varchar(30), @DateCounterEnd, 100)
				 + ' into ' + @tableDestination
				 + ' - TS :- ' + convert(varchar(30), getdate(), 100)
				 +  '... '
	
		WHILE @DateCounter < '20121231' BEGIN SET @OutputCounter = CONVERT(varchar(20), @DateCounter, 112); --RAISERROR(@OutputCounter, 0, 1) WITH NOWAIT; -- PRINT 'Processing entry for ' + @OutputCounter SET @StartingSaleKey = @MaximumSaleKey - @NumberOfSalesPerDay - FLOOR(RAND() * 20000); SET @OrderCounter = 0; set @iCycle = @iCycle + 1 INSERT [Fact].[SaleLargeColumnStoreClustered] WITH (TABLOCK) ( [City Key] , [Customer Key] , [Bill To Customer Key] , [Stock Item Key] , [Invoice Date Key] , [InvoiceDateKeyYear] , [Delivery Date Key] , [DeliveryDateKeyYear] , [Salesperson Key] , [WWI Invoice ID] , [Description] , Package , Quantity , [Unit Price] , [Tax Rate] , [Total Excluding Tax] , [Tax Amount] , Profit , [Total Including Tax] , [Total Dry Items] , [Total Chiller Items] , [Lineage Key] ) SELECT TOP(@NumberOfSalesPerDay) [City Key] , [Customer Key] , [Bill To Customer Key] , [Stock Item Key] , [Invoice Date Key] = @DateCounter , [InvoiceDateKeyYear] = datepart(year, [Invoice Date Key]) , [Delivery Date Key] = DATEADD(day, 1, @DateCounter) , [DeliveryDateKeyYear] = datepart(year, [Delivery Date Key]) , [Salesperson Key] , [WWI Invoice ID] , [Description] , Package , Quantity , [Unit Price] , [Tax Rate] , [Total Excluding Tax] , [Tax Amount] , Profit = tblFS.[Profit] + ( tblFS.[Profit] * rand ( tblFS.[City Key] * tblFS.[Unit Price] ) ) , [Total Including Tax] , [Total Dry Items] , [Total Chiller Items] , @LineageKey FROM Fact.Sale tblFS WHERE tblFS.[Sale Key] > @StartingSaleKey
		
			and   tblFS.[Invoice Date Key] >='2013-01-01'
		
			ORDER BY 
					[Sale Key];

			set @lNumberofRecordsAdded = @@ROWCOUNT
		
			PRINT @CHAR_TAB
					+ cast(@iCycle as varchar(10)) 
					+ ' - '
					+ 'Day : '
					+ convert(varchar(30), @DateCounter, 101) 
					+ ' - Number of record(s) added to '
					+ @tableDestination 
					+ ' ' + cast(@lNumberofRecordsAdded as varchar(10))
					+ ' TS :- ' + convert(varchar(30), getdate(), 100)

			SET @DateCounter = DATEADD(day, 1, @DateCounter);

		END;
	

		print @CHAR_TAB
				 + 'Added data '
				 + ' Begin Date ' + convert ( varchar(30), @DateCounter, 100)
				 + ' End Date '+   convert ( varchar(30), @DateCounterEnd, 100)
				 + ' into ' + @tableDestination
				 + ' TS :- ' + convert(varchar(30), getdate(), 100)
				 +  ''
	
		set identity_insert [Fact].[SaleLargeColumnStoreClustered]  off


		if (@defrag = 0)
		begin


			print @CHAR_TAB
				 + 'Reorg Index '
				 + '[Fact].[SaleLargeColumnStoreClustered].[INDX_FACT_SaleLargeColumnStoreClustered] '
				 + convert(varchar(30), getdate(), 100)
				 +  '... '

			ALTER INDEX [INDX_FACT_SaleLargeColumnStoreClustered]
				on [Fact].[SaleLargeColumnStoreClustered]  REORGANIZE
				;

			print @CHAR_TAB
				 + 'Re-orged Index '
				 + '[Fact].[SaleLargeColumnStoreClustered].[INDX_FACT_SaleLargeColumnStoreClustered] '
				 + convert(varchar(30), getdate(), 100)

			print @CHAR_TAB
				 + 'Update Statistics on Index ' 
				 + ' [Fact].[SaleLargeColumnStoreClustered].[INDX_FACT_SaleLargeColumnStoreClustered]  '
				 + convert(varchar(30), getdate(), 100)
				 + ' ....'

			update statistics [Fact].[SaleLargeColumnStoreClustered] 
					[INDX_FACT_SaleLargeColumnStoreClustered] 
				with FULLSCAN;

			print @CHAR_TAB
				 + 'Updated Statistics on Index '
				 + ' [Fact].[SaleLargeColumnStoreClustered] [INDX_FACT_SaleLargeColumnStoreClustered] '
				 + convert(varchar(30), getdate(), 100)
				 + ' ....'


		end
		else if (@defrag = 1)
		begin
		

			print @CHAR_TAB
				 + 'Rebuilding Index '
				 + '[Fact].[SaleLargeRowStore].[PK_Fact_SaleLargeRowStore] '
				 + convert(varchar(30), getdate(), 100)
				 +  '... '

			ALTER INDEX [INDX_FACT_SaleLargeColumnStoreClustered]
				on  [Fact].[SaleLargeColumnStoreClustered] REBUILD
				;

			print @CHAR_TAB
				 + 'Rebuilt Index '
				 + ' [Fact].[SaleLargeColumnStoreClustered].[INDX_FACT_SaleLargeColumnStoreClustered] '
				 + convert(varchar(30), getdate(), 100)
		

		end		
	END

	dbcc shrinkfile('WWI_Log') with no_infomsgs;

END

go

Fact.usp_SaleLargeColumnStoreNonClusteredPopulateTable

use [WideWorldImportersDW]
go

if object_id('[Fact].[usp_SaleLargeColumnStoreNonClusteredPopulateTable]') is null
begin

	exec('create procedure [Fact].[usp_SaleLargeColumnStoreNonClusteredPopulateTable] as ')

end
go

alter procedure [Fact].[usp_SaleLargeColumnStoreNonClusteredPopulateTable]
(
	  @EstimatedRowsFor2012 bigint = 12000000
	, @defrag bit = 0
)
as

begin


	DECLARE @OrderCounter bigint = 0;
	DECLARE @NumberOfSalesPerDay bigint;
	DECLARE @DateCounter date;
	DECLARE @DateCounterBegin date
	DECLARE @DateCounterEnd date
	DECLARE @StartingSaleKey bigint;
	DECLARE @MaximumSaleKey bigint;
	DECLARE @OutputCounter varchar(20);

	DECLARE @LineageKey	int

	DECLARE @iCycle int
	DECLARE @lNumberofRecordsDeleted bigint
	DECLARE @lNumberofRecordsDeletedTotal bigint

	DECLARE @lNumberofRecordsAdded	 bigint
	DECLARE @lNumberofRecordsAddedTotal bigint

	DECLARE @CHAR_TAB  varchar(10)

	DECLARE @tableDestination sysname

	set @CHAR_TAB = char(9);
	set @DateCounter = '20120101';
	set @DateCounterEnd = '20121231'

	set @DateCounterBegin = @DateCounter;

	set @MaximumSaleKey 
			= (
				SELECT MAX([Sale Key]) 
				FROM   Fact.Sale
			  );

	set @LineageKey	=1

	set @NumberOfSalesPerDay   = @EstimatedRowsFor2012 / 365;



	set @lNumberofRecordsAddedTotal = 0

	BEGIN


		SET NOCOUNT ON;
		SET XACT_ABORT ON;

		-- exec sp_helpindex '[Fact].[SaleLargeColumnStoreNonClustered]'
		SET @tableDestination = '[Fact].[SaleLargeColumnStoreNonClustered]';
		set @DateCounterEnd = '20121231'

		PRINT 'EstimatedRowsFor2012 Total '
				+ cast(@EstimatedRowsFor2012 as varchar(30));

		PRINT 'Targeting ' 
				+ CAST(@NumberOfSalesPerDay AS varchar(20)) 
				+ ' sales per day.'
				;

		--added by dadeniji 2017-08-23 7:01 AM
		PRINT 'DateCounter ' + convert(varchar(30), @DateCounter, 100)
		PRINT 'DateCounterMax ' + convert(varchar(30), '20121231', 100)

		IF @NumberOfSalesPerDay > 50000
		BEGIN
			PRINT 'WARNING: Limiting sales to 40000 per day';
			SET @NumberOfSalesPerDay = 50000;
		END;

		set @lNumberofRecordsDeleted = 1
		set @iCycle = 0
		set @lNumberofRecordsDeletedTotal = 0

		/*
			Msg 35330, Level 15, State 1, Procedure usp_SaleLargeColumnStoreNonClusteredPopulateTable, Line 119 [Batch Start Line 2]
			DELETE statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, and then rebuilding the columnstore index after DELETE has completed.
		*/

		print @CHAR_TAB
			+ 'Disabling Index '
			+ '[Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_Customer_Profit] '
			+ convert(varchar(30), getdate(), 100)
			+  '... '

		exec('
				ALTER INDEX ALL
				on   [Fact].[SaleLargeColumnStoreNonClustered] DISABLE
				;
			')


		--ALTER INDEX [INDX_FACT_Customer_Profit]
		ALTER INDEX ALL
				on   [Fact].[SaleLargeColumnStoreNonClustered] DISABLE
				;

		/*
		ALTER INDEX [INDX_InvoiceDateKeyYear_DeliveryDateKeyYear]
				on   [Fact].[SaleLargeColumnStoreNonClustered] DISABLE
				;
		*/

		print @CHAR_TAB
			+ 'Disabled Index '
			+ '[Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_Customer_Profit] '
			+ convert(varchar(30), getdate(), 100)


		print @CHAR_TAB
			+ 'Pruning data '
			+ ' Begin Date ' + convert ( varchar(30), @DateCounter, 100)
			+ ' End Date '+   convert ( varchar(30), @DateCounterEnd, 100)
			+ ' from ' + @tableDestination
			+ convert(varchar(30), getdate(), 100)
			+  '... '

		while (@lNumberofRecordsDeleted != 0)
		begin

			set @iCycle = @iCycle + 1

			delete top ( 10000)
			from   [Fact].[SaleLargeColumnStoreNonClustered] 
			where  [Invoice Date Key] <= @DateCounterEnd set @lNumberofRecordsDeleted = @@ROWCOUNT set @lNumberofRecordsDeletedTotal = @lNumberofRecordsDeletedTotal + @lNumberofRecordsDeleted PRINT @CHAR_TAB + cast(@iCycle as varchar(10)) + ') ' + 'Pruning records prior to ' + convert(varchar(30), @DateCounterEnd, 100) + ' Number of record(s) removed is ' + cast(@lNumberofRecordsDeleted as varchar(10)) + ' :: Total thus far ' + cast(@lNumberofRecordsDeletedTotal as varchar(10)) end print @CHAR_TAB + 'Pruned data ' + ' Begin Date ' + convert ( varchar(30), @DateCounter, 100) + ' End Date '+ convert ( varchar(30), @DateCounterEnd, 100) + ' from ' + @tableDestination + '. TS is ' + convert(varchar(30), getdate(), 100) if not exists ( select * from [Fact].[SaleLargeColumnStoreNonClustered] where [Invoice Date Key] > @DateCounterEnd
		)
		begin

	
			PRINT 'Adding base data Fact.Sale - those greater than '  
					+ convert(varchar(30), @DateCounterEnd, 100)
					+ '. TS is '
					+ convert(varchar(30), getdate(), 100)

			set identity_insert [Fact].[SaleLargeColumnStoreNonClustered]  on 

			insert into [Fact].[SaleLargeColumnStoreNonClustered] 
				(
					  [Sale Key]
					, [City Key]
					, [Customer Key]
					, [Bill To Customer Key]
					, [Stock Item Key]

					, [Invoice Date Key]
					, [InvoiceDateKeyYear]

					, [Delivery Date Key]
					, [DeliveryDateKeyYear]

					, [Salesperson Key]
					, [WWI Invoice ID]
					, [Description]
					, Package
					, Quantity
					, [Unit Price]
					, [Tax Rate]
					, [Total Excluding Tax]
					, [Tax Amount]
					, Profit
					, [Total Including Tax]
					, [Total Dry Items]
					, [Total Chiller Items]
					, [Lineage Key]
				)

			select 
					  [Sale Key]
					, [City Key]
					, [Customer Key]
					, [Bill To Customer Key]
					, [Stock Item Key]

					, [Invoice Date Key]
					, [InvoiceDateKeyYear]
						= datepart(year, [Invoice Date Key])

					, [Delivery Date Key]

					, [DeliveryDateKeyYear]
						= datepart(year, [Delivery Date Key])

					, [Salesperson Key]
					, [WWI Invoice ID]
					, [Description]
					, Package
					, Quantity
					, [Unit Price]
					, [Tax Rate]
					, [Total Excluding Tax]
					, [Tax Amount]
					, Profit
					, [Total Including Tax]
					, [Total Dry Items]
					, [Total Chiller Items]
					, [Lineage Key]

			from   [Fact].[Sale]
	
			where  [Invoice Date Key] > @DateCounterEnd

			set @lNumberofRecordsAdded = @@ROWCOUNT


			set identity_insert [Fact].[SaleLargeColumnStoreNonClustered]  off
				
			PRINT 'Added base data Fact.Sale - ' 
					+ ' those greater than '  
					+ convert(varchar(30), @DateCounterEnd, 100)
					+ ' - Number of record(s) inserted during Initial loading is ' 
					+ cast(@lNumberofRecordsAdded as varchar(10))
					+ '- TS is '
					+ convert(varchar(30), getdate(), 100)

		end

	
		set @iCycle = 0;

		set identity_insert [Fact].[SaleLargeColumnStoreNonClustered]  off

		print @CHAR_TAB
				 + 'Adding data '
				 + ' Begin Date ' + convert ( varchar(30), @DateCounter, 100)
				 + ' End Date '+   convert ( varchar(30), @DateCounterEnd, 100)
				 + ' into ' + @tableDestination
				 + ' - TS :- ' + convert(varchar(30), getdate(), 100)
				 +  '... '
	
		WHILE @DateCounter < '20121231' BEGIN SET @OutputCounter = CONVERT(varchar(20), @DateCounter, 112); --RAISERROR(@OutputCounter, 0, 1) WITH NOWAIT; -- PRINT 'Processing entry for ' + @OutputCounter SET @StartingSaleKey = @MaximumSaleKey - @NumberOfSalesPerDay - FLOOR(RAND() * 20000); SET @OrderCounter = 0; set @iCycle = @iCycle + 1 INSERT [Fact].[SaleLargeColumnStoreNonClustered] WITH (TABLOCK) ( [City Key] , [Customer Key] , [Bill To Customer Key] , [Stock Item Key] , [Invoice Date Key] , [InvoiceDateKeyYear] , [Delivery Date Key] , [DeliveryDateKeyYear] , [Salesperson Key] , [WWI Invoice ID] , [Description] , Package , Quantity , [Unit Price] , [Tax Rate] , [Total Excluding Tax] , [Tax Amount] , Profit , [Total Including Tax] , [Total Dry Items] , [Total Chiller Items] , [Lineage Key] ) SELECT TOP(@NumberOfSalesPerDay) [City Key] , [Customer Key] , [Bill To Customer Key] , [Stock Item Key] , [Invoice Date Key] = @DateCounter , [InvoiceDateKeyYear] = datepart(year, @DateCounter) , [Delivery Date Key] = DATEADD(day, 1, @DateCounter) , [DeliveryDateKeyYer] = datepart ( year , DATEADD ( day , 1 , @DateCounter ) ) , [Salesperson Key] , [WWI Invoice ID] , [Description] , Package , Quantity , [Unit Price] , [Tax Rate] , [Total Excluding Tax] , [Tax Amount] , Profit = tblFS.[Profit] + ( tblFS.[Profit] * rand ( tblFS.[City Key] * tblFS.[Unit Price] ) ) , [Total Including Tax] , [Total Dry Items] , [Total Chiller Items] , @LineageKey FROM Fact.Sale tblFS WHERE tblFS.[Sale Key] > @StartingSaleKey
		
			and   tblFS.[Invoice Date Key] >='2013-01-01'
		
			ORDER BY 
					[Sale Key];

			set @lNumberofRecordsAdded = @@ROWCOUNT

			set @lNumberofRecordsAddedTotal = @lNumberofRecordsAddedTotal
												+ @lNumberofRecordsAdded
		
			PRINT @CHAR_TAB
					+ cast(@iCycle as varchar(10)) 
					+ ' - '
					+ 'Day : '
					+ convert(varchar(30), @DateCounter, 101) 
					+ ' - Number of record(s) added to '
					+ @tableDestination 
					+ ' ' + cast(@lNumberofRecordsAdded as varchar(10))
					+ ' TS :- ' + convert(varchar(30), getdate(), 100)

			SET @DateCounter = DATEADD(day, 1, @DateCounter);

		END;
	

		print @CHAR_TAB
				 + 'Added data '
				 + ' Begin Date ' + convert ( varchar(30), @DateCounter, 100)
				 + ' End Date '+   convert ( varchar(30), @DateCounterEnd, 100)
				 + ' into ' + @tableDestination
				 + ' TS :- ' + convert(varchar(30), getdate(), 100)
				 +  ''
	

		print @CHAR_TAB
				 + cast(@lNumberofRecordsAddedTotal as varchar(10))
				 + ' records added '
				 + ' into ' + @tableDestination
				 + ' TS :- ' + convert(varchar(30), getdate(), 100)
				 +  ''
	
		set identity_insert [Fact].[SaleLargeColumnStoreNonClustered]  off


		if 
			(
				    ( @defrag = 0) 
				and ( 1= 0)
			)	 
		begin


			print @CHAR_TAB
				 + 'Rebuilding Index [Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_Customer_Profit] '
				 + convert(varchar(30), getdate(), 100)
				 +  '... '

			--ALTER INDEX [INDX_FACT_Customer_Profit]
			--ALTER INDEX [INDX_InvoiceDateKeyYear_DeliveryDateKeyYear]
			ALTER INDEX ALL
				on  [Fact].[SaleLargeColumnStoreNonClustered] REORGANIZE
				;

			print @CHAR_TAB
				 + 'Rebuilt Index [Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_Customer_Profit] '
				 + convert(varchar(30), getdate(), 100)

			print @CHAR_TAB
				 + 'Update Statistics on Index ' 
				 + ' [Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_SaleLargeColumnStoreClustered]  '
				 + convert(varchar(30), getdate(), 100)
				 + ' ....'

			update statistics [Fact].[SaleLargeColumnStoreNonClustered]
					 --[INDX_FACT_Customer_Profit]
					 --[INDX_InvoiceDateKeyYear_DeliveryDateKeyYear]
					 --ALL
				with FULLSCAN;

			print @CHAR_TAB
				 + 'Updated Statistics on Index '
				 +  ' [Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_SaleLargeColumnStoreClustered]  '
				 + convert(varchar(30), getdate(), 100)
				 + ' ....'


		end
		else if 
				(
					   ( @defrag = 1)
					or ( 1=1)
				)
		begin
		

			print @CHAR_TAB
				 + 'Rebuilding Index [Fact].[SaleLargeColumnStoreClustered].[INDX_FACT_Customer_Profit] '
				 + convert(varchar(30), getdate(), 100)
				 +  '... '

			--ALTER INDEX [INDX_FACT_Customer_Profit]
			--ALTER INDEX [INDX_InvoiceDateKeyYear_DeliveryDateKeyYear]
			ALTER INDEX ALL
				on   [Fact].[SaleLargeColumnStoreNonClustered] REBUILD
				;

			print @CHAR_TAB
				 + 'Rebuilt Index [Fact].[SaleLargeColumnStoreNonClustered].[INDX_FACT_Customer_Profit] '
				 + convert(varchar(30), getdate(), 100)
		

		end		
	END

	dbcc shrinkfile('WWI_Log') with no_infomsgs;

END

go


 

Invoke SP to load data

Invoke SP to load data into tables

Fact.usp_SaleLargeRowStorePopulateTable

use [WideWorldImportersDW]
go

/*
	truncate table [Fact].[SaleLargeRowStore]
*/
declare	 @EstimatedRowsFor2012 bigint
declare  @defrag bit 

set @EstimatedRowsFor2012 = 12000000
set  @defrag =1

exec [Fact].[usp_SaleLargeRowStorePopulateTable]
	  @EstimatedRowsFor2012 = @EstimatedRowsFor2012
	, @defrag = @defrag

Fact.SaleLargeColumnStoreClustered

use [WideWorldImportersDW]
go

/*
	truncate table [Fact].[SaleLargeColumnStoreClustered]
*/

declare	 @EstimatedRowsFor2012 bigint
declare  @defrag bit 

set @EstimatedRowsFor2012 = 12000000
set  @defrag =1

exec [Fact].[usp_SaleLargeColumnStoreClusteredPopulateTable]
	  @EstimatedRowsFor2012 = @EstimatedRowsFor2012
	, @defrag = @defrag


Fact.SaleLargeColumnStoreNonClustered


use [WideWorldImportersDW]
go

/*

	truncate table [Fact].[SaleLargeColumnStoreNonClustered]

*/

declare	 @EstimatedRowsFor2012 bigint
declare  @defrag bit

set @EstimatedRowsFor2012 = 12000000
set  @defrag =0


exec [Fact].[usp_SaleLargeColumnStoreNonClusteredPopulateTable]
	  @EstimatedRowsFor2012 = @EstimatedRowsFor2012
	, @defrag = @defrag


Metadata – Fact.Sales*

Number of Records

Code


set nocount on
go
set XACT_ABORT on
go

use [WideWorldImportersDW]
go

; with cteTable
(
      [id]
	, [table]
	, [objectID]
)
as
(
	select 
			  tblA.[id]
			, tblA.[name]
			, [objectID]
				 = object_id(tblA.[name])
	from  
		(

			select 1 as [id], '[Fact].[SaleLargeRowStore]' as [name]
    
			union

			select 2  as [id], '[Fact].[SaleLargeColumnStoreClustered]' as [name]
    
			union

			select 2 as [id], '[Fact].[SaleLargeColumnStoreNonClustered]' as [name]

		) tblA
) 
select
	      cteT.id

        , [table]
            =  tblSS.[name]
                + '.'
                + tblSO.[name]
 
        , [indexName]
            = tblSI.[name]
                     
        , [indexType]
          = tblSI.[type_desc]
 
        , [numberofRecords]
            = sum(tblSP.[rows])
 
        , numberofPagesData
            = sum(tblSPS.in_row_data_page_count)
 
        , numberofPagesLOB
            = sum(tblSPS.lob_used_page_count)
 
        , usedPageCount
            = sum(used_page_count)

        , reservedPageCount
            = sum(tblSPS.reserved_page_count)
 
        , [reservedPageInMB]
            = sum(tblSPS.reserved_page_count *1)  / 128

from   sys.schemas tblSS
 
inner join sys.objects tblSO
 
    on tblSS.schema_id = tblSO.schema_id
 
inner join sys.indexes tblSI
 
    on tblSO.object_id = tblSI.object_id
 
 
inner join sys.partitions tblSP
 
    on tblSI.object_id = tblSP.object_id
    and tblSI.index_id = tblSP.index_id
 
 
inner join sys.dm_db_partition_stats tblSPS
 
    on tblSP.object_id = tblSPS.object_id
    and tblSP.index_id = tblSPS.index_id
    and tblSP.partition_id = tblSPS.partition_id
 
 
inner join cteTable cteT
        on cteT.[objectID] = tblSO.object_id
 
group by
		  cteT.id
        , tblSS.[name]
        , tblSO.[name]
        , tblSI.index_id
        , tblSI.[name]
        , tblSI.[type_desc]
 
order by
	  cteT.id
        , tblSS.[name]
        , tblSO.[name]
        , tblSI.index_id


Output

Explanation

  1. We have above 12 million records in each of our targeted table ( Row Store  Clustered/ Column Store-Clustered / Column Store – NonClustered)
  2. Row Store Clustered
    • Clustered Index => 2.691 GB
    • Index on Clustered Key & Profit – 360 MB
  3. Column Store Clustered
    • 196 MB
  4. Column Stored Non Clustered
    • Heap => 2.687 GB
    • Column Store Index on “Invoice Date Year” & “Delivery Date Year” – 17 MB

 

Source Control

GitHub

Availed in Github here.

 

Summary

Clustered Column Store offers the best savings in terms of storage footprint.

We have about 12 million records which should hopefully be a good start for untarget queries.

Untargeted, that is, queries that we have not hard-crafted specific indexes for.

One thought on “WideWorldImportersDW – Fattening Up – fact.Sale

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s