Data Analyst Portfolio Project - SQL | Step-by-Step Guide From SQL Database to Interactive Dashboard

if you're looking for a cool SQL project to add to your data analyst portfolio you've come to the right place [Music] hello and welcome to learn bi online with me Adam finer helping you to do more with data and to build your data analyst portfolio with awesome projects like this one in this video you're going to learn how to design and build a SQL database how to write custom SQL queries and finally how to connect your database to a bi tool to build some interactive dashboards so it's a project that will demonstrate to any potential employers a load of different skills that you'll be required to have for working with SQL originally this project was posted in three parts to separate out and teach these three different skills in shorter more digestible videos but I did a poll and asked whether you thought I should combine them into one longer video and you voted with a resounding yes so here you go I've put time codes to the three separate parts in the description so you can find them more easily if you've got any questions or comments just leave them in the comments section and I'll reply to every one of them if I can finally before we get started if you appreciate the content and would like to help out the channel in some way you can either donate via the super thanks option below the video or you can purchase the project files to help you rebuild this project anywhere you choose via the link in the description if you do thank you very much indeed okay without any further Ado let's get started so we'll be using a real world scenario but to keep the whole project more manageable we'll be simplifying it a little so that these videos don't end up being hours long here is the scenario the client Ben is opening up a new Pizzeria in his town it won't be a dining just take out and delivery a bit like a Domino's he's given us a project brief the first part requires us to design and build a tailor-made bespoke relational database for his business that will allow him to capture and store all of the important information and data that the business generates this will in turn help Ben to monitor business performance in dashboards that we'll build later on we're just taking care of the back end he's hiring someone else to build the front-end ordering system there are three main areas that the brief requires us to concentrate on customer orders stock levels and staff we're going to start with customer orders the way we're going to approach this task of Designing our database and the tables in it is to spec out all of the fields for the data we want to collect then we'll go about the process of normalizing the data adding more related tables and defining the table relationships normalization is something very important in the relational database model and I'll talk more about it in a minute so Ben has given us a list of the different data he'd like to collect for each order here it is item name item price quantity customer name and delivery address now this list is only the starting point for us to spec out all of the fields we'll need for our orders table we know that we're going to need to include things like an order ID field and split out the delivery address into different parts and from looking at the menu Ben gave to us we can see that there are different sizes of pizzas and beverages so we could include this as a separate field we could also include a field for the product category so Pizza sides desserts and beverages here's a mock-up in Excel of what the table could look like we have our order ID and I've split out the delivery address into different address parts notice that I've also included a row ID why did I do this well let's enter data for a first sample order you notice that each order can contain multiple items therefore we couldn't use it as a primary key to identify each individual row hence the row ID that will serve as our primary key okay so now we have an idea of the fields we need for our orders table I'm going to show you a cool tool we can use to make our job of Designing and building our database much easier it's called quick database diagrams or quick dbd and I'll put a link to it in the description basically you specify here on the left the tables and their fields and on the right this produces a diagram of our database and its tables I've gone ahead and created our orders table so you can see you need to specify the field name followed by the data type and for row ID I've written PK after the data type to specify it as our primary key great so now we can see what our orders table in its current state would look like let's go back to our first dummy order in Excel and take a look at the data can you tell me anything that jumps out at you for me it's the fact that we have a lot of repetition of data in the various rows this is called redundancy and The Way We Fix It Is by What's called normalizing the data so essentially normalizing the data is the process of organizing it to reduce redundancy and make it more flexible and efficient what we really want to do is to create new additional tables for both customers and delivery addresses so that instead of using all these different fields in the orders data we can just use an identifier instead this will in turn make the database much more efficient let's start with customer names in the orders table we have two Fields first name and last name to create a customer table here on the left I'm going to write the table name customers return one dash and another return you'll see that a new table has been added to the diagram in this customer table I'm going to start by adding cost underscore ID with an INT data type as a primary key then I'm going to cut the two customer field names from the orders table and paste them into our new customers table that's great but now we need to replace those two fields in the orders table with the customer ID field as an identifier like so now all we need to do is to specify the relationship between these two tables I do this by dragging from cost ID in one table to cast ID in the other in our syntax on the left we can see that cost ID has now been specified as a foreign key connected to orders.cust ID makes sense let's do a similar operation for our delivery addresses I'll create a new address table containing an address ID field I'll cut the address fields from the orders table and paste them into the address table and make sure to add the address ID field to the orders table in their place there's one more thing we need to do to the address table and that is to add a null constraint to the delivery underscore address 2 field the reason for this is that by default quick dbd applies the not null constraint to every single field meaning that it cannot contain null values however the delivery underscore address 2 field is not always needed so can in fact be null once that's done I will then specify the relationship between the address and orders tables now this is looking a lot better but I'm also going to need to create a product or item table this will serve two main purposes first it will reduce the amount of data in the table like with the other two examples and second let's imagine that at some point Ben might want to change the name of an item if we have an item table containing a list of all menu items we would only need to change one row of data in the table rather than changing all the rows in the orders table where the item existed much more efficient here in Excel I have what the item table data looks like we have item id SKU item name item category item size and item price I'm going to go ahead and create this new item table and specify the relationship in quick DVD like so looking good and that is the orders part taken care of so for the stock Control Data essentially what Ben would like to be able to do is to put in place a way for him to know when it's time to order new stock to do this we're going to need more information about what ingredients go into each Pizza their quantity based on the size of the pizza and the existing stock level if we really wanted to add more complication to the project we could also factor in different lead times of different suppliers in delivering new stock which would mean us needing to calculate exactly when to reorder each item or ingredient but I think to keep things more simple we'll just assume that the lead time for all items is the same so Ben has kindly supplied us with the necessary information on each pizza ingredient and the weight that the item is sold in he's also given us a list of the ingredients and the amounts that go into each pizza I've turned all of this information into two separate tables that I'm going to create in quick DVD we can see from the data that the recipe ID is the same as the skew from the item table and the ingredient ID appears in both the ingredient and recipe tables so those are the relationships that I've specified with all of this data Ben will be able to calculate exactly how much each Pizza costs to make if Supply prices go up he'll just need to update the ingredient prices in the ingredients table finally we'll need a table to hold stock levels for each ingredient this inventory table will contain inventory ID Item ID and quantity fields and that's it for the stock control part the final part is Staff data according to the brief Ben would like to note two things which staff members are working when then based on their salary information how much each Pizza is actually costing him not only in terms of ingredients but also the chefs making the pizza and the cost of delivery based on the time it took to deliver let's start with the staff table here it is in Excel we have staff ID Staff first name Staff last name position and hourly rate then we have a shift table this contains shift ID the day of the week the start time and finish time most staff work both the lunchtime and evening shifts on any given day but some do not which is why we need to split them up the final table we're going to need is the rotor table this will tell us who is working when for this we'll need a row ID a shift ID the date and the staff ID the relationships between the final three tables are shift ID from the shift table to shift ID in the rotor table then staff ID from the staff table to staff ID in the rotor table and finally date from the rotor table to created date in the orders table this last one will give us the join we need between these staff tables and the order tables now we have everything we need for Ben's database at least for this simplified scenario once we've designed the database there's just the small matter of creating it to be done fortunately this is where quick dbd makes life very easy indeed all I need to do is to go to the export menu where I can choose to export the SQL code into a variety of different rdbms syntax I'm going to be using my SQL so I'll select this option if I open the downloaded file you can see that it contains all the code needed to create the tables so at this point you might be thinking but how do I get access to an instance of my sequel well it's actually not that difficult I'm not going to go into it in this video maybe I will in another one but just search on YouTube for how do I install my SQL on my computer once it's installed you'll be able to create connections to it so that you can then create databases like I'll be doing now in Navi cat Link in the description if I go into navigat I can right click on my MySQL connection and create a new database I'll call it Pizza DB for the character set I'll choose utf-8mb4 and I'll set collation to utf-8 mb4 underscore Unicode underscore c i and here it is on the left to create our tables in this database I'll right click and select execute SQL file choose my SQL file and run hey Presto the tables have now been created in my database I can now populate the tables where I already have the data I've downloaded the ingredients Excel sheet as a CSV file and now I can just use the navicat import wizard select CSV add the file continue continue the target table is ingredient check the fields are correct and then either use the append or copy methods click Start no errors great we'll refresh the ingredient table and we can now see the data has been added simple with all of our tables set up we'll now be able to write these SQL queries necessary to create views of the database that will in turn allow us to build the dashboards Bend needs in the next video we're going to start with the orders data and here's a quick reminder of the tables we built we have tables for menu items orders customers and delivery addresses if we look at the brief we can see a list of the queries we'll need to build a view for total orders total sales total items sold average order value sales by category top selling items orders by hour sales by hour orders by address in a map and orders by delivery method here in the orders table we've got row ID order ID created at item id quantity customer ID delivery method and address ID so we're going to need to join the item data and the address data to the orders data let's get started here in navicat I'm going to create a new query and start selecting The Columns I need select return the first query is total orders for this we'll need the order ID column so that we can count them now because we'll be joining data from different tables in the database to simplify the query syntax I'm going to be using table aliases so let's start with o for orders dot order underscore ID next the brief asks for the total sales for which we'll need the item price column from the item table so we can sum up all of the values I for item dot item underscore price four total items sold we'll need the quantity column o dot quantity to get an average order value we can simply divide total sales by total orders that we can do in our bi tool so no need for any calculations in our query here next up is sales by category so we'll want to add I dot item underscore cat number six top selling items for this we'll need I dot item underscore name numbers seven and eight orders and sales by hour are going to need o dot created underscore at which contains a timestamp for nine orders by address I'm going to add all of the address Fields so a DOT delivery underscore address one a DOT delivery underscore address two a DOT delivery underscore City and a DOT delivery underscore zip code and finally for query 10 from the orders table we'll need the O DOT delivery column okay so those are the columns we need and now we just have to specify how to join these three tables item and address to orders first up we type from orders followed by its Alias o as the table will join the others too then we're using a left join to the item table I on the joining condition o dot item id equals I dot Item ID for those of you who are unfamiliar with SQL joins I'll put a link in the description to another of my videos that breaks them down for you but you can still carry on with this video and watch that one after if you like let's keep going so we've joined the item table to orders and now we'll do the same to the address table I'm just going to copy and paste this row and change it to the address table on o dot add underscore ID equals a DOT add underscore ID and that's it we run the query and we can see the result it contains all of the columns we'll need for our first dashboard okay so the next part of the brief relates to inventory and stock levels if we look at the client brief we can see the requirements in terms of data total quantity by ingredient total cost of ingredients calculated cost of each pizza and percentage stock remaining by ingredient we're going to need to create two different views of the data in order to give us the result we'll need for all of the queries we have to include in the dashboard you'll see why as we go through in fact this is something that you need to bear in mind when you're doing this kind of work you ideally want to have as few views I.E different data sources as possible to work with in your bi tool but sometimes you'll actually be better off not trying to twist your data into knots to try and get the desired result when you could actually create more than one view that will give you the same result and won't adversely affect the way you need to build your dashboards the trick is to identify which approach to take and this comes with practice okay so let's start off with working out how to produce a SQL result that will give us total quantity by ingredient to calculate this we'll need to know how many orders there were and what the recipe is for each Pizza we can then multiply one by the other to get the total quantity or weight first what I'm going to do is to create order quantity per pizza and for this I'll need the orders table and the item table that contains all of the menu items and their names again I'm going to use table aliases so select o dot Item ID I dot SKU I dot item name then we'll need to aggregate the quantity column so sum o dot quantity in parentheses as order quantity from orders Alias o well then left join from the item table I on the joining key o dot item id equals I dot item id then we'll use the group by for our aggregation o dot Item ID I dot skew I dot item name if we run that we get what we need or at least the first step of what we need the number of orders per Pizza but now we'll need to break down these pizzas by ingredients that comes from the recipe table so we'll need to join to this next join recipe are on I dot skew equals r dot recipe ID and the columns we want to include in the result are r dot ing ID and r dot quantity as recipe quantity because we've added these columns to the result we'll also need to add them to the group by when we run that we can see the breakdown by ingredient ID as well now but we're going to need the ingredient name and that comes from the ingredient table which means you guessed it we'll need to join that as well left join ingredient ing on ing dot ing ID equals r dot ing ID and we'll add the ing name column to the result and therefore the group by we're looking good so far we can now calculate the total quantity of ingredients using this result the next thing we need to do is to calculate the total cost of ingredients we already have the quantity so if we can get a unit cost for each ingredient we can then multiply this by the number of units I.E the quantity or weight we've actually already got access to this data because it's in the ingredient table that we've already joined we just now need to include the columns which are ing dot ing weight and ing dot ing price let's not forget that we also need to add them to the group by now at this point one of the things I'm going to need to do is to multiply order quantity by recipe quantity but the problem we have is that order quantity is already an aggregated field this means I can't use it in the calculation we need in the same select statement so how do we do it then well the answer lies in what are called sub queries let me show you how this works I'm going to put this whole query in between parentheses before the open parenthesis I'm going to type select star from and after the closed parenthesis I'm going to give this whole query an alias of S1 now if I run this query I get exactly the same result as we had before but now I can specify the columns I want from the S1 query and result and do anything to them as if they were in a regular table like for instance using them in calculations just what we're looking to do this will also give us a chance to select just the columns we're interested in to make our result as tidy as possible the other columns will remain available to specify at a later date if we so desire I want S1 dot item name S1 dot ing ID S1 dot Inc name s1.ing wait S1 dot Inc price S1 dot order quantity S1 dot recipe quantity and then we want to calculate order quantity multiplied by recipe quantity as I'm going to call this ordered weight for cost per unit it'll be the ing price divided by ink weight as unit cost finally we want to calculate the unit cost multiplied by the ordered weight to get the ingredient cost so we'll just copy and paste those two calculations here and call it ingredient cost if we wanted to we could actually remove the columns that we don't need like in weight and ing price because we don't really need them but they're not doing as any harm so I'm just going to leave them in this is great we now have all we need to not only Calculate cost of ingredients but also the cost to make each Pizza which is 0.3 in the brief the remaining two points percentage stock remaining by ingredient and list of ingredients to reorder based on remaining stock levels we're going to need to create another query to give us what we need but it'll be based on the work we've already done we're going to want to take the result we've just created with our custom SQL query and manipulate it some more to make this job easier I'm actually going to turn our custom SQL query into a new view but I hear you saying I thought what we've done so far was a view well in fact no we've been writing what are called ad hoc custom queries what's the difference the short answer is not a lot at least in terms of what our use case is and the data model we're working with as far as I can gather and I am no SQL expert there is relatively little difference in terms of query performance and they both work in almost identical ways if you do really want to know what the difference is please feel free to investigate for yourself but that's as far as I'm going to go in Navi cat I'm simply going to select all and copy the query then New View paste and save as stock one going forward if we want to work with the data in this view I can just refer to it as stock one as I would a table like you see here in this new query now I want to calculate a the total weight ordered B the amount of inventory and C the amount remaining for each ingredient the first step is to aggregate the ordered weight for each ingredient that we can do by selecting in name and summing ordered weight as ordered weight of course we need the group by with our aggregate function so Group by ing name and there we have the start of our new desired result what we need now is the total weight of inventory for each ingredient we started with for this I'm going to join the inventory table and for this we'll actually need to include the ing ID column because it'll be our joining key I'm going to turn this query into a sub query like we did before and call it S2 now let's left join the inventory table to it like so we can see in the result that now we have the quantity for each ingredient in stock but we also need to add in the total weight in stock from the ingredients table that will be the quantity Times by the weight that each ingredient is bought in like so in fact we can actually take out these two columns and just leave the calculation perfect the last piece of this particular puzzle is to calculate total inventory weight minus the ordered weight as remaining weight and we're done that is everything we need for the stock part of the brief so if you remember from part one we have three Separate Tables for the staff part staff rotor and shift we want to start with the rotor table because this contains the date so we'll select r dot date because we'll give the rotor table an alias of r then I'd like the first and last names of the staff members using the Alias s for staff I'll specify s dot first name and s dot last name I'm also going to need their hourly rate for our calculations s dot hourly rate now we want to join the staff table to the rotor table so left join staff on the joining key is r dot staff ID equals s dot staff ID if I run that we can see it gives us the result we need next up I'd like to include data from the shift table as well specifically the start and end times of each shift so with an alias of sh I'll type sh dot start time and Sh dot end time in order to join this data I'm simply going to copy and paste the previous join we just made and modify it to be shift sh instead of s and r dot shift ID equals sh dot shift ID great we're almost there the final thing we'll need to do is to calculate the staff cost per row this will essentially involve calculating the number of hours in each shift and multiplying this by the hourly rate sounds simple but it's actually not as simple as that at least the way I do it isn't that simple if you're watching this and you know a better way please do let me know in the comments below I'm going to be using the time diff function turning the result into a total number of minutes and then dividing this by 60 to get a number of hours as a decimal to then be multiplied by our hourly rate here it is and I'm simply going to walk you through it so the first part asks for the difference in hours between the start hour and the end hour which is then multiplied by 60 to turn it into a number of minutes then I add to this the difference between the start and end minutes this gives us a total minutes that I then divide by 60 to get the decimal hours we can see in the result that this is giving us the correct calculation in order to get the staff cost for each row I just need to copy and paste this row and multiply it by s dot hourly rate and voila will now be able to sum up all of our staff costs and hours worked for any given period and break that down by staff member in this third and final part of my data analyst portfolio SQL project we're going to be designing and building dashboards using data we collected in the database we designed in part one and then wrote custom SQL queries for in part two let's dive in firstly I'm going to talk about the tools I'll be using and where the data is being queried from for this exercise I'll be using Google data Studio as our bi tool the reasons being that it's free to use so you can follow along and it has all of the functionalities we're going to need the data we'll be visualizing is in a database inside of a Google Cloud MySQL instance if you'd like to know how to create a similar instance let me know in the comments and maybe I can make a video tutorial but for this video I'm just going to show you how to connect to the database using our bi tool if you use a different bi tool and you'd like to reproduce this project you can get your hands on a SQL dump file via the link in the description that will allow you to recreate the mySQL database anywhere you like okay that's the housekeeping out of the way now let's connect to our database and build our first dashboard to create our first data source in data Studio I'm going to click the create button and select data source now I need to find and click the cloud SQL form MySQL connector here in order to connect to the database we're going to need four pieces of information first the instance connection name that I can find in the Google cloud back office then the name of the database we want to connect to followed by a username and password that you can set up also in the Google cloud back office once I authenticate data studio will communicate with the database that will return a list of all the tables and Views that are available you can see here all of the different tables as well as the stock one view we created in the previous video you'll also notice that we have the option to select custom query this is what we're going to use here's the query that we wrote for the orders dashboard in the previous video again if you'd like to get your hands on this and all of the other queries we'll be using today just use the link in the description I'm going to copy and paste here and hit connect now we're presented with the data source schema I.E all of the fields it contains what we need to do now is to check that all of the fields have been attributed the correct data type created at is date and time delivery is Boolean delivery address 1 has been assigned as text which might be okay but it would be better to specify it as Geo address address 2 contains no data so I'll just leave that delivery city is City and delivery zip code needs to be changed to Geo postcode item category and item name are both text that's fine item price I'm going to choose currency USD order ID has been assigned date not sure why so let's make this text because it's qualitative and finally quantity is a number great let's name this data source orders data I'm now going to hit create report and add our new data source and we'll find ourselves in the report Builder ready to start building our first dashboard looking at the client brief for the orders dashboard we can see that there are 10 data visualizations or queries that he would like to include total orders total sales total items average order value sales by category top selling items orders by our sales by hour orders by address in a map add orders by delivery and pickup starting with total orders we're going to want a scorecard visualization because it only displays one single kpi figure unless you're comparing date periods then you'll have a figure for the difference between the two figures anyway we're just going to show total orders the calculation for this will be a distinct count of order IDs for those of you unfamiliar a distinct count Returns the number of different values in a column whereas a simple count will return the number of rows containing any value or to put it another way the number of non-blank rows if we count the number of distinct order IDs this will give us the number of orders I'm just going to delete this table that data Studio added and then add a scorecard by default we've got record count which is the count of the number of rows in the data set which I'll replace with order ID you can see here it says ctd which stands for count distinct it's the aggregator used for the query and it's what we're looking for we have a total of 58 orders we're going to want to change the name of the kpi that's displayed by clicking on the pencil icon total orders great we now have our first visualization next up is total sales I.E the amount of money these 58 orders generated to calculate this we're going to need to create a new calculated field that will multiply the item price by the quantity I'll hit add a field call this total sales and write the formula item price multiplied by quantity save and finished I'll now add another scorecard and use our new calculated field there we go all that's left to do is to turn it from a simple number into US dollars and in the style tab I'm going to choose compact numbers number three on our brief is total items so this will yet again be another scorecard what I'm going to do here is simply copy and paste this second scorecard using my keyboard shortcuts and add the quantity metric to the query we'll also want to rename it to Total items you may at this point be wondering why I'm not talking about the design aspect of the dashboard well that's because at this stage all I'm interested in is creating the 10 visualizations asked for in the brief once I have all of these on the dashboard I can then start to think about things like widget size placement colors Etc basically we'll be building the dashboard in two phases queries and then design 0.4 is average order value to get this figure we'll need to divide total sales by total orders so another calculated field I'll call it average order value and type in the formula some total sales divided by count distinct order ID you might be wondering here why I'm using the sum aggregator for this formula whereas I didn't for the other one the answer is that the total sales figure needed to be calculated on a row by row basis whereas here we want the sum of total sales divided by the distinct count of order ID again I'm going to copy and paste this other scorecard and replace the metric with average order value we're also going to change this from a number to USD you might be thinking that this figure is quite high and you'd be right that's what happens when you allow people free reign to order anything they like from a fictional Pizzeria normally I would have removed the unrealistic orders from the data set but as there weren't that many to begin with I decided to leave them in there if you decide to get a copy of this data you can obviously modify the values any way you wish you could also add more orders if you like okay the next Point 0.5 of the brief asks for sales by category so we'll need the total sales metric broken down by item category for this I could use a column or bar chart but I'm going to use a Donuts chart because there are just four values and I think it's best suited to the query a donut chart is simply a pie chart with a hole in the middle let me add one to the dashboard now let's add total sales as our metric and item category as our dimension looking good you might now be saying but if you use a pie chart it looked more like a pizza that'd be really cool but no the purpose of the dashboard isn't to look cool it's to display as effectively as possible the data within it there's also the ink to data ratio to consider and a donut chart uses less ink to display the same information and gives the data more space to breathe for more information about the ink to data ratio and other dashboard design tips check out the link in the description point six is top selling items for this I'm going to use a bar chart the reason being that it shows the values vertically which evokes hierarchy better than a column chart does I could use a simple table which would also show the values vertically but the length of the bars will also allow us to easily see how the values compare you might suggest using Color encoding for the values of a table but still not as effective as a bar chart so with total sales at our metric and item name as our Dimension I'm just going to drag the y-axis to the right to increase the area available for the item labels great points seven and eight are orders by hour and sales by hour I'm going to put these together into a line chart you might be thinking that a Time series chart would be better if we're using a date time field but I'll show you what happens when we do I'll first change the date time level to hour and you can see that we only have data for the opening hours of the restaurant meaning the rest is blank and with a Time series chart in data studio all values of the date or time level used must appear in the chart you have options in the style tab for what to do with missing values but neither line to zero line breaks or linear interpolation are what we need nope just going to have to use a line chart instead which also means we'll need to change the sort method to our descending next I'll add in order ID for total orders that's no good because the scales used by the two different metrics are vastly different we'll need to go into the style options and put total orders onto a right y-axis and finally let's change the name of the metric to Total orders and that is 0.7 and 8 taken care of 0.9 is orders by address in a map so let's add one to the dashboard we're going to use a Google Maps bubble map we can see that data Studio has added delivery City but looking at the map we can see that we've got one bubble on the east coast of the US and another in the UK the reason for this is that the delivery City for some orders is Manchester our Pizzeria is in the United States so we need to somehow add in the country to the query as well as the full delivery address to be able to plot all of the bubbles but there's only one field allowed for plotting the location so how do we solve this problem well we're just going to have to create a new field that concatenates the different address fields and adds United States at the end let's do this now we'll call it full address and use the concat function inside the parentheses we'll type delivery address 1 comma comma space in quotes comma delivery City comma comma space in quotes comma delivery zip code comma comma spacing quotes comma United States in quotes save and finished then we'll replace the current Dimension with our new full address one that's better now we'll zoom in to see the different bubbles you might notice one or two bubbles plotted far from the Manchester cluster that's because I generated these random addresses using a random address generator online if we're dealing with a much larger data set or if it was a real world data set I would investigate a problem like this further and clean the data but all I'm going to do here is to zoom in to see Manchester only one last thing I'm going to do is to add total sales to the size metric and we're done the final query the brief asks for is to show orders by delivery and pickup we can create this by adding the delivery Dimension to a pie chart and then use the distinct count of order ID As the metric like so and we're done we have now added all of the 10 required visualizations to our orders dashboard so here we are back in data studio and here is part two the inventory part of the dashboard brief we have total quantity by ingredient total cost of ingredients calculated cost of pizza percentage stock remaining by ingredient and list of ingredients to reorder based on remaining inventory I've already gone ahead and created the two stock data sources using the custom SQL queries we wrote in part two the first thing I'm going to do is to add a new page to the report from here then on the right I'm going to name the first page orders and this new page inventory so the first two points both include ingredients and because we have over 40 ingredients and products that we need to report on I'm already thinking that the best way to visualize this data is using a simple table let's add one to the Page by dragging ingredient name from the field list now we just need to add our metrics first is total quantity which is our ordered weight metric and then total cost which is our ingredient cost metric let's sort by ordered weight I'm going to rename the fields as total quantity and total cost and then add a summary row to give us our column totals but it doesn't make much sense for the quantity column because the unit of measurement is different for different products and categories so what I'm going to do instead is to add a scorecard showing just the total ingredient cost figure then we can uncheck the summary row next up we've got calculated cost of pizza and for this we're going to need to add a new field that multiplies the recipe quantity by the unit's cost let's go ahead and do this again we're going to do the calculation row by row so we don't want to include the aggregators now let me drag item name from the field list and add in our new cost of pizza metric great but what I also want to do is to filter the table on just the pizzas and to do this I'm simply going to add a filter from here call it Pizza only and include from item name contains Pizza and it's done we can also change the data type to currency USD and sort on cost of pizza we can see that the seafood pizzas cost the most to make for the final two points of the stock brief we're going to use the second data source we wrote a custom query for in part two percentage stock remaining by ingredient and a list of ingredients to reorder based on remaining inventory if we think about it both of these are pretty much asking for the same thing and are going to be referencing the same data I.E the amount of inventory remaining will also show us which ingredients need to be reordered based on this figure all we would need to do is to use conditional formatting to highlight ingredients and products in the table based on the percent remaining sounds good let's do it I'm going to start by adding a table from the correct data source by dragging ingredient name and then adding the metrics total inventory weight and ordered weight so the percent remaining will simply be the difference between these two divided by the total inventory weight let's add this field now percent remaining as total inventory weight minus ordered weight in parentheses divided by total inventory weight then I'll add this to the table I'll need to change the data type to percent and I don't need the two original metrics so I'll remove them from the table this is all looking good so far in terms of the fact that we've got everything we've been asked to include in the dashboard but I'm not happy with it and I think we can make it much better by combining these two ingredient tables into one you might think that this would be complicated due to them coming from two different data sources but it's actually really simple and we can use one of data Studios more advanced features I'll just select the two tables right click and hit blend data this will create a third table that contains the data from both tables and now I can just delete the two original tables much better it makes our dashboard much more efficient and easier to understand the final piece of the puzzle is to use conditional formatting to show which products and ingredients need to be reordered straight away and which will need reordering soon in the style tab under conditional formatting I'll add a new condition which will be if the percent remaining is equal to or less than 0.25 or 25 percent then we'll color the entire row with a red color save and add another now if the percent remaining is greater than 0.25 and equal to or less than 0.5 or 50 percent then we'll color the entire row yellow now we just need to change the Sorting method to ascending and we can easily see which products need reordering I like it just going to jump in here quickly to say that I hope you're finding this video useful if you are please do give it a thumbs up and don't forget to subscribe and turn on notifications if you haven't already so you don't miss an upload and remember if you'd like to get a copy of the data we're using for this project as well as the MySQL dump file for you to be able to recreate the database wherever you like please check out the link in the description this third and final dashboard is going to be the easiest by far to build as we'll only need two scorecards and a simple table with no calculations necessary I've already gone ahead and added a new page and called it staff first issue that we're going to need to address is the fact that the staff data source contains data for multiple dates but the orders data contains only data for a single date so we're going to need to apply a date filter to this whole page so that we're just looking at the data for the same date as the orders we will however add a date range control that will allow dashboard viewers to look at other dates if or when more data is added first we'll change the current page settings by right-clicking the page and selecting the relevant option data source will choose staff data date range Dimension is date and we'll set a custom date range of the 10th of August the date we have in the orders data next up I'll add a date range control from here to the top right of the page we can see that the date range is set to 10th of August if we look at the client brief we can see we have just four points total staff cost total hours worked hours worked by staff member and cost per staff member starting with total staff cost this is going to be a single figure so we'll use a scorecard visualization I'll add one here and replace record count with the staff cost metric then we'll rename our metric as total staff cost total hours worked I'll simply copy and paste this first scorecard and replace the metric with hours in shift then rename the metric to total hours worked Perfect the final two points hours worked by staff member and cost per staff member can be put into a table in fact what I'm going to do is just recreate the data source table by adding all of the metrics and dimensions so I'll add a chart and in Dimensions we'll have first name last name start time and end time and in metrics we'll have hours in shift hourly rate and staff cost great if I double click any of the column lines The Columns will resize to fit there we go that's our staff dashboard done in terms of queries you might be thinking that I should increase the size of the widgets so there's less blank space on the page but really there's no need the data is easy to read as it is and as reporting requirements evolve there may be more queries added to it it's fine as it is so now we've created all of the queries asked for in the client brief the next step is to add Design Elements colors Etc let's go to theme and layout in terms of branding what we have is the restaurant menu there are loads of pre-built themes we could use but if we go right to the bottom we can also use an image for creating a new theme I'll just hit extract theme from image and select the menu file I think we'll choose this middle one looks good there are a couple of other elements I'd like to add to all pages the first is a rectangle at the top of the page I'll first draw it and then right click and select make report level now when we load the other Pages we'll see the same rectangle here on page three I'm also going to make the date range control report level as well back on the orders page I'm going to add a page title orders okay the next thing I'm going to do is to resize and reorganize the widgets on the page [Music] like so this widget here needs more information to tell viewers what they're looking at so I'm going to add text here delivery question mark I think this is looking good but there's something else I'm going to do to make it even better and that's to make the grid lines lighter first for this table by going to style and cell border color we'll choose a light gray it's actually best practice to completely remove lines from charts like this as it increases the ink to data ratio and potentially distracts attention from the data itself but rather than doing that I'm just going to make them much lighter so you can still make them out but they're not distracting when it comes to these scorecards most people would add a border to them and even a drop shadow but normally I don't for the same reason I just mentioned a moment ago it's just additional ink that doesn't really add to the legibility of the data they're not really close together to begin with so I don't see any reason for borders to demarcate them okay let's move on to the inventory dashboard we're going to need a title on this dashboard as well so I'm going to go back to page one copy the orders text box and then paste it onto page two and we'll change the text to inventory again here I'm going to select the two tables and in the style tab I'm going to change the cell border color to a light gray finally on page three I'll paste the text box again and change it to staff and then change the cell border color the very last thing I'm going to do is to check to see if I need to add any more titles or text to the dashboards to make them easier to understand a lot of dashboards I see add a title to every single visualization to tell the viewer what they're looking at but this is often unnecessary the reason being that a lot of the time it's very easy to understand what data you're looking at without the need for a title sometimes all you need to do is to add an axis title rather than a chart title like this line chart here the legend tells us that we're looking at two lines for total sales and total orders the only confusing thing is what the x-axis represents so rather than adding a chart title I'm just going to add the x-axis title from the style tab for our bar chart the legend tells us which metric we're looking at as does our map in fact the only other piece of information necessary is the metric used in the donut chart so I'm just going to add a small text box and type total sales a quick glance at the other two dashboards tells us that no additional text or information is needed so that folks is all we've finished building and designing our three dashboards if you'd like to rebuild this project anywhere you like just hit the link in the description to get your hands on all of the project files I really hope you enjoyed watching this project as much as I enjoyed making it if you got any ideas for other projects let me know in the comments thanks so much for watching and I'll see you soon for another video Until then stay bi curious

Channel: Adam Finer - Learn BI Online

Views: 334,875

Keywords:

Id: 0rB_memC-dA

Length: 61min 21sec (3681 seconds)

Published: Fri Oct 14 2022