Describe the characteristics and purpose of fact and dimension tables and explain how you recognise that this data mart is based on a star schema design. Illustrate your answer using the data mart tables in Figure 32.3

Question

What will be an ideal response?

Answer 1

The fact table has relatively few attributes but many records and constitutes the largest part of the
decision-support database. The fact table is made up of foreign keys and (usually) one or more metrics.
Each dimension table has relatively more attributes but few records compared with the fact table. The
more attributes contained by dimensions the wider the range of analysis supported. The primary key for
each dimension table is a single simple surrogate key, which is copied to the fact table as foreign key.
The purpose of the fact table is to contain any important metrics (streamDuration) and any additional
descriptors (customerRating) about the TV programmes/films streamed. The attributes of the
dimension tables allow a range of queries about this events being analysed. For example, the
dimensional tables allow analysis of the timing of streaming.
The purpose of the dimension tables is to contain attributes to allow streaming to be queried from
different perspectives.
The schema that describes this data mart is referred to as a ‘star schema’ because of the star shape that
results from surrounding the fact table in the centre with the dimension tables.
The schema is a star schema because the dimension tables are de-normalised and contain repeating data
such as that found in the dimFilm and dimTVProgramme dimension.
The purpose of the star schema is to reduce the number of joins between dimension tables and hence
speed up queries.