2030AFE Business Intelligence

Trimester 2 2025

Assignment 2

(worth 55% of the overall assessment)

DUE DATE: 6:00 PM Brisbane time, Thursday 16 October 2025

(Online submission via Assignment 2 link under Assessment in Learning@griffith website)

Instructions:

• Data files are provided in Excel/CSV format in the assessment section of the course website.

• In question 1, the Dashboard should be constructed using Power BI Desktop.

•Your answers to question 1 part 1 can be submitted electronically as a Power BI report.

• Your answers to question 1 part 2 can be submitted electronically as a Word document.

• Your answers to question 2 can be submitted electronically as a Power BI report.

• Your answers to question 3 can be typed in a Word document and submitted

electronically.    (Excel or Python can be used to analyse data for part b of question 3).

• You are required to keep a copy of the submitted assignment to re-submit, in case the original submission is lost for some reason.

Marking criteria:

For each question, marking will use the following weighting:

1. Correctness of the identification of the data type and methodology used (20%)

2. Successful application using the computer software (Power BI desktop for questions 1&2, Excel or Python for question 3) (35%)

3. Interpretation of the charts/graphs and correct answers to the question(s) (35%)

4. Professional presentation of the assignment (10%)

QUESTION 1 [30 marks]

You are required to use the file OfficeWorld_Database.xlsx to develop a Dashboard in Power BI Desktop. Complete the following tasks:

Task 1: Dashboard Development in Power BI (15 marks)

a) Use the Power Query Editor in Power BI Desktop to clean and transform the data before loading it into Power BI. (1 mark)

b) Construct a data model following a star schema architecture. (1 mark)

c) Design a Dashboard using appropriate visualizations. Ensure the dashboard provides interactive filtering options, such as slicers, to support dynamic data exploration. (13 marks)

Submit the dashboard as a Power BI Report file.

Task 2: Business Problem Analysis (15 marks)

Assume the role ofa Data Analyst in an organization. Using the dashboard you created, analyse a hypothetical business problem. Demonstrate how the dashboard can support data- driven decision-making to address the issue and propose solutions. (15 marks)

Submit your analysis in a Word document (maximum 1500 words).

QUESTION 2 [10 marks]

Part 1: Using the dataset “Laptop_sales_ 2025.csv”, generate box plots to summarize the revenue by product. Based on the box plots, provide a description of the revenue distribution for each product using appropriate descriptive statistics (e.g., median, quartiles, spread). (2 marks)

Part 2: Application of DAX (Data Analysis Expressions) (8 marks)

•    All the questions must be solved exclusively using DAX formulas in Power BI.

•    Do not use built-in interactive features such as slicers, filters, or manual selections to answer the questions.

•    Your answers should show that you can write and apply DAX expressions. Please stick to the DAX functions and techniques we’ve covered in this course.

•    Please do not rely on AI-generated DAX codes (e.g., from ChatGPT or similar tools). Using such codes will result in a reduction of your marks.

•    Use the file: Laptop_sales_ 2025.csv

(a) Calculate Cost of Goods Sold (COGS) for each record using the formula: COGS = Unit Cost × Units Sold

(b) Calculate Profit for each record using the formula:

Profit = Revenue − COGS

(c) Create a set of measures that calculate the total profit for each product from online sales in the combined North and South regions. Display these results in a Pie Chart that shows both the absolute profit values and their percentage share.

(d) List the Sales Representatives in the “West” region who have made at least one sale of both “ProBook” and “Ultrabook”, with a combined sales value greater than 25,000. Present the names of these Sales Representatives in a Table. Finally, create a separate measure that counts the number of Sales Representatives who meet this criterion and present it using a card visual.

(e) Create a measure to rank all Sales Representatives by their total profit, ordering them from highest to lowest. Present the results in a table that displays each Sales Representative alongside their corresponding rank.

Submit Q2 as a Power BI  file.

QUESTION 3 [15 marks]

a)  What is text mining? Briefly describe one application in Business (2 marks)

b)  For numerous local governments in Canada, tourism serves as a vital source of revenue to uphold a high quality of life for their residents. One method of generating tourism revenue is through taxation applied to the hotels where tourists choose to stay. A tourism hotel operator seeks to explore the relationship between guest nights and local government tax revenue, with the intention of predicting future tax revenue. Annual data for both variables spanning from  1980 to 2024 have been collected. Using an appropriate  data mining technique  learned  in this  course,  analyse  the relationship between these two variables and provide insights to support tourism revenue planning. You may use Excel or Python to conduct your analysis.

Tasks

1) Exploring the Relationship (2 marks):

Measure and describe the strength and direction of the association between guest nights and tax revenue.

Explain what the result suggests about the connection between tourist activity and government revenue.

2) Visualization (1 mark):

Create an appropriate chart to visually represent the relationship between guest nights and tax revenue.

Use the visualization to support your explanation of how the two variables are connected.

3) Building a Predictive Model (10 marks):

•   Develop a model that uses guest nights to explain and predict changes in tax revenue.

•   Present the key outputs ofthe model (e.g., main coefficients, fit statistics).

•   Assess the model and interpret the results to explain whether higher numbers of guest nights are linked with higher tax revenues.

•   Discuss how the model could be applied for forecasting future revenues, and highlight any limitations.

(You can use Excel or Python to do the analysis.)

Data File: Tourism_data.xlsx

(Answers to Question 3 can be submitted in a Word document)