Oracle Advanced person Needed

Week 4 – Snowflake Schema

Semester 1, 2020

FIT5195 – Business Intelligence and Data Warehousing

Developed by: Agnes Haryanto

Agnes.Haryanto@monash.edu

Soon Lay-Ki

Soon.LayKi@monash.edu

MONASH INFORMATION

TECHNOLOGY

Agenda

1. Hierarchies

1. Hierarchy vs. Non-Hierarchy

2. Hierarchy versus Multiple Independent Dimensions

3. Linked Dimensions

4. Hierarchy Design Considerations

2. Determinant Dimensions

1. Determinant vs. Non-Determinant Dimensions

2. Determinant Dimension vs. Pivoted Fact Table

3. Non-Type as a Determinant Dimension

4. Multiple Relationship between a Dimension and the Fact

Using FLUX

1. Visit http://flux.qa/ on your internet enabled device

2. Log in using your Monash account (not required if you are already logged in to Monash)

3. Click on the “+” to join audience

4. Enter the Audience Code:

• Caulfield – 3GANT7

• Fully Flex – 39WRG8

• Malaysia – VTVPLW

5. Select FIT5195 in the Active Presentation menu

6. Answer questions when they pop up

Hierarchies

Hierarchies

▪ A Hierarchy is formed when a dimension is broken down to two or

more dimensions in a hierarchical manner.

➢ As a result, the star schema becomes a snowflake schema.

(a) A simple Star Schema

(b) A Snowflake with Hierarchy

Hierarchies

(a) A Snowflake with a Bridge Table (b) A Snowflake with Hierarchy

Hierarchies

1. Hierarchy versus Non-Hierarchy Dimensions

2. Hierarchy versus Multiple Independent Dimensions

3. Linked Dimensions

4. Hierarchy Design Considerations

1. Hierarchy vs. Non-Hierarchy

1. Hierarchy vs. Non-Hierarchy

1. Hierarchy vs. Non-Hierarchy

HierarchyNon-Hierarchy

1. Hierarchy vs. Non-Hierarchy

CityID City Name State CountryID

MEL Melbourne Victoria AU

SYD Sydney New South Wales AU

KUL Kuala Lumpur Selangor MA

JNB Johannesburg Johannesburg SA

CampusID CampusName Address Suburb Postcode CityID

CL Clayton Campus Wellington Rd Clayton 3800MEL

CA Caulfield Campus Dandenong Rd Caulfield East 3145MEL

PA Parkville Campus Royal Parade Parkville 3052MEL

SY Sydney Campus Opera Boulevard Sydney 2001SYD

MUM Malaysia Campus Jalan Lagoon Bandar Sunway 47500KUL

MSA South Africa Campus Peter St Johannesburg 1725JNB

(a) Campus Dimension Table

CountryID Country Name

AU Australia

MA Malaysia

SA South Africa

(b) City Dimension Table (c) Country Dimension Table

1. Hierarchy vs. Non-Hierarchy

(a) Correct Hierarchy (b) A Wrong Hierarchy

1. Hierarchy vs. Non-Hierarchy

1. One table vs. many tables

The consequence is in the join query processing when producing reports.

2. Normalized vs. Un-normalized

With the hierarchy option, the tables are normalized, which follows the relational

model. In contrast, the non-hierarchy option, the table (e.g. the Campus

Dimension table) is unnormalized, which is basically 1NF with visible replication

on information, which is prone to anomalies (e.g. insert, update, and delete

anomalies).

3. Drilling down and rolling up

The hierarchy model is not about drilling down information exploration.

1. Hierarchy vs. Non-Hierarchy – Summary

▪ From the query point of view, both versions need two queries.

▪ From the query processing point of view, the non-hierarchy version uses

one join operation only, because it only needs to join the fact and one

dimension.

▪ From the conceptual point of view, the hierarchy model does not actually

offer a better roll up or drill down features.

2. Hierarchy vs. Multiple Independent Dimensions

2. Hierarchy vs. Multiple Independent Dimensions

(a) Separate Dimensions (b) Combined Dimensions

(c) Hierarchy Dimensions

2.1. Separate vs Combined Dimension

(a) Separate Dimensions (b) Combined Dimensions

