You have been employed by The Cold Dog Marketing Company, a company that sells Cold Dogs, to make a data warehouse so they can analyse their sales to assist with their management decision making. They wish to be to answer the following questions.

 

1. What was the total sales revenue for each product during the last month?
2. Which supplier had the highest total sales in the last quarter?
3. Which branch had the highest total sales in the last year, and what was the total sales revenue for that branch?
4. Which salesclerk had the highest total sales in the last six months, and what was their total sales revenue?
5. What was the trend in sales revenue for each product type by sales clerk over the last year, broken down by month?
6. Identify the top three performing branches in terms of total sales revenue over the last year, and compare their sales performance by product typeand supplier.

 

You have been given access to three data sources from which you will need to draw your data. Below are the sources and the tables within them that are of interest to you. These are discussed below (Primary Key, Foreign Key):

 

Source 1: Human Resources Database 

The Human Resources (HR) database contains data about the people who are employed by The Cold Dog Marketing Company. You have been granted access to a VIEW that includes the data you may need for the various analyses required. It only includes details of employees currently employed by The Cold Dog Marketing Company in a sales-related position. The view has been supplied to you as a text file, see LMS.

 

The VIEW is called viewEmployee and has the following columns:

viewEmployee (EmpID, LastName, FirstName, StartDate). 

 

Source 2: Inventory System Database

The Inventory System maintains stock levels for all products sold by The Cold Dog Marketing Company as well as the details of the Supplier. There are two tables to which you have been granted access. The Product table, and the Supplier table. They are structured as follows. The tables are supplied to you as a xlsx file, see LMS.

 

PRODUCT (ProductNum, ProductName, SupplierNum, ProductType)

SUPPLIER (SupplierNum, SupplierName, Country)

 

Source 3: Sales Database

The Sales database is supplied to you as a text file, see LMS.

 

Note:

• the Date data type stores dates as day and time
• the Date column in the Sale table is named Sale_Date.

 

SALE (SaleID, ClerkID, CustomerID, Sale_Date)

SALE_LINE_ITEM (SaleID, LineID, ProductID, ProductPrice, Quantity)

SALE_CLERK (Clerk_ID, Clerk_Name, BranchID)

CUSTOMER (CustomerID, FamilyName, GivenName, Address, Suburb, State, PostCode)

PRODUCT (ProductID, ProductName)

BRANCH (BranchID, BranchPostCode)

 

 

What you have to do:

TASK 1 (20%)  Based on the list of questions the client wants answered (see above), 

1. Discuss the most appropriate level of granularity for your Fact Table, considering the analyses required. (Aggregated or detailed fact table and explanation)?
2. Explain why you chose that level of granularity for the Fact Table, discussing the benefits and suitability of your choice.
3. Identify and discuss alternative granularity levels you considered and explain why these alternatives were not chosen.

EXPLAIN CLEARLY PLEASE

 

Task 1 should take no more than one (1) page.

 

 

TASK 2 (30%) Design a Star Schema with appropriate relationships to support the analyses of the six questions provided in the scenario section.  (FACT TABLE DOES NOT HAVE PRIMARY KEYS, ONLY COMPOSITE KEYS WHICH ARE MADE UP OF THE DIMENSIONS'S PKs)

 

 

TASK 3 (50%) Based on the Star Schema you created in Task 2, create the physical data warehouse structure on rhea. You will need to ensure that you have granted the appropriate permissions to the marking account, "MARKERTL". 

 

For this task, complete the following.

1. Create the Dimension tables designed in Task 2, including all constraints and columns in the dimension tables.                                                [15 Marks]

 

1. Create the Fact table(s) designed in Task 2, containing appropriate.
2. Foreign key columns (and constraints) referring to the Dimension tables.
3. Measure columns.                                                                    [15 Marks]

 

1. Provide the structured SQL, including constraints, used to answer the following ADDITIONAL question that management wants answered,
2. What is the total sales revenue for each product type sold by each branch in the first quarter of 2022?
3. Explain the meaning of each line (statements/clauses) in the SQL query you provided in (3, a)