Categories: SQL Server

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 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

View Comments

Recent Posts

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

5 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

1 week ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

1 week ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

2 weeks ago

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

3 weeks ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

1 month ago