Databas – Part V
Chapters 32 Data Warehousing Design
32.1
your answer with a diagram showing the stages of the lifecycle.
The Business Dimensional Lifecycle – Principles
o Focus on the business e.g. Identify business requirements and their associated value.
o Build an information infrastructure e.g. build a single, integrated, easy-to-use, high
analytics, training, support etc
The Business Dimensional Lifecycle Key Features
1. Business Requirements Definition plays central role by influencing Project Planning and providing the
foundation for the three tracks that follow concerning:
o Technology (top track)
32.2 Describe the proposed approach to designing a star schema in the Dimensional Modelling stage of
tables and one fact table with maximum of six attributes per table) star schema to illustrate each step.
Dimensional Modeling Stage
The DM is created using a two phased approach
Databas – Part V
Part I Create a high-level DM (4 step process)
Step 2 Declare the Grain
Decide on level of detail or the grain needed for the selected business process. In other words, what a
Step 3 Choose the Dimensions
Part II – Identify Dimension and Fact Attributes
32.3 Identify three differenc
approach.
There are a number of differences between the approaches and the student is free to identify any three such
32.4. The star schema shown in Figure 32.1 describes part of the database that will provide decision-support
for a property sales company. Describe the main characteristics of fact and dimension tables and discuss
the purpose of the tables shown in the star schema of Figure 32.1.
Figure 32.1
The fact table has relatively few attributes but many records and constitutes the largest part of the
decision-support database. The primary key for the fact table is composed of the foreign keys, which
relate to the dimension tables.
Each dimension table has relatively more attributes but few records compared with the fact table. The
star schema is to reduce the number of joins between tables and hence speed up queries.
Databas – Part V
The purpose of the fact table is to contain the attributes that describe the important metrics associated
32.5 Identify three types of analysis that the star schema shown in Figure 32.1 can support about property
sales.
For example, the star schema can support analysis of property sales according to
32.6 What do slowly changing dimensions (SCDs) represent to a database designed for decision-support?
A database designed for decision-support will normally store data that can be several years old. During
32.7 Describe the three types of SCDs.
Most common techniques for dealing with SCDs that are not significant are called Type 1 and those
that are significant are called Type 2.
Databas – Part V
32.9 Identify two possible examples of SCDs in the property sales star schema shown in Figure 32.1 and
discuss the types of change each represents.
An example of a SCD is the position attribute in Staff. The position held by a member of staff is likely
32.10 Discuss what the bus matrix (shown in Figure 32.2) for an online retailer represents and how it can be
used to facilitate the creation of a data warehouse.
Time
Customer
Promotion
Product
Courier
Location
Warehouse
Staff
Customer
Registration
X X X X
Figure 32.2
Following the establishment of analytical themes, the business processes that are associated with them
Databas – Part V
32.11 Produce a star schema for the Product Delivery business process using the information shown in
Figure 32.2. Based on your understanding of this business process, add a maximum of 5 (possible)
attributes to each dimension table in your schema. Complete your star schema by adding a maximum of
8 (possible) attributes to your fact table. Your choice of attributes should demonstrate that you have a
realistic idea of how this star schema is likely to be queried.
Databas – Part V
numberDayOfWeek
numberDayOfMonth
numberWeekOfYear
filmCertification
filmGenre
filmYearRelease
filmDirector
filmProductionCompany
mainActor1
mainActor2
dimMember
memberID
dimTVProgramme
TVProgrammeID
TVSeriesLanguage
TVSeriesDirector
[TVProduction Company]
factStreaming
streamID
Figure 32.3
32.12 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
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.
Databas – Part V
32.13 Identify three types of analysis that the data mart shown in Figure 32.3 can support about media
streaming.
For example, the data mart can support analysis of media streaming according to the
following.
32.14 The data mart shown in Figure 32.3 cannot support the analysis of media streaming according to the
age of the member at the time of the streaming. Describe the changes necessary to the data mart to
support this type of analysis.
Student should answer as follows:
32.15 What do slowly changing dimensions (SCDs) represent to a database designed for decision-support?
A database designed for decision-support will normally store data that can be several years old. During
they are to be tracked as not all changes are significant.
32.16 Describe three types of slowly changing dimension (SCDs) and discuss the best SCD Type to track
Databas – Part V
Most common techniques for dealing with SCDs that are not significant are called Type 1 and those
that are significant are called Types 2 and 3.