2.1. Separate vs Combined Dimension

(a) Separate Dimension Model – Campus Dimension Table

(b) Separate Dimension Model –

Country Dimension Table

(c) Separate Dimension Model – Fact Table

2.1. Separate vs Combined Dimension

(d) Combined Dimension Model – Campus Dimension Table

(e) Combined Dimension Model

– Fact Table

2.1. Separate vs Combined Dimension (c) Separate Dimension Model – Fact Table (e) Combined Dimension Model – Fact Table

2.1. Separate vs Combined Dimension

(a) Separate Dimension Model – Campus Dimension Table (b) Separate Dimension Model –

Country Dimension Table

(d) Combined Dimension Model – Campus Dimension Table

2.1. Separate vs Combined Dimension

(a) Separate Dimensions (b) Combined Dimensions

2.2. Combined Dimension vs. Hierarchy

(b) Combined Dimensions (c) Hierarchy Dimensions

2.2. Combined Dimension vs. Hierarchy

(a) Hierarchy Model – Campus Dimension Table

(c) Hierarchy Model – Fact Table

(b) Hierarchy Model – Country Dimension Table

2.2. Combined Dimension vs. Hierarchy

▪ Reasons on why the Hierarchy model is not ideal:

➢ An efficient query processing to query the fact and dimensions,

➢ The identifier of the Campus Dimension (e.g. CampusID) already covers the

child dimension (e.g. Country Dimension), and

➢ Campus and Country information is often regarded as one entity; at least in this

case study.

(b) Combined Dimensions (c) Hierarchy Dimensions

3. Linked Dimensions

3. Linked Dimensions

(a) Linked Dimension Model – Campus Dimension Table

(c) Linked Dimension Model – Fact Table

(b) Linked Dimension Model – Country Dimension Table

4. Hierarchy Design Considerations

Hierarchy – Summary

▪ A dimension hierarchy is connecting two or more dimensions in a hierarchical

manner, using a many-1 relationship.

➢ As a result, the dimensions in a hierarchy are normalized, in 3NF, using the

context of Relational Database Design.

▪ Compare and contrasts five different models:

1. Separate Dimension model

2. Combined Dimension model

3. Hierarchy model

4. Linked Dimension model

5. Hierarchy Design Considerations

Determinant Dimensions

31

Dimensions and Fact Tables - Recap

A Star Schema for the Sales Case Study

32

Example #1:

Querying Fact Table

33

Example #2:

Querying Fact Table (cont.)

34

A star schema may have a special dimension

all data retrieval from this star schema must use this dimension

else, the retrieved data will become meaningless.

Determinant Dimensions

Determinant

_DIM

Determinant_

Att

..

..

Special dimension

Key identifier

35

Introducing a Determinant Dimension – Petrol Station Case Study

Sample operational database of a company about petrol prices from all petrols

stations in Victoria:

36

The requirements for the data warehouse

are to answer the questions related to

(a) average petrol price,

(b) minimum petrol price, and

(c) maximum petrol price

Petrol Station - Star Schema Version-1

Chosen Dimensions:

(i) day of week,

(ii) suburb, and

(iii) petrol company.

Two-column Table for Petrol Prices based on Day of Week

Category Fact Measures

37

Minimum price for which petrol type?

Petrol Station - Star Schema Version-1 (cont.)

38

▪ Not all petrol stations sell all types of petrol, e.g. Premium 98.

▪ Calculating the average price for Premium 98 on Monday cannot be:

Total_Petrol_Price_P98 / Number_of_Petrol_Station

▪ The number of petrol stations is different for different petrol type!

Petrol Station - Star Schema Version-1 (cont.)

39

▪ Solution: Split the Number of Petrol Station column into six columns – one

for each petrol type.

▪ Question: How many columns in total?

Petrol Station - Star Schema Version-1 (cont.)

Two-column

Table for Petrol

Prices based

on Suburb Two-column Table for Petrol Prices based on Company

40

Petrol Station - Star Schema Version-1 (cont.)

41

Petrol Station - Star Schema Version-2

42

Petrol Station - Star Schema Version-2

Min. price for

which petrol

type??

43

select DayofWeek,

