Databases Reference
In-Depth Information
Type 1 Time Variant dimension there is no representation of the past. So,
there is no need to try to join to the past. Instead, each entity has only one
row, and that one row is the goal of a join to that entity.
When the query path goes from a fact table to a Type 1 dimension table,
the fact table joins to an Entity Key in a dimension table. That dimension
table joins to another dimension table via the Entity Key of the second
dimension table. The second dimension table joins to a third dimension
table via the Entity Key of the third dimension table, and so on.
entity foreign keys
When the query path goes from a Type 1 dimension table to a fact table,
the dimension table joins to the fact table via a foreign Entity Key. A Type
1 dimension table can join to a second Type 1 dimension table by finding
its Entity Key as a foreign key in the second dimension table. The second
dimension table can also join to a t hird dimension table by finding its
Entity Key as a foreign key in the third dimension table, and so on. Finally,
the lowest-level Type 1 dimension table joins to a fact table by finding its
Entity Key as a foreign key in the fact table.
To optimize the performance of these joins from a Type 1 dimension
table to another Type 1 dimension table, or a fact table, the foreign Entity
Keys should be covered by a secondary index. By joining to the second-
ary index, the join from one dimension table to another dimension table
should be able to find a more eἀ cient explain than a full table scan.
fAct tABles
The rows of a fact table represent individual enterprise events or transac-
tions. In a data warehouse, the fact tables represent the business functions
of the data warehouse. The rows of the fact tables represent the individual
occurrences of those business functions. The Entity Keys and Instance
Keys in a fact table identify the individual enterprise entities involved in
the business function.
The primary key of a fact table has no relevance to this Time Variant
Solution Design. So, the primary key should be designed to optimize BI
reports that query large numbers of fact rows. The foreign keys that join
to dimension tables, however, are relevant to this Time Variant Solution
