Database Reference
In-Depth Information
We normally relate a fact table to several dimension tables, but we do not relate
fact tables directly with other fact tables. Facts and dimensions are related via
surrogate keys. This is one of the foundations of Kimball's methodology.
When you build an Analysis Services solution, you build Analysis Services
dimension objects from the dimension tables in your data mart and cubes on top
of the fact tables. The concepts of facts and dimensions are so deeply ingrained
in the architecture of Analysis Services that you are effectively obliged to follow
dimensional modeling methodology if you want to use Analysis Services at all.
Star schemas and snowflake schemas
When we define dimension tables and fact tables and create joins between them, we
end up with a star schema. At the center of a star schema there is always a fact table.
As the fact table is directly related to dimension tables, if we place these dimensions
around the fact table, we get something resembling a star shape, as shown in the
following diagram:
DImCurrency
PK
CurrentKey
U1
CurrencyAlternateKey
CurrencyName
DimProduct
PK
ProductKey
DImCustomer
U1
FK1,I1
ProductAltemateKey
ProductSubcategoryKey
WeightUnitMeasureCode
SizeUnitMeasureCode
EnglishProdName
FrenchProductName
StandardCost
Finished GoodsFlag
Color
SaftyStockLevel
ReorderPoint
ListPrice
Size
SizeRenge
Weight
DaysToManufacture
ProductLine
DealerPrice
Class
Style
ModelName
LargePhoto
EnglishDescription
FrenchDescription
ChineseDescription
ArabicDescription
ThaiDescription
StartDate
EndDate
Status
CustomerKey
PK
FactInternetSales
FK1,U1
U1
GeographyKey
CustomerAlternateKey
Title
FirstName
MiddleName
LastName
NameStyle
BirthDate
MaritalStatus
Suffix
Gender
EmailAddress
YearlyIncome
TotalChildren
NumberChildrenAtHome
EnglishEducation
SpanishEducation
EnglishOccupation
SpanishOccupation
FrenchOccupation
HouseOwnerFlag
NumberCarsOwned
AddressLine1
AddressLine2
Phone
DataFirstPurchase
CommuteDistance
SalesOrderNumber
SalesOrderLineNumber
ProductKey
OrderDateKey
DueDateKey
ShipDateKey
CustomerKey
PromotionKey
CurrencyKey
SalesTerritoryKey
RevisionNumber
OrderQuantity
UnitPrice
ExtendedAmount
UnitPriceDiscountPot
DiscountAmount
ProductStandardCost
TotalProductCost
SalesAmount
TaxAmt
Freight
CarrierTrackingNumber
CustomerPONumber
FK3,I6
FK6,I5
FK7,I4
FK8,I1
FK2,I3
FK4,I7
FK1,I2
FK5
U1
DImSalesTerritory
PK
SalesTerritoryKey
U1
SalesTerritoryAlternativeKey
SalesTerritoryRegion
SalesTerritoryCountry
SalesTerritoryGroup
 
Search WWH ::




Custom Search