For information and reference only – not to be submitted as your own work.

This extract shows how a strong response to the NZSE BBIM601 Business Intelligence Assessment 2 project can be structured. It covers the method for Part A (stakeholder analysis, KPI design, RFM segmentation, a data cube with an OLAP operation and the BI cycle) and the cleaning and transformation logic for Part B (hotel booking and cardiovascular datasets). Every figure below is illustrative and is not a result from the RetailCo, hotel_booking or cardio_disease files. You must run the method on your own data and report your own outputs. You can read the full brief on our BBIM601 Assessment 2 question page.

Part A, Task 1: Stakeholder analysis

RetailCo is a mid-sized chain selling electronics, furniture and office supplies online and in store. Its problems (uneven sales trends, regional differences and weak retention) belong to different decision-makers, so the analysis starts with who needs which decision support. A short stakeholder table keeps the reasoning visible to the marker.

Two stakeholders are the minimum requirement. Choosing three and showing one distinct decision for each tends to demonstrate stronger analysis than describing generic needs.

Part A, Task 2: Three KPIs

Each KPI should state what it measures, why it matters to the problem and which stakeholder owns it. Define the formula and the grain (for example monthly, by region).

Part A, Task 3: RFM analysis and alternative segmentations

RFM scores each customer on Recency (days since last order), Frequency (number of orders) and Monetary value (total spend). The usual approach ranks customers into quintiles and combines the scores. In Power BI, a calculated table can hold the customer-level measures (Microsoft, n.d.). The following DAX is a method outline, so adapt the table and column names to your RetailCo file.

RFM Customers =
VAR SnapshotDate = CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) ) + 1
RETURN
ADDCOLUMNS (
    VALUES ( Sales[CustomerID] ),
    "Recency", DATEDIFF ( CALCULATE ( MAX ( Sales[OrderDate] ) ), SnapshotDate, DAY ),
    "Frequency", CALCULATE ( DISTINCTCOUNT ( Sales[OrderID] ) ),
    "Monetary", CALCULATE ( SUM ( Sales[SalesAmount] ) )
)

The same logic in Python is easier to inspect and to score:

import pandas as pd
snapshot = df["OrderDate"].max() + pd.Timedelta(days=1)
rfm = df.groupby("CustomerID").agg(
    Recency=("OrderDate", lambda d: (snapshot - d.max()).days),
    Frequency=("OrderID", "nunique"),
    Monetary=("SalesAmount", "sum"))
rfm["R"] = pd.qcut(rfm["Recency"], 5, labels=[5,4,3,2,1])
rfm["F"] = pd.qcut(rfm["Frequency"].rank(method="first"), 5, labels=[1,2,3,4,5])
rfm["M"] = pd.qcut(rfm["Monetary"], 5, labels=[1,2,3,4,5])
rfm["RFM_Score"] = rfm[["R","F","M"]].astype(int).sum(axis=1)

Recency is scored in reverse because fewer days since the last order is better. Ranking frequency before qcut avoids duplicate bin edges when many customers have a single order.

The brief asks for at least two options beyond the existing segmentation label. Two defensible choices are:

Compare the options by segment size, average spend and how clearly each suggests an action (for example a win-back offer for “Big spenders at risk”). Explain which option you recommend and why. For example, an illustrative result might show that a small segment generates a disproportionate share of revenue, which would justify a loyalty programme. Report only what your own data shows.

If you need support running this analysis end to end, our coursework help team can work with your datasets and tools.

Part A, Task 4: Data cube schema and OLAP operation

A star schema suits this case because it separates numeric measures from descriptive dimensions (Kimball & Ross, 2013; Microsoft, 2024). The fact table holds one row per order line, linked to dimension tables.

To answer a question such as “Which region had the highest growth in Q3?”, use a slice on Quarter = Q3 (and the preceding quarter for comparison), then aggregate SalesAmount by Region. A drill-down from Region to Store, or from Quarter to Month, then explains why the leading region grew. A dice restricted to Region = (two regions) and Category = Electronics compares a smaller sub-cube. State which operation you used in each screenshot caption.

Part A, Task 5: The BI cycle, challenges and responses

