We'll start by connecting to a variety of data sources. This will leave you with some great workbooks to practice on and develop on your own after this course. Then we'll move into creating a wide variety of charts, including Univariate. The first module will focus on connecting to data sources. Now it has eight lessons in this module, and we'll start by connecting to text, Excel, and Access files. I have the files for you in the video description. And they're listed on this slide. So you're going to want to grab U. S. City's Population..
That is a text file. Airline Comparison is an Excel file. And there is an Access Database file called Northwind that you're going to want to grab as well. But wait, hold on, there's more that you're going to grab before we're done with this slide. In the second lesson, you're going to learn how to paste in a data source. from the clipboard. And we'll be using two different Excel files for that. They're named vehicles and pricing..
We're going to use those same two Excel files in lesson three, when you learn how to merge multiple data sources. And in lesson four, We're going to connect to other databases. That's the title of the lesson. We're actually going to connect to a SharePoint list. Um, if you have access to SharePoint, um, if you want to, you can take a moment and go create a quick list on SharePoint that we will connect to. Later in this module, the other thing is in order to connect to a SharePoint list,.
You're going to need a specific driver. There is a word document in the video description called useful links, and it's a link to the driver that you will need. We'll move on to lesson five. You'll where you'll get introduced to the Tableau interface. Lesson six, you'll learn about changing data types and metadata, and we're going to use a built in Tableau file for that one, so you won't find it in the video description. In lesson seven, you'll learn about applying filters. And in lesson eight, you'll learn about dimensions and measures..
Applications, including Excel. Word, you can also copy and paste HTML tables from web pages. And tables that are copied as comma separated values or tab delimited can be pasted into Tableau. So there's two things that could happen when you paste data into Tableau. If you paste data on the data source view, Tableau will create a new connection in an existing data source, if any. If you paste data on the sheet view, Tableau will create a new data source..
That you can begin analyzing. So a connection versus a data source. And that data source would be saved as a text file to your Tableau repository when you save the Tableau workbook. So just keep that in mind. We're going to get to that in this module. I've added a slide here about the data sources that are supported in Tableau Desktop. And there's an active link on the slide where you can go in and look at the supported connectors on your own. Um, you'll see them when we get into Tableau, but I just wanted you to.
Have this slide for future reference. So go ahead and launch Tableau now and we'll get started. So I'm in Tableau Desktop and the first thing we're going to do is we're going to connect with a text editor. data source. So it just a little bit about this home screen interface here in Tableau. Okay. You have your toolbar at the top as usual. You have a couple of menu items there. Um, we have the connect. area on the left, the connect pane, where you can look on Tableau server..
You can connect to a file, to a different kind of server. There's even more. So I told you there's a lot of different data sources, also known as connectors, that you can connect to. And this is where you can find them by doing the arrow next to, to more. And then you have some saved data sources that come with Tableau desktop. And you'll see the sample Superstore one that we're going to connect with a little bit later. Um, you also have accelerators..
And they're pre built dashboards that you can play with. And on the right side, you have a more accelerators link, which you can get to if necessary. Um, if you have an existing Tableau workbook, over on the right at the top, there's an open a workbook link that you can use to access that. So what we want to do in the connect pane under to a file, We're going to select text file and then you're going to navigate to wherever you put the files that you bought in from the video description. The text file is U..
Posts Related:
- Flow Control Combining And Separating Bundles of Data
- Excel VBA Introduction Part 58.16 - SQL for Excel Files - Basic Union Queries
- How To Design A Responsive Portfolio Website Using HTML, CSS & JavaScript Part 7 BlogSpot
S. city's population. At this point I only have one text file in my folder and I'm - ID Card Make
Going to just double click it. To be able to connect with it in Tableau. We'll go into a deep dive on the Tableau interface in just a little bit in this module. But what I want to draw your attention to right now is this data grid on the bottom. You're actually seeing the data that is in that text file that we just connected to..So that's all the data in the text file right there. Um, it's showing a hundred rows at a time as you'll see over here on the right and it actually has Like nine fields and 304 rows showing here on the left. Um, we're not going to do anything with this file. This is one of the files that you will have that you will be able to come back to and build up later after you learn a bunch of cool things in this advanced course. So what we want to do is we just want to save this file. So there's this little bit of a toolbar here where you can click on save, or you could do control S..
And if it doesn't take you there automatically, you want to navigate to your Documents folder and look for a folder called MyTableauRepository. When you locate it, you're going to double click it, and then you're going to double click a Workbooks because this is a Tableau workbook. And we're just going to name this workbook U. S. Cities Population and then save. We can go to the file tab there, the file menu, and choose close. And then what you want to do is right under the word file, right under the file menu, you have the house icon..
You want to go back to the start page, which is your home page. When you're saving workbooks, they will show up here. automatically for you. You can pin them there so they never disappear. Or, in our case, we're going to do the X to say don't show this workbook. The point of that one was how to connect to a text file. Now we're going to connect to a Microsoft Excel file. Um, you've already grabbed this file from the video description, but when I went over that slide, I said we're going to use a file called Airline Comparisons. I've changed my mind and we're going to use the vehicles file that you.
Brought in from the video description. So in your connect pane, go ahead and click on Microsoft Excel underneath to a file. And then I'm going to double click vehicles to connect to it. So you'll notice, um, a couple of things here. Um, on the left side, it's showing you the connection. So it's the vehicles file. That's a Microsoft Excel file. And then that file has a worksheet in it called inventory. So what you're seeing is the data that's on that inventory sheet. Now we will be using this file again in just a little bit..
So we're going to save this one, but we'll reopen it in just a little while. So go ahead and do your save and we're going to name it vehicles and pricing. And what, once it's saved, we can go to file and close. And I'm going to go back to my start screen by using the home icon. So I'm going to leave the vehicles and pricing tile here because we're going to reopen that Tableau workbook in just a little while..
And the last type of file that we're going to connect to right now is the Microsoft Access database. named Northwind. And before I do that, I'm going to just get this airline comparison file off of my start screen. And under to a file in the connect pane, I'm going to select Microsoft access. Now this one is a little bit different when you go to open it. It doesn't just take you to where you Want to go you have to click the browse button at the top and you'll see that Northwind Database that was.
In the files from the video description and I'm gonna just double click it So we don't have a database password. That's not necessary We don't have any workgroup security that we have to work through at the bottom We're gonna go ahead and click on open. You'll notice it was processing the request and then it opens it up. And this is in data source view like we saw earlier..
And again, you're going to learn more about this, but it made the connection. If you look at the top of the left pane, it lets you know the connection that you have here. And it's showing all the tables. In the data, in the database, as well as queries that are in the database as well. So what we're going to do is what we've been doing so far. Again, you're going to have these three files that you can play with on your own to build them up after you learn what you're going to learn in this course. So we're going to go ahead and save it..
And we're going to call it Northwind.
And go ahead and close it. And go back to your start screen. Now we're going to paste data into an existing workbook. Um, I got rid of my Northwind tile here. And I'm gonna use the Vehicles and Pricing tile to reopen that Tableau workbook. And when you open a workbook, it takes you to a different view. This is called Sheet View..If you look at the bottom of your screen, you'll see that it's on the Sheet 1 tab. There. And what we want to do is we want to switch over to the data source tab, which is to the left of sheet one. So go ahead and click on data source to get to that tab. And this is what you've been used to seeing. So what we're going to do here now is we're going to just switch over to the directory where you brought in the files from the video description and open the file named pricing..
In Excel, and now I'm going to just click anywhere in this data, and I'm going to do control a C to select and copy all the data. And then I'm going to switch back over to Tableau. If we were to use control V here to paste in data source view, let me show you what would happen. Let's go to the data menu. And you'll notice that in data source view, you have two paste data options..
The one with control V is what makes a connection. Now technically you could paste it in as a data source here, but the default behavior is pasting it as a connection here. And let's go back to sheet one for a moment. And look at the data menu and you'll see that there's only one paste option and when you're in sheet view, it will only paste it as a data source, not a connection. You don't have an option here. So control V here means pasted as a another data source..
Let's go back to data source view, and I'm going to just do control V here, and you'll notice on the left that I have a new connection. It's called clipboard, and it has a string of numbers after it. The first numbers before it gets to a letter represent the date that you. Copied it to your clipboard and so that's one way of getting more data in if you had it if it was an HTML Table from a website it would come in as another connection.
Along with the connection that you have existing Let's do the drop down arrow next to that clipboard item and choose Remove. So, we added it as a connection. We removed the connection. Now let's go back to Sheet 1. And in Sheet 1, we're going to do Ctrl V. So it's still on the clipboard for us..
And if you look on the left side, we're on the data pane there, we have a clipboard entry, the same name, but it puts it in here as a new data source instead of a new connection to an existing data source. And you could tell the data source because it has the database icon in front of it. So, if you paste it into, using the default Ctrl V, if you paste it in Sheet View, It's going to become a new data source. If you paste it in data source view, it will create a new connection..
So what we're going to do here now is we're going to close this file, but we're not going to save the changes because we're going to marry these two files in a different way in our next lesson. So let's go ahead and go to file close and then say, no, you don't want to save the changes. Thanks. Before we move on, um, to merging multiple data sources in Tableau, there's some.
Slides I want to go over with you. But before I forget, switch back over to the Excel pricing file and close it. The first slide I want to go over with you is doing a deeper dive into Data Source View. So we've seen this view several times when we have been connecting to our data sources so far. But what I want to just do a deeper dive in just so you get familiar with what things are called and so on and so forth as you work in Tableau..
On the left side of the screen where you have the letter A, That is known as your left pane. It displays details about your data. So it's letting you know what connection is there, what sheets are there, so on and so forth. And then in the upper center, where it says Sample Superstore in big letters there, that is known as the Relationships Canvas. And you'll see that there is a relationship between the orders and the returns table in that sample superstore file..
And the line between those two table panes is known as a noodle. Underneath that layer, okay, the relationship canvas, there's another canvas called the joins canvas or the joins unions canvas. And that's indicated by the letter C on this slide. So right here, this is your joins canvas. It's not visible when you first go in. You have to do something to make it show up. And you'll see what that's used for in just a little while. On the bottom of the screen, in the background, you have your data grid..
That's when we, where we've been looking at the data that we've been connecting to. And to its left is another grid called the metadata grid. Here's some useful information for you in terms of the data types that are supported in Tableau. And you can change the data types of your data that you're connected to in Tableau, which you'll see. In this module, so I said that when you're in data source view, the default.
View is the relationships canvas. That's also known as the logical layer. And you use that layer to combine data using relationships. When we looked at that returns table, that was kind of like a pop out on the slide. That's the other layer that you can get to. So there's a physical layer. underneath the logical layer. And you use the physical layer to combine data between.
Tables using joins and unions. Each logical table contains at least one physical table, and it's also known as the join union canvas. So this one talks about the difference between relationships, joins, unions, and blends. Relationships do not merge the data. But keeps it separate and it's based on a common field joins are also based on a common field, but they actually merge the data from the same source joins.
Will combine the data and then will aggregate it unions also merge table data. But unions append the related fields as rows in the merge data set, and then you have blends. And that's an approach to combine data from multiple varieties of sources and display them on a single screen. Blending will aggregate the data and then will display the combined data. Now, because we're talking about relationships in this module, there are two relationship terms at the bottom of the slide, cardinality.
And referential integrity. Don't worry about them for now, you'll get to see what they mean as we work with relationships in just a little bit. There's another slide here for your future reference with the cardinality and referential integrity options available in Tableau. And I've included a few slides indicating the join types. that are available to you. So back in Tableau, I am going to reopen our vehicles and pricing.
Workbook, and I'm going to make my way to the data source tab. So at the top of the data tab on the left, next to connections, I'm going to go ahead and click add, and I'm going to choose. Microsoft Excel and navigate to where you have your files and open the pricing file. So now I have two connections in here. I have vehicles and I have pricing and vehicles has the inventory sheet..
And if you look under sheets here, you'll see there's a pricing sheet, which is from the pricing file. So what we're going to do is we're going to drag that sheet right onto this. Relationships canvas. And so it creates a relationship based on a common field, or it's going to create a relationship based on a common field. Now both of these files have a Venn column in it. And so if you look at your data grid at the bottom, it's showing you the Venn column from the pricing file..
And that file Also has dealer cost and manufacturer suggested retail price. If you hover your mouse... Over the noodle, joining those two tables, inventory and pricing, you'll see that the relationship is inventory to pricing. The cardinality is many to many. So many inventory items are defaulting to many prices. And then you have your related fields there. So, VIN from the inventory table equals VIN from the pricing table..
And if you look down here in your data grid to the left, there's, you learn more link how the relationships differ from joins, where relationships is not combining or merging the data, it's just relating it. And you see that it's using the VIN number from inventory equals the VIN number from pricing. And so it created that relationship. Tableau is really good about that as long as you have a matching field with the same name. Now the fields don't have to have the same name. If they don't, Tableau probably will not be able to create the relationship.
For you automatically like it did here. So right now we're looking at the relationship canvas and this is also known as the logical layer. To get to the physical layer we're going to double click on that inventory table on the canvas. So when I double click it, it goes to the physical layer where is where you which is where you create your joins and unions. It's also known as the join union canvas. It lets you know that inventory is made of one table. So remember from the slide, even if you're only using one table, it will show in.
Both the logical and the physical layer. Now we want to create a join between the inventory and the pricing tables. So what we're going to do here is we're going to grab the pricing sheet and drag it and drop it into the canvas. And it automatically creates a join between the two tables. So it's saying if I hover over the join symbol, It's an inner join of inventory. Notice it appended the pricing table with the number one, right?.
And it's based on the VIN field in both tables. So an inner join. In the inner join, only the common matching data between the two tables is displayed. And that's what we're going to have here. Now, if I want it to change the join type, I can just click right on that join symbol and you'll see the other join types that are there. We're not changing it, but I just wanted to let you know that you could, if you want it to, and I'm going to close that join type window..
And now if I look at the bottom, it's merged the data, right? So you'll notice. In your data grid down here, it's letting you know, underneath the data type symbol, it lets you know which table it's pulling from. So they're your inventory tables. The first, the inventory tables are the first set of fields. And now you have your pricing tables as your second set of fields. And since we don't want the VIN showing twice, I'm going to hide the.
VIN column from the pricing table. And I'm going to just right click on the column header and choose Hide. So there's our merged dataset. Now, if we want to go back to the logical layer at this point, we can do the X in the upper right hand corner. of that physical layer, and it brings you back to the logical layer. And again, in your data grid, you can see your combined merged data set. Also, if you notice up here in the canvas, it lets you know. If you hover over inventory, um, it lets, you know, it's logical table.
And it's physical tables, and it also gives the symbol for the join there. So, you know, that those tables are joined. So the automatic relationship that happened is because the different. Tables have a common field. It was able to automatically create the relationship for you, but that did not merge the data together that just related those tables to each other. We use the join. from the physical layer in order to be able to merge the data together..
And then we got rid of the redundant Venn column that we didn't need in our merge data set. Go ahead and save this file and you can go ahead and close it. So now we're ready to connect to a SharePoint list. So I'm going to go back to my home, start my home icon to get back to my start screen. And I'm going to go ahead and get rid of my vehicles and pricing tile here..
And this time where it says connect on the connect pane on the left, we're going to go to the more arrow. So we saw this a little bit earlier. These are all the connectors or the data sources that you can connect to. And on that list in the middle toward the bottom, you will find SharePoint list. And that's what I'm going to select. So this is the one that you will need a driver for..
Now, if you don't have the driver, it will let you know as you go through this process. So you have to give it the URL to your SharePoint site. And the addition of SharePoint. And then you're going to, I'm using a username and password for authentication. And I'm gonna go ahead and put in my password and then click sign in. Um, you have the Word document from the video description. That gives you a link to the driver, but in the pop up that comes up, if you don't have it, it will also have a link to that driver..
