How to design a data warehouse

Designing a Data Warehouse from the Ground Up Webinar Recording with Q & A

image Thank you to everyone that registered and attended my webinar Designing your Data Warehouse from the Ground Up webinar this past Tuesday. And I’d also like to give a special thanks to my good friend, Mitchell Pearson (b|t), for helping me present this webinar. We had a great time!

Also, thank to everyone that tuned into the live broadcast of the webinar on Periscope! I hope you enjoyed the unique perspective Periscope gives.

The good news is that the recordings are now available! Below you’ll find the watch the recording of the webinar at, the link to view the Periscope broadcast and then also my PowerPoint slide deck I used in the webinar.

View the Pragmatic Works webinar recording

View the Periscope broadcast recording on YouTube

View the PowerPoint slide deck

Questions and Answers

As with any webinar with an audience of 600 people, there were lots of questions! Unfortunately I just can’t answer all of them but I figured I’d answer a few here:

Q: the guy speaking sounds like Marco Rubio

A: That’s not a question and thanks? I guess?

Q: When approaching a new DW do you find it helpful to create bus matrix as you talk to the different business experts?

A: I find a bus matrix very helpful. Its a good way to visualize the grain of the fact tables and the dimensions that are and are not related to each fact table. If you’re wondering what a bus matrix is, read this.

Q: if you lose your product dimension, how do you rebuild the dimension with surrogate keys? you won’t know which sales corresponds to which product

A: If you lose a dimension and must rebuild it from scratch, you’ll either need to restore a back up or pray that you have a way to recreate history in your data warehouse. This is assuming that you have no other way to derive which sale relates to which product. Backups are important. 🙂

Q: What is your opinion on uniqueidentifiers in place of int for PK?

A: I would definitely go with an INT because SSAS is going to work better with integer data types. A GUID data type in SSAS will be treated as a string which will decrease performance. Just an FYI, here are the supported data types in SSAS. SSAS works best with single field, non null INT keys.

Q: For a DW for an online store – for the DimCustomer – would it be the ShipTo or the BillTo? Or would we need two DimCustomers?

A: In this scenario you’d have a single dimension for Customer but there would be two keys on the fact table: 1) ShipToCustomerSK and 2: BillToCustomerSK. Each key would have a FK relationship to the Customer dimension. This is what is known as a role-playing dimension. Look here to read more about role playing dimensions.


Thanks again to all the attendees! I hope you enjoyed the webinar and maybe even possibly learned something! Let me know what you thought of the webinar and if you have any further questions or feedback leave a comment down below. Thank you.

One thought on “Designing a Data Warehouse from the Ground Up Webinar Recording with Q & A”

Leave a Reply