Assignment

Part 1

1. The following statement applies to snowflaked schemas:
a. Large savings in storage space
b. Normalized structures are more difficult to update and maintain
c. Schema is less intuitive and end-users are put off by complexity
d. Ability to browse through the contents is easier
e. Improved query performance because of additional joins

2. The following statement applies to aggregation:
a. Aggregates are pre-calculated summaries derived from the most granular table
b. Each aggregate table is created as a specific summarization of only one dimension.
c. Formation of aggregate fact tables is not effective in improving query performance.

3. The following statement applies to data warehouses:
a. Data warehouses are typically constrained to a single application.
b. Data warehouses are optimized for performing read-write operations of single point transactions.
c. Data warehouses integrate copies of transaction data from disparate source systems.
d. Data warehouses are linked to the front-end application.

4. The following statement is an example of subject-oriented:
a, Each sales representative's sales are tracked over time.
b. The data warehouse is designed to track sales.
c. Sales representatives' information is only stored once.
d. Revenue is calculated by sales, region, etc.
e. Revenue data is extracted from the data warehouse.

5. The following statement is an example of nonvolatile:
a. Each sales representative's sales are tracked over time.
b. The data warehouse is designed to track sales.
c. Sales representatives' information is only stored once.
d. Revenue is calculated by sales, region, etc.
e. Revenue data is extracted from the data warehouse.

6. The following statement is an example of historical:
a. Each sales representative's sales are tracked over time.
b. The data warehouse is designed to track sales.
c. Sales representatives' information is only stored once.
d. Revenue is calculated by sales, region, etc.
e. Revenue data is extracted from the data warehouse.

7. The following statement is an example of summarized:
a. Each sales representative's sales are tracked over time.
b. The data warehouse is designed to track sales.
c. Sales representatives' information is only stored once.
d. Revenue is calculated by sales, region, etc.
e. Revenue data is extracted from the data warehouse.

8. The following is an example of integrated:
a. Each sales representative's sales are tracked over time.
b. The data warehouse is designed to track sales.
c. Sales representatives' information is only stored once.
d. Revenue is calculated by sales, region, etc.
e. Revenue data is extracted from the data warehouse.

9. Which of the following is correct in related to Online transaction processing (OLTP) and online analytical processing (OLAP) systems:
a. Normalized schemas for OLTP systems.
b. Regular backups with full, incremental, and archives for OLTP systems.
c. Star schemas for OLAP systems.
d. Large data sets of historical information in OLAP systems.
e. All of the above.

10. Which of the following statements is true in multidimensional structures:
a. The fact table is referred to as a cube
b. The columns within the fact table are referred to as measures.
c. The cube has edges which are referred to as dimensions.
d. Cubes, measures and dimensions interact with each other for reporting
e. All of the above.
f. None of the above.

Part 2

Provide a definition and example for the keyword listed below (100 - 150 words).

1. Data warehouse
2. Dimension
3. Fact
4. Star schema
5. Snowflake schema
6. Describe the time dimension.
7. Describethe grain of the data warehouse.
8. Define the ETL process.
9. Define Normalization/Denormalization
10. Define Business Intelligence

Part 3

You are working on a DW/BI project for a clothing retailer. They want to better understand the details surrounding what's selling in each store.

Develop a multidimensional model to analyze sales of 75,000 products over 5 years in 300 stores reporting daily sales for each type of promotion. Each promotion has a name, promotion description, promotion offer, a start and end date. The 300 stores are located in sales territories in different regions in the U.S. Sales are tracked by cost and units sold.

Document any assumptions you make. (If you had a customer, these assumptions would have to be clarified with the customer. Since there is not a customer for the test, your assumptions will be included in the evaluation.)

Step 1 - Choose the Business Process, Describe the Data Warehouse. Define the questions that the Data Warehouse will answer.

Step 2 - Declare the Grain.

Step 3 - Identify the Dimensions.

Step 4 - Identify the Facts.

Step 5 - Show the Data Model and validate the model.