Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

You have been tasked with creating a data warehouse so the client... You have been tasked with creating a data warehouse so the client can

You have been tasked with creating a data warehouse so the client...

You have been tasked with creating a data warehouse so the client can analyse their sales in order to assist with their management decision making, in particular, they wish to be to answer the following questions:

-Total of sales of each product by Year/Month/Week/Day

-Total sales of products by supplier by Year/Month/Week/Day

-Total sales by branch by Year/Month/Week/Day

-Total sales of product by branch by Year/Month/Week/Day

-Total sales by sales clerk by Year/Month/Week/Day

-Total sales by product by sales clerk by Year/Month/Week/Day

- Total sales by product type by sales clerk by Year/Month/Week/Day

There are access to three data sources from which there is a need to draw the data. Below are the sources and the tables within them. 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.

The VIEW is called viewEmployee and has the following columns:

viewEmployee (EmpID, LastName, FirstName, DateStarted).

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 below:

PRODUCT (ProductNum, ProductName, SupplierNum, ProductType)

SUPPLIER (SupplierNum, SupplierName, Country)

Source 3: Sales Database

The Sales database is an oracle database (luckily for you, it's hosted on arion.murdoch.edu.au and owned by SICT394AssignTL - you have SELECT privileges on these tables)

SALE (SaleID, ClerkID, CustomerID, SaleDate,)

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)

My questions:

  1. Based on the list of questions the client wants answered (listed above),what is the most appropriate level of granularity for your data warehouse. Why is this level and not the other is the most appropriate? Minimum word 200.
  2. Based on the answer to the first question, what is the appropriate Star Schema that will support the analyses as listed above

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

General Aviation Marketing And Management

Authors: Alexander T. Wells, Bruce D. Chadbourne

2nd Edition

1575241927, 978-1575241920

More Books

Students also viewed these General Management questions