- For more projects to share like this. Please support me by following me in my social media accounts.
Github>>>>>> https://github.com/lois4801
LinkedIn>>>>> https://www.linkedin.com/in/artemis-jay/).
Business requirements analysis, schema designing, database troubleshooting, sql query, data cleaning, data analysis, data modeling, data visualization using looker studio.
Client Altor is opening a pizzeria.It is a takeout delivery like dominos. The project is to design and build a relational database for his business that allows him to capture and store all the important information and data that the business generates. These will in turn help Altor to monitor business performance through the dashboard that will also be built later. He has a summary of data that he wanted in the database. This includes the recipes.
- What are the total orders per category?
- Delivery percentage as per my rider’s performance?
- Total order, and total quantity delivered for the whole shift/week/month.
- Identify my best seller items and its corresponding total sales.
- Give a cost analysis of my pizza per category.
- What is my sales percentage per category.
- Give me the ingredient analysis per pizza for me to know its prices, total quantity, and percentage remaining.
- Show me staff percentage analysis,
- Show me staff position summary and its staff rotation shift analysis.
A. Customers Orders
- To design the database and table in it, we will spec out all the fields of the data we want to collect. Then normalize the data adding more related tables and defining the table relationship. He has specified the different data he wants to collect from each order.
Orders Data Required • Item name • Item price • Quantity • Customer name • Delivery address • Product Category • Pizza • Sides • Desserts • Beverages
B. Stock Control Requirement
- Wants to be able to know when its times to order new stock. -To do this were going to need more information about the following what ingredients go into each pizza ,their quantity based on the size of the pizza ,the existing stock levels
- Different lead times of suppliers to calculate exactly when to order each item or ingredients. The owner said lead time for delivery by suppliers is the same for all ingredients.
C. Staff data requirements
- Wants to know which staff member are working when.
- Based on the staff salary information, how much each pizza costs( ingredients+chefs+delivery)
- There are various ways to do this. Due to lack of resources, budget and connection issues, I will be doing it with this method to save money.
- Everything that can be done in MySQL can also be done in Access but I prefer doing it in MySQL because it is more friendly user.
- There are various kinds of visualization that can also be use. But the friendliest I have used so far is Google Looker Studio. Due to connection issues as well, I have manually imported those visualization datasets in my google drive after changing the type into Google Sheets.
-
To populate the data I decided to import those excel files into MySQL for Querying and creating my dataset for DataVisualization.
- Connecting mysql server to the google looker studio is pricey. I have to connect to google cloud for me to access my database in mysql server.
- So right now, Ill just upload my database and create a visualization using google looker studio I have loaded in sql workbench all my sql queries then download a copy of csv.
- I will also use dashboards in csv file to create visualizations on the google looker studio during the importation and make sure that all fields are aligned to the type of data that I want present on each cell. I made a mistake during my first try and won’t show any number during table or chart creation.
To request for an access to my PowerBI Dashbaord. Kindly please follow me first in github and linkedIn and click this link to motivate me to share more projects like this.Thanks for the support( https://github.com/lois4801 , https://www.linkedin.com/in/artemis-jay/).
