Power BI Portfolio - MonCity — Uber On Campus

type

Post

status

Published

slug

power-bi-car

summary

A dashboard for a uber-like business using Power BI.

1. The idea - Uber for campus

MonCity's is self-driving / autonomous vehicles system network. Much like Uber for campus — designed to transport students and staff within and between the different zones of the university campus.

We aim to enhance mobility and convenience, much like how Uber operates in urban environments.

This dashboard shows operations by analysing maintenance, costs, bookings, and accidents, helping to identify areas for improvement and efficiency in MonCity's transportation services.

Dashboard.pbix

1405.0KB

Overview:

Solution - Requirements Analysis

Data Design - Data Modelling

Data Warehousing - ETL Process

Data Analysis - Actionable Insights

2. Skills to demonstrate

As a part of my Power BI portfolio, I will implement Power BI for a case study to demonstrate competency, expertise in power BI reports, dashboards. The skills and technical knowledge applied are as below :

Perform ETL using SQL and DAX programming.

Access Data from Excel, SQL.

Data Preparation (update and append table).

Transform Data into Data Modeling and explore data by removing duplicates, adding conditional columns, unpivot-splitting columns, normalise-denormalising tables.

Create custom tables, relationships, star or snow flakes schema Tables.

Prepare data Visualization and reporting using Charts (bar, line, column, scatter, pie, donut, ribbon and waterflow ), table, matrix, KPI, slicers, map etc.

Perform DAX measure calculation using sum, count, calculate, divide, time intelligence.

Create Dynamic Measure, Dynamic Dimension, Tooltip, Bookmark in reporting.

Limitation

Our current setup, while efficient and robust, only uses the personal version of Power BI. By upgrading to an enterprise version, we can unlock capabilities:

Publish, Share and collaborate dashboard, workspace, apps.

Create alert, subscription in dashboard

Set up enterprise gateway.

Building Dashboard to highlight the report using Natural Language Query

3. On how to use the dashboard

3.1. Number of booking

3.1.1. By faculty and age group

3.1.2. By faculty and month

3.2. Pivot

3.3. Dynamic Ticker

3.4. Filter by Team

3.5. Filter by Carbody

4. On solution analysis

Step 1 : Requirements Analysis (4W1H)

Who needs to see the operational dashboard?

Operational managers who oversee vehicle maintenance and fleet operations need to see the dashboard. This may also include financial managers, safety officers, and department heads from faculties like Engineering, Business, and, IT, who are concerned with the operational efficiency and safety of transportation services.

What does he/she want to see?

They would want to see key performance indicators (KPIs) such as the number of maintenance records, the total maintenance costs, the distribution of costs by team and vehicle type, and accident statistics. These stakeholders might also be interested in the usage patterns by faculty and age group to inform scheduling and resource allocation.

Where do the people want to see the dashboard?

The dashboard is likely to be accessed in an office environment for the operational staff, while faculty heads might access it from their departments. It could also be made available through an intranet or a secure online portal where these stakeholders can access it from anywhere, be it on-campus or remotely.

How does he/she want to see it?

To have a 360-degree view of the operational performance, they prefer to see it by Team, Car Body Type, Maintenance Type, Season, Faculty, Age Group….

I uses visualisation such as treemap, donut, pie charts, tables along with Tooltip and DAX Dynamic Measure to display key metrics. This enable them to easily visualize filter on-demand, drill down into and slice and dice the figure.

Step 2 : DW Design - Define schema into a Logical Model

Step 3: DW Design - Convert logical model to Physical Model in a business matrix

Step 4 : ETL Process and Dashboard generation

First, we used Oracle SQL to provide to raw data wrangling and exploration to ﬁrst clean the data.

Then, ETF process was performed to transform raw data to Fact and Dim tables.

Finally, we used Power BI to create a dashboard to visualise some of the patterns and characteristics present in it. We used DAX measure calculation using sum and etc.

Step 5 : Analyse the dashboard and provide key insights

To support business intelligence needs, part of my jobs is to design data warehouse based on business objectives and develop reports with insight of customer behaviour and help identify trends in business performance over time. As such, here makes four suggestion to our manager and other relevant stakeholders like Data / Business Analyst.

Suggestion 1: Marketing campaigns specifically for old-age adult group

As MonCity has an increasing trend on aging population and generally old-aged groups are in needs of a more convenient way of commute, we think that there is a demand for this group of people. This graph shows that the demographic of old-age adult group contributes the lowest number of booking across all faculty. To expand our customer base, we should target this clientele. Generally, offering discounts, having affiliated marketing, and email marketing campaign are the examples of marketing campaigns. This is to gain the awareness of our brand, and knowing that our service could help them Therefore, a part of the investment should go towards the marketing on old-aged group.

Suggestion 2: Buying more bus

In expanding our business, we will need buy more cars, and this pivot table can give an insight to decide which should be bought. This figure shows that People Mover has higher maintenance cost but less seats (10), whereas Minibus has lower maintenance cost but more seats (20). From a pragmatic point of view, we want to buy cars that have less maintenance cost meanwhile containing more seats to take more passengers. Therefore, when deciding which types, we would not recommend buying more People mover because of high maintenance cost with the least number of seats. While the maintenance cost of bus is not as low as mini bus but but it can carry twice as much. We hence suggest that the further expenditure on buying cars should go towards buying bus.

Suggestion 3: New KPI should be made in tracking the performance of maintenance cost

With cumulative maintenance cost being 125,000, we should worry about the cost of maintenance. To reach a breakeven, we suggest that Data Analyst and other top management should work together and come up with a new KPI in tracking the cost. From the left chart, we found that M005 has contributed the most maintenance cost, therefore, such a KPI needs to take a greater account for M005, and find a way to minimise the type of maintenance.

Suggestion 4: Promotions on slack months or seasons

We suggest that there could be a promotion on a specific month or seasons. For example, February has the has lower number of booking maybe because that was the holiday. To boost the sales, we suggest the marketing team could investigate that further.

In conclusion, the above suggestions — including inflow and outflow of the investment — are based on the dashboard we made. For further analysis or a need to edit the dashboard, please feel free to contact our team.

Data Source

Additional information

Despite being fictional, MonCity is one of the largest smart cities in the world, and was developed for education and research purposes. In MonCity, there are four different zone areas (ZoneA, ZoneB, ZoneC, ZoneD). A wide range of self-driving cars is provided for Monash students and staff members to travel between different zone areas or within the same zone area.

MonCity has an existing operational database that maintains and stores all of the self-driving car-related information, such as the booking, maintenance, and accident records required for management's daily operation. However, in order to improve work efficiency, management at MonCity has decided to hire your team of data warehouse engineers to design and develop a data warehouse that can quickly generate reports based on their needs. Management at MonCity wants to generate reports to keep track of the bookings, accidents and maintenance information, such as calculating statistics of booking records and accident records, which can be used for self-driving car analyses later. For the accident details, they are particularly interested in the number of accidents per self-driving car (e.g., the number of accidents caused by Car01).