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

In the preceding diagram, we can see that there is one fact table, FactInternetSales ,
and four dimension tables directly related to the fact table. Looking at this diagram,
we can easily understand that a customer buys a product with a specific currency
and that the sale takes place in a specific sales territory. Star schemas have a useful
characteristic; that they are easily understandable by anybody at first glance.

Moreover, while the simplicity for human understanding is very welcome, the same
simplicity helps Analysis Services understand and use star schemas. If we use star
schemas, Analysis Services will find it easier to recognize the overall structure of
our dimensional model and help us in the cube design process. On the other hand,
snowflakes, described later, are harder both for humans and for Analysis Services
to understand, and we're much more likely to find that we make mistakes during
cube design—or that Analysis Services makes incorrect assumptions when setting
properties automatically—the more complex the schema becomes.

It is not always easy to generate star schemas; sometimes we need (or inexperience
causes us) to create a more complex schema that resembles that of a traditional,
normalized relational model. Look at the same data mart when we add the
Geography dimension, 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

PK CustomerKey

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

FK3,I6
FK6,I5
FK7,I4
FK8,I1
FK2,I3
FK4,I7
FK1,I2
FK5

ProductKey
OrderDateKey
DueDateKey
ShipDateKey
CustomerKey
PromotionKey
CurrencyKey
SalesTerritoryKey
RevisionNumber
OrderQuantity
UnitPrice
ExtendedAmount
UnitPriceDiscountPot
DiscountAmount
ProductStandardCost
TotalProductCost
SalesAmount
TaxAmt
Freight
CarrierTrackingNumber
CustomerPONumber

U1

DImSalesTerritory DImGeography

PK SalesTerritoryKey PK GeographyKey

U1 SalesTerritoryAlternativeKey

SalesTerritoryRegion
SalesTerritoryCountry
SalesTerritoryGroup

City
StateProvinceCode
StateProvinceName
CountryRegionCode
EnglishCountryRegionName
SpanishCountryRegionName
FrenchCountryRegionName
PostalCode
SalesTerritoryKey FK1