UCCD3233 Data Warehouse Modeling and Implementation

UNIVERSITI TUNKU ABDUL RAHMAN
FACULTY OF INFORMATION AND COMMUNICATION TECHNOLOGY (PERAK)
BACHELOR OF INFORMATION SYSTEMS (HONS)
BUSINESS INFORMATION SYSTEMS
BACHELOR OF INFORMATION SYSTEMS (HONS)
INFORMATION SYSTEMS ENGINEERING
JAN 2021
UCCD3233 Data Warehouse Modeling and Implementation
GROUP ASSIGNMENT II INSTRUCTIONS
Group Assignment II: (Weightage: 10%)
Important Dates:
Assignment Submission: 10th April 2021
Method of Submission: Email (UCCD3233Assessment@gmail.com)
P/s: Your marks can be deducted if you fail to follow the instructions given or you are submitting the assignments late.
The group assignment having weightage of 10% marks consists of THREE tasks:
• Design the Architecture of a Data Warehouse – Marks: 3%
• Reseller Data Warehouse Pre-Requirements – Marks 4%
• Reseller Data Warehouse Post-Requirements – Marks 3%
Total Marks for the assignment: 10%
Prior to attempt the group assignments YOU MUST:
a) Form a group consist of maximum 4-5 students.
b) Select a project manager.
c) Study the existing system data set.
In this assignment, you are required to design a logical data model and to implement an XYZ Reseller Digital Sales data warehouse based on your schema design using the MSSQL Server / Oracle system. Populate your data warehouse with the provided data sets.
Background: A Reseller chain database has consists of five dimensions:
● DimBranch: BranchKey, BranchName, TypesOfBranchs
● DimItem: ItemKey, EnglishItemName as ItemName, ItemBrand, TypesOfItems, TypesOfBranchs
● DimLocation: LocationKey, Street, City, ZipCode, Country
● DimTime: TimeKey, DateNumber, DayOfDate, MonthOfYear, CalendarOfYear
● DimSales: TimeKey, ItemKey, SalesAmount, TurnioverRatio
The reseller company decided to measures the following attributes for the fact table:
FactResellerSales: Measures – SalesAmount, TurnoverRatio
Dataset:
The assignment uses the XYZResellerDigitalSales dataset. The dataset is available from WBLE (Web-Based Learning Environment – Student Portal).
The assignment consists of two parts:
1. Using software provided to you, you are asked to design a data warehouse from an existing database. The software will allow you to describe the structure of the data warehouse, and will produce as output SQL code to create and populate the data warehouse.
2. Once you construct the data warehouse, you will have to instruct SQL Server Integration Services (SSIS) data flow package based on the requirements for your data warehouse. You can use SQL Server Data Tools (SSDT).
Task 1: Design the Architecture of a Data Warehouse (3 marks)
Choose which model (star/ snowflake/ galaxy) you will use to capture the design of your data warehouse (fact and dimension tables) and draw your star/snowflake/galaxy schema. Translate the star/ snowflake/ galaxy schema into a collection of relational tables and show the resulting physical table design (fact and dimension tables) in your database. Include all necessary primary and foreign key definitions within the fact and dimension tables.
Task 2: Reseller Data Warehouse Pre-Requirements (4 marks)
The reseller chain has hired you to provide some analytical support for their operations. In particular, the management of the reseller chain wants to obtain answers to the following questions:
a. Load the reseller fact table with using Lookup Transformation methods based on the following structure:
TimeKey ItemKey BranchKey LocationKey SalesAmount TurnoverRatioStudentID
xxx xxx xxx xxx xxxx xx
b. During the Lookup Transformation performance, change the output alias as “TurnoverRatioStudentID” instead of “TurnoverRatio” (the StudentID should be one of your group members).
c. Perform a conditional split into “Types of Items” with creating different groups as an output table in Microsoft SQL Server Management Studio.
TASK 3: Reseller Data Warehouse Post-Requirements (3 marks)
a. Based on your answer in Task 2.c, shows the total records in the form of a message box for each of the groups based on “Script Task” process.
Submission:
You will submit based on the data warehouse design. The files have contained the design of your data warehouse. This includes a textual description of the warehouse schema, the diagram of the data warehouse, ETL (Extract, Transform and Loading) process under SSDT tools, DDL/DML SQL commands, and execution information.
1. What are the compulsory things in the Documentation (refer to: Sample Cover Page and Table of Contents):
a. The data warehouse schema (star/snow flake/ galaxy schema diagram).
b. Print screens the ETL process and outputs for of each of the tasks.
c. Softcopy that includes all the materials (Documentation, other softcopies materials such as pictures etc.)
d. Execution Information (for example refer to: Appendix A).
2. Do not let other groups borrow, copy, or reuse your assignment. NO PLAGIARISM.
3. Kindly confirmed your softcopy (executable package) can be run on any computer before you submit.
4. Please kindly follow the due date accordingly as marks may be deducted for late submissions.
[Total : 10 marks]
Sample Cover Page and Table of Contents
Cover Page:
Group Members:
Student ID Full Name Programme Practical Group Marks
Table of Content:
Task 1: Design the Architecture of a Data Warehouse
● Star/ Snowflake/ Galaxy schema
● DDL/DML SQL commands
Task 2: Reseller Data Warehouse Pre-Requirements
● Show the ETL (Extract, Transform and Loading) process and output under SSDT tools for each of the questions.
● Output in SQL Server Management Studio for each of the questions.
● DDL/DML SQL commands (if use)
Task 3: Reseller Data Warehouse Post-Requirements
● Show the ETL (Extract, Transform and Loading) process and output under SSDT tools for each of the questions.
● Output in SQL Server Management Studio for each of the questions.
● DDL/DML SQL commands (if use)
Supporting Materials
● Execution Information
● Other relevant materials
Appendix A
Example of Execution Information:
_______________________________________________
Good Luck
Order Now

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?