Assignment+2 | Data Warehouse | Table (Database)

Please download to get full document.

View again

of 7
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report



Views: 35 | Pages: 7

Extension: PDF | Download: 3

Related documents
This is the assignment of data warehouse.
  Submission Deadline: Sunday 19 th  February @ 23:55  Assignment 2 This assignment gives you the opportunity to apply most of the concepts you learnt till now. This case study contains two data sources with sample data along with a statement of  business needs. Using the data sources and business needs, you will specify a dimensional model with dimensions, measures, and grain, create a schema design for the data warehouse that integrates the data sources, identify summarizability problems in the design, and populate data warehouse tables from sample rows in the data sources. Data Sources Fitness Unlimited is a leading provider of exercise centers with a variety of fitness  programs and membership options. Fitness Unlimited maintains a retail database to track sales of services and merchandise. In the ERD for the retail database (Figure 1), a sale contains a heading ( Sale ) with sales date and a collection of merchandise recorded in the M-N relationship Contains . Service purchases are recorded in the ServPurchase  entity type with 1-M relationships from ServiceCategory and Member. Typical services are lessons, premium equipment usage, and social events. The  MemTypeOf   relationship is optional for members because guest members can use a fitness center and purchase merchandise and services on a short term basis without having a paid membership. Tables with sample rows are shown after Figure 1.   Assignment 2 Page 2 MmbrIdMmbrNameMmbrZipMmbrEmailMmbrDate Member  MemTypeIdMemTypeNameMemTypePrice   MemberType ServCatIdServCatNameServCatPrice ServiceCategory ServMember MemTypeOf  MerchIdMerchNameMerchPriceMerchType Merchandise SaleIdSaleDate Sale ContainsSoldTo ServPurchIdServPurchDate ServPurchase ServCatOf Qty FranchIdFranchRegionFranchPostalCodeFranchModelType Franchise FranchiseOf    Figure 1: ERD for Retail Fitness Database Franchise  FranchId FranchRegion FranchPostalCode FranchModelType F1 Northwest 98011 Full F2 Mountain 80111 Medium F3 Central 45236 Limited MemberType  MemTypeId MemTypeName MemTypePrice M1 Platinum $1,000 M2 Gold $800 M3 Value $300 ServiceCategory ServCatId ServCatName ServCatPrice SC1 Ball machine $15 SC2 Private lesson $75 SC3 Adult class $150 SC4 Child class $125   Assignment 2 Page 3 Merchandise  MerchId MerchName MerchPrice MerchType MC1 Wilson balls $3 Balls MC2 Wilson racket $200 Racket MC3 Adidas shoes $100 Shoes MC4 Racket stringing $40 Racket Member  MmbrId MmbrName MmbrZip MemTypeId MmbrDate FranchId MmbrEmail 1111 Joe 98011 M1 1-Feb-2009 F1 2222 Mary 80112 M2 1-Jan-2010 F2 3333 Sue 45327 M3 3-Mar-2011 F3 4444 George 45236 F3 Sale SaleId SaleDate MmbrId 1111 10-Feb-2013 1111 2222 13-Feb-2013 2222 3333 13-Feb-2013 2222 4444 14-Feb-2013 3333 Contains  MerchId SaleId Qty MC1 1111 2 MC2 1111 1 MC4 2222 1 MC3 3333 1 MC4 4444 1 ServicePurchase ServPurchId ServPurchDate MmbrId PassCatId 1111 13-Feb-2013 1111 SC1 2222 14-Feb-2013 2222 SC2 4444 15-Feb-2013 4444 SC3 Franchises also sell special events to corporate and other organizations. Since special event promotions and sales are not standard among franchises, spreadsheets are typically used to track special events. The franchise sales database was never extended to accommodate special event sales. The Special Events Worksheet shows a typical format for tracking special event sales by a franchise. Most franchises use a similar spreadsheet.   Assignment 2 Page 4 Special Events Worksheet Corporate Customer Id Corporate Customer Name  And Location  Event Type Code  Event Name Event Date Amount CC1 First Data, Greenwood Village L-A Adult Social 13-Feb-2013 $1,000 CC2 DU Tennis, Denver L-B Pioneer Social 14-Feb-2013 $500 CC3 Creek High School, Greenwood Village L-C Team Practice 21-Feb-2013 $200 Data Source Size Estimates To compute grain size, you should use these estimates about cardinalities of tables and unique values of some columns.    Franchise rows: 350    Franchise postal codes: 200    MemberType rows: 10    Merchandise rows: 500    MerchType values: 30    ServCategory rows: 20    Member rows: 50,000    Member zip codes: 500    Sale rows: 150,000 per year    Contains rows: 450,000 per year    ServicePurchase rows: 100,000 rows per year    SpecialEvents Worksheet rows: 300 per year per franchise with 200 franchises using this spreadsheet    150 unique customers per special event worksheet Business Needs The data warehouse should support analysis of merchandise sales and service purchases  by franchise, merchandise or service type, and customer over time. For merchandise, sales
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks