Lab+Task+Solution.doc - Solution for Lab Task 1. The...

Solution for Lab Task
1. The dimensions in the problem somewhat difficult because dimensions should be combined
in data sources. Item and customer combine parts of both data sources. The calendar
dimension is a standard, hierarchical dimension. Email can be parsed to be hierarchical as
part of the customer dimension.
 Franchise
o FranchId: retail database only
o FranchRegion retail database only
o FranchPostalCode: retail database only
o FranchModelType: retail database only
o For the special events worksheet, selected franchises maintain spreadsheets so the
franchise will be derived from the spreadsheet submission.
 Calendar
o Date columns in the retail database (SaleDate, ServPurchDate, and MmbrDate) and
spreadsheet (EventDate); hierarchical (year  month  day)
 Item: combines Merchandise, Service, and Events
o ItemId (Merchandise table) | ServCatId (ServiceCategory table)
o ItemName (Merchandise table) | ServCatName (ServiceCategory table) |
MemberTypeName (MemberType table) | Event Name (spreadsheet)
o ItemType (Merchandise table) | ServiceCategory table |Event Type Code
(spreadsheet)
 Customer: combines members and corporate customers
o MmbrId (Member table) | Corporate Customer Id (spreadsheet)
o MmbrName (Member table) | Corporate Customer Name (spreadsheet but must be
parsed, because two values are stored in same column in spreadsheet)
o Corporate Customer Location (spreadsheet): must be parsed
o MmbrEmail: retail database (Member table); hierarchical (top level domain  second
level domain  local part)
o