Min_Petrol_Price

from PetrolFact

where DayofWeek = ’Monday’

and PetrolType = ’Unleaded’;

Petrol Station - Star Schema Version-2

select DayofWeek, PetrolType,

Min_Petrol_Price

from PetrolFact

where DayofWeek = ’Monday’;

44

Petrol Station - Star Schema Version-2

Determinant Dimension uses dotted box in the star schema.

The new star schema with a Determinant Dimension for Petrol Case Study

Determinant vs. Non-

Determinant

Dimensions

46

The Olympic Games Case Study

▪ The Olympic Games committee maintains an operational database that

stores all matches, games, as well as the medal winners of the Olympic

Games over the years

▪ Let’s build a data warehouse to analyse the medal counts, by each country,

sport, and at which Olympic Games

Determinant vs. Non-Determinant Dimensions

47

The Olympic Games Case Study - The Star Schemas

Version-1

Version-2

The questions are:

1. What is the difference

between these two

versions of star schema?

2. Is Medal Type Dimension

a Determinant

Dimension?

48

The Olympic Games Case Study - The Star Schema Version-1

Two-column tables

(pivot tables) for each

dimension category in

star schema version-1

49

The Olympic Games Case Study - The Star Schema Version-2

50

▪ Is MedalTypeDIM a determinant dimension?

– To retrieve the data from version-2 star schema, must we have the

information from Medal Type Dimension?

Determinant vs. Non-Determinant Dimensions

Version-2

COUNT = total medals from

other dimensions, without the medal type dimension)

51

Fact Table for Version-1

The Olympic Games Case Study - The Star Schema Version-1 vs Version-2

Fact Table for Version-2

52

The Olympic Games Case Study - The Star Schema Version-1 vs Version-2

Differenc

es

Version 1 Version 2 (with

MedalTypeDIM)

Storage Lower with only three records Nine records

Modelling The schema looks more

complex and crowded; but the

storage cost is lower

More concise and compact with

less number of measures in the

fact, easier to understand

Query

Processin

g

Less join processing between

dimension tables and the fact

table.

Requires additional join with

MedalTypeDIM

Determinant

Dimensions vs.

Pivoted Fact Table

54

The PTE Academic Test Case Study ▪ PTE Test consists of four main components: Listening, Reading, Speaking, and

Writing

▪ There are many test venues (all major cities in Australia, and overseas as well), each

country may have a different price setting

▪ A student taking a PTE test will have one score for each of these four components,

as well as one overall score

▪ The score is a numerical score, ranging from 10 to 90

▪ The results will normally be released five days after the actual test date

▪ A student may take a PTE Academic Test several times, in order to improve the

score

▪ A student is identified by his/her RegistrationID, which stays the same student stays

the same

Determinant Dimensions vs. Pivoted Fact Table

55

ERD for PTE Academic Test

56

The PTE Academic Test Case Study - Sample Records for 8 Students

57

The PTE Academic Test Case Study - Sample Records for 8 Students

58

The PTE Academic Test Case Study - Star Schema with Determinant Dim.

59

To implement the star schema in SQL:

create table CountryVenueDim

as

select distinct C.CountryCode, C.CountryName, T.TestPrice

from Test_Venue T, Country C

where T.CountryCode = C.CountryCode;

create table CitizenshipDim

as

select distinct C.CountryCode as Citizenship, C.CountryName

from Student S, Country C

where S.Citizenship = C.CountryCode;

create table YearDim

as

select distinct to_char(TestDate, ’YYYY’) as Year from Test;

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

60

create table GradeDim (

Grade varchar2(3),

Description varchar2(20),

MinScore number,

MaxScore number

);

insert into GradeDim values (’4.5’, ’Functional’, 30, 35);

insert into GradeDim values (’5’, ’Vocational’, 36, 49);

insert into GradeDim values (’6’, ’Competent’, 50, 64);

insert into GradeDim values (’7’, ’Proficient’, 65, 78);

insert into GradeDim values (’8-9’, ’Superior’, 79, 90);

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

Superior

61

create table TestComponentDim (

TestComponent varchar2(20)

);

insert into TestComponentDim values

(’Listening’);

insert into TestComponentDim values

(’Reading’);

insert into TestComponentDim values

(’Writing’);

insert into TestComponentDim values

(’Speaking’);

insert into TestComponentDim values

(’Overall’);

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

Create temporary fact table:

create table TempFact

as

select TV.CountryCode, S.Citizenship,

to_char(T.TestDate, ’YYYY’) as Year,

TR.ListeningScore, TR.ReadingScore,

TR.WritingScore, TR.SpeakingScore,

TR.OverallScore, TR.RegistrationID

from Test_Venue TV, Test T, Student S,

Test_Result TR

where TV.VenueID = T.VenueID

and T.TestNo = TR.TestNo

and TR.RegistrationID = S.RegistrationID;

62

alter table TempFact

add (

GradeOverall varchar2(3),

GradeListening varchar2(3),

GradeReading varchar2(3),

GradeWriting varchar2(3),

GradeSpeaking varchar2(3)

);

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

update TempFact

set GradeOverall = (

case

when OverallScore >= 30 and

OverallScore <= 35 then ’4.5’

when OverallScore >= 36 and

OverallScore <= 49 then ’5’

when OverallScore >= 50 and

OverallScore <= 64 then ’6’

when OverallScore >= 65 and

OverallScore <= 78 then ’7’

when OverallScore >= 79 and

OverallScore <= 90 then ’8-9’

end);

Repeat for

GradeListening, GradeReading,

GradeWriting, and GradeSpeaking

Superior

63

▪ The TempFact table has the correct grades for each student.

▪ The problem now is to breakdown the grades for a student into multiple test

components – Create another temporary fact for each of the test components, eg. a temporary

fact called ’OverallFact’ for the Overall Score, and so on so forth

create table OverallFact

as

select CountryCode, Citizenship, Year,

GradeOverall As Grade, ’Overall’ as TestComponent,

count(RegistrationID) as Total_Students_Overall

from TempFact

group by CountryCode, Citizenship, Year, GradeOverall, ’Overall’;

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

Repeat for

GradeListening, GradeReading, GradeWriting, and GradeSpeaking

64

▪ There were eleven students taking the test which are now grouped into nine

records in Table OverallFact

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

65

▪ There are also three other fact tables: ReadingFact, WritingFact, and

SpeakingFact, with identical table structures

▪ These five fact tables can now be ’combined’ to form one final fact table

using a union operator: create table FinalFact as

select CountryCode, Citizenship, Year, Grade, TestComponent,

Total_Students_Overall as Total_Students

from OverallFact

union select * from ListeningFact

union select * from ReadingFact

union select * from WritingFact

union select * from SpeakingFact;

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

66

▪ The final fact table contains 45

records

▪ Conclusion: – When the input record in the

operational database contains the

scores of all the test components

as one record, it will be

challenging to break down the

records into multiple records in

the final fact table, because there

is a Test Component dimension.

The PTE Academic Test - Star Schema with Determinant Dim. (cont.)

67

The PTE Academic Test - Star Schema without Determinant Dim.

● Fact measure for all the test components

● This means that the fact table is a Pivoted Fact Table ○ Shifts the Test Component into the fact measure;

○ The Determinant Dimension - TestComponentDim is then removed from the star schema

68

▪ How many records do we have in fact table with five fact measures?

▪ However, since our test result data is incomplete, we will have problems

with the final fact, because in the non-determinant dimension version, we

need to keep track the zeroes – Even if the data is the test result is a lot, but still does not cover all possible

combinations from the dimension!

The PTE Academic Test - Star Schema without Determinant Dim. (cont.)

Answer:

60 records in the final fact table

= 2 countries of test venues * 6 countries of citizenship * 1 year * 5 grades

69

▪ ‘Superior’ grade is only available for Listening and Reading scores

▪ Using the normal fact table, there will not be entry in the fact table for Superior-Speaking, and for

Superior-Writing;

▪ There will not be zero value in TotalStudents, where the Grade column is ’Superior’ and the Test

Component column is either ’Speaking’ or ’Writing’ → less than 60 rows of records in the fact table

The PTE Academic Test - Star Schema without Determinant Dim. (cont.)

Superior

70

Solution: Step1

