Helper tables are used by the Oracle Business Analytics
Warehouse to solve complex problems that cannot be resolved by simple
dimensional schemas.
In a typical dimensional schema, fact records join to
dimension records with a many-to-one relationship. To support a many-to-many
relationship between fact and dimension records, a helper table is inserted
between the fact and dimension tables.
The helper table can have multiple records for each fact and
dimension key combination. This allows queries to retrieve facts for any given
dimension value. It should be noted that any aggregation of fact records over a
set of dimension values might contain overlaps (due to a many-to-many
relationship) and can result in double counting.
At times there is a requirement to query facts related to
the children of a given parent in the dimension by only specifying the parent
value (example: manager's sales fact that includes sales facts of the manager's
subordinates). In this situation, one helper table containing multiple records
for each parent-child dimension key combination is inserted between the fact
and the dimension. This allows queries to be run for all subordinates by
specifying only the parent in the dimension.
No comments:
Post a Comment