While searching for a fairly large dataset to use for performance baselining, I was looking at the option of duplicating data in the Adventure Works database to match our needs.
In order to check for weak spots, I need at least 10M fact rows and preferably at least one dimension with approximately 1M members.
One approach would be to generate the cross product of all customers by first-, mid- and last name, this makes app. 11M records. These members could the be matched to an abitrary number of facts.
So, how to get an arbitrary number of fact rows:
- BEGIN TRANSACTION
- DECLARE @FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar(50), @SalesOrderNumber NVARCHAR(2) = ‘SO’
- DECLARE @MaritalStatus nchar(1)
- DECLARE @Gender nvarchar(1)
- DECLARE @RecordCount INT, @GeographyKey INT, @CustomerKey INT, @SONumber INT = 100000
- DECLARE customer_cursor CURSOR FAST_FORWARD
- FOR SELECT FirstName, MiddleName, LastName FROM dbo.AllCustomers
- OPEN customer_cursor
- FETCH NEXT FROM customer_cursor
- INTO @FirstName, @MiddleName, @LastName;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @RecordCount = FLOOR( CAST( DATEPART(MILLISECOND, GETDATE() ) AS DECIMAL ) / 4 )
- SET @GeographyKey =( SELECT TOP 1 GeographyKey FROM DimGeography ORDER BY NEWID() )
- SET @Gender =( SELECT TOP 1 Gender FROM DimCustomer WHERE FirstName = @FirstName ORDER BY NEWID() )
- SET @MaritalStatus =( SELECT TOP 1 MaritalStatus FROM DimCustomer ORDER BY NEWID() )
- SET @SONumber = @SONumber + 1
- INSERT INTO dbo.DimCustomer
- (
- CustomerAlternateKey
- , GeographyKey
- , FirstName
- , MiddleName
- , LastName
- , MaritalStatus
- , Gender
- , IsModified
- )
- VALUES
- (
- CAST( LEFT( newid(), 15 ) AS nvarchar(15) )
- , @GeographyKey
- , @FirstName
- , @MiddleName
- , @LastName
- , @MaritalStatus
- , @Gender
- , 1
- )
- SELECT @CustomerKey = SCOPE_IDENTITY()
- DECLARE @SQL VARCHAR(MAX) =
- ‘
- WITH Data AS
- (
- SELECT TOP ‘ + CAST( @RecordCount AS VARCHAR(4) ) + ‘ f.ProductKey, OrderDateKey, f.DueDateKey, f.ShipDateKey, ‘ + CAST( @CustomerKey AS VARCHAR ) + ‘ AS CustomerKey, f.PromotionKey, f.CurrencyKey, f.SalesTerritoryKey, ”’ + @SalesOrderNumber + CAST( @SONumber AS VARCHAR(10) ) + ”’ As SalesOrderNumber, f.SalesOrderLineNumber, f.RevisionNumber, f.OrderQuantity, f.UnitPrice, f.ExtendedAmount, f.UnitPriceDiscountPct, f.DiscountAmount, f.ProductStandardCost, f.TotalProductCost, f.SalesAmount, f.TaxAmt, f.Freight, f.CarrierTrackingNumber, f.CustomerPONumber FROM dbo.FactInternetSales f ORDER BY NEWID()
- )
- INSERT INTO dbo.FactInternetSales
- SELECT
- ProductKey
- , OrderDateKey
- , DueDateKey
- , ShipDateKey
- , CustomerKey
- , PromotionKey
- , CurrencyKey
- , SalesTerritoryKey
- , SalesOrderNumber
- , ( RANK() OVER ( ORDER BY NEWID() ) ) AS SalesOrderLineNumber
- , RevisionNumber
- , OrderQuantity
- , UnitPrice
- , ExtendedAmount
- , UnitPriceDiscountPct
- , DiscountAmount
- , ProductStandardCost
- , TotalProductCost
- , SalesAmount
- , TaxAmt
- , Freight
- , CarrierTrackingNumber
- , CustomerPONumber
- FROM Data’
- –PRINT (@SQL)
- EXEC(@SQL)
- FETCH NEXT FROM customer_cursor
- INTO @FirstName, @MiddleName, @LastName;
- END
- CLOSE customer_cursor;
- DEALLOCATE customer_cursor;
- COMMIT TRANSACTION
Running this script will generate between 1 and 275 million fact rows, depending on how long you let it run.
Alert:
This script is put together in the least amount of time possible, with absolutely no consideration towards performance.runs incredibly slow, so any hints on how to make this run in less than a week are highly appreciated! 😉
Alert (again)
This script will run for days!
