Database Reference
In-Depth Information
A common way of handling this situation is to denormalize the special offer
information into a dimension directly linked to the fact table, so we can easily see
whether a specific sale was made under special offer or not. In this way, we can use
the fact table to hold both the facts and the bridge. Nevertheless, bridge tables offer a
lot of benefits, and in situations such as this, they are definitely the best option. Let's
take a look at the reasons why.
It is interesting to consider whether we can represent the relationship in the
preceding example only using fact tables (that is, storing three types of data for each
sale: product, sale, and special offer) or whether a bridge table is necessary. While
the first option is certainly correct, we need to think carefully before using it because
if we do use it, all data on special offers that did not generate any sales will be lost.
If a specific special offer results in no product sales, then the fact table is not storing
the relationship between the special offer and the product anywhere—it will be
exactly as though the product had never been on special offer. The fact table does
not contain any data that defines the relationship between the special offers and the
products, it only knows about this relationship when a sale is made. This situation
may lead to confusion or incorrect reports. We always need to remember that the
absence of a fact may be as important as its presence. Indeed, sometimes the absence
of a fact is more important than its presence.
We recommend using bridge tables to model many-to-many relationships that do
not strictly depend on facts to define the relationship. The relationships modeled
by many-to-many relationships are often not bound to any fact table and exist
regardless of any fact table. This shows the real power of bridge tables, but as
always, the more power we have the bigger our responsibilities will be, and bridge
tables will inevitably sometimes cause us headaches.
Bridge tables are modeled in Analysis Services as measure groups that act as bridges
between different dimensions, through the many-to-many dimension relationship
type, one of the most powerful features of Analysis Services. This feature will be
analyzed in greater detail in Chapter 6 , Adding Calculations to the Cube .
Snapshot and transaction fact tables
Now that we have defined what a fact table is, let us go deeper and look at the two
main types: transaction fact tables and snapshots.
A transaction fact table records an event, and for that event, certain measurements can
be made. When we record a sale, for example, we create a new row in the transaction
fact table that contains information relating to the sale, such as what product was sold,
when the sale took place, what the value of the sale was, and so on.
 
Search WWH ::




Custom Search