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 the 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.
5. Identify summarizability potential problems in your star schema and indicate preferred resolutions of the summarizability problems. For incomplete dimension-fact relationships, you should also indicate if columns in a dimension table allow null values.
6. You should populate your data warehouse tables based on the data in the sample tables and spreadsheet. You do not need to write SQL INSERT statements or insert data into database tables. You can just show table listings in your solution. You should indicate mappings from data sources into tables. For example, a mapping may involve generating new primary key values for a data warehouse table or using a default value for a missing value.
    

 
 

Transcribed Image Text:

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 MemType Of 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. 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 MemType Of 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.

Answer rating: 100% (QA)

To address the complex business needs and design a proper star schema for Fitness Unlimited well tackle this in a systematic way following the guidelines youve provided 1 Dimension Identification and ... View the full answer