# Dimensional Modeling of Multiple Business Processes - Tutorials Based on a Case Study

Show Video

hi guys this is Mina i'm making this video in  response to requests from some of my viewers   i'm excited that the videos i'm creating are  having significant impact and that you guys are   asking for more in one of my previous videos  i showed how to construct a dimensional data   model based on a single business process and thus  a single fact table was created in this tutorial i'll   show you how to build a dimensional model based  on multiple business processes multiple business   processes mean different types of measurements and  since you don't want to combine various types of   measurements into a single fact table we are  going to have to create multiple fact tables   at the end of this video you would  know the different types of fact tables   what is a conform dimension, a role playing  dimension, the four steps dimensional design   process you would also get to learn more  about dimensional model matrix design   and also understand dimensional  modeling of multiple business processes to help us understand these particular  topics i'll first introduce you to the   types of fact tables that exist the first  on our list is a transaction fact table   this fact table contains measures  captured to the transactional level   it corresponds to a measurement at a point  in space and time that is to the atomic   level so this is a typical transactional table and  we have our primary key, the generate dimensions   the foreign keys of the associated dimensions  and the metrics which is the sales amount this   sales amount is a transactional metrics which  means that it's captured to the lowest level   it's captured at a point in space and time there  is no aggregation in this metrics this is what   makes this fact table a transaction fact table and  again we have our optional audit information load   date load date is just to capture when  the table was loaded into our database next we have the periodic or snapshot fact table  which contains records that are summarized over a   standard period such as day or weekly or monthly  so in this case we have our fact table which is   employee salary fact table and the metric has been  aggregated bi-weekly next we have the accumulating   snapshot fact table this fact table contains  summary of measurements occurring at predictive   steps between the beginning and end of a process  so for this particular fact table we have grade control drilling grade control is basically the  drilling that is done in the mining industries   to further define an ore body whose  grade has been determined from   resource exploration drilling so this is like a  subsequent drilling that is done to further define   the grade of an ore body so here in this fact  table we use accumulating snapshot fact table to   capture the metrics which is total meters  drilled at specific stage of the drilling process   last but not least we have the factless fact table  and it's a fact table that has no metrics at all   so here we can have our primary key for the fact  table we can also have the foreign keys of   all the associated dimensions you can have the  load date stamp which is the audit information   you can even have the degenerate dimension which  is optional also but there wouldn't be any fact   or measurement or metric in this fact table and  this is often referred to as factless fact table these fact tables can  however share dimension table   so a dimension that can be used by multiple  fact tables is often called the conform dimension   here i have the employee's dimension and this  employee dimension is required for both fact   employee salary and fact sales salary which means  that i don't need to create duplicate employee   dimension tables i just need to create one and  include this primary key of the employee dimension   in the fact tables as foreign keys and that  links this employee dimension to both facts   this particular dimension employee dimension that  is being referenced in two or more fact tables   is called a conformed dimension i'd also like to  point out that there is another type of dimension   we need in order to model a dimensional data  model that has multiple fact table and that is the   role playing dimension a role playing dimension is  a single dimension that plays multiple roles and   what do i mean by this so for instance if we have  like transaction date, ship date, order date these   are all different kinds of dates we don't need to  create different tables for transaction date shape   date other date we can just use or create a single  date table that can be referenced multiple times   within a fact table so that is a role playing  dimension this single date dimension that will   create can be referenced multiple times in  a fact table with each reference linking to   a logically different or distinct role of the  dimension so we have order dates foreign key   ship date foreign key, delivery date foreign key  in the fact table and each foreign key refers to   a separate view of the date dimension so that the  references are independent this separate dimension   views with the unique attribute column names  are called rows so instead of creating order dates dimension, ship date dimension, delivery date  dimension we just create them as foreign keys   and link them to Dim_Date and these foreign keys in the fact table all play different rows they all   refer to different instances of the dim date and  that's what we call the role playing dimension so this calendar date dimension can be found in  almost all dimensional data models and they allow   us to navigate of the fact table through familiar  date, month, year, physical year we can break our   fact table down to various levels as indicated in  the dim date now that you've been introduced to   these concepts let's move on to some real stuff  before i proceed please support this channel by   subscribing or liking my videos as usual this  video will be based on a case study you can   find the case study in the description section  as well so this case study is about today's   library company that has several libraries across  several cities and they have books in different   departments and employees manage the borrowing  and returning of books so in recent months the   library has had few visitors almost no new members  and reduced quantities of supplies book supplies   from vendors for unknown reasons the management of  today's learners library has decided to implement   analytic system across the entire organization  to increase member satisfaction and attract and   also attract visitors currently each member  has an asset score that allows them to use   the library and borrow materials the management  has decided to track new membership registration   attendance trend, the impact of advertisement  on new registration, the material borrowing   and return efficiency based on number of days  between collection and return dates they're   also curious about most other books in each  department and library at each time of the day   you want to know the number of visitor  facing employees in a given day   books are supplied to libraries by only  certified vendors and the purchasing   manager wants to know the life cycle of a book so  that he can determine when to purchase new ones management has also decided to track any  changes in information about the employees   the members, the books, the vendors and all of that   so this is our case study we are going to work our  way through how to model this particular scenario   so i've provided a copy of this case study  in the description section of this video feel   free to refer to it as you follow along on how i  solved it if you have a better way of serving it   feel free to let us know in the comment section  as well this is a matrix that i've designed for   this case study and as i've already introduced  you to dimensional modeling in my previous videos   you know that we have to identify the four  stages of dimensional model which is to identify   our business process, the granularity which is the  level at which you want to capture the information   the numeric or the fact and name your fact table  as well as identify your dimension tables from   the question so from the question, I identified two  business process one is to model the the book loan   which is the borrowing and returning of books  as well as the purchase order which is which   will be used by the purchasing manager to order  books the granularity that i want to capture   is per every transaction per every book that will  be borrowed and in the case study i identified   two metrics which is the quantity of books that  will be borrowed and also probably to fine the   members if books are over due for return i'm  going to name this business process or this fact   as fact books on loan and i'm also  going to capture the purchase order transaction that is other every single other every  single book that we order is going to be captured   i identified the metrics for the purchase order as  purchase amount and quantity and i'm going to call   this business process or fact table fact purchase  trans and these are all the dimensions that i   identified from the question if you find more  just let us know in the comment section all right   so this is my dimensional model matrix that i have designed and i have basically   the business process here the granularity here  fact and then the fact table i also have the   dimensions listed out i have denormalized some  of the dimensions to obtain just a single table   so as you can see i have book, department, book  defect all denormalized into one table that   i'm going to call book i'll keep vendor as it  is membership will remain as a single table   library and city have been denormalized  to the table library and employee   advertisement and dates will each be a  dimension in my database now i'm going to specify   which dimensions will be included in which fact  table and here i can boldly say that dimension   book can apply to both fact tables so i'm just  going to put a cross sign here and a cross sign   there so this is how i'm going to build my star  schema so i have book here and it applies to both   fact tables this is fact purchase transaction this  is fact book loan so book applies to both of them   and then i have vendor, vendor only applies  to the purchase transaction fact because   the the borrowing and lending section of the  library doesn't have anything to deal with the   the vendor so they don't need the vendor table  in their modle or in the business process   so i'll just include vendor in  the fact purchase transaction here   membership only applies to the  borrowing and returning business process   it doesn't apply to the purchasing of  the book so i'll just keep that here okay so library applies to both of them because  transactions will have to be made in the library   and supplies will also have to be made to  the library employee can work at both sides   of the library under the purchase side and also on  the books borrowing and lending section as well   advertisement is to advertise the library for new  membership to come in we don't need to advertise   to buy books (from vendors) so I'm not going to include it in the  purchase transaction fact and date will definitely   apply to both of them because you're going to have  one date dimension that borrowing and lending can   reference to perform transactions as well as the  purchase order and return date so here i have   book which is being referenced in both fact tables  i also have vendor which applies only to the   fact purchase trends i have membership  which applies only to the books on loan   fact i have library which applies to both of them  so i have library in the center and it's linking   to both fact tables i have employee which links to  both fact tables i have advertisement which links   to only the fact on loan table and then date  which is which will be used by boot fact table   actually the fact transaction fact table  would require two dates which are order date   and delivery date and as i've already explained  earlier these are role playing dimensions so the   date dimension will play multiple role for the  fact purchase transaction table it will play the   role of order date and also as a delivery date  the date dimension also played two rows for the   books on loan fact table it's going to play  the role of borrow date and return dates and this section i have listed out all my  dimensions and my fact and i've given it   attributes so i have the primary keys for all  the dimension and then the business key which   is optional business key is the key that is used  by the business it it can be used to track any   changes that will be made to any of this table you  can either include it in your model or you can   leave it so because this business key can change  we always have to make sure that we have a primary   key which is a surrogate unique identity key auto  increment so that we can make sure that all our   tables have a unique identifier all right these  are all attributes that i have given to my table   so you can give whatever attribute that is being  required of you and this is an optional audit   information to track when the data was loaded into  the database i have effective start date effective   end date and current status in my tables to  track any changes to any of these attribute   i'm using the slowly changing dimension type  2 method i have a video that explains this   slowly changing dimension in details so if you  want to understand more about slowly changing   dimensions feel free to click on the link above  or in the description section for more details   these are my fact tables so i have my primary keys  my business keys, the degenerate dimensions which are   optional and most importantly i have my metrices for both tables i've already explained this uh   foreign keys of the associated dimensions  these are the keys that link the dimensions   to the table so and i also have my slowly  changing dimension type 2 in the fact table   to track any changes that will be  made to any of these attributes moving on to my SQL server management  studio i will connect to my server   all right so i'm going to create the database  and the tables and i've already written a query   for that here so i'm going to open it and execute i have a video that explain in details   how to write sql code to create databases and  tables so basically what this code is doing is i'm   going to create a database called library system  and use the library system as the database where   all of these tables will be created in all of  these tables okay so i have the dim employee table   these are all the attributes that i've given to  it and this this is the constraint that makes the   employee id the primary key and also this employee  id is an identity key and auto-incrementing so it's more like starting from one i gave it  to start from one and increment by one so it's   going to be one two, three, four, five giving the  records in this table unique identities i also   have the membership dimension table and member  id is a primary case also and auto-incrementing i   have the vendor dimension table, i have the library  table these are all dimensions the book dimension   advertisement dimension, the date dimension  and then moving on to my fact so here i have   the purchase transaction fact it contains the  primary key of this table which is an identity   column and this is the degenerate dimension this  is the primary key i talked about previously these   are my metrics these are the following keys of the  associated dimensions and in order to link this   columns to the corresponding dimensions i  have these constraints written out again   watch my previous videos on how to create tables  using sql code to understand this code better   so first of all the the first line here makes the  transaction id actually it has to be purchase   transaction because purchase transaction is the  identity column so this line of code here makes   the purchase transaction here the primary  key and the rest of the code here make the make these columns the foreign keys and  links them back to the appropriate tables   making them foreign keys i have the audit information here and they slowly  changing dimension information here as well one thing you have to be careful about is that  since all the primary keys in the dimension   tables are integer you have to make sure that  the corresponding columns in the fact table are   also integer basically they all have to be of  the same data type so if the date key here was   a different data type the corresponding um date  key here should have been of the same data type as   this one here so the data type in the fact table  has to correspond to a data type of the dimension   table otherwise the code will give you an error  the same thing was done for the fact books on loan   table so i have my primary key constraint and  my foreign key constraints written out here   these are all the columns and  these are the columns that make the foreign case you also have to make sure  that the name that you give to the foreign   and primary keys are unique between all the  facts otherwise you will get an error as well   now that you understand this  command i'll go ahead and execute it   command completed successfully  so i've created all those tables   in my database i'll refresh my database and  open the library system database go to tables   and these are all the tables that i've  created if you want to see the diagram or the   data model that we've created click on new this  arrow basically means that i need to change the   owner of this database so i'll click ok and go  to the database right click go to properties and the properties go to file if you have this  particular error so i need to change the owner to   system administrator 'SA' click on ok right  click on the database object again go to   database diagram and this is what you want so  i'm going to click yes and select all the tables close and this is what we have as our  data model a dimensional data model   guys so i'll go to zoom and fit so i  can have a full view of this dimensional model   now that i have it here i'm going to rearrange  all of them like what we had in our excel sheet so   what i did here was to arrange all the conform  dimension in the center and the fact tables   as well as the dimensions that refer only  just a fact table that makes the diagram look   cleaner so let's do the same thing here  okay so i'm gonna bring my fat loan here and my purchase transaction fact  here and all the dimensions   that are peculiar to the fact  loan i'm gonna bring them   close by and put all the conform dimensions  in the center i can have a nice rearrangement i'm a very visual person so i like to  arrange my data models as you can see here   i can spend some time doing  this i actually do like it i hope you guys understand this concept and i'm  gonna take a time to explain this over again so   i have this dimension here the advertisement  dimension that refers only to the book alone   fact table, as well as the membership, applies only  to this fact table now every other dimension that   you see here in the center link to both fact  tables okay so i have fact loan book on loan   and then i have fact purchase transaction they  both share these dimensions these are conformed   dimensions and this vendor dimension only  applies to the purchase transaction fact table   these two relationships from the date dimension  to the purchase transaction fact table   are role-playing dimensions so date  dimension here is linked to the order date   and delivery date foreign keys  in the purchase transaction table   and they both represent logically distinct row of  this date dimension i also have the borrowed date   and return date in the book books on loan fact  table and they are both distinct keys that are   linked to the date dimension so when we query for  borrow dates we get different date out of the date   dimension because the information or the following  case of the borrowed date information is different   from the return date board key sorry return  date key here okay so this is our model for   multiple fact tables so you can carry this  model on for several fact table and you can do   and the sky will be your limit just this  principle and you can create as many   fact tables as you want for your dimensional  data model so in this tutorial, we have learnt   the different types of fact table which are the  transaction fact table the periodic fact table   accumulation snapshot fact table and factless  fact table i've also introduced you to confirm   dimensions role-playing dimensions and  the four-step dimensional design process   which includes identifying the business  processes the granularity the fact subsequently   defining your fact table and identifying all your  dimension tables in this video i've shown you how   to create a dimensional data model matrix  so this is the design you can use to create   your dimensional data model in the future so you  state your business process your granularity your   facts and dimensions and use this model to map up  which dimension you're going to include in which   fact table all right and this is the star schema  this is the multiple star schema combined together   and arranged nicely so we have two fact table and  these are dimensions that are surrounding it the   center here we have dimensions that are conformed  to both fact tables and we have dimensions that   refer to only a single fact table listed here i've  also introduced you to role-playing dimensions   now that we've created our dimensional  model we are going to answer all of the   questions that are required in this case  study in a subsequent video so stay tuned thank you guys so much for watching this video  and please feel free to subscribe to my channel   if you have benefited from these videos  and see you in my next one bye bye

2021-04-30 18:29

Show Video