Meshach Bentil-Bentum CST 4704 Midterm
Below are the relevant dimensions along with each and every relevant column
Using the Star Schema due to hierarchical attribute model it provides for analysis and speedy performances in querying the data
I will use SSIS ETL package to populate dimension and fact table of data warehouse with appropriate values and we can schedule this package for daily execution and daily processing and populating of previous day data in dimension and fact tables, so our data will get ready for analysis and reporting.
Creating Branch Office dimension table in the Data Warehouse which will hold car rental location details
Create Customer dimension table in Data Warehouse which will hold customer personal details
Create Car dimension table in Data warehouse which will hold car details
Create Date Dimension table which will create and populate date data
Create Fact Reservation table to hold all the transactional entries of the previous rentals with appropriate foreign key columns which refers to primary key column of my dimensions.
With Full Load it truncates the data from all the tables and reload with fresh data from the sources table.
For my design, I tested that the data in loaded in the order specified by entity which is dimension first and the fact tables later. And I also made sure that the data is correct and matches with what is shown in the source table like the total number of records, length of data without any data lose. Below is an example:
Incremental Load Process:
Only modified (new and updated) data will be loaded into the data warehouse.
With my Design, if I take a record which is updated in the OLTP system or source table then it will be identified by a timestamp (Remember I added loaddatetime in my dimensions) which will be used to determine whether the records should be considered for incremental or delta into my dimensions.
In order to analyze branch performance, company A wants to create dimensional data warehouse that covers the following business processes:
–USING MS SQL SERVER MANAGEMENT STUDIO