Project insights

Home

Project Overview

Project Insights

Project Management

About Us

ANLY482 Main Page

Project Pain Points

We learnt that working with an external organisation has its difficulties and yet we need to be aware of stakeholder management. The realities of real world data being unorganized, unclean and difficult to make sense of are also part of the learning and experience process but it help to widen our perspectives.

The problems we faced when we took the data from the sponsor was the fact that it was messy. There was no proper storage of the data, with different files being in different folders. On top of that, the data given to us were in a raw format with varying consistent formats. Hence, it was a huge challenge for the team to understand or even use the data effectively.

Despite this, we have identified 2 key pain points that the sponsor is experiencing:

1. There is no proper database storage for the data, which prevented the business user from having an aggregated high level view of the records for analysis purposes
2. The data was too disorganized, despite having very detailed and deliberately formatted tables. We observed that recorded invoices always had a different format and some of the phrasing changes often even if it was meant to talk about the same thing.

Dealing with the Data

The data that is available to us comes from different file formats and also do not have the same number of pages. However, what we have identified that is consistent across is that the data set captures items or activities picked by the school to be a part of their programme package. With this in mind, we decided to pick up key activities and save it into a separate data file that ultimately aggregates all the information across every invoice that we have been given. We will then use this new dataset for market basket analysis as for our EDA conditions to continue and improve on the understanding of the data.

Building an End-to-End solution

Moving past interim, we decided that we should focus on building an end-to-end solution for our sponsor. This is because of the limited data that we managed to retrieve from the invoices. Additionally, only managing to do a market basket analysis as only core analysis proved insufficient to solve the problem that our sponsor faced, which was largely related to resource allocation and aggregation of data. Based on some recommendations from our supervisor (Prof. Kam), we began to look at the bigger picture and how we could go about creating this end-to-end solution. We finally settled with using R Shiny, as our backend solution for market basket analysis was based on R codes, and it would be easy to integrate visualization into the solution easily.

Methodology

1. Workflow Diagram

The first step in this project was to create the workflow diagram. The purpose of the workflow diagram is so that we can understand:

1. Logical flow of business process
2. How our end-user would interact with the application
3. What data needs to be collected into our database

2. Entity-Relationship Diagram (ER Diagram)

Subsequently, we built an Entity-Relationship diagram for the purpose of building the database. The purpose of building an ER diagram is so that we can understand how the databases are linked to each other and what data will be stored for each table. This helps us in understanding the data types that we need to capture as well.

3. End-to-End Framework using Shiny

Lastly, we came up with how our R Shiny application would work. The database is built on the helper methods that we create for each database, the client, bookings and activity databases where each helper file (e.g. clienthelpers.R) include "Create", "Update", "Read" functions that help to ensure the database can be built. We also utilised dashboardhelpers and mbahelpers to do the data manipulation and visualization for the dashboard and market basket analysis respectively.

Application Prototype

1. Dashboard Overview

This is an overview of the dashboard application built. On first glance, the user is able to get a calendar-type view of the upcoming events and activities. This allows the user to ensure that there are no double bookings and properly allocate resources to each activity. A table view of the upcoming activities can also be seen below where specific details of the activities are shown.

Also on this overview, the user can see a high level view of the types of activities (both prep and actual day activities) that it has done with its clients. This allows the user to understand the popularity of each activity and perhaps create plans to cater to those less popular programs.

2. Databases
As seen from the left panel of the application, there are 3 databases that we have created in this project, the client, booking and activity database. These 3 databases allow the user to "Create", "Update" and "Read" new data.

Example of client database:

One interesting feature about these databases is that the data is all linked to a local database stored on the user's machine. Whenever the user clicks submit or update for a new data point, this data is combined with the existing database (using an rbind function of R). This new dataset is then saved into a directory on the user's machine as a database and holds the date-time stamp of the change. For example, 180414160000_client.XLS would be the filename saved by R when a user either updates or submits a new data, where the date time stamp would be 2018/04/14 at 1600 as according to the naming of the file.

Another interesting feature is that when submit or update is clicked, the data is immediately populated into the data table seen on the right. This shows the user that the update or submit has been successful as the new data point is added. The data tables used in this entire application is based on the DT package in R, which allows the creation of interactive tables with search function, filter function and auto-pagination, which will definitely be helpful as a front-end tool for visualizing the data in table format.

3. Market Basket Analysis

The last function of this application is the visualization of Market Basket Analysis. This analysis is based on the types of activities requested by clients and completed by the company. Based on our data points, we are unable to generate proper results for a market basket analysis due to the limited data that we managed to get from the invoices. As a result, we built this analysis as a tool for the company to use in the future as they populate more data points and get a more representative view of the types of baskets. This will help the company with identifying popular activities as well as those that are less subscribed, and even help with the bundling and pricing of activities for the company.

Conclusion

In this project, we have built an end-to-end solution for our sponsor so that they are able to

1. Capture data
2. Manipulate data (Backend using R codes)
3. Analyze data (Through Market Basket Analysis)
4. Visualize data (Using the dashboard and data tables)

The rationale for these application ties in neatly with the needs of our sponsor and helps to aggregate data and get a higher level view of the data that they have. This will definitely allow for more interesting applications in future as the company can build on more databases (e.g. Costing and pricing tables) so that they can get a better analytical model based on the database and framework that we have created.