ICT394 Business Intelligence - Assignment Tutor Online

ICT394 Business Intelligence Application Development

This assessment addresses the following Unit Learning Outcomes:

Describe the common data sources that exist in organisations and their use
in BI
Demonstrate practical skills in the processes associated with extraction,
transformation and loading (ETL) of organisational data
Design and implement a simple data warehouse environment
Submission Instructions:
Please submit a single word document (or similar, e.g., pdf is acceptable) using the
link in Moodle.
From the Unit Information Guide:

It is your responsibility to keep a copy of any work handed in for
assessment purposes. It is recommended that you keep both a hard
copy and an electronic copy.
Assessments that are not received by the due date will be regarded as
late unless an extension has been granted by the unit coordinator.

The Scenario
You have been employed by The Cold Dog Marketing Company, a company that
sells Cold Dogs, to create a data warehouse so they 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 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 products 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
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.

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:
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)
Note:
 The owner of the tables is now SICT394AssignTL
 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 (25%): Based on the list of questions the client wants answered (see
above), discuss what you see as being the most appropriate level of granularity
for your data warehouse. Your discussion will need to explain why you have
made this choice, and why the alternatives have been discarded. This should take
no more than one (1) page.
TASK 2 (30%): Based on your answer to Task 1, design a Star Schema that will
support the analyses as listed above.
TASK 3 (45%): Create the data warehouse structure on arion
. You will need to ensure that you have granted the appropriate permissions to
the marking account, “MARKERTL”.
Note:

For this task, please create the following:
o The Dimension tables, but only include the primary key (i.e., there
is no need to create the other columns in the dimension tables, just
the primary key column)

o The Fact table(s) which should contain the following:
 Foreign key columns (and constraints) referring to the
Dimension tables
 Measure columns
o THERE IS NO NEED TO POPULATE THE DATA WAREHOUSE
(though we might do that later as a tutorial exercise).

To Submit:
You will need to submit a single word-processed document that includes

The written answer for Task 1.
A screen shot of the design you have created for Task 2 (please do not
submit your visio file, or whatever file you use, just a screen shot, copied
and pasted into a word document please).
Marking Process for the Data Warehouse:
The marker will be checking your tables using your star schema as the starting
point for your database.