Join Ron Davis for an in-depth discussion in this video Adding data sources, part of Data Modeling with Excel Power Pivot.
- PowerPivot Data Models. We have opened the PowerPivot Data Model, and for a quick review, remember there's Excel, and then within Excel, when I click manage, I'm in the Data Model, so that's why we see this ribbon. We're going to go in and get external data. The first thing we'll look at is this pull-down, and we can select From SQL. If we select that, there we go, we get the popup in here, and I would enter here the name of the server. If I pull down the selection arrow, it's going to do a query and go out and look for all of those.
We don't want that. So I'm just going to put a dot. As soon as I put dot, that means local host, or the machine that I'm currently sitting on. So the friendly name is now "SqlSever localhost." Database name, I would pull this on down and select the database that I want, such as BlueBuffaloPressSample_01, and then move through there and do my selection, which I'll show you here in just a little bit. Right now, I'm going to cancel out of that. Everything is just about the same. If I go from Access, it's going to ask me the name of the Access database and I would import from Access.
This is a little different than you think. Here, from Analysis Services, it's asking me the name of the Analysis Services Box, or I can bring in, from PowerPivot, let me show you this. See, it says "From Analysis Services, or PowerPivot," so I would select the Analysis Services Box that I had running, or PC that I had running on there, and I would bring in that database. Or I would come in and I would select the PowerPivot workbook that was deployed to SharePoint.
This is only for importing PowerPivot data that has been deployed into SharePoint that has PowerPivot enabled on it, which is not the easiest thing you ever did. But that's what that's for. From Data Service, this is semi-cool. If I select from Windows Azure Marketplace, there we go, then I get this little popup in here. If you're not familiar with it, you can go in and take a look at it, this Azure data. I'm going to say Free, and I can bring down certain data down here that's up and posted into the Azure database.
Some of it's free, some of it you have to pay for. The idea here is, you create a Data Model based on, essentially, the data that you have access to. Then you enhance it with other data, such as you see here. So I can get down on Survival Analysis or Binomial Distribution Generator, Forecasting, Nominal Distribution, there's other ones in here that are fairly cool. I can come in and I can look at, if I can find it ...
Here's Business and Finance. I can also bring in other information, such as weather information. I could use that weather information, if I had Zip codes to tie together, so I can do an analysis, say, on sales, based on the weather as it was that day, et cetera. So this is other information I can bring into my data source. As a matter of fact, not only can I bring this in, if you have data that you want to sell, after you've cleaned out all the information that would identify an individual user, and you wanted to sell it, you would do it right here.
Move on. I can also bring in OData, which is a data description of how that we feed data into a source. "From other sources" really means everything. It shows every connection we have, such as Oracle, Sybase, Informix, on and on, you see. Bring it on down. A couple of the really important is this one, Report. That allows me to create a connection into Microsoft Reporting Services Report. Now, Microsoft Reporting Services Report is a XML description of two things, one, where to get data from, two, how to present the data.
If you combine them together, you've got a Report. What we do here is, we use only part of that. We use the part of the XML schema in Reporting Services that tells us where to go, how to connect, and what to bring back. That's very handy. I suspect, if you're working for a company that's running SQL, they're running Reporting Services. If they're not, investigate why not. It's excellent. Here's Excel, bringing in other information from Excel, and then a Text file, so I can bring in other information from a flat file, which we'll do later on in the demo.
I'll cancel out of that. If I have an existing connection, that's going to be added right in here, under my Existing Connections. Now let's go ahead and do an import. We're going to bring in From Database, From SQL, and the server name in here, again, is dot, local host. Pull down my selection arrow here, and I'm going to bring in BlueBuffaloPressSample_01. I'll test the connection, okay, and I'll just go Next. Now I have two options. We're going to select Write a Query. Don't be concerned, we're not actually going to enter any code.
We're just going to use the Designer. We'll go Next. Now, this name is very important. I'm going to name this ImportedData. The name is important because it's going to translate into the name of the table that we save. And then users, when they're actually in there and looking at PowerPivot, using pivot tables, et cetera, will see that name, and if it just says, like, Query One, it won't make any sense to them. So you want to put a decent name in here. Now, down here we're going to click Design. Now we're looking into our database.
These are schemas, don't worry about it. As we expand that out, we'll expand out Sales, and what we're going to bring in is OrderDetails and Orders and Customers. Now we're going to say Run Query, and there we have. We're going to say Okay. Here is the TransX SQL that was written for us, and you see it joined those three tables that we had into one table that's going to say Imported Data. You can call Validate. Of course, it's going to validate because we wrote it in Designer.
The real reason the Validate's in there is in case you import this T-SQL from somewhere else. And we'll call Finish. Success, 4,900 rows, and I'll call Close. Now, when I call Close, the table is going to be compressed. That was that little flash. It didn't really matter, you only had 5,000 or so rows. But that's what we had. So here, this should look a lot like an Excel spreadsheet. If I go over into Diagram View, I now have that table in diagram view. Let's bring in some more.
This time, I'm going to say, From Database, From SQL, dot, same database, Sample_01, Next, and I'm going to Select tables so you can see the difference. We're going to select those tables in here. We're going to select the same ones, Customers, OrderDetails, and Orders. If I select, by the way, select Related Tables, let's show you that. We're going to automatically detect what tables are related to the tables that we brought in. Now, I don't want to bring those in at this time, I just wanted to show them to you. These are the three tables we're going to bring in.
By the way, if I wanted to filter, I could click Preview and Filter. Then, in here, if I pulled down my selection arrow, I can go through and say, "I don't want to bring in "record three or record six," or anything like that, and that will work just fine. I can also do a sort. Word to the wise: that works just fine, depending on the size of the table that you're bringing in. If I'm bringing in a table of thousands and thousands and thousands of rows, and going through and doing this selection here, it probably isn't the greatest thing you ever did. If I don't want to bring in columns, I would just clear out the check, and then I wouldn't bring that in.
I'm going to say Okay, and we're going to call Finish. If I click Details, nothing interesting, success! We call close, bang! And we have here ... Let me shrink this down ... You see the little spinny? What was happening there is, okay, I stopped so I could change the zoom bar up in here. I have three tables that I imported, the same as I have over here, but they're separate tables now. If I click on the links between them, those are the relationship links, and they show me which field is related to which.
We'll go into that a little bit later on. Now I have data. Here's the same data in one table, and I brought it in separately. It would all depend on what you're trying to do within your data model. Okay, let's bring in a different type of a file. We'll bring one in from Other Sources. I'm going to bring in a flat file, text file, which is a flat file. It's comma deliminated, actually. If I go Next, this opens up. Now, I want to show you the file that I'm bringing in. I've already opened it in Notepad.
It just shows across the top the shipper ID, which is one through five, the company name, and the phone. You can see all these. They're separated down here by columns. This is a standard file that's outputted a lot of times by files such as point of sale systems, et cetera. Now I'll drop that down and I'm going to name it Shippers. Again, remember, that's going to end up as the table name. I'll browse into my file, which is Sale Shippers, and I'll say Open.
You notice the deliminator is comma. And here we go, one through five, and here's my headers. And I call Finish, Close, and now I have brought in from a flat file, or from another source, into my Data Model another table. Now that we've imported data into the PowerPivot Data Model, we'll move forward and create some relationships between tables.
- Data modeling concepts and terminology
- Exploring the Power Pivot interface
- Adding data sources
- Working with date tables
- Understanding perspectives and key performance indicators (KPIs)
- Getting started with DAX syntax
- Understanding evaluation context