|
Fact table name
|
Fact granularity
|
Brief justification
|
|
Customer_Data_Mart
|
Customer
|
Details for--
-booking
-age
-life time value
-group
e.tc
|
|
Store_Data_Mart
|
Location
|
|
|
Hiring_Data_Mart
|
Time
|
|
|
Booking_Data_Mart
|
Time, Customer
|
|
|
Vehicle_Data_Mart
|
Store, Time
|
-availability, Timing
|
|
Promotion_Data_mart
|
Cost, Time
|
Details for -
-profit
-effect of total sales
-e.tc
|
|
Design feature
|
Brief justification
|
|
Additive Fact Table
|
The total in Hiring fact tables illustrate the
total amount collected daily, weekly and monthly. The amount is deducted the
expenses of all vehicles and vip cards.
The cost of sales in Store data mart shows the
total money of the sales after a day, week, month and even years. Hence from
the tables we can be able to determine the total amount received at a certain
day, certain month and year. Hence the database can be searched using
calendar.
|
|
Semi-Additive
|
The profit and total cost in promotion fact
table which calculate the total expenses incurred during promotion time.
Profit obtained after calculating the amount raised during promotion time
compared to cost before and after promotion. The profit and cost are additive
but the promotion occur at a given period of time.
|
|
Non-Additive Facts
|
The Age in Customer fact table cannot be added
or deduct to determine the total age of the customers. To determine the group
of the customers one will use ratios or range e.g. group1 contain age 0f 20
to 30.
|
|
Nulls in Fact Tables
|
Customer may hire vehicle with or without VIP
card hence the VIP_id included in the hiring fact table might be null if
customer does not hire using VIP cards. Hence a default number is inserted in
case the VIP_id is empty.
|
|
Conformed Facts
|
The vehicle price before within and after
promotion must be properly calculated. The promotion cost dimension in the
promotion fact table ensures all three prices
can be traced.
|
|
Transaction Fact Tables
|
All the fact tables are transactional.
For customer fact table heavily depend on
customer information to determine the group, the classes that is most active
and life time value among others.
Promotion fact table must include the media
company used, the media category and amount gained after using certain media.
Vehicle fact table must be able to determine
available vehicles and determine if they meet the need of the customers.
Store fact table include the items available
and the rate of buying during hiring. etc.
Hiring fact table must be able to trace
customer, vehicle and mode of payments etc.
Booking fact table the customer and vehicle
must verified e.g. c
|
|
Periodic Snapshot Fact Tables
|
The calendar dimension is the main granularity
promotion, customer, hiring and booking fact tables heavily depend on it.
On the customer fact table will be able to
determine the frequent customers and their recent time.
Promotion fact table need to mark start and
end date and will be able to predict the certain when the profit was high or etc.
The period dimension is used to mark start and
end dates for hiring vehicles and will reference it to calendar.
|
|
Fact less Fact Tables
|
In the hiring fact table, the status dimension
enables to determine the initial and final status but it is not predictable
as for example the damage might be small big or might not even occur at all.
The probability might there no damage at all or very small which cannot be
heavily dependent on incomes
|
|
Aggregate Fact Tables or Cubes
|
Foreign keys are included in each fact table
to accelerate querying. For example, we might want to query the activities
carried out at certain date. We will query the calendar for certain day and
all information of the registered users, promotion included, the hired
vehicles, the sales sold, the sold VIP cards and all transaction payments
made. etc.
|
|
|
|
|
Dimension Table
|
|
|
Dimension Surrogate Keys
|
The items keys in the store fact table might
follow certain order especially if they are using barcode reader which will
identify item type, category and price. etc.
|
|
Natural, Durable, and Supernatural Keys
|
All fact tables primary keys are natural as
they are created automatic with an incremental order inform of integers.
|
|
|
|
|
Degenerate Dimensions
|
The Hiring fact table contain return dimension
which only contain primary key which can be used to calculate how many
returns were made.
Customer might have hired many vehicles but
will be referenced by that one return key just like an invoice
|
|
Deformalized Flattened Dimensions
|
The vehicle fact table contain vehicle
details which is demoralized into vehicle name, model,
category and manufacturer
|
|
Multiple Hierarchies in Dimensions
|
The Calendar dimension contain hierarchy of
date, month and year where’s location dimension contagion hierarchy of city
to state to country.
Hence queries can be done using parents
country and year or their sub-child’s
|
|
Flags and Indicators as Textual Dimension
Attributes
|
For example the vehicle dimension have vehicle
name, vehicle, registration number, vehicle color which one can be able to
understand even if viewed separate.
|
|
Calendar Date Dimensions
|
The calendar dimensions will allow easier
querying of dates using day, month or even a year.
For example to determine type of car sold on
certain date such as 12/05/2018.
|
|
Role
-
Playing Dimensions
|
Calendar dimension represent a role playing
dimension as a certain date can be included several times in the hiring fact table.
For example, having different customers hiring vehicle at the same day. The
date will be repeatedly included using their foreign key.
|
|
Junk Dimensions
|
The payment dimension represents the junk
dimension as it combines all possible methods without separating them. e.g.
using credit cards, cash on pay, bank cheese etc.
|
|
Value Chain
|
Each fact table represent the flow of hiring vehicle.
The retailer ship vehicle to their destination stores, then customers hire
the vehicles either by booking or registering direct. Payments are then made
after hiring and the direct payments from sales.
|
|
|
|