Map each component to the stages of the BI cycle: identify business needs (stakeholders and KPIs), collect and integrate data (star schema and data model), analyse (RFM and OLAP), deliver (dashboards) and act and review (recommendations and monitoring). Typical challenges include inconsistent customer identifiers across online and in-store systems, missing dates, and stakeholders interpreting KPIs differently. Matching responses are a single customer key, validation rules at load, and a KPI dictionary agreed with owners.

Part B: Cleaning and transformation method

Hotel booking dataset

The dataset is described in Antonio et al. (2019). Check your own file for the number of rows and columns, which variables are numeric or categorical and three key columns (for example hotel, is_canceled and lead_time). Then work through these steps in Power Query or Python.

1. Profile missing values. Fields such as country and children commonly contain gaps, and company or agent may be mostly empty, so decide to impute, flag or drop and justify each choice.
2. Correct data types, particularly the arrival date parts, which should combine into a true date.
3. Check outliers in lead_time, adr (average daily rate) and stays. An adr of zero or a negative value needs an explanation before it is kept.
4. Create Total Stay Nights as the sum of weekend nights and week nights.
5. Create lead-time bands of 0–30, 31–90 and over 90 days.

df["total_stay_nights"] = df["stays_in_weekend_nights"] + df["stays_in_week_nights"]
df["lead_band"] = pd.cut(df["lead_time"], bins=[-1, 30, 90, df["lead_time"].max()],
                         labels=["0-30", "31-90", ">90"])
cancel_rate = df.groupby("hotel")["is_canceled"].mean()

The pandas documentation confirms that pd.cut includes the right edge by default, so a lower bound of −1 keeps zero-day lead times in the first band (pandas, n.d.). In Power BI the equivalent is a DAX switch measure or a conditional column. For the interactive report, add slicers for hotel type and customer_type and visuals for cancellation rate by hotel, monthly trend, top five countries and special requests against cancellation.

Cardiovascular dataset

The Kaggle cardiovascular dataset is described as 70,000 patient records with 11 features and a target (Kaggle, n.d.). Group the features as objective (for example age, height, weight), examination (for example blood pressure, cholesterol, glucose) and subjective (for example smoking, alcohol, activity), and name cardio as the target. Check unit issues, since age in this file is commonly recorded in days, and look for impossible blood pressure or height values before analysis. Then calculate BMI and categorise it.

df["bmi"] = df["weight"] / (df["height"] / 100) ** 2
df["bmi_cat"] = pd.cut(df["bmi"], bins=[0, 18.5, 25, 30, 200], right=False,
                       labels=["Underweight", "Normal", "Overweight", "Obese"])
df["age_years"] = (df["age"] / 365.25).round().astype(int)

Using right=False gives the intervals below 18.5, 18.5 up to 24.9, 25 up to 29.9 and 30 or above, matching the brief. Present prevalence of cardio by gender, age group, cholesterol, BMI category, activity and smoking, and add a gender slicer to the dashboard. Insights should be phrased as associations in the data, not causes, and any percentages in your report must come from your own results.

Need a second pair of eyes on your reports? Our case study help service and the companion planning guide are good next steps.

About the expert

This extract was reviewed by Dr. Sophie McAllister, Managerial Finance & Business Analytics Specialist (Victoria University of Wellington, as shown on her profile).

Want a tailored plan for your own datasets? Submit your brief and we will guide you through it.

References

Antonio, N., de Almeida, A., & Nunes, L. (2019). Hotel booking demand datasets. Data in Brief, 22, 41–49. https://doi.org/10.1016/j.dib.2018.11.126

Kaggle. (n.d.). Cardiovascular disease dataset [Data set]. https://www.kaggle.com/datasets/sulianova/cardiovascular-disease-dataset

Kimball, R., & Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.

Microsoft. (2024, December 30). Understand star schema and the importance for Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Microsoft. (n.d.). Data Analysis Expressions (DAX) reference. Microsoft Learn. https://learn.microsoft.com/dax

pandas. (n.d.). pandas.cut [Documentation]. https://pandas.pydata.org/docs/reference/api/pandas.cut.html