BBIM601 Business Intelligence Assessment 2: BI Application and Multi-Area BI Reports

This NZSE project asks learners to act as a business intelligence analyst. Part A designs a BI application for a retail chain using stakeholder analysis, KPI design, RFM customer segmentation and an OLAP data cube. Part B prepares two interactive BI reports, one on hotel booking performance and one on cardiovascular disease risk, using at least two BI tools such as Power BI, Tableau, Python, R or Google Looker.

Part A: Business Intelligence Application (56 marks, LO3)

Scenario: You are hired as a BI analyst for RetailCo, a mid-sized retail chain selling electronics, furniture and office supplies online and in store across several regions. Management has noticed inconsistent sales trends, regional performance differences and poor customer retention, and needs a BI solution to understand sales patterns, customer behaviour and regional performance. A RetailCo Sales dataset is provided on Canvas.

BI methodology: Stakeholder Analysis and KPI Design

1. Identify at least two key stakeholders (e.g. Sales Manager, Marketing Analyst, Regional Manager). For each, state their role and the insights or decisions they need support with (e.g. forecasting demand, comparing regions, optimising campaigns). [8 marks]
2. Propose three KPIs that could address RetailCo’s challenges (e.g. Total Sales by Region, Gross Profit Margin). For each KPI explain what it measures, why it matters and which business function or stakeholder it serves. [12 marks]

BI methodology: RFM analysis; BI technique: customer segmentation

3. Apply the RFM (recency, frequency, monetary) method to the dataset. Apart from the existing “segmentation” label, give at least two options for segmenting customers and carry out segmentation for both options. [14 marks]

BI techniques: data cube schema and OLAP

4. Design a data cube schema from the dataset and perform an OLAP operation (e.g. drill-down, slice/dice) to answer a specific business question, such as which region had the highest growth in Q3. [14 marks]
5. Summarise how each component of your solution follows the BI cycle, and discuss the challenges you met while applying the BI techniques and how you addressed them. [8 marks]

Part B: BI Reports for Multiple Application Areas (44 marks, LO4)

BI tools: Power BI, Tableau, Python, R or Google Looker (at least two). BI techniques: data cleaning, transformation, visualisation, reporting and insight generation.

BI report for hotel booking performance (hotel booking dataset)

1. Dataset understanding, preparation and quality improvement [8 marks]
   • Describe the dataset structure: number of rows and columns, categorical and numerical variables, and three key columns for hotel operations.
   • Identify and fix quality issues such as missing values in important columns, inconsistent data types and outliers.
   • Create a Total Stay Nights field (stays_in_weekend_nights + stays_in_week_nights) and group lead_time into bands (e.g. 0–30, 31–90, more than 90 days), with steps or screenshots.
2. BI report and insights on hotel booking [12 marks]
   • Booking performance: total bookings and cancellation rates by hotel type (Resort vs City Hotel) and the monthly booking trend.
   • Customer profile and behaviour: average lead time and length of stay by customer_type, top five countries by bookings, and the relationship between special requests and cancellations.
   • A summary of key findings and business recommendations.

BI report for cardiovascular disease (cardiovascular disease dataset)

3. Dataset understanding, preparation and quality improvement [8 marks]: describe the rows and columns, the difference between objective, examination and subjective features, and the target variable; fix outliers, missing values and inconsistent coding; engineer a BMI column (weight in kg ÷ height in m²) and group it into Underweight (<18.5), Normal (18.5–24.9), Overweight (25–29.9) and Obese (≥30).
4. BI report on cardiovascular disease patterns [8 marks]: compare disease rates across gender, age groups (your own bins) and cholesterol levels with interactive dashboards or charts, and give two key insights.
5. Patient risk profile report [8 marks]: show prevalence in relation to BMI category, physical activity and smoking status, build a summary dashboard of key risk indicators with a gender filter, and give two actionable insights such as the highest-risk profiles.

Submission Notes

• Submit a Word document or PDF with screenshots of all scripts and their results, plus compressed files of the scripts and data store, on Canvas.
• Answers must be in your own words; AI writing and paraphrasing tools are not permitted, and Turnitin checks similarity and AI content.
• At most 15% of the content may be quoted or paraphrased, with APA 7 citations. All tasks must be attempted to pass.