Creating a Star Schema with Data Academy: A Step-by-Step Guide

Transcription of the Video:

Once all the dimensions have been defined within a star schema, it's then necessary to define the fact table and link those Dimensions into that fact table. Our star schema is based around sales orders and sales order details, so we're going to base our fact table on the sales order detail table. We select that from the drop list and add it into our fact table definition.

This time, it's been prefixed with "ssor fact," so let's modify the fact detail. This time, we're going to name the target sticking with our convention, but order analysis. Can now begin adding our facts into this fact table. We start with sales order detail ID and we're immediately highlighted the fact that no primary key has been defined. This is indeed the primary key for this particular fact table.

The other facts we need are the line total and the order quantity since we want these summarized, we'll apply a sum aggregation to them. Select sum from the group function on the line total and again on the order quantity. Those are the basic facts that we're going to have within our fact table. We need to join in though a sales order header table so select joins sales order header.

Note again, data Academy is assumed. Sales order ID is the joining field which indeed it is. So, we've now entered our facts and we've also put our joins in. Now, we need to connect up our dimensions and to do that, we go to our fact Dimensions Tab and begin selecting Dimensions to add into this particular schema.

We start with our time Dimension and we're given a list of fields in which to link up from the fact table to the dime Dimension. We're going to base this on the order date of the sales order header. Now, we can begin adding our additional Dimensions. Select customer for example add that in as a new dimension. Then, we have to tie up the relationship between the dimension and the fact table by identifying the field on the fact table that links up to the dimension.

Academy assumed this to be customer ID since customer ID was identified as the business key for this particular Dimension. So, by adding that in, that links up the dimension field customer ID to the fact field customer ID. I can then add in the additional Dimensions linking them up on the link keys and lastly, I'm going to add in the salesperson Dimension and Linkin to salesperson ID.

In this instance, the dimension field is not called sales person ID; it's called employee ID so I just need to identify the key field relevant with salesperson. May not have all of the salesperson's details on file when the fact table is created. I can therefore infer missing members in other words if there is a for which I don't have a relevant salesp person member record in the dimension data Academy will automatically create for me a dummy record.

I achieve this by clicking that checkbox then what I can do is for the attributes within the dimension, I can say what I would like them defaulted to in this case unknown. So, if there is a fact table with a link to a salesperson record that doesn't exist, A salesperson record will be created in the dimension table and given the word unknown in the salesp person field.

If at some later stage that salesperson record then exists data Academy will automatically update the sales person Dimension record to reflect the new details that have arrived. Having added all of my Dimensions into this star schema and define my fact table, All That Remains now is to run it.

"WEBVTTKind: captionsLanguage: enonce all the dimensions have been defined within a star schema it's then necessary to define the fact table and Link those Dimensions into that fact table our star schema is based around sales orders and sales order details so we're going to base our fact table on the sales order detail table so we select that from the drop list and add that into our fact table definition note this time that it's been prefixed with ssor fact so let's modify the fact detail and this time we're going to name the target sticking with our convention but order analysis we can now begin adding our facts into this fact table we start with sales order detail ID and we're immediately highlighted the fact that no primary key has been defined this is indeed the primary key for this particular fact table the other facts we need are the line total and the order quantity since we want these summarized we'll apply a sum aggregation to them select sum from the group function on the line total and again on the order quantity those are the basic facts that we're going to have within our fact table we need to join in though a sales order header table so select joins sales order header and note again data Academy is assumed sales order ID is the joining field which indeed it is so we've now entered our facts and we've also put our joins in we now need to connect up our dimensions and to do that we go to our fact Dimensions Tab and begin selecting Dimensions to add into this particular schema we start with our time Dimension and we're given a list of fields in which to link up from the fact table to the dime Dimension and we're going to base this on the order date of the sales order header now we can begin adding our additional Dimensions we select customer for example add that in as a new dimension we then have to tie up the relationship between the dimension and the fact table by identifying the field on the fact table that links up to the dimension Academy assumed this to be customer ID since customer ID was identified as the business key for this particular Dimension so by adding that in that links up the dimension field customer ID to the fact field customer ID I can then add in the additional Dimensions linking them up on the link keys and lastly I'm going to add in the salesperson Dimension and Linkin to salesperson ID in this instance the dimension field is not called sales person ID it's called employee ID so I just need to identify the key field relevant with salesperson I may not have all of the salesperson's details on file when the fact table is created I can therefore infer missing members in other words if there is a for which I don't have a relevant salesp person member record in the dimension dat Academy will automatically create for me a dummy record I achiev this by clicking that checkbox then what I can do is for the attributes within the dimension I can say what I would like them defaulted to in this case unknown so if there is a fact table with a link to a salesperson record that doesn't exist A salesperson record will be created in the dimension table and given the word unknown in the salesp person field if at some later stage that salesperson record then exists data Academy will automatically update the sales person Dimension record to reflect the new details that have arrived having added all of my Dimensions into this star schema and Define my fact table All That Remains now is to run itonce all the dimensions have been defined within a star schema it's then necessary to define the fact table and Link those Dimensions into that fact table our star schema is based around sales orders and sales order details so we're going to base our fact table on the sales order detail table so we select that from the drop list and add that into our fact table definition note this time that it's been prefixed with ssor fact so let's modify the fact detail and this time we're going to name the target sticking with our convention but order analysis we can now begin adding our facts into this fact table we start with sales order detail ID and we're immediately highlighted the fact that no primary key has been defined this is indeed the primary key for this particular fact table the other facts we need are the line total and the order quantity since we want these summarized we'll apply a sum aggregation to them select sum from the group function on the line total and again on the order quantity those are the basic facts that we're going to have within our fact table we need to join in though a sales order header table so select joins sales order header and note again data Academy is assumed sales order ID is the joining field which indeed it is so we've now entered our facts and we've also put our joins in we now need to connect up our dimensions and to do that we go to our fact Dimensions Tab and begin selecting Dimensions to add into this particular schema we start with our time Dimension and we're given a list of fields in which to link up from the fact table to the dime Dimension and we're going to base this on the order date of the sales order header now we can begin adding our additional Dimensions we select customer for example add that in as a new dimension we then have to tie up the relationship between the dimension and the fact table by identifying the field on the fact table that links up to the dimension Academy assumed this to be customer ID since customer ID was identified as the business key for this particular Dimension so by adding that in that links up the dimension field customer ID to the fact field customer ID I can then add in the additional Dimensions linking them up on the link keys and lastly I'm going to add in the salesperson Dimension and Linkin to salesperson ID in this instance the dimension field is not called sales person ID it's called employee ID so I just need to identify the key field relevant with salesperson I may not have all of the salesperson's details on file when the fact table is created I can therefore infer missing members in other words if there is a for which I don't have a relevant salesp person member record in the dimension dat Academy will automatically create for me a dummy record I achiev this by clicking that checkbox then what I can do is for the attributes within the dimension I can say what I would like them defaulted to in this case unknown so if there is a fact table with a link to a salesperson record that doesn't exist A salesperson record will be created in the dimension table and given the word unknown in the salesp person field if at some later stage that salesperson record then exists data Academy will automatically update the sales person Dimension record to reflect the new details that have arrived having added all of my Dimensions into this star schema and Define my fact table All That Remains now is to run it\n"