INFO 4311 Data Warehousing Alternative Assessment Semester 1, 2025/26 | IIUM

INFO 4311 ALTERNATIVE ASSESSMENT

KULLIYYAH OF INFORMATION AND COMMUNICATION TECHNOLOGY

INSTRUCTION S TO CANDIDATES

Please refrain from opening the question paper until instructed to do so.

This question paper consists of 5 pages, excluding the cover page.

WARNNG

Cheating is strictly prohibited and will be subject to disciplinary action, including dismissal, as outlines in the Student Academic Performance Evaluation Regulations (SAPER).

Answer all parts and submit in ITa’leem.

FINAL ASSESSMENT BRIEF: DATA WAREHOUSE AND DASHBOARD DEVELOPMENT (100 MARKS = 40%)

ISSUE DATE : 13 JAN 2026 (TUESDAY) 10:00 A.M. DUE DATE : 16 JAN 2026 (FRIDAY) 12:00 NOON

You are given the following:

1. Data Warehouse (Data Mart) Star Schema as in Figure 1.
2. SQL script file (Bikesales_DW.sq/) to create the BikesaIes_DW Data Mart.
3. Three (3) cleaned Excel spreadsheet datasets — Bikestores 2016.xlsx, Bikestores 2017.xlsx and Bikestores 2018.x/sx— as Data Sources to your Data Mart.

Figure 1: BikesaIes_DW Data Mart Star Schema

ANSWER ALL QUESTIONS BELOW.

Question 1

Using Microsoft SQL Server, execute the SQL script file provided to create the Bikesales DW Data Mart following the schema in Figure 1. For such Data Mart, complete the Bus Matrix (Table 1).

Table 1: Bus Matrix

Deliverable:

BikeSales DW Bus Matrix

[Total: 10 marks]

Question 2

Using Microsoft SQL Server Integration Services (SSIS), populate Bikesales_DW Data Mart created in question 1 with data from the 3 Excel spreadsheet datasets provided.

Source: Excel     Destination: SQL Server Data Mart

Use SSIS transformation features — Lookup, Sort, Union All and Data Conversion — in the

Extract, Transform and Load (ET L) process, You may use other relevant features if necessary.

Capture screenshots of the Control Flow, Data Flow, Execution Flow and Output of all packages you built (see examples below), compile in the document and briefly describe the ET L implementation of the Bikesales_DW.

Control Flow:

Package,dtsx {Design] X

Oath                                                           m fåent HendlK:i Package Explorer O Progress

SQL – Truncate DimProducts Table

DF Extract From Source to Dimproducts

DESKTOP-TIJBPUF.Northwind                DESKTOP-TIJBPüF.NorthwindDW

Data Flow:

Package.dtsx (Design!       X

Control How       Data                Parameters El Event Handlers         package Explorer O Execution Results

Data Flow Task:          DF Extract .frorp          _tp QImProgucts

OLE DB Source Products

You may have other transformations from your ET L rules, so you may add more in between source and destination

OLE DB Destination DimProducts

Execution Flow:

VarSQbEes            List Output

Maximisation of the Execution Flow:

(hjtputl

Qehug i±ployees                                       Itartinx.

Ox4&d30DA    Data        Task:-                                         source,                     ValIdatf01} phase is beginn3ng.

FICK                                                            SSIS.Pfpeline: V*lfdacicin jphBS*

• -Er•F: “Pen tu                     , Preparfi           phRf* inforæatlch:                      Flo«                  SS15.Pføe1ine:

Result Output — The Data:

Run a query for each dimension table and fact table in Microsoft SQL Server to check whether all tables have been populated with data. Capture full screenshots of result data populated in dimension tables and fact tables (see example below). A sample of data visible in the screenshot to show successful loading of data for each table is enough.

‘$ii%”                             ftofeg.

Answer

Request Answer of this Assignment