Connect and Link Data Between Excel and Blue Link

Transcription

0:00 – 0:34 In this introduction to using Excel to data mine Blue link data, the end result is going to be something along the lines of being able to do analysis with something like a pivot table, which is what you’re seeing here, or placing data into a table for analysis. But the key is to be able to do this without rekeying information by being able to fish the data directly out of your Blue Link database and this video which is the first in a series of videos, is going to show you how to actually connect to the data. But just before we go there I wanted to show you why you would want to do this.

0:35 – 0:52 So this is now connected to a live data connecting live in real time to a database if I process another transaction will have to do is right click on here, click refresh, and it’ll pull the current version of the data in here and include the transaction that was just posted like a second ago.

0:53 – 1:09 Now I can do all kinds of analysis. So for example, right now I have sales by customer class and by product class. But let’s just say that I’m interested in grabbing an analysis by salesperson. So I might say show me that analysis by salesperson, by customer.

1:10 – 1:30 And now I can see the analysis by each salesperson, which customer classes they’ve sold to. Or maybe what I want to do is maybe I don’t want to actually analyze it by inventory class. I simply want to do it by customer class by salesperson. It’s as easy as that. So now that you’ve seen why you might want to be able to do this, let’s start off with how we would connect to the data.

1:31 – 2:07 Now let’s explore how we actually connect this Excel spreadsheet workbook to the data, the Blue Link database in this case. There are two distinct methods we’re gonna cover on how to connect spreadsheets to the data. The first one is a basic approach using something called Microsoft Query, which is available in all versions of Excel. The second approach is a more advanced approach that actually allows more sophistication in manipulating the data. It is only available in certain versions of Excel. It’s probably more appropriate for people who are a little bit more comfortable and confident with manipulating data and some of the more advanced technique.

2:08 – 2:28 So let’s go ahead and delve into the first approach using Microsoft query. Now also mentioned that some of the locations, some of the options vary from version to version and even as Office 365 gets updated and they tend to move around. But the basic principles are the same. In order to connect to data, you will find the data tab at the top of your ribbon and click on that to find the ribbon.

2:29 – 2:41 You will find a bunch of options and again, I’ve seen them move around literally overnight in Office 365, but there always be one called Get Data and that’s where you go to try and link this workbook to a database.

2:42 – 3:00 So you pull down the list and you’ll find a number of options from file, from database, etc. For the basic approach which we’re looking at first Microsoft query, you’ll wanna go from other sources and one of those sources, uually near the top, although I’ve again seen this move around is from Microsoft query, so you’re going to want to click on that one.

3:02 – 3:30  At which point you’ll see a dialogue something like this called Choose Data Source a quick word on data sources. In order to use this approach, you will have to have the Blue Link database setup as a data source using an ODBC connection. If you know what I’m talking about, you’ll know how to do this, but if you don’t, your best bet is to contact your IT professional or Blue Links support or help desk department and they can quickly set this up for you or walk you through it.

3:01 – 3:48 Alternatively, a little bit more adventurous, you can simply Google ODBC connections and see how to set them up. The important thing is to set up the ODBC connection before you get to this point and then it’ll simply show up in your list of databases, as in my case where I’m seeing my demo data, which is a file that I’m going to use;

3:49 – 4:15 and just before I select it, I want to point out this checkbox here Use the Query Wizard. You can use something called a wizard to walk you through a bunch of sequential screens to help you figure out which data you’re trying to put into the spreadsheet. Feel free to experiment with that, but I’m not going to cover that in this training course because I’m gonna show you a slightly more direct route which will hopefully be useful for at least for sales analysis, which is the most common use of pivot table and direct Excel connections.

4:16 – 5:05 So make sure this check if you want to follow the way I’m doing it. Make sure this checkbox is unchecked if you wanna play with the wizard, by all means go ahead and do it, but if it’s unchecked and you go ahead and click OK, what displays next is a list of so-called tables. For those that are a little bit more technical minded, it includes tables and views. But this is effectively all the data that is stored in the Blue Link database and you need to know what it is that you want to query before you can go ahead and do that. So I’m going to tell you if you’re trying to do Sales Analysis the best thing to do is to use something called the Book Sales All view which is under B, so you can actually just type B to jump there quickly and then make note that there are a bunch of booked sales something, but you wanna select booked sales all view and then you’re gonna click the add button.

5:06 – 5:21 And the result is Microsoft Query shows in a separate window, and you can resize the window if you want to make it easier, and you can resize the interior window as well. But what it’s done is it’s given you a list at the top of the so-called fields, the pieces of data, the data elements that you might be able to use.

5:22 – 5:53 You don’t really need to worry about this because you wanna pull all the information which will effectively be every single detail of every single posted invoice and every unposted sales order, from the detailed line items to the information on the header screen, such as ship two addresses and customer purchase order numbers and so on, through to information that’s sucked from the inventory file for each product such as inventory class and information that is pulled in from the customer such as customers, payment terms and things like that.

5:54 – 6:26 That’s all gonna be included if all we do is take the asterisk, which means all fields and either double click it or drag it down to the grid. So I’m just going to double click it, it’s going to add all of the information. So actually in this grid down here, but it’s not every single line item of every single as they posted invoice or sales order plus the associated inventory and customer fields for as far back as we go. What we need to do now is click on this little exit door which will return the data so that will close this window and return that data into the spreadsheet. But it has a couple of questions for me.

6:27 – 6:46 That’s what really one question. What do you wanna do? Do you wanna look at this just as a table? Would you like to use it as a pivot table report, which is the kind of report that I showed you at the beginning of this video. You can also immediately dump it into a chart. And where do you wanna put it? Do you wanna put it where the cursor is on this worksheet or do you want to specify a different location?

6:47 – 7:29 So again, typically I’ll do is I’ll typically be using a pivot table, so I’ll typically select pivot table report. I’ll let it place that report right there on cell A, one of my current sheet, and I’ll just click OK and now what it’s gonna do, it’s gonna suck in the appropriate data behind the scenes. Give me a list of all the different pieces of information in the fields that I might want to be able to manipulate down here on the right hand side and this is where in subsequent videos you will learn how to actually use this data. But all the data is Linked-In and just to prove that there is actually data there, if I just pull the amount field into this values column you will see a dollar value and if I simply pull a class. You’ll now see we’re seeing very raw, unfiltered, unfinished data.

7:30 – 8:11 From here we can go ahead and do the analysis which you’re learning in subsequent lessons. So that was the basic approach to connecting to data using Microsoft query and two potential drawbacks of this is that number one, it is an older technology and at some point in time in the future it may end up being retired and second, if you are connecting to a very significant volume of data like you’ve got, you know, millions of rows of sales history records because you go back 20 years or whatever it is, then Microsoft query does have certain realistic limits in terms of how much data it can handle and manipulate and you may end up with   significant performance problems. So I’ve seen on a very large database query take like over 1/2 hour just to refresh the data

8:12 – 8:24 And I guess it’s largely for these reasons amongst others, that Microsoft introduced the more advanced data tools that are available in Excel well as other of the Microsoft 365 suite of offerings.

8:25 – 8:43 So now let’s look at the advanced approach using data tools. So I’m starting with a brand new Excel workbook that has not been connected to data. In fact, I’ve just created the new sheet right now. Once again, I’m gonna navigate to the data ribbon and I’m gonna find the Get Data option and pull down the list, but this time instead of going to from other sources as we did previously.

8:44 – 9:37 We’re gonna go directly to the database. Blue Links data is stored in a Microsoft SQL Server or SQL Server database. So not surprisingly what we’re gonna be doing is we’re gonna be selecting the option that’s at the top of this list. Again, I’ll caution you that I have seen this option move around up and down as we get version updates on Microsoft 365 or Office 365, whatever name they’re giving it this particular month, and in this instance you need to name the SQL Server database. Now, unlike with Microsoft query, you don’t have to set up one of those data source names or DSNS using an ODBC connection. The stuff that we cover off quickly before we started using query. The only thing you really need to know is you need to know the name of the SQL Server Database Server, the Server on which your database is located and as long as you have permission to it and you have the name, you’re you’re good to go.

9:38 – 9:54 Easiest way to find out the name of the server on which your database or databases reside. Bear in mind if you have multiple companies, you would have multiple databases, but they’ll all be on the same server. The easiest way to find the name of that server is to simply ask the Blue Link Help Desk to give you the name of the server.

9:55 – 10:28 Or if you are an On-Premise customer then your IT department or your IT outsource company should be able to give the same, so the name of the server that I want to connect to BLSales2. Now I can type in the actual name of the database if I know what it is but in the real world, I never know what it is. Or I can just click on OK and the system is going to go out, connect to that server as long as I have the right permissions, and then as you see, it’s gonna pop up a dialog where it’s actually gonna list for me the different databases. So if you had multiple companies, each one of these would be a separate database.

10:29 – 10:48 Also, if you have playground data, that would be a separate database and so on. So again, if you’re not 100% sure which is your real live database, it’ll usually be named BL under score and then the name of your company and so without any suffix at the end. So it should be fairly obvious. But if you’re not sure, again you can check in without help desk to find out what the appropriate company name is.

10:49 – 11:19 when you think that which company it is, you simply click the little triangle next to that company name and it’ll expand down to show you the names of all the so-called views and tables, the different units of storing data. And you can see there’s a bewildering number here, so it’s probably better if you have an idea of what you’re looking for. We’re gonna look for the exact same view as we did last time, which was that Book Sales All View. In which case we simply go ahead and start typing in and it’ll filter as we type until we see what we’re looking for.

11:20 – 11:52  Interesting difference here between what you see here, what you saw in Microsoft query. When you click on the view, the system loads a preview of what that data looks like so you can see immediately, all the fields, all the columns going across, all the pieces of data that are gonna actually be available to us in our spreadsheet and but it only loads a few rows and that’s so this in this situation we could have 20 million rows and it’s still gonna be just as quick at this point to this point because it’s just loading a few rows just to give us A flavor of what the data looks like.

11:53 – 12:20 So once we’re happy that this is the appropriate view in the appropriate company, we have to decide what to do. There are a couple of options here. Transform data is something which we’re gonna look a little bit later. So you typically wouldn’t do this right up front, although you technically could. And if you were dealing with a lot of records, one of the ways you could transfer form the data is to say sure, only load the transactions for the last five years because I’m not interested in anything beyond that, but you’ll see later how we could do that. But the idea is we want to actually load this, and we got to choose what we want to do.

12:21 – 12:53 So if I simply click load, what it’s gonna do is it’s gonna load this data in its raw form into a table in the spreadsheet. And that’s what I’m going to do first, just so you can see what that looks like.So here it’s loaded the data all sales history into a table in Excel. It is refreshable. I can refresh this data at any stage by once again right clicking and hitting refresh or alternatively using some of the tools up top. For example, on the data tab you can do refresh all or selective refreshers and it’s going to bring the data up to date.

12:54 – 13:29  Also notice that there is a little panel on the right hand side that actually shows me the name of the data source, in this particular case the this view called Book Sales All View that I’ve attached to and as I hover on it I get more options but we’ll come back to that. But now let’s go back to where we were just before we loaded data into the spreadsheet because a far more useful way of doing this is to load the data selectively for different approaches depending on how you want to use the data. So when I pull down the load button, instead of clicking on load, I click on load two. I’ll get a pop up dialog that will look very similar to what you saw from Microsoft query.

13:29 – 14:09 It just has a couple of other additional elements, but it’s basically the same idea. So the default which is what we did before, is a table, but what we could do is we could load this directly into a pivot table report, which is something you would frequently do, but another alternative is load it to just don’t load it onto the spreadsheet at the moment because we’re gonna use it a little bit later, but we’re gonna do now is we’re going to load this, we’re going to create a connection, and for reasons that I’m not going to go into in this video, I do recommend clicking this checkbox saying adding this data to the data model, that is for much more advanced data modeling, which we’ll cover in a future separate session that will not be part of this series.

14:10 – 14:33 So the only difference between what we saw before and what we’re seeing here is that previously it loaded the actual contents of the data into this tab while giving you this tab on this the panel on the right hand side, but in this case it hasn’t actually loaded any of the data visibly into the spreadsheet, but it has created a live connection which is shown over here to that data that is accessible at any time when you want to work with the spreadsheet.

14:34 – 15:17 Now we have the ability to do what’s called data modeling with this connection, with this database connection and change the underlying data and the way it works and linking other pieces of information and do filtering to say only show me the last two years worth of information. Only show me sales that happen on a Monday. If you really want to do something like that, that will be the subject of a separate video tutorial. I just wanted you to be aware that you can do that, but from here now that we have the data and into the spreadsheet, and this will handle huge amounts of data. You could have millions of rows here and it would be fine. It would take a little bit of time to load initially, but it would work. It would not fail as Microsoft query might, and it would not take an inordinate amount of time to refresh the data when there are new transactions.

15:18 – 15:59 So that’s one of the many advantages of this more modern approach to linking your data. But if I were to hover over this and right click so when I hover over it you see a little dialogue pop up. We’re not gonna work with that dialogue right now, but if I right click the right mouse button I get a little bit of a right click menu. And if I click on load to now I get something very similar to what we’ve seen before and I can go ahead and say I want to add a pivot table or I could do a pivot chart, or I could even do the table that I did in the first case earlier on. But I’m gonna do a pivot table. I’m gonna load that and as you can see, this is pretty much identical to what we saw when we created the pivot table from the Microsoft query connection. So there’s nothing further to add here.

16:00 – 16:21 We are now connected um, we can go ahead and just give us a little bit more real estate, and we can start doing things like putting value there as before and getting an unfiltered value and dragging the appropriate fields into our columns and rows and so on. So again, future tutorials will show you how to actually use the pivot table, but this tutorial has given you two different ways of linking the data.