Mini case for the assignment in module 3

The assignment in module 3 gives you the opportunity to apply most of the concepts.

of module 3 and some of the concepts of module 2. Since the assignment is based

closely on the practical problems in module 3, you should attempt the mini case study

for the practice problems and then review the details of the solution.

This mini case study contains two data sources with sample data along with

a business needs statement. With the data sources and the needs of

business, will specify a dimensional model with dimensions, measures, and granularity, will create a

design of a schema for the data warehouse that integrates the data sources, will identify the

integration problems in the design and will fill the rows tables of the data warehouse.

example in the data sources.

Data sources
Fitness Unlimited is a leading provider of fitness centers with a variety of

fitness conditioning programs and membership options. Fitness Unlimited maintains

a retail database to track the sales of services and goods. In the ERD for the

retail database (Figure 1), a sale contains a title (Sale) with sale date and a

collection of merchandise registered in the M-NContains relationship. Service purchases are

record in the entity type ServPurchase with 1-M relationships of ServiceCategory and

Member. The typical services are lessons, use of premium equipment, and social events.

relationMemTypeOf is optional for members because invited members can use

a fitness center and purchase short-term products and services without having a membership

Paid. The tables with sample rows are shown after figure 1.
April 19, 2019 Assignment of module 3 Page 2

Franchise MemberType
FranchId MemTypeId
FranchRegion MemTypeName
French Postal Code MemTypePrice
Franchise Model Type

MemTypeOf

Member
Franchise Of Member ID Sale
Member Name
ServiceCategory MmbrZip Sold To SaleId
SaleDate
ServCatId Member Email
ServCatName Member Date
ServCatPrice
Qty
Contains

ServPurchase ServMember
Merchandise
Service Category ServPurchId
Of
Service Purchase Date MerchId
MerchName
MerchPrice
Merchandise Type

Figure 1: ERD for the Retail Fitness database

Franchise
FranchId
F1 Northwest 98011 Complete
F2 Mountain 80111 Medium
F3 Central 45236 Limited

MemberType
MemTypeId
M1 Platinum $1,000
M2 Gold $800
M3 Value $300

ServiceCategory
ServCatId Service Category Price
SC1 Ball machine $15
SC2 Private lesson $75
SC3 Adult class $150
SC4 Secondary school $125
April 19, 2019 Assignment of module 3 Page 3

Merchandise
MerchId MerchPrice
MC1 Wilson balls $3 Balls
MC2 The Wilson racket $200 Racket
MC3 Adidas sneakers $100 Shoes
MC4 The racket $40 Racket

Member
Member ID FranchId Member Email
1111 Joe 98011 M1 February 1, 2009F1 [email protected]
2222 Maria 80112 M2 January 1, 2010 F2 [email protected]
3333 Sue 45327 M3 March 3, 2011 F3 [email protected]
4444 Jorge 45236 F3 [email protected]

Sale
SaleId SaleDate Member ID
1111 10-Feb-2013 1111
2222 13-Feb-2013 2222
3333 February 13, 20132222
4444 February 14, 20133333

Contains
MerchId Qty
MC1 1111 2
MC2 1111 1
MC4 2222 1
MC3 3333 1
MC4 4444 1

Service Purchase
ServPurchId
1111 13-Feb-2013 1111 SC1
2222 February 14, 20132222 SC2
4444 15-Feb-2013 4444 SC3

Franchises also sell special events to companies and other organizations.

Since promotions and sales for special events are not standard among the

Franchises, spreadsheets are often used to track events.

special. The franchise sales database was never expanded to accommodate sales

of special events. The special events worksheet shows a typical format for the

sales tracking of special events by a franchise. Most franchises

they use a similar spreadsheet.

April 19, 2019 Assignment of module 3 Page 4

Special Events Worksheet

Corporate Corporate Customer Name Event Type Event Date Amount
Customer And Location Code
Id
CC1 First Data, Greenwood Village The A Adult Social February 13, 2013 $1,000
CC2 DU Tennis, Denver L-B Pioneer social 14-Feb-2013 $500
CC3 Creek High School L-C Team Practice February 21, 2013 $200
Greenwood
Village

Estimates of data source size

To calculate the grain size, you should use these estimates about the cardinalities of

the tables and the unique values of some columns.

Filas de Franquicia: 350

Códigos postales de Franquicia: 200

MemberType rows: 10

Goods Rows: 500

Valores de MerchType: 30

Service Category Rows: 20

Member Rows: 50,000

500

Sales Rows: 150,000 per year

Containment Rows: 450,000 per year

ServicePurchase rows: 100,000 rows per year

SpecialEvents rows of the worksheet: 300 per year per franchise with 200

Franchises using this spreadsheet

150 unique clients per special event sheet

April 19, 2019 Assignment of module 3 Page 5

Business needs
The data warehouse must support the analysis of merchandise sales and purchases.

franchise services, merchandise or type of service, and customer over time. For the

merchandise, the sales amount is calculated as the number of times the selling price. For the

service purchases, each unit sale is registered separately, so only the price

Service at the time of purchase is recorded. For the customer, the sale of goods.

They must be tracked by postal code, membership date, and member type. For the

franchise, merchandise sales must be tracked by franchise region, postal code

and type of model.

The corporate sales office wants a high level of flexibility for analysis.

sales. For data mining analysis, they need details by individual client, product or

service, and franchise and date. For typical reporting applications, they need details by

customer location, franchise location, type of product or service, and the week.

Problems
You must design a star schema (or variation) to support revenue analysis.

You must pay close attention to the grain of the fact table, the main part of the diagram.

star schema. As part of the design, you must identify all relevant dimensions

with specified hierarchies. In the documentation, it should identify the integration issues

in the star schema and indicate the assignment of data sources in tables.

You must fill the data warehouse tables based on the data from the tables.

operational and the spreadsheet. It is not necessary to insert the data into the tables. You can only

show table listings in the solution document. The sample rows must include all

the income events from the interval of February 10, 2013 to February 21, 2013.
April 19, 2019 Assignment of module 3 Page 6

1. You must identify the dimensions, assign dimensions to data sources, and specify the

hierarchies of dimensions. For each dimension, you must identify its data sources and the

attributes in each data source. For hierarchical dimensions, you must indicate the levels

from wide to narrow.

2. It must specify measures, related data sources, and measure the properties of

aggregation.

3. Identify the grain in your dimensional design using business needs as

guide. Next, you must indicate the relative storage requirements for the grain

using statistics from data sources. With cardinality estimates

provided, it must determine the size of the fact table or the dispersion and, to

continuation, calculate the unknown grain size variable. For example, it must

calculate the dispersion if the size of the fact table is given.

4. Expand the analysis to design a star schema (or variation) to support the analysis.

of inventory. For each table, you must define the table name, the primary key and the

columns. It is not necessary to write complete CREATE TABLE instructions.

5. Identify the possible integration issues in your star schema and indicate them.

preferred resolutions of integration problems. For dimension relationships-

incomplete facts, it should also indicate whether the columns of a dimension table

allow null values.

6. You must fill in the data-warehouse tables based on the data from the sample tables and

from the spreadsheet. It is not necessary to write SQL INSERT statements or insert the

data in the database tables. It can only display the listings of the tables in its

solution. It should indicate assignments of data sources in tables. For example, a

April 19, 2019 Assignment of module 3 Page 7

Assignment may involve the generation of new primary key values for a table.

from the data warehouse or the use of a default value for a missing value.

Quality of the solution

Quality is quite subjective in data warehouse designs, but some
elements are less subjective. I suggest addressing these quality elements in the part
adequate of the solution.

Schema pattern: must use a recognized schema pattern: star schema,

constellation or snowflake.

Selection of the fact table: you must study the selection of the fact table in the
solution for the mini case of the practice to get inspired. Normally, the table of facts
combine a two-level solution in a source scheme into a single fact table.
For example, an order header and the order details are usually combined in
a fact table that records order details with dimension relationships for
capture the order header.

There is missing data in the filled tables: you must ensure that the filled tables include
all income events shown in both data sources. The best verification
the design of the schema consists of assigning example rows from the data sources to the data-
warehouses.

Simplicity: normally, a data warehouse schema design simplifies the

schemes of the underlying data sources. Simplification may imply the
combination of some elements from data sources in decisions about dimensions and
fact tables.

Classification
The evaluation method for this assignment is peer review. Each problem has
a degree equal.

Submission
You must submit 6 documents for peer review for the assignment of module 3. Each
The document contains a complete solution to the problem. You must format it.
April 19, 2019 Assignment of module 3 Page 8

carefully your documents so that it can be easily graded. Please write the
problem number at the top of the page.