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 retail database

$($

sample tables and ERD in Figure

$13$

$.$

CS

$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.

Franchises also sell special events to corporate customers 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.

Data Source Size Estimates To estimate 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 Intelligence Requirements

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 amount is computed as quantity times selling price. For services purchases, each unit sale is recorded separately so only the service price at the time of purchase is recorded. For customers, merchandise sales should be tracked by zip code, membership date, and member type. For franchise, merchandise sales should be tracked by franchise region, postal code, and model type. The corporate sales office wants a high level of flexibility for sales analysis. For data mining analysis, the sales office needs details by individual customer, product or service, franchise, and date. For typical reporting applications, the sales office needs details by customer location, franchise location, product or service type, and week.

Schema Integration Requirements

You should design a star schema

$($

or variation

$)$

$$to support revenue analysis. You should pay close attention to the grain of the fact table, a major part of the star schema diagram. As part of the design, you should identify all relevant dimensions with hierarchies specified. In your documentation, you should indicate design transformations, summarizability problems in your star schema, and mapping from data sources into tables. You should populate your data warehouse tables based on the data in the operational tables and spreadsheet. You do not need to insert the data into your tables. You can just show table listings in your solution document. Your sample rows should include all revenue events in the range from February

$10$

$,$

$2020$

$,$

$$to February

$21$

$,$

$2020$

$.$

$1$

$.$

$$You should identify dimensions, map dimensions to data sources, and specify dimension hierarchies. For each dimension, you should identify its data sources and attributes in each data source. For hierarchical dimensions, you should indicate the levels from broad to narrow.

$2$

$.$

$$You should specify measures, related data sources, and measure aggregation properties.

$3$

$.$

$$Identify the grain in your dimensional design using the business needs as a guideline. You should then indicate relative storage requirements for the grain using statistics for the data sources. Using the cardinality estimates provided, you should determine either the fact table size or sparsity and then compute the unknown grain size variable. For example, you should compute sparsity if the fact table size is given.

$4$

$.$

$$Extend your analysis to design a star schema

$($

or variation

$)$

$$to support inventory analysis. For each table, you should define the table name, primary key, and columns. You do not need to write complete CREATE TABLE statements. Apply design transformations, especially the flatten and merge transformations where appropriate.