Get all possible combinations from all dimensions using Cartesian product

between all dimensions:

create table AllDimensions

as

select CO.CountryCode, CI.Citizenship, Y.Year, G.Grade

from CountryVenueDim CO,CitizenshipDim CI,

YearDim Y, GradeDim G;

The PTE Academic Test - Star Schema without Determinant Dim. (cont.)

71

Solution: Step 2

Re-create five temporary fact tables, one for each test component, using Outer

Join operation between AllDimensions table and each of the temporary fact

tables created in the previous section (see slide #35).

The PTE Academic Test - Star Schema without Determinant Dim. (cont.)

create table OverallFactNew as

select A.CountryCode, A.Citizenship,

A.Year, A.Grade,

nvl(O.Total_Students_Overall, 0)

as Total_Students_Overall

from AllDimensions A, OverallFact O

where A.CountryCode = O.CountryCode(+)

and A.Citizenship = O.Citizenship(+)

and A.Year = O.Year(+)

and A.Grade = O.Grade(+);

create table ListeningFactNew as

select A.CountryCode, A.Citizenship,

A.Year, A.Grade,

nvl(O.Total_Students_Listening, 0)

as Total_Students_Listening

from AllDimensions A, ListeningFact O where

A.CountryCode = O.CountryCode(+)

and A.Citizenship = O.Citizenship(+)

and A.Year = O.Year(+)

and A.Grade = O.Grade(+);

create table ReadingFactNew as select ...;

create table WritingFactNew as select ...;

create table SpeakingFactNew as select ...;

72

Solution: Step 3

Create the final fact table using the join operation

The PTE Academic Test - Star Schema without Determinant Dim. (cont.)

create table FinalFact2 as

select O.CountryCode, O.Citizenship, O.Year, O.Grade, O.Total_Students_Overall, L.Total_Students_Listening,

R.Total_Students_Reading, W.Total_Students_Writing, S.Total_Students_Speaking

from OverallFactNew O, ListeningFactNew L, ReadingFactNew R, WritingFactNew W, SpeakingFactNew S

where O.CountryCode = L.CountryCode

and L.CountryCode = R.CountryCode and R.CountryCode = W.CountryCode

and W.CountryCode = S.CountryCode and O.Citizenship = L.Citizenship

and L.Citizenship = R.Citizenship and R.Citizenship = W.Citizenship and W.Citizenship = S.Citizenship

and O.Year = L.Year and L.Year = R.Year and R.Year = W.Year and W.Year = S.Year

and O.Grade = L.Grade and L.Grade = R.Grade and R.Grade = W.Grade and W.Grade = S.Grade;

73

Solution: Step 4

Because our sample data is small, there are many records with zeroes values in

the Total Students columns. Delete records where Total Students in all of the

five test components are equal to zero.

The PTE Academic Test - Star Schema without Determinant Dim. (cont.)

delete from FinalFact2

where Total_Students_Overall = 0

and Total_Students_Listening = 0

and Total_Students_Reading = 0

and Total_Students_Writing = 0

and Total_Students_Speaking = 0;

Non-Type as a

Determinant

Dimension

75

▪ Type Dimension maybe a candidate for a Determinant Dimension:

– PetroTypeDim is a Determinant Dimension in the Petrol Price case

study,

– MedalTypeDim in the Olympic Games case study is a not a

Determinant Dimension

▪ Type Dimension is a good candidate for a Determinant Dimension, but

needs further examination to check if it is really a Determinant Dimension

▪ Other dimensions - non-type dimension can potentially be a Determinant

Dimension

Non-Type as a Determinant Dimension

76

▪ The Class Type Dimension in

this example is not a

Determinant Dimension

because we could get Number

of Students without referring to

any of the class types. ▪ E.g. retrieving number of

students in the Science

Faculty last year is certainly a

useful information, even

without Class Type

information.

Non-Type as a Determinant Dimension – University Enrolment Case Study

Type Dimension

77

Total student of

Science Faculty

Non-Type as a Determinant Dimension – University Enrolment Case Study

Total student of

Science Faculty

last year

vs.

The information is

dependent on the YearDIM

- determinant dimension

78

Converting a

Determinant Dimension

to a Pivoted Fact Table

Non-Type as a Determinant Dimension – University Enrolment Case Study

79

Question 1:

What if the number of records in the Determinant Dimension is huge?

– Number of fact measures increased to 20, 50?

– May not be practical

Solution:

Enforce Determinant Dimension through the User-Interface level ▪ Users are forced to enter/choose a criteria from the Determinant

Dimension.

▪ In filling a web-based form, this enforcement is normally indicated by a

star (*) next to the text entry to indicate that this text entry must be filled

in, before the form can proceed.

Pivoted Fact Table for University Enrolment Case Study

80

Question 2:

What if there are a lot of attributes in the Determinant Dimension? – If we shift the Determinant Dimension to the fact measures, only the key

identifier is kept in the fact measure.

– All other attributes from the Determinant Dimension will be lost from the star

schema. This issue will be discussed in the next section.

Pivoted Fact Table for University Enrolment Case Study (cont.)

Multiple Relationship

between a Dimension

and the Fact

82

▪ Private Taxi Case Study

– The dimension (or a Determinant Dimension) to be shifted to the fact

measure (e.g. Pivoted Fact Table) has many attributes.

– If only the key identifier of the dimension (or the Determinant

Dimension) is used as the fact measure, all other attributes of the

dimension (or the Determinant Dimension) are no longer exists in the

star schema

Multiple Relationship between a Dimension and the Fact

83

▪ Assume that

– The business rules stated that there is no Determinant Dimension. Even without a

Determinant Dimension, we could potentially shift a dimension to become fact measures

– The company is a small private taxi company, has only five cars

▪ In the fact table, instead of having three fact measures, for each fact measure, we could

replicate five times – one for each car.

Private Taxi Case Study

Replicate for each

car ===> 15 fact

measures

Remove this

CarDIM?

84

▪ CarDIM should not be removed – Other details of the car are

included in the data retrieval

▪ Because Car Dimension is not a

Determinant Dimension, we can

simply keep the original star

schema – Include only CarNo in the fact

table

– Use join operation to get more

details about car in query

Private Taxi Case Study (cont.)

select F.MonthYear, D.Make,

D.Model,

sum(F.Total_Kilometers)

from PrivateTaxiFact1 F,

CarDim D

where F.CarNo = D.CarNo

and F.CarNo = 5

and F.MonthYear = ’1908’

group by F.MonthYear, D.Make,

D.Model;

85

Insist that fact measures are broken into five cars

▪ Need to have five Car Dimension’s key

identifier in the fact

Private Taxi Case Study (cont.)

To retrieve Total Kilometers of five cars:

select F.MonthYear, D1.CarMake, D1.Year,

D2.CarMake, D2.Year, D3.CarMake, D3.Year,

D4.CarMake, D4.Year, D5.CarMake, D5.Year,

sum(Total_Kilometers)

from PrivateTaxiFact2 F, CarDim D1, CarDim

D2, CarDim D3, CarDim D4, CarDim D5

where F.CarNo1 = D1.CarNo

and F.CarNo2 = D2.CarNo

and F.CarNo3 = D3.CarNo

and F.CarNo4 = D4.CarNo

and F.CarNo5 = D5CarNo

and MonthYear = ’1908’

group by F.MonthYear, D1.CarMake, D1.Year,

D2.CarMake, D2.Year, D3.CarMake, D3.Year,

D4.CarMake, D4.Year, D5.CarMake, D5.Year;

86

▪ Although conceptually the dimension is already shifted to the fact measure,

we still need to keep the original dimension in the star schema.

▪ This is the only way to preserve all other attributes of that dimension.

▪ This relationship replication is applicable to shifting a Determinant

Dimension to the fact measure.

Multiple Relationship between a Dimension and the Fact

87

Two alternatives to enforce the implementation of a star schema that features a

Determinant Dimension

1. Enforce through the user-interface, whereby the user has to fill in or to

choose the Determinant Attribute during the search.

2. Shift the Determinant Dimension into a Pivoted Fact Table, whereby the key

identifier of the Determinant Dimension is incorporated into the fact

measure.

Determinant Dimension - Conclusion

Oracle/FIT5195-5-MoreComplexProcesses_update (1).pdf