ITS BI Foundation Assignment: Star Schema Background: You and a colleague have b

Question

ITS BI Foundation Assignment: Star Schema Background:

You and a colleague have been hired to create a reporting system for venture capitalists to assess entrepreneur proposals. Aspiring entrepreneurs are required to complete a form when submitting a proposal for funding as shown below:

Company Name:

Proposal Date:

Headquarters (city):

Proposal Amount:

Founder Salary Amounts (totals):

Industry (select one): Retail, Health Care, Construction, CPG, High Tech, Energy, Manufacturing

Product Type (select one): Consulting, Sales, Call Center, Hardware, Software, Devices

Proposal (free form description):

The venture capitalists classify the Industry values of Retail and Health Care as a Consumer industry type and the others as Business industry type. The Product Type of Consulting, Sales, and Call Center are classified as a Service product line and the others as Product product line. The venture capitalists also track by state whether the state laws requires a certificate for LLC dissolution (yes or no).

The venture capitalists want to examine proposal amounts by date (day, month, year), city (and state and state LLC dissolution), product type, and industry (using the related hierarchies for each dimension).

Step 1: Identify star schema categories (2 points)

Assign the data fields available and relevant to be used in the star schema and assign each to one of four categories: fact, dimension, attributes, or hierarchy (provide the answers in the form of a list or table).

Step 2: Create star schema (4 points)

Create a representation of the star schema (use whatever tool you prefer to render the design) similar in presentation to the schemas discussed in class. Indicate which table is the fact table and which tables are the dimension tables. Do not include any metadata such as data type or length.

Step 3: Create a second star schema (3 points)

The venture capitalists decide they only want to see the product by line, not type. Create a second star schema reflecting this change. Indicate which table is the fact table and which tables are the dimension tables. Do not include any metadata such as data type or length.

Explanation / Answer

Answer:

In this case the facts are:

1.Sales

2. Date

3. sector (retail, health care etc)

4. units sold

Dimensions are:

1. Sector

2. Products

3. Time

4. Sales

A star schema would contain one fact and other would be linked to it as a dimension.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.