Designing the Data Warehouse for Analysis Services - Expert Cube Development with SSAS Multidimensional Models - page 15

All fact tables represent many-to-many relationships between dimensions,
but for bridge tables, the recording of this relationship is their only reason
to exist. They do not contain any numeric columns—facts—that can be
aggregated (hence the use of the name factless fact table). Regular fact
tables generate many-to-many relationships as a side effect, as their reason
for being is the nature of the fact, not of the relationship.

Now, let us see an example of a bridge table. Consider the following situation in an
OLTP database:

SpecialOfferProduct

PK,FK2
PK,FK1,I1

SpecialOfferID
ProductID

U1 rowguid

ModifiedDate

SpecialOffer Product

PK SpecialOfferID PK ProductID

Description
DiscountPct
Type
Category
StartDate
EndDate
MinQty
MaxQty
rowguid
ModifiedDate

U1 Name

ProductNumber
MakeFlag
FinishedGoodsFlag
Color
SaftyStockLevel
RecorderPoint
StandardCost
ListPrice
Size
SizeUnitMeasureCode
WeightUnitMeasureCoder
Weight
DaysToManufacture
ProductLine
Class
Style
ProductSubcategoryID
ProductModelID
SellStartDate
SellEndDate
DiscontinuedDate
rowguid
ModifiedDate

U2

U1

FK3
FK4

FK2
FK1

U3

In any given period of time, a product can be sold on special offer. The bridge table
( SpecialOfferProduct ) tells us which products were on special offer at what times,
while the SpecialOffer table tells us information about the special offer itself: when
it started, when it finished, the amount of discount, and so on.