Ch 8 Ideas

1
Ch 8 Ideas
Managing employee transactions in a type 2
dimension Audit dimension Keyword
dimensions Survey facts
2
Employee transactions
A transaction is performed in a legacy system
each time an employee profile changes We can
capture these with a factless fact table
Employee Transaction
Date
Transaction fact
Time
Transaction type
Figure 8.1
3
Employee transactions
If the Employee Transaction dimension is a type 2
with effective and end dates then all but the
time and transaction type are included.
Employee Transaction
Date
Transaction fact
Time
Transaction type
4
Employee transactions
Figure 8.2 augments the Employee Transaction
dimension with transaction time and a transaction
indicator. This obviates the need for the star
schema of Figure 8.1
Employee Transaction
This could be the Employee surrogate key This
enables the dimension to appear wherever the
Employee dimension appears
Employee transaction key (PK) Employee id
(natural key) Employee name Health insurance
plan Vacation plan transaction type Transaction
date Transaction time Effective date Expiration
date Most recent indicator
5
HR Snapshot
Figure 8.3
Month
Employee Transaction
snapshot
organization
Employee transaction key (PK) Employee id
(natural key) Employee name Health insurance
plan Vacation plan transaction type Transaction
date Transaction time Effective date Expiration
date Most recent indicator
Salary paid Overtime paid Overtime
hours Employee count New hire count Transfer
count Promotion count
Obvious metrics
Indicators 1 or 0
How could we arrange for the Month dimension to
be derivable from the Date dimension?
6
Audit dimension
The audit dimension has one row for each run of
an ETL process Any fact row created by an ETL
process can be related to the audit dimension.
Now, any analyst/DBA can determine when a fact
got into the database Figure 8.4 lists typical
attributes for the audit dimension
7
Keyword dimension

• Consider the skills that employees may have
• PL/I, Pacal, Fortran, C,
• Skills are multivalued and cannot be included in
  an Employee dimension attribute.
• Solutions
• Use a skill outrigger table
• One row per skill (here well use
  Union/Intersection)
• Many values per row (here well use like)

Employee
Skill group
Many to many
Employee
Skill list
one to many
8
Surveys
Sent date
survey
received date
Survey fact
Response category
Responding employee
response
Reviewer employee
Question