From the course: Implementing a Data Warehouse SQL Server 2019
Star and snowflake schemas - SQL Server Tutorial
From the course: Implementing a Data Warehouse SQL Server 2019
Star and snowflake schemas
- [Instructor] The relationships between fact and dimension tables can take on two different arrangements in a data warehouse. These are named based off of their shape, either star or snowflake. Star designs are the preferred method of connecting dimension tables and fact tables. This type of design will relate a fact table at the center directly to any number of dimension tables in one to many relationships. This is possible when every dimension table is fully denormalized, or fully flattened, and all of the context about the dimension is included in the single table. Fact tables that follow the star pattern are simpler to create and only require the query engine to traverse a single join to find the context of a fact. If the design of the data warehouse includes dimension tables that relate to other dimension tables, then you have what's known as a snowflake design. These are to be avoided if possible because they introduce additional joins into the reporting and analysis process and are more difficult for the query engine and end user to navigate. This design pattern occurs when dimension tables retain some level of data normalization, which is typically carried over from the transactional OLTP database. We can take a look at the Wide World Importers database and explore its design by creating a database diagram. I'll expand the Wide World Importers data warehouse here and then right-click on Database Diagrams and choose New Database Diagram. I'm going to get this message here that says I don't have one or more of the support objects required. Just go ahead and press Yes and you'll be able to create a diagram. Now I want to take a look at the order fact table here. I'll go ahead and select it and press Add, and then I'll add in into the diagram here in the background. We can see which dimension tables it connects to by adding all of the dimension tables into the diagram, so I'll start up here at the very top and we'll add in the city dimension, the customer dimension, the date dimension, employee, the payment dimension, the stock item dimension, then supplier, and finally the transaction type dimension. Once all of the tables that are in the dimension schema, the ones in the dimension parentheses here, are added into our diagram we can go ahead and close the Add Table window. Now this diagram doesn't fit on my screen very well, so I'm going to right-click here, anywhere in the background area, and go down to Zoom, and I'm going to switch this over to 50%. That'll fit everything just a little bit better. We'll go ahead and zoom back in again in just a moment. Now I'm going to scroll down here and I'll find the order fact table, and you can see it's related to a number of other tables. These tables up here at the very top don't relate to that order fact table. They include the transaction type, supplier, and the payment method dimensions. So I'm going to right-click on each of these and remove them from the design diagram. Now we can be sure that we're visualizing all of the context that's available about our orders fact table. Let's go ahead and move these into position so I can see them all in one screen. I'll go ahead and drag this down and make it a little bit shorter, and we'll do the same thing with some of these other tables. Then I can zoom back into the diagram. At this point we can see that the order fact table uses a star design to all of the dimension tables since each one has a relationship directly to the fact table and none are connected to each other. Each of these dimension tables gives me additional context about the fact. So for instance, the city key here in the order fact table relates to the city key in the city dimension. This is where I can find out additional information about the order, for instance the city it came from, the country, the continent, and so on. Because each dimension table links directly to the fact table, it makes it easy to understand the full context of the orders. Let's go ahead and save this diagram now by clicking on the disc icon up here on the toolbar, and I'll call it Orders. That'll place a copy of it over here in the Database Diagrams folder and we can review it later if we need to.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.