Examine the dimensional model (star schema) shown in Figure 32.13. This model describes part of a database that will provide decision support for a taxi company called FastCabs. This company provides a taxi service to clients who can book a taxi either by phone a local office or online through the company’s Web site. The owner of FastCabs wishes to analyze last year’s taxi jobs to gain a

Question

better understanding of how to resource the company in the coming years.

(a) Provide examples of the types of analysis that can be undertaken, using the star schema in Figure 32.13.
(b) Provide examples of the types of analysis that cannot be undertaken, using the star schema in Figure 32.13.
(c) Describe the changes that would be necessary to the star schema shown in Figure 32.13 to support the following analysis. (At the same time consider the possible changes that would be necessary to the transaction system providing the data.)
(d) Identify examples of natural and surrogate keys in the star schema shown in Figure 32.13 and describe the benefits associated with using surrogate keys in general.
(e) Using examples taken from the dimensional model of Figure 32.13, describe why the model is referred to as a “star” rather than a “starflake” schema.
(f) Consider the dimensions of Figure 32.13 and identify examples that may suffer from the slowly changing dimension problem. Describe for each example, whether type I, II, or III would be the most useful approach for dealing with the change.

Answer 1

(a) For example, the star schema can support analysis of taxi jobs according to –
The average mileage of taxi jobs according the day of the week and the location of the office managing the call. The make and/or model of taxis associated with the most profitable jobs. The most popular time/day/week for taxi jobs.
(b) The relationship between the age of clients and the number of jobs, timing of jobs and the number of cancellations of jobs. The number of jobs carrying a single passenger against jobs for more than one passenger. The relationship between the time that employees have worked for the company and their
average work rate.
(c) • Analysis of taxi jobs to determine whether there is an association between the reasons why taxi jobs are cancelled and the age of clients at the time of booking. The age of the client at the time of booking would have to be calculated and held in the fact table.
• Analysis of taxi jobs according to the time drivers have worked for the company and the total number and total charge for taxi jobs over a given period of time.
The date each employee joined the company would have to be held in the Staff dimension table.
• Analysis of taxi jobs to determine the most popular method of booking a taxi and whether there are any seasonal variations.
The method of booking would have to be captured and held in the fact table.
• Analysis of taxi jobs to determine how far in advance bookings are made and whether there is an association with the distance traveled for jobs.
The date of booking would have to be captured and held in the fact table.
• Analysis of taxi jobs to determine whether there are more or less jobs at different weeks of the year and the effect of public holidays on bookings.
Public holidays would have to be held in the Date dimension table.
(d) An example of the natural key for Branch is officeNo and the associated surrogate key is called officeID.
The benefits associated with the use of surrogate keys are –
- Protects BI applications from changes in the source systems e.g. introduction of new software.
- Allows easier integration of data from multiple source systems e.g. some systems may use different keys to describe the same objects.
- Allows the creation of dimension records that do not exist in the source system e.g. orders without an assigned sales rep.
- Allows the tracking of changes in dimension attributes over time. (see slowly changing dimensions)
- Integer surrogate keys are an efficient key in relational databases.
(e) The presence of the denormalized location data (street, city, postcode) in the Office and Client, and denormalized time data (month, quarter, season) in Date make the DM a star schema.
(f) The DM appears to be using Type I SCDs as there is no evidence of historic information being held such as previous salary and jobDescription for staff which changes over time. Storing historic data would require that the dimension tables had additional rows (Type II) to capture past and current data and/or additional columns (Type III) to allow for this.