Integrating SQL and Tableau with Real Life Practical Examples | KAIMA LM | Skillshare

Integrating SQL and Tableau with Real Life Practical Examples

KAIMA LM, BI/Data Scientist

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
18 Lessons (1h 14m)
    • 1. BI COURSE SERIES OVERVIEW

      3:15
    • 2. Introduction to Software Integration

      6:15
    • 3. Combining SQL and Tableau

      5:15
    • 4. Loading the Database

      3:11
    • 5. Task 1

      5:53
    • 6. Task 1 Solution in SQL

      4:13
    • 7. Exporting your Output in SQL and Loading it in Tableau

      4:00
    • 8. Visualising Task 4 Solution in Tableau Part 1

      6:01
    • 9. Visualising Task 1 Solution in Tableau Part 2

      5:17
    • 10. Task 2

      3:54
    • 11. Task 2 Solution in SQL

      3:21
    • 12. Visualising Task 2 Solution in Tableau

      5:58
    • 13. Task 3

      2:06
    • 14. Task 3 Solution in SQL

      2:50
    • 15. Visualising Task 3 Solution in Tableau

      4:21
    • 16. Task 4

      2:37
    • 17. Task 4 Solution in SQL

      2:22
    • 18. Visualising Task 4 Solution in Tableau

      3:01

About This Class

About this Class

Business Intelligence comprises of several skills . SQL and Tableau integration is one of the six essential skills a business intelligence analyst must posses.

Learning a programming language is meaningless without putting it to use. That’s why integrating SQL and Tableau, and performing several real-life Business Intelligence tasks is the main focus of this course

Basic Knowledge

  • A basic knowledge in SQL (My SQL) and Tableau is required
  • You’ll need to install MySQL and Tableau Public.

 What you will Learn

  • Use SQL to create, design, and manipulate SQL databases
  • Extract data from a database writing your own queries
  • Create powerful professional visualisations in Tableau
  • Combine SQL and Tableau to visualise data from the source
  • Solve real-world business analysis tasks in SQL and Tableau

 

Transcripts

1. BI COURSE SERIES OVERVIEW: I and welcome to our business intelligence analyst course, Siri's. This course entails the six essential skills you need to make a great BR on a list. My name is Karima, another business intelligence analyst. Was spent a significant amount of time walking on business intelligence dust requiring more skills like statistics, Excel, database story, Bablu, beytin, the combination off SQL and tableau on even the combination of SQL with tableau and biter. I'm really excited to present to you because Siri's that stands out because each other call Siri stitches. You is done alone skill you need a Xabi are on a list, and by the end of critical Siri's, you would know how to apply Eat in the real world walking environment they cost. There is curriculum that has been prepared for you. Consists off diverse sets off topics are serious. Start with getting you to know the world of data and data signs. We explain all that to Science Jagan's on areas of activities before diving into more sophisticated analytical. Thus, once we have built a solid theoretical foundation, we go through statistics applied in excel and teach database management in s key. Well, then you will be ready to land disabilities off data of data visualization are reporting by creating professional visualizations with Bablu public, one of the most popular, be Iittle available all day. What I consider to be remembered if it's off this program, is that you will let out of leverage, ask your skill and combined them with Bablu to visualize the data contained in disobeys files. Once you know how to do that, it is time to start accordion infighter. But pretty soon he will understand that Lenin, a programming language, is meaningless without putting it to use. That's why, in final Siri's off, this course will integrate SQL bite and and taboo, which would allow us to build a model that predicts clients default rates on. Visualize your findings in taboo, and this will take your preparation to the next level. Because Siri's is it truly also adventure. To get the most out of this series journey, please don't skip any of the lessons in every Siri's as well gradually beauty or knowledge . Given that we teach several skills, it is essential he starts at the beginning as this lessons at the foundation, you need to tackle the more advanced topics you finally town in the program. Many of our lesson contained downloadable resources that will help reinforce what you've learned, such as cost notes, exercise files, pdf materials and the notebook files. Everything is included and can be downloaded easily. I strongly suggest you complete all exercises as they are designed, not only for practice but also as an additional source of information that will end the hands. You'll be I problem solving skills. Are you excited? Awesome. Let's begin this journey together. See you they 2. Introduction to Software Integration: today, the world of programming gives us a chance to use various technologies allowing us to approach and solve diverse kinds of problems, and that is a huge advantage. More precisely, programming allows us to connect on exchange information between servers, software application and frameworks operating in different do means. For example, SQL is a language that has been specifically designed for the domain of relational deter. These management systems will contrast the strong points off programming languages like are important at the execution off complex mathematical computations on the application. And it's fair off business statistics on finance C plus plus. Instead, it's ideal for creating very a set off software toes ranging from operating systems, too. Graphic design applications. Then you could come across sultry applications that are more user friendly while at the same time delivering the exact type of outsports required for your job. For instance, tableau is a soft way use massively in business intelligence on analytics as it provides a wide range off visualizations off deter sits. During the last few decades, the list off programming, languages and software applications has grown so much that it seems to be endless. Nevertheless, when we examined more closely, we can see that each of these technologies boils down to the following structure. They involve the administration on manipulation of a given amount of data to produce a specific type of outsports that will help businesses improve decision making. So when we look at the bigger picture, we can see that today there is an abundance off software each really wants for its domain. And from a technological perspective, days it's amazing. But what does the picture look like from a business point of view? Well, imagine. We want to solve the following business task considering the following the two states create a bar chart showing the breakdown off male and female workers in the continent. If you have a deter in excel, that seems to be an easy one, writes because selected columns containing the data of interest and then insert a chance gruff to your liking. However, this will not always upon in real life, companies are often using and what software tool for database administration, another tool for computations, 1/3 1 for visualizations and so on. The reasons for using a variety of tools come be numerous optimized the capacity of the Children's software. A practical reasons cost reduction, data security, historical reasons or simply because it's so which deals with all due means together does not exist. Here comes the good news. All the software applications on languages are not living alone. They are not detached from the entire ecosystem. They can be connected and he can communicates. That is, they can exchange information between one another. Andi, if they thought that comes to your mind right now, is that you would probably need to implement some sort of integration. Then you're spot on in programming terms, you can integrate two or more programming languages and twos with the idea of extracting a particular type off output. Great. So let's no doubt our theoretical discussion to what we are about to present in the next few sections. SQL and Tableau you come integrated. This means that these two tools conserve a common goal. Andi school is namely on strange such business questions as the world's we mentioned before . To begin, you can still a very large amounts of data, and it is a bays. Then you can manipulate eat with ehskyoo, so using this language, you will create a maintain the foundations of the analysis, however, looking at hundreds and hundreds of thousands of rules off numbers, context is typically not soil for when you're trying to interpret these data. This is where tableau can help. Its main functions include quickly connecting to a sever such as the SQL Server, extracting the necessary data, a plein relevant calculations and then visualizing the obtained information. Speaking in a more technical language, we can see this software will let you create graphs, charts, reports and dashboards operations that I most for any business intelligence analysts out there. In fact, it is namely the reports and dashboards that allows end users such as company executives on general managers to understand the core business and extract insights about it ends to conclude. Integrating sq Oh, Andi Bablu is about taking your data from the depths of your data. Bids to eat ek steamed beautiful representation in Taboo. Practicing this activity will be the focus off the next section. This we'll start from our next lesson explained. The few ways in which SQL and tableau you can communicate will be waiting for you there. Thanks for watching 3. Combining SQL and Tableau: in this lesson will explore the reasons why Connected SQL and tableau. You can be extremely useful. Let's begin by saying That's a blue is a absolutely wonderful island intuitive, soft way That's easy to understand even for beginners. Its goal is to help user projects their data but offering a huge variety of data visualisation tools to choose from Morava. This can happen very quickly just by dragging and dropping the relevance objects you see on the inter fees. And that can be relatively easy oppression. If analysts idea data organized any proper formats, however, that's really the case in real life. It's namely, ask you, Oh, that's gonna help us overcome this problem. And it does so in multiple ways. Information only the most notable ones union of data. Yes, it is true that in today's versions of Taboo, you can create all sorts of joints in a left right on outer joins as well as a union of two data tables. However, you could also use Bablu custom SQL option, which is something like Get week to the data on your sever. Assess your databases through taboo on extract the exact query outsports you'll need for your analysis start procedures. SQL Store Presidio's allows you to apply complex table calculations that can achieve in much better performance if wrong on the SQL several rather than on the tableau in memory engine. In fact, the latter is suitable for similar types of calculation when only using it's small added to sit. Therefore, no let off stored procedures is another valuable to when booking with w Organizing your data in a proper for months for analysis. Sometimes you won't be able to store your data in a clean format, so you need to pre process it before analysis. Bablu is not the best place to complete the process in step prepossessing on the data base level. Instead, it is associate ID with much better performers. While doing these one small, ask your turns out to be people's, therefore walking on databases level by using ehskyoo or costal ehskyoo. A person, for example, can convert data from ehskyoo. Two rules change the format of data values those preparing the detail for analyses in W so we can infer in general knowledge s cure can help you agreed you were walking with some blue when preparing your data for analysis. Also It's a very powerful to when you need to make calculations that you use to create beautiful, a meaningful visualizations. Perfect. Now we must see that all the three options we just mentioned can be applied through the custom SQL option available in tableau deck. Stop only the preservation of taboo. However, while aiming to provide our students with the most demanded skills in industry, we always suggest assessable software tools. That's why we based our next sections on my SQL workbench, which is among the most popular open source gets killed. It's Abi's On and tabla public. The free version after blue are called the free tableau version. Impede our linen process. There is nothing to worry about. It's a blue public is characterized by the black off interconnectivity options that we will overcome by executing one additional step. Storing your rescue output in this series view foul that we will immediately mode in sub blue. Basically, all problems be solved together will be the following structure. Receive a business task. Use SQL to execute the query retrieving a relevant to set from the data bees exports the nearly obtain data, and it's see as we found to be used in Taboo creates a professional understand individualization in taboo that clearly respond to the initial business task. I will help and users come hope with business insights. Remember that a logic behind Conaty No walking ehskyoo individualization off. Captain Data in Taboo is the same regardless of whether you're using tableau text up or republic. So although using the tableaus cost of ask, your option is generally we can are more efficient. What we'll do is perform exercise on its tools we mentioned, which is great, since this is an essential but on skill set off any good business intelligence on a list out, they keep the peace for next. Listen, See you there. 4. Loading the Database: right. So now that you know why and how s Q and W are integrated, you are ready to set off the databases you'll be walking on, which will be the last place before you roll up your sleeves and proceed with more thought provoking. According you'll be using quite a big deter states that is a modification off the employees database that can be found on Get Hope. The latter is frequently used by programmers were about to step it up in notch and dig deeper into rescue. This means data set you be used in here will be related to your work with the employees it obese. We hope you find this relation helpful, allowing you to manipulate it. It's a bees will structure on content. You already acquainted with more fickle, seen on challenging B I tasks. That's why in the next sections we have included exercises that will be a great opportunity for you to for that improve U S hero skills. Okey perfect. We have organized to get a B script in a ehskyoo far. They can download our role. Please ascends the link available in the resource session off this lesson, then downloaded it abyss file named employees more. Don't forget the location you're storing it in. Once the process is ready, open the script file for a workbench as he would open other SQL files. All right, here's a script that she's called down. You see the cold that will create your inside. It obeys from the first to the last piece. There is nothing else to see. His run this file and with for a while. Once the education places over refreshed your schema section, you see the employees more databases appear on your list. So having arranged digital based in walked change from this moment on, you can concentrate on the business intelligence tasks that can be found in the next sections. Great allow holes to make two final newts, one all tables and employees more deter bees beer to same name as the tables from the employees that obeys their corresponding to with an added prefix CLS co standing for taboo . The idea of using such names is to help you not confused the content between the same tables from the to deter busies whilst you reminding you that you are walking on SQL Tableau exercises within modified vision off the employees deter bees, employees more two. Apart from the ehskyoo foul, continue to deter biz You need from the resource section of this lesson become download the relational schema off the employees mode. Please use the spirit of foul as a reference when joining tables on the upcoming exercises . Well, enough said in our next section, Woods on the composition into action. Thanks for watching. 5. Task 1: all right, So, along with an extra videos, which are related to the first, ask your tab Blue problem on our list. Who shares on general notes about you using the two software tools together, as well as a few organization, are details regarding the letters to call. We believe sharing these notes from the beginning will help you focus on tasks given those will be able to smoke. We proceed. It sets in its ask, explaining its solution on then with its visualization in blue. I haven't said That's let's see the first problem on our list creative visualization that provides a breakdown between the male and the female employees walking in the complain itchy starting from 1990. Fine. That's imagine type of graph who, like utilize to respond. Jake Task given If I charts, it would kill. Issue the portion off female versus male employees walking in a company. However, we have been asked to separate the data by you Remember, then it's might be a good idea to display by chance for each year starting from 1990 until the most recent year for which we have some data, I don't think so. In that case, would not have the information about the total number of employees. What can in a company in both male and female? A much better option would be a bar. Charts wouldn't eat as well as having bars to contrast the number of female A male employees in a given year. We have them next to each other so we can compare unwell changes. Now we've decided what shots will have to create to visualize the output. But what's outfalls? It was something, Extract said from employees. Mind right, well, this is the SQL Outsports he must retrieve from the data days. It consists of three columns, one continent a year. We're doing the calculation for another one in the kitchen, the gender and in third cologne, showing them of employees from its agenda. What came in the company for the specific year? In fact, the latter is a key statistic that will answer the business question at hand. And if we take a moment, they could easily relate this piece of data to the bar charts were aiming for right. We have the information split by year on gender, so from this data we can create a bar for each year using a line to designate the current special of male and female employees for doxy. Also years old. Haynes. Human Need We're Writing A QUERIES selection was composed of three columns. Kalinda Gender. A Number Off employees. The 1st 1 can be found in the from Date column in the city Departments Employees Table observed. Before going to extract this value, you can employ a function you have not been act to use so far in this course the year function. There you can feel the general field, which deter from the employees table. In fact, they thought column showing the number of employees can be retrieved from the sea and placed able to another more In the query. You'll have to use a joint between the employees and see department employees who buy year on gender. Apply condition that we will need data from 1990 own included. Considering this helpful notes, please try to solve the task on your old. In other words, tryto pertain. The output we talked about immediately go off course. Don't worry. If this task seems a bit add at end of the day, I think you are the stage where you should face area challenge. Believe me, it will benefit you in the long run. In addition, I give you a problem off such difficulty. We know some of the steps you will need to go through. We know be clear, so we expect you to rewatch some of the early every dues off course whenever necessary or, for instance, used to cost Q and A to ask how two years the year function. Then come back to your food and use all the rest of your Ask your knowledge to produce the required query. Perfect to conclude your a couple of notes on organization Off the lectures in this section after a video like this world in which we give you a problem that you must solve. Who opposed the task itself in its next four months to provide this option? Because if you want to ask them to solve the same problem, which also many hints, or if you're going to the revised in the exercises, lets you might prefer saying the task and it text four months only without watching the entire videos. Then we always proceed. It's a video solution off the given task, followed by the rescuer cooled off solution shown in a text for months as well on it is not going to that moment where we would turn to visualizing the obtained results in taboo. Therefore, the next thing you're going to see is the task in the written for months and the next lecture who solved it's us together, explaining the logic behind right in the query that will deliver desired outputs also see you there. 6. Task 1 Solution in SQL: all right, so let's turn our attention to the solution of the first problem. Stick into the hands. Provided any periods. Video We most select three columns. The first almost contained the extracted from the date column from the T Department Employees table. The year function delivers this output directly. We just after indicates impor intense is the initial call. We're going to retreat the year off interest from and then give the new column in nice name Kalinda. He quick notes on this field off the output table. Why do we simply take the year from the From Date column? Does this guarantee that will count the correct number of employees that have started walking during that year? Yes, it will. To obtain the sq out sport were women, not. We'll need to do two things. The first is to extract the year in which an employee signed the first contract. This information has been stored in databases as a dates any from date field. It's the department employees table. Next, who count the number of employees who have walked in a compliment for that specific A. In another column. When these two steps are completed, we use these numbers on tableau to produce individualization that will respond to the business question we have to solve Great Aunt to quickly approve such data is Eunuch unless select all the rules from this table and then all the distant ones to see the number returned is the same. This man's this table contains information about a single contract employees on that is perfect in our situation. Cool. We're done with the first selection. The Muslim Forget toe. Add that to other columns. Now selection the gender and number of employees. To be honest, filling the gender field is straightforward and given the last column in name, indicating it contains in numbers, sames appropriates so we can hand this line with us now off employees. This name also shoes that's in this feud were storing aggregate values. We have used count, which is an aggregate devotion to the normal off employees from the data source provided Okay, first step door or a, however selected the fields off interest the most indicates when his deter must call from has prompted. You must join, see employees and see the apartment employees check in the relational schema. This translates into connecting the two tables true, The Employee number column. So the court we need is this one. Yes, we shouldn't forget to mention that we used the aliases. If what's the employees on D four C department employees? This we could. More is the distinguish where the columns were using in this query are coming from so going back a little, and most had the aliases at. It's a part of the query. We'll need the year found in the front seat column, and it's it department employees. They're good, while agenda will be formally tea and police table. Also, we need to grow by year on gender. This is actually easy to do when it's time group by year, coma, gender, even do It is not 1/2 into court. It's a very important line, so please don't forget to type it. Finally, remember too hard the condition that you want all calculations to be don't from the year 1990 into having close that he's the most type. Having on then states that the calendar year column is more than or equal to 1990. Let's check if we are working currently by the Houston. The giving Queary absolutely exactly that trances rule would remain about in the next video , I assure you an important step we must complete before important this data in taboo. Thank you for watching. 7. Exporting your Output in SQL and Loading it in Tableau: excellent. We wrote on Iran the cord that delivered the table output. That is an important step for the solution off problem one. So now the most print this data Inter blue, as we mentioned earlier in this cause, will export the data that we obtained in a walk range into its ears. We found four months, which would then ballooned it in taboo I used for analysis. Perfect. Let's begin. While still a workbench, you need to click on the icon depicting a tiny data table on a floppy disk as you over above it. You really following description? Export record Sets Toe on XLR File So that's precisely the option we need. Click on that icon and choose a location where to save the output, give it a name and conveniently leave that up of data R CH V. Although, as you can see, by expanding this list, you could pick off options like HTML or Jason. Next, click on Save and that's it. From this end. Now you can open a blue to create it's a Blue book expanded file list for the main menu and click on New Cool, Then to get to the main page you can hide a click on this cooperation off. It's a blue I call appear or deter sauce down. By doing so, you'll enter please, where you can select tells you want to work with. So at this point you have to connect to its next flower, because the name of the sea every type off far is an observation for you. Comma separated value found, which means that it's X value. Contain and the foul are only separated by a comma and click on its txt file and then select toe open this easy file you just created in my square. Work change. Fantastic. Now you conceded it alluded in the lower part off the interviewees. All right, let's take a few quick observations we got in. It's a blues interface. First tableau generally indicates a feud off the data set. Continue number values with the hash sign on another one. Continue text values, which lets us ABC Second. It will assign capital letters to each word that is part of a column name. I will also substitute Underscore with a blank space. So, for instance, Kalinda on the school year retain with small letters who just become calendar year written in capital letters, which is space between the two words. As a result, we consider blue clearly indicates that Kalinda on number of employees are columns continue number values, while gender is a column, continuous string values only. Also the tableau worksheets is where you'll be creating beautiful visualizations. I'll be doing your analysis. Let's click on shit one on the year you're ready to begin by the way you can. If you want. Change the title of the worksheet by double clicking on its name and typing anyone for the sick of this exercise less rename this worksheets to chat. One Lovely to conclude, we like to say we don't intend to repeat the explanation for the activity presented in this lecture. A spot on the solution off the next exercises we will have most remind you to steal your ask your data in your file office here. The four months and there will proceed from the point where the obtain that the states has been successfully loaded in taboo. Amazing. In the next video, we'll show you how to create a professional, understandable visualization in taboo that clearly respond to the initial business task. Thanks for watching 8. Visualising Task 4 Solution in Tableau Part 1: Okay, people, let's visualize our data and use our business. Intuition. Generalize eats properly. Fortunately, SQL and tableau. Who can help us solve these task relatively quickly, However, there are still many steps to go through, so don't fall into the trap of thinking this can happen in the blink of an eye. Less proceed first. What's up? A graph are we may not. A bar chart where each column represents a year. We want the data it contains to be split by gender on into two rules. It should be this week, so we can clearly see how many employees off each gender have walked for the company in any given year. Well, we should let this logic guy does. Why would create individualization in question? Right? Annoying That's a blue is a drug and drop soft way. We can drag a calendar year hope and drop it in columns committee. See the years for what? We have details available In tabloid terms, the area where Abdul ization is saying is called The View on the field. Next to the column label is a place where we can designate the peels off our graph greats. Let's move on knowing that we have to project the number of employees required. We can drag in normal of employees to rules or P A. Technically, this is supposed to provide a link to the now clearly distinguishable buzz. This is not a bar chart do, is it? It is the top of charts some blue choose for also automatically. The problem is, it does not suit our go to change it expanded drop down list on peak by to convert this visualization into a bar charts. Nice. Finally, we have mentioned gender as a characteristic by which we must separate our visualization. So yes, how we must include this variable in the equation. Most drag and drop gender over the detail. Marquis. Those will separates each bar into two sections, divided by a white line. However, let's be frank A. This is not a picture we imagined, and I wouldn't they want to respond to someone that thes is a respectable and professional visualization because we see no longer hours on. Everything is in one color. In fact, all one needs is a bit of knowledge about W on 14 and this graph was suddenly Azumi better shape. So let's see what we can do about that. To begin, we had an adjusted color aspect of the initialization. Apart from providing the details about the graph, gender is a variable according to which most separates the picture in terms off Carlos. Right? So we need another gender field to drag over the color books in a mark section. However, this this step you must be careful about years. Why, if you click on gender over a year on drug It to Kahlo, you will had a second Collotta graph, but you will lose gender as its detail. Hence applied the friendly old keyboard Commit Mission Control Z to restore the graph to its previous state. Okey Now what are we make these adjustments properly? There are two ways you can take gender once more. For me, it's a pain on the left side of the screen and drop it over Corno. The visualization responded immediately. Wonderful. Okay, let's check another possible way to do the same price control and see to restore the graph to the previous state. It involves orderly control button on the keyboard and taking the gender field for remarks block on drop its off course over the color books. Fantastic. That's better. Next for the sick off showing you how to adjust Piccolo's chronograph to your liking. Less set blue as a Kahlo for male employees on orange as the color for female ones. The quickest way to do this is to double click on the category and pick blue, then click on the other. Deter itin. If a selective orange, apply a click. Okey to confirm Andi, you are the too close, switched Cool. What else? We can increase the size of the bars as they appear to thin. Let's do that. Click on the size and then run really drug, the indicator maximally to the right. In addition, we can designate a fixed with, say, 20 points. Eight Wife click enter and now people appears to be the perfect with excellent. It is high time we had some numbers to the visualization to see how many of the employees have been hired each year, hold control built in and then drag and drop the number of employees to the liberal box to the left. Okay, that some results on. If you click on the number written in black and choose for months, then on the left hand side in four months from Spain will appear expanding. List next to the pain. Caption on up for white in boutiques. Click outside the pain to see that now all numbers, any graph appear. We seem Carlo forms on style. Fantastic. We can say the for Martine off organization is very good. However, there are calculation adjustments to be made before we can see that the graph response to our business task keep the pace for the next video. We'll deal with this issue precisely, see you there. 9. Visualising Task 1 Solution in Tableau Part 2 : Dole. Our graph is looking better and better, however, doesn't tell us much. What we see is a good looking projection of the numbers we obtained in sq. In other words, the visualization tells us how many workers have joined the company in a certain year. It does look, Tello's how many employees were hired Indarti, which would answer the business question act So we needed graph when you know above employees during the company each year will accumulate. In other words, we must turn this data into a cumulative graph. Let's show how this is done in taboo by clicking on this time erect on go. We can hope on hope in menu where we have to select a quick table cock pollution that's must be a ronin toe top. Fine the graphics cumulative but in numbers do not reflect that. To correct this mistake, we need to make the same adjustments for the number of employees. Field with Dean Imax book, click Alitalia Rectangle or simply right click on the name over over quick, simple calculation on select Running Total again. Also, that's what we were aiming for Right now. The bus tells us how many employees from each gender were employed doing it certainly year . Amazing. Basically, the main task has been completed. Congratulations, anyway. There are still a couple of things you should sports instantly. First, the numbers are displayed. Why they're done with off the bus. As a matter of fact, you already know how to adjust the for Martine off the numbers so you could decrease their full size as an exercise. Second, you can imagine that it would be better if we expressed the portions off. Each tender forgiven you. But each Let's do that. Most sets in Utica pollution for the normal Off Employees field as a liberal switch from running toto, 2% off to town, not much convincing, apart from the fact that some percentages are somehow displayed. So let's improved organization off the charts a little. You see, the bars do not correspond to the years accurately to correct these, take all size and change the alignment off the bus to center. Then DoubleClick holy or results are access to let the visualization of feel more space, choose a fixed range and narrow it by a click on or critical firm. Good job what we are left wheat because city percentages are really small, while so because they represented a mountainous setting bar as a percentage of the storm off, all lumbers across the table. In other words, this blocky. It represents around 45,000 people, which is almost 4% on some off all employees walking complaint for all years. 1990 1991 1992 and so on until you to tumble into. But that's not what we need. Yes, how to change percentage is the proper way was law. Open the money related to the number of employees level and check what options you have when you over over computers in. As we said, we don't need to see the values computed across the table, but values for each bar that calls thes option table down. Amazing. We obtain what we wanted. Well, find out told regarding the former teen selected percent of total soul field from the formats for Spain and I just the default numbers to be percentages toe one. There's a LA police only That's incredible, isn't it? We have in front of walls in well formatted graph that we can analyze happily. Was it worth all the effort to get to this point? Yes, absolutely. Because the visualization allows us to see right in me that in 1990 there were less than 20,000 workers in a common name while into double into just trophies. Later, there were more than 40,000. However, the increase in number off employed workers throughout the years has been constant on the ratio between male and female employees, and the company has always bean 60 to 40%. Approximately great nasty graph that's can quickly lead toe accurate business insights will have to say that in the last couple of videos we went into a lot of detail. This was to make sure that we have done our best to help you memorize the steps on developed intuition required while visualising it. It'll set in taboo from now who keep on getting you through. All important steps were creating the chance. However, we will increasingly rely on you to practice. The tricks shown in the videos are a watch some parts if necessary. Thanks for watching 10. Task 2: congratulations. We are making excellent progress. The previous video introduced you to combine in sq Oh Andi Bablu onto the structure in which we have organized the lectures in this section. I hope you have done the corresponding exercises to because it is time to move on to the next business task. And, it's ask, is completely normal of new managers to the number off female managers from different departments for each year starting from 1990. Okay, you already know what steps you must take to solve a problem. First, you should imagine a type of a visualization that could respond to the problem clearly on. Then it should figure house away to retrieve the set from the employees monster Toby's continued to deter. That will let you obtain the desired graph behind this logic. Perhaps you're thinking the solution would be another bar chart in which each bar can spit into two rules represents in the mill employees on the other female employees. However, we will ask you to accept the following challenge response to the given problem with an area charts on area chats. What is this? Area charts is a type of graph which can be leaved as a line charts where the area between the lines on between the lowest line on the always older axes has been field with Carlos Those The results allows you to visually compare the proportion of relationship between the quantities examined greats. So what information is necessary to extract from Aditya bees? Apparently, you need a column for a department name on gender, as we will want to compare our results by this fields, then to count the managers who have walked in a certain departments. We use the employee numbers. Okay, here comes a challenging parts. Can we shoe that in? Manager has worked at the particular department in year X on Does Not Work there in here. Why, but written the active column, which will display one or zero. It depressing as it has not worked for the company and setting Colin I e. So the remain tree fields we need in the sq out sport would be the from on two dates from the department manager table for each employee on a calendar year. In this way, if it cleaned a year falls within the boundaries off the from on two dates. Inactive food who see one otherwise zero baby speak. So before we let you try to solve this task alone. Yeah, few means while creating active cologne, you need a kiss statement to designate the circumstances on the which active most indicate one and then the world's under which it most any kid. Zero letter. When joining the tables, use the following information. The color consent In the year from the hired, it's off the tee employees table. Most cross joined it, see department manager and then joined etc. Departments and see employees Remember that across during ensures that you can combine each rules from a table with each rule from the other table key finally over by employee number on calendar year to finish writing the query. Azimi agree. This is more challenging task, don't you? Well, give it a short and then we'll solve it together in our nest video. See the 11. Task 2 Solution in SQL: right, So yes, acquitted or give owes the desired. Ask your output. Let's analyze it step by step. Naturally, we must start by states and defuse that. We want to see any table outsports department name, gender employee number from date to deeds and Kalinda. All these columns need to be preceded by the aliases college table they would be extracted from. Actually, I just mentioned all but one of the required fields, the active field to create it. Elastic Advanced age off the Haynes from the previous video. This must be a case when, right, So use case Wayne on then the following two conditions. The year off. The final date off the contract to be greater than a calendar year. The year off. The staff state to be smaller than it seem. Kalindi. If this actually let's active display worn by typing, then wall otherwise or as campus aid in Ehskyoo Thames, Hell's let a display zero. Finally, to give this field a name, type in end as active perfect. We are done with designating the columns of interest. Now let's think about the joints. The Heat two letter to this part off. The query began in the following week. A column containing the from the higher date off the tee employees table Most cause join the department manager table. The idea of extracting a column from the employees table most ring a bell on it is to use its a query within the from close in. This inquiry must select the year off the ballot. It's viewed from the tea employee table, the most type cross Joint C department manager and then join the department's auntie. Employees on the really wants much in fields. There's bias and is called any from close to retrieve information only for deuce calendar years where data is available For all the dignity tables to demonstrate this is Joe. We need let's as acute in a separate select statement, starting with Select Everything from, Ah Clay, this intermediate outsports shoes that for each manager has expressed by the difference employee numbers. There's enough information regarding all the subsequent feuds. These fuels can be found within the data tables men shown enjoying for all years from 1990 to 2000 inclusive. So, looking at the query from top to bottom, we can see that we have selected several off the many called blooms Retreat in last query outputs to completely in shock Weary. Don't forget to abide by the last instruction or by employee number one calendar year. Well, at this stage, you can execute the query on Obtain the out sport we're aiming for at the beginning. Fantastic. Now export this SQL outsports in The CHG. File would for you in some blue we will show you out a little distant or set in the same workbook it started while solving problem. One boats in a different wash it see you there. 12. Visualising Task 2 Solution in Tableau: all right. Excellent. In the last video, we executive in relatively long querian, ehskyoo and stored. It's outwards in the sea, every foul, what we must do to finalize the solution of problem, to ease loaded data into blue. And then you realize it's in a way that's were respond to the business tax giving. That's exactly will do in this lesson. Great. We already knew that, Lord. The data in Taboo, you can start a new workbook, click on the text file, select the CIA's before in question. Impress open. However, there is another option you might want to take advantage off if you wish to remain in the same workbook, creates a graph in any worksheets. First click or new worksheet in the lower left part of the blue to create a new work Shit. Notice that if you click on data source, you go to the same stats page he used for the Privy ALS worksheet called Chat one. Therefore, what you need to create is in fact, in new details source. To do that, go back to the new worksheet and expand the determine you choose new data source. Let textile again pick the new CIA chief I'll 100 press open now You cannot says she to which we can conveniently be. Name two shots two. On the year you are ready to walk on your visualization. Perfect. So we want to compare the number of active mill employees. Verceles. The number of active female employees in a company in for each year, right hands, which is 70 years to be laid on. The results are access By dragging the calendar year field hope on dropping, it's in the columns section should designate which statistic will represent the rules on our visualization. Do you agree there is no need for discussion? This must be the active field. Click on on old active drag and drop beats over the rules. Sex. You can see that in the row section is some function with an argument. Active appeared. Why suit? The reason is that tableau automatically navigates in America. Values off the feud on. In our case, this is doing a perfect job because any SQL out active would take values off one or zero only. But so many values automatically force the blue shows. The total number of employees that have been hired in a certain Kalinda and that is precisely what we need. Amazing. At this stage, you repeat what you did in the last exercise, assigned gender as a field that will define digital level on the columns for the graph. To do these, you can drag on drop agenda was a lot of detail on one's over cooler. Great to be consistent with periods charts, Please swap the colors representing the male and female employees. Nice at this point, we go to something that's probably need for you. Expand the drop down list from the mark section and change the type of organization to area . Wow, it looks good, doesn't it? And how can we interpret it in taboo? Often over certain parts of the visualization allows you to obtain specific quantitative information, for example, as defined by two dots that just appeared. This area over here tells all that in 1996 there are 64 mil managers walking in the company while we over a little bit higher. You can see that for for managers were employed during the same calendar year. Biologically, you can check the information related to any of the years depicted on the charts. Wonderful to reply to the initial task accurately. We should create the same type of charts for each department, Remember? And how can we do that? The quickest way is to buy. Click on the pattern name and then select show Futa. That being don't the pattern name will appear in the fields of section because it's time for the suppression in tableau is called filtering. Always does. It is going to see future the charts by department name. Besides, in multiple value least appears on the right spot on the screen or key. By taking on on taking the different boxes, you can explore the distribution off male Russell's female employees for different years for the entire company or specific departments such as marketing production, our quality management profits. Well doing before we conclude, we must admit that if you deem some of this graph slightly, hold will agree with you. The deter in this, as well as other exercises from this section, have been fabricated. It's every based on the employee's disobeys, which, as mentioned earlier, any calls coming found on get hope. But if Children to keep using descended us it in this section to all be eats slightly modified we've done this simply for lead in prophecies. We have to face the trade off between solidifying your ask your knowledge and focusing on a combination with blue or shifting before cause on an entirely new details It we choose the first option. Okey. We hope you're enjoying watching this video. Thanks for watching. 13. Task 3: all right. Excellent. We believe you now have a very good common off ehskyoo Bablu on the combination of the two . That's why in the remaining videos off, the sexual will be giving us hints. So what's the solution of a problem while expecting you spend more time looking for one on the own on now? Tax three Completely agree salary or female Brussels mill employees in the entire company until year 2000 and two and had a future allowing you to see that each department OK challenge accepted. In this case, a line chart would do a good job, Don't you think it's going? Just illustrated every salaries paid for set in here. But we also give a good indication about a trained, true hardy entire period from 1990 to start onto. In other words, in line chat would clarify whether the average salary off men off female employees given the department will increase constantly or whether they have been shot, increases or decreases at any point in time. Also, here's the full output you need before creating the line charts. Considering the periods to task you had to solve in the section producing this result shouldn't be a problem for you even need for columns that can be obtained after joining four or five tables from the employees. More data base salaries to the employees. Two department employees on the department's Please use the original skimmer only database to help yourself. Final fuse. You can join the stables are. Then you need to group by three of the columns. In this lesson, we won't tell you precisely which ones, but we hope because to take advantage off this suggestion finally, don't forget too hard a condition that we're limited outsports for the year 2000 and two only fantastic. I think that's enough about tax number three for the moment, good luck and thanks for watching. 14. Task 3 Solution in SQL: Okay, So did you manage to solve the problem on your own? Let's check the actual court You needed to begin. Let's just say that we will assign s from Alias to T salaries while for the rest of the tables from the employees more deter these. We use the same aliases we used any previous reduce That's been said. It is obvious we must start by designating the gender and the department name columns from tables they're coming from. Then, if you're watching carefully where we're showing you, the output required any periods video. You must have noticed that salaries values were under 22 digits after the decimal points dropped in Sudan. Outsports. You need the round fortune with the first argument equal to the average salary value and sickle arguments You Quarter two. Fantastic to create the last column in our results. It's on SQL Function, which has become quite well known in this sexual will be necessary. I'm talking about the year function. The idea is to extract, just the year from the information stored in the from date field off the salary stable, finally renamed his column to calendar year, as we have done in the last few lessons. Great. Now let's verify that you are written the chorus court in your from close. Physically, we said you would need to join all tables for employees more apart from the department manager. By looking at the relational skimmer, you can spot that you need to join the salaries to the employees on department employees on the employee number on the then collect, see department employees and see department through the department number. The same function written in ehskyoo looks just like this. Perfect for the more, since that's number three required. Also separate the detail by department gender. Honey, this will be the three fields by which will group outcomes Mover not is that here? We can use the department number instead. Wanted batsman name. Is there a problem when doing so? No, there isn't this way. Walk bench was simply deliver on Outsports started by department number Cologne. All do. It's not displayed. All right. In conclusion. Remember too hard. They having close restricts in the calendar year to be no higher, Dan to tell them to Great. So is acute. Andi is the ultimate we imagined at the beginning. Yes, it is cool as usual your sub task before starting the next video will be to export the results it you just obtained in a series we found four months and then blew it Any new. The vessels in a new warship in Taboo. Thanks for watching. 15. Visualising Task 3 Solution in Tableau: we've made accident progress so far. Let's keep the pace scope. We can create any worksheets. Colonnades chats three and then expanded it. ERM, anyone to select needed a source option as usual. Truths. Text file on open to see every file related to task. Three. You can see the data in the lower part of the screen writing. We swore on 34 rules, which information divided into four columns. Gender on department name containing text data on salary and candy. Continue numeric values Court. Let's go back. The first sheet in our documents will create a visualization. Technically, the metal solving this task is identical to the one he used for the periods to problems. Nevertheless, let's do this exercise together to make sure all the require steps are clay was more. The calendar year will form the columns on our graph. Next considered at this time, we'd like to measure the average salary, so dragged the salary field on drop it in Tools, however, have expressed on the vertical axis line. Is crossing numbers that very around the value of a 1,000,000? And by having a quick look at sq a lot support, this sounds a bit Strange writes. This happened because tablet automatically solve the values for both genders on for all departments. But that's not what we're looking for. Expanding many related to this feud on Designate. You would like to measure this field by the average value. Now the vertical axis shows incredible numbers. All right, what else are we supposed to do to this visualization? The pretty level off detail on the color, by gender off course. To achieve this effect, struggle drop gender on detail on Coehlo. In addition to the consistent with previous exercise, please swap the Kahlo's representing the two genders again. Perfect. Are we missing anything? Well, we have to feel about departments. Remember by Click on Department name and choose Shoe Filter. Let's go check photograph chorus supposed to the numbers. Obtaining is killed Output trick on all on, then on marketing to see the line chance referring just to the marketing department in 1996 . The arrest salary of the employees WAAS 66,004 and $68. Is this true? According to our rescue outsports as well, 100% I was very salary off the female employees in the same department equal to $63,679. Yes, amazing. This proved we have walked correctly once more on problem about three have been solved. Congratulations. At this stage you can apply some analytical reasoning to interpret individualization. For instance, you can't defend at any given time period. The salaries values have bean maintain constantly for any department. Over the average salaries off male and female employees were similar on United 93. On. After that, new employees were paid more, with a few thousands of dollars difference. Be nice a lot. Also called Clue the video, which won't find our remark. You must have noticed we took the average salary values in SQL On In Taboo. There would chose every salary values once more we deep. That's because when you're using a measured in euros section in taboo, almost always aggregated data in a certain way and to keep already obtained, every values correct. One more simply select average. Furthermore, the option allows us to compute the average salary values for all departments. So in year, two talent for his does the original salary in all departments. 1 $65,679 while For the female employees, it's for 60,005 around $60. Great. We hope you enjoyed walking on tax number three as well. See you in the next video. 16. Task 4: all right. Excellent. We are ready to begin walking. Or task number four, which is create our SQL Start Presidio that will allow you obtain the average male or female salary per department within its certain salary range. Let this range, but defined by two values the user, Cannon said when calling the Presidio. Finally, visualize your pertained results it in sub blue at the double bar charts. Therefore, the focus of this task will be a new creation off on a scale. Start Presidio for the more you're familiar with all but one of the steps that can take you to the final visualization off the results sets you will retrieve. That's what exception is out. Italy bar charts into a double bar charts in sub blue. Consequently, this will be the trick will talk about later in this section. Fantastic. Let's clarify It's us giving through an example. By using the men and Max functions, you can clearly see that the lowest A no salary ever paid to an individual in this complaint has been around $30,000 while the highest has been around all around $60,000. Then imagine your boss just told you that there's no been many people who have hand less than 30,000 or more than $90,000. So to improve the significance of the numbers obtained as our average salaries paid in different departments, you can exclude all values below $50,000 on approve 90,000. When estimating the average is. For that reason, you need to include two in parameters in SQL store Presidio. Fine. Another hands would like to give you is that this time you will need on out parameter alcohol as a lot of fact or you want to do is retrieve it didn't set. They can export a use in taboo litter. Right? There is no need to study results sets in and out barometer. So you guys keep incorporating one in your query. Finally, remember, it will suffice to select three columns in the task, it is said, you need to separate the average salary values by gender on departments. As a result, this will be the only columns that will have to be mentioned in select statements. Also, presentence the solution off this task for you all did in our next video because solely together. Thanks for watching 17. Task 4 Solution in SQL: right. So, yeah, there is a brief in Spanish in off the query with which you can create the desire to start procedure. First futures salary would be a suitable name. Then, as we mentioned in the previous video you want me to include on out barometer to in any parameters will be enough. One representing the minimum on another one. The maximum salary valley this way values below the forced or both. The second arguments won't be involved in the calculation of the average salaries. Perfect. The data type of the two kilometers will be that this is a standard choice for professionals when walking with numbers express in dollar amounts. The explanation is that won't typically prefers to write dollar amounts with a few digits after the decimal point. Great. Let's move on a specified He will need three columns, each derived from a different data table. Next, don't forget that you will need to joining three tables with it. See department stable as it to create the link between the department numbers start it's it department employees table on the name off that apartment. The latter is information that should be shown in the final results. Sets Okay. The next fine of course, is very important. It is aware condition stating that only salary values between the two in parameters should be taken into consideration. Then group our department number on gender to complete the begin end. Bloch finally sets the day limiter back to semicolon. Nice. Your start procedure is ready so you can run this query to create Eat don. And now you can call the Presidio. For instance, use 50,000 and 90,000 has in many more on maximum bound for Our calculation is acute and amazing. In the results said displayed, you can observe the average salary per department for its gender. Remember that any calculation off this averages only values between 30,000 and 90,000 have been involved. All right, study house. Put in it, see every foul on Load it in the new wash it in the blue will be waiting for you there. Thanks for watching 18. Visualising Task 4 Solution in Tableau: all right. Perfect. This is a deter state from task for obtain for salaries between 50,000 and $90,000 per year . So to turn it into a beautiful visualization, please go to new sheets and renamed it Chat for Okey I presumed at this stage you already much quicker at figuring out how to construct a graph in taboo to solve. That's four. You can drag the department's name. Field two columns on every salary to rules Opportunity bar charts to the consistent. Expand the drop down list from the mark section and choose, but then correct the way you measure the lens off the bus by by clicking on every salary over and over measures on selecting average, I cannot seize the numbers on the vertical axis changed accordingly. Nice model Apple. If at this moment you drug gender to detail on or Cholera, you're creatively charts similar to the one used in Problem one. You will split the bus in the graph vertically. However, this time we're supposed to create a double. But charts remember this means you need to use two bars for each department. 81 represented the meal on another, the female every salary. Therefore, use control on DZI or drug gender to in no sexual area on the screen to restore the visualization. Toe eats previous points greats Here comes the new trick drug gender. Two column on drop. It's on the right side, off department name those. He will horizontally organize the child by department name first and then by gender. Next, assign gender as a column mark for the visualisation and, as usual, exchanging Carlos corresponding to the mill of female average salary values. Excellence. Your graph is practically ready now. You can move the general block beneath IMAX section to open hope. More space in the view. Use your mouse to lower the other sections displayed on screen on enlarged charts if you like. Finally, when it comes to the business interpretation off the result, you could focus only on the orange bars. Andi, in which departments the average man or female salary was relatively high on away. It was lure for the more You can point at any off these bars, if you're interested in, is that every salary value? Naturally, you can repeat. This exercise for the blue bars represented the same statistic for the male employees. Lovely. We are done with chat for on the year we round up the course. Thanks for watching