Dimensional Modeling of Multiple Business Processes - Tutorials Based on a Case Study
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
