Bigger Fact Table for Wide World Importers

Microsoft released a new sample database a couple of months back: Wide World Importers. It’s quite great: not every (unnecessary feature) is included but only features you’d actually use, lots of sample scripts are provided and – most importantly – you can generate data until the current date. One small drawback: it’s quite tiny. Especially the data warehouse is really small. The biggest table, Fact.Order, has about 266,000 rows and uses around 280MB on disk. Your numbers may vary, because I have generated data until the current date (12th of August 2016) and I generated data with more random samples per day. So most likely, other versions of WideWorldImportersDW might be even smaller. That’s right. Even smaller.

Since I want to have a sample database where I can create some decent clustered columnstore index (and where queries don’t return results in 3 milliseconds) I used the following query to generate a bigger Orders fact table. It keeps the same structure as the original one (the degenerate dimensions referring to the OLTP database remain the same), only the surrogate key is regenerated.

SELECT
	 [Order Key] = ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),-1)
	,[City Key]
	,[Customer Key]
	,[Stock Item Key]
	,[Order Date Key]
	,[Picked Date Key]
	,[Salesperson Key]
	,[Picker Key]
	,[WWI Order ID]
	,[WWI Backorder ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Total Including Tax]
	,[Lineage Key]
INTO [Fact].[Order_Big_CCI]
FROM [Fact].[Order]
CROSS JOIN
(SELECT * FROM SYS.columns WHERE object_id < 50) tmp

I used the following construct the generate the surrogate key:

ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

Since the ROW_NUMBER() window function needs an order by clause, we “trick” SQL Server by using SELECT NULL. This makes sure that SQL Server doesn’t attempt to sort the result set, which would be quite catastrophic with over 90 million rows. The ISNULL is added to make sure we have a non-nullable column (which helps if you want to add a primary key later on).

I use a CROSS JOIN to multiply the original fact table. You can adjust the WHERE clause to have either more or less rows. On my system, the query generates about 90 million rows in 4 minutes. It takes 18.5 GB on disk (make sure the size your data / log files properly before running the query, otherwise auto-growth kicks in).

One downside of using SELECT INTO is that it doesn’t create a clustered index on the table. If you have to add this later on, it can be considerate overhead. An alternative method would be to create an empty table first with indexes, then load the data into it.

------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