Bigger Fact Table for Wide World Importers

Koen Verbeeck, 2016-08-22 (first published: 2016-08-12)

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads