C2C Auctions is an online auction business that allows individuals to buy and sell new or used items to others using their online auction platform. You are to assume the role of a Data Warehouse Consultant that has been assigned to develop a Dimensional Model for their auctions process. The following information has been obtained for use in constructing the Dimensional Model.

Business Background

An auction site is a marketplace for buyers and sellers to exchange goods using the auctions approach. The success of such a marketplace depends on having a large enough number of participants and having a good balance of buyers and sellers with available demand and supply of items. The key performance metrics for the overall business would be the number of transactions in the marketplace and the total amount of money exchanged in the transactions. Key business decisions would be related to achieving the goals of increasing number of transactions (items sold) and transaction amounts (item price). C2C Auctions is trying a number of strategies to achieve the above goals.

Strategy

The first strategy the company plans to implement is to develop a mobile app and expand their customer base (buyers and sellers) in 18-55 age group range with incomes in the range of 70k 250k. As part of a second strategy, the company also wants to explore providing a new local auctions feature for buyers and sellers are located in same zipcode or city and can do transactions without shipments.

Data and Information Sources

The transactional database schema of the company is shown below. In addition to the transactional database, other information available to the company are a demographics database (not shown in schema) from a third party that includes income, age and address information.

Deliverables

A short report (minimum 250 words, maximum 1 page, 11point font) describing the 4 step dimensional process used to arrive at the star schemas. The description for each step should describe output of the step and justification. (For example The grain of the model is one row per This grain was chosen because..

The Star Schema

Descriptions of each table and table column

Three sample reports that can be generated from the data warehouse and how those will generate business understanding and help make decisions.

Short narrative (Min 250 words, Max 1 page) that describes how the data warehouse can help the company in its strategy.

Sample Report Format
Report Name	Store Sales Performance Report
Report Description	This report provides an overview of variation in sales for different stores across different years, and month to month (year ago) comparisons. For example to compare store sales across different years for the month of September set filter to Month = September. The time dimension can be rolled up from Month to Year. The store dimension can be rolled up to City, State and Region. The report can help identify stores that have a declining sales or profit trend over years, and help identify if the slowdown in sales is in specific months or across the year.
OLAP Report Column Labels	Year
OLAP Report Row Labels	Store (with Geographic Hierarchy)
Report Filters	Month= September
OLAP Report Cell Values	Extended Sales Dollar Amount Extended Gross Profit Dollar Amount

Rubric

Dimensional Modeling Process narrative lists the key outputs and justification (except business process since it is already given). (15 points)

The start schema includes all relevant dimensions and facts, and is free of errors. (40 points)

Definitions and descriptions are given for each table and table column. (10 points)

Facts are identified as additive, semi-additive or not additive.

Degenerate dimensions (if any) are identified.

Include all details of three sample reports. (15 points)

The narrative clearly describes how specific data and specific reports from the DW can be used in specific decisions related to the companys two strategies. (20 points)