BUSINESS INTELLIGENCE - M33150 - FHEQ 7: Happy Phones Case Study

School of Computing 2025/26

Deliverables

Notes and Advice

• The Extenuating Circumstances procedure is there to support you if you have had any circumstances (problems) that have been serious or significant enough to prevent you from attending, completing or submitting an assessment on time. If you complete an Extenuating Circumstances Form (ECF) for this assessment, it is important that you use the correct module code, item number and deadline (not the late deadline) given above.
• ASDAC are available to any students who disclose a disability or require additional support for their academic studies with a good set of resources on the ASDAC moodle site.
• The University takes any form of misconduct (such as plagiarism or cheating) seriously, so please make sure your work is your own. Please ensure you adhere to the Student Conduct Policy and watch the video on Plagiarism.
• Any material included in your coursework should be fully cited and referenced in APA 7 format. Detailed advice on referencing is available from the library, also see TECFAC 08 Plagiarism.
• Any material submitted that does not meet format or submission guidelines, or falls outside of the submission deadline could be subject to a cap on your overall result or disqualification entirely.
• If you need additional assistance, you can ask your personal tutor, student success advisor ana.baker@port.ac.uk, academic tutors simon.jones@port.ac.uk & eleni.noussi@port.ac.uk or your lecturers.
• If you are concerned about your mental well-being, please contact the Well-being service.

AI Statement

AI tools, including Generative AI, should NOT be used to:

• Generate any content to include in your assessment submission.
• Generate code to include in your assessment submission.
• Generate datasets.

AI tools, including Generative AI, CAN be used to:

• Develop your understanding of the concepts covered in the module.
• Practice use of analytics and programming skills.
• Check/improve the clarity of your language.

Use AI tools critically. They make mistakes and their language is not precise.

Overview – Happy phones LTD

Happy phone LTD is a limited company, delivering mobile communications services in the UK. Happy phone LTD has approximately 53 retail stores, and services more than 1 million connections across its mobile network.

Happy phone LTD is currently using an outdated customer service system and they are looking to improve it.

In the current system, when a customer calls the helpline, they have 3 choices, either to go to sales or to connections or to "other." This is not very efficient and does not cover the workload of the company, since its growth. When a customer selects an option then they may wait up to 2 hours to be connected with a representative.

The representative, based on their expertise, may or may not be able to answer the customer's inquiry.

The top 3 queries the Happy phone LTD are getting from their customers are:

1. Lost/stolen phone inquiries
2. Bill inquiries
3. Connectivity issues

If the customer is not satisfied with the response or if the representative is unable to answer, then the customer's query is escalated and the customer is connected to the second line of customer service, which deals with more complicated queries.

After the escalation takes place, if the customer is happy then the query is resolved, or if there are still issues, the customer is then connected to the daily customer services manager. Happy phone LTD has two customer service managers, who alternate their customer service problem solving duties daily. At that stage, the manager must solve the problem, one way or another.

They are looking to identify problems around customer support and knowledge base management.

You have been assigned to delve into their data mart and identify potential trends and problems.

To access the star schema and the data:

1. Connect to the university's VPN.
2. Go to Azure Data Studio, connect with your login details as we did in the workshop and are detailed on Moodle.
3. Select the Happy Phones database. You are NOT allowed to use any other data warehouse.

Star Schema Tables

Query Handle Fact (fact table)

Customer Dim

Date Time Dim

Question Dim

Mobile Plan Dim

Department Dim

Representative Dim

Task 1 {30 marks}

Write and run 3 SQL queries. You must submit a screenshot of the queries running and its results as well as the short description of the business rationale in no more than 100 words per query.

Notes:

• Your queries must be meaningful and demonstrate the strength of DW in supporting decision makers.
• Your queries must use a broad range of fact tables and dimensions of the provided DW.
• You should provide a short description of each query, to explain the business rationale for creating it.
• All 5 queries must be different from each other, using different fact tables and dimensions.
• All 5 queries should include at least one data warehouse concept. Queries such as "select * from table name where simple condition" and repetitive queries will get marked as zero.

(Note: the task text says "3 SQL queries" at the top but the notes reference "5 queries" — this inconsistency appears in the original document as provided.)

Task 2 {20 marks}

Modify the given schema and suggest at least 1 more dimension that would provide you with insights that you wish there were there.

Hint: It is common knowledge in the company that the extent of the knowledge base is under utilized and underrepresented in the data warehouse.

Submit the dimension in the form of a table.

For the dimension:

• Provide reasoning and rationale for your choice (also known as: why this dimension and how can we use it?)
• You need to submit it, following the same naming convention that exists in the data warehouse.
• The dimension should seamlessly blend together with at least either the fact table or one dimension of the current schema.

Task 3 {34 marks}

Happy phones LTD has raised concerns regarding the quality and consistency of the customer service data in their data warehouse. There are reports of missing escalations, duplicated customer interactions, and inconsistencies in the timestamps used across fact tables. These data quality issues are believed to originate from limitations in the existing ETL process, which has not been revised since the initial deployment of the system.

You are required to evaluate the data warehouse with a focus on data quality and ETL pipeline design. Identify and critically discuss multiple types of data quality problems that are likely present in the current implementation, considering both their technical causes and their business impact. Then, propose and justify a set of specific ETL improvements that could mitigate these issues and improve the analytical value of the customer service data warehouse.

Your work should demonstrate a deep understanding of data warehousing principles and best practices in ETL pipeline development. You are expected to address both the technical aspects and the organizational implications of poor data quality and ETL inefficiencies.

Task 4 — Quiz

Select the correct answer(s) for each of the following questions.

Q1 {2 marks} What risks do business people face with respect to expertise and experience while introducing new BI technologies and analytical techniques? (select 1 correct answer)
a. Organizations will not have the expertise in the new concepts and techniques to effectively use the new tools
b. Business people will know how to leverage the new BI technologies and analytics to obtain business value.
c. Both
d. None

Q2 {2 marks} When defining data quality requirements, you should keep in mind that: (select 1 correct answer)
a. Source system data is always excellent
b. Data quality problems are often caused by conflicting data silos
c. Data cleansing tools will fix any data quality problems
d. The most common cause of data quality problems is sloppy data entry

Q3 {2 marks} When designing a BI application, what is more important? (select 1 correct answer)
a. Elegant design
b. Cool fonts
c. Consistent templates
d. Eye-catching graphics

Q4 {2 marks} Which of the following is true: (select 1 correct answer)
a. Once created, the data marts will keep on being updated from the data warehouse at periodic times
b. Once created, the data marts will directly receive their new data from the operational databases
c. The data marts are different groups of tables in the data warehouse
d. A data mart becomes a data warehouse when it reaches a critical size

Q5 {2 marks} Use storyboards to: (select 3 correct answers)
a. Work out the visual details of multimedia items
b. Determine how the business person will interact with the application
c. Understand how different analyses are related
d. Create the workflow between different analyses

Q6 {2 marks} What is most important when it comes to data visualization? (select 1 correct answer)
a. The use of dashboards
b. Flashy visuals that catch the users' eyes
c. Enhancing user understanding
d. Using multimedia in the presentation of data

You must submit 1 pdf document including the answers to all 4 tasks.

Marking Scheme

General criteria for all tasks:

Task 1 {3 × 10 = 30 marks}

Task 2 {20 marks}

Task 3 — Marking bands

• 0–13 marks (Fail – Below 40%): The submission lacks clarity and technical understanding. Data quality issues are vague or too general, with little connection to ETL processes or the Happy phones context. Recommendations, if included, are unrealistic, unsupported, or missing. Work may be poorly structured or difficult to follow.
• 14–20 marks (Pass – 40–59%): The submission identifies some relevant data quality issues and shows a basic understanding of ETL. Explanations are brief or underdeveloped. Some technical terms may be used, but analysis lacks depth. Recommendations are general and only loosely connected to the case study. Structure and writing are mostly clear.
• 21–27 marks (Good Pass – 60–69%): The submission shows a clear understanding of how specific data quality problems arise in the ETL process. The issues are well explained and relevant to the case. Recommendations are practical and linked to both technical and business outcomes. Writing is well-structured and mostly precise.
• 28–34 marks (Excellent – 70% and above): The submission demonstrates a deep and insightful analysis of data quality issues and their causes in the ETL pipeline. Recommendations are technically strong, well justified, and clearly improve the data warehouse's reliability. Business impact is clearly explained. Writing is fluent, professional, and well organized.