John Underwood is a Technical Evangelist at ThreeWill. He has nearly thirty years of software development experience. He is an experienced technical instructor with superior presentation skills and is proficient in delivering standard curriculum as well as developing and delivering custom curriculum.
Good afternoon folks, this is John Underwood, coming to you from Alpharetta, Georgia on behalf of ThreeWill. I’d like to welcome you to today’s event, where we’re going to start talking about beginning your journey with Power BI. For those of you that have been to events that I’ve presented before, I thank you for attending again. If you’re new, you can see a little biographical data about me on the screen there. My title at ThreeWill is technical evangelist and that’s a really cool job title, because it means I get to do a lot of different things. I write some code. I do video production. I get to teach and do events like this. I’m real thankful for the opportunity to work at ThreeWill, it’s a great place.
A couple of little follow-up matters. If you wish to contact me after the event with questions and so on, you can see my e-mail address there. Also if you are Twitter enabled, you can see my personal account there @jbutweets, and then also a company link there. ThreeWill Labs is a research portion of ThreeWill that specializes in kind of looking over the horizon at technologies and then making that available to our engineers and our customers. I’m real happy to be working with that team as well. Most everything you’d want to know about me or the company, you can also find at threewill.com. Also one other thing, even if it isn’t posted here.
I will mention that today’s event is being recorded and once the event is over, within a few business days, it will appear on Vimeo. You can go to vimeo.com/threewill. If you don’t remember that we also post those links on threewill.com. Lots of different ways for you to find information and content and I hope you’ll find those things useful. Now just a little bit about who I’m presenting to today. I think it’s helpful for you to know who my intended audience is, so you can kind of figure out where you fit in the picture. The first of those things that I’d like to say about my audience is really first, it’s about SharePoint users. That could be a broad spectrum of users. That could be someone that is a real SharePoint cowboy or cowgirl. They’ve been using it a long time. They just want to be able to begin taking advantage of self-service BI.
You may also be relatively new. I think what you’re going to find is that as long as you kind of know what it is you’re after, the tools that I’m going to present to you are going to be very helpful in taking advantage of these self-service business intelligence capabilities. If you’re an IT professional, be that an admin or a coder or anyone else, that kind of works on the IT side, then really there’s probably two aspects of this that will be important to you. First of all you might actually find that using some of these self-service business intelligence tools would help you do your job. Would help you spot trends about things your users might be doing or not doing. Then even though it is called self-service BI, there are going to be a couple of things that I will show you today, that will require some IT intervention on behalf of our customers or users, in order to set that up.
It isn’t so much that I’m going to teach you how to configure it, that’s not really the goal of this time together, but just to show you what’s there and what’s available and how you might do that. Even if you’re purely in the user audience, the second part might be a little bit important to you if for no other reason, just because if you begin to use these things, you might have to go to your IT people and basically say, I need you to do this thing for me, so that I can take advantage of this capability. Hopefully regardless of the place you find yourself there, you’re going to find some useful information. Just remember this is about breadth not depth. I’m not going to go really, really deep on any of these things, but rather I’m just going to try to give you the lay of the land and the simple path that you can follow to begin to get some value out of self-service business intelligence.
Then with that, what are we going to cover? Well, we’re going to start out by trying to understand the message a little bit. I’ve even had Microsoft people privately admit to me that the message or the way that they describe the products in the self-service BI suite is not always the clearest or the easiest to understand. I think if you have a place to hang your hat on, what’s what and what are the steps we’re going to go through. I think that will simplify matters greatly for you. Then from there we’ll actually go through the process. The first step in having some kind of meaningful business intelligence experience is to find or discover data. I think what we’re going to see in this part is that our definition of data might be a lot broader and a lot bigger than traditionally we thought of it as being.
Then from there we’re going to analyze that data in some way or another. We’re going to slice and dice. We’re going to play what if? The whole goal there is to gain some understanding. To find things that we didn’t know before. That really is the goal of business intelligence. Then once we’ve gone through our analysis, we’ve got to present that data in some fashion or another. What you’re going to see is that at least for the parts I’m going to demo to you, there are potentially three different ways that you may show this data. Then the last step of that is Power BI for Microsoft 365. I think you can draw a little conclusion right here, just from the way I’ve laid these out. That is whatever self-service business intelligence is, Power BI in the Microsoft 365 Suite is actually just kind of a small component of that.
Our goal throughout this event together is to eventually get to the point where we’re using Power BI to share this useful business intelligence data, but the gathering of the data and the steps we go through are really going to be more about using the self-service tools. Where do those self-service tools lie? Well, let’s kind of drill down on that and see if we can figure out the message and where the tools are going to be. Data exists in many forms and in many places, particularly nowadays with the things that we see on the Internet. Let me give you some examples. Sure, my company has databases and I might want to pull some data out of that. What we see now on the Internet is that there are a lot of places within an HTML page that’s got a table in it. That table has some useful data, that I want to act upon.
Not only that, there are various standards that are present on the Internet that you may or may not be aware of, that act as data feed, where you put in an address, instead of getting back a webpage, you get back some structured flow of data that you could actually use and analyze. These things will exist potentially both inside and outside of our company. Not only might there be a page on Wikipedia of data, there might be a catalog page within my company that I’d like to pull in and do some data analysis on. If you have this strict traditional definition of oh we’re going to burrow into a database and pull out some things. Yeah, sure we can do that, but the idea of data now is a much broader definition in my opinion.
Then when we say we’re engaging in business intelligence, what we’re really trying to do is just take that data, merge it and pull it together in order to spot some kind of trend. We’re looking for things that we might not have found if we just looked at a simple tabular report. Now the fact that it is self-service, the idea here is that we should be able to go part of the way or all of the way without IT kind of having to help us. We should just be able to fire up our tools and do this. Another thing that self-service implies, it says something about the size of the data, so if you’re familiar with the term data warehouse, this is not a data warehouse, at least not in the volume of data. We may do some of the same kinds of things.
In fact what I would tell you is behind the scenes we’re actually using some of the same tools that one would use in managing a data warehouse. If you’re not as familiar with that term, it’s okay, don’t let it intimidate you. Just understand that self-service BI is probably more about hundreds of rows or thousands of rows, or maybe tens of thousands of rows. We’re looking at sets of data that have 30, 40, 50 million rows that would go into the many terabytes data. That’s not really self-service. Microsoft and other companies make other BI tools that would let us analyze those mass chunks of data. That maybe a gray line, but somewhere out there, there’s a line where you say, gosh this things too big for self-service. I need something a little bit more industrial strength.
Now when it comes to using the tools here, the Microsoft naming can be a little bit confused. They use the word power a lot. I would invite you as you go through this just to remember that whatever it is we’re doing here, it’s really just three simple steps. We have to find the data that we’re looking for and we probably have to shape it or scrub it in some way. What do I mean by shape and scrub? Well, there may be rows in there that we don’t need. There may be columns that we don’t need. There may be values in there that are interesting to us, but they’re the wrong data type and they need some kind of transformation. We’re going to see that there’s a particular tool that gives us an innate ability to discover and shape data in the fashion that we would like.
Then from there it’s about analyzing the data. Running scenarios. For those of you that are familiar with the phrase or the term pivot table. That’s a lot of what we’ll do in this step. We’ll learn about the tool that will allow us to do that. Then finally the presentation of the results. That can take a couple of forms. We’re going to learn about each one of those. Then ultimately whatever we’re presenting may wind up inside a power BI on a SharePoint side. Again, as we go through the tools just kind of keep in mind, each one of these tools is going to fit into one of these categories. Then what are the tools that we’re going to use? Well, you’ll see a graphic there on your screen that will illustrate it for you. We start on the left with Excel, most of what you do in self-service BI is done in Excel. SharePoint is in the game of presenting it.
What we’re going to do along the way is going to happen in Excel. You see three steps at the bottom of the Excel window. Finding the data, analyzing your data and then visualizing or presenting your data. Then where does SharePoint play in this? Well, it’s about taking that presentation and pushing it to a much bigger audience. Think of it this way. One could certainly create an Excel workbook that had some nice discovery and some nice analysis and some excellent insightful visualizations, but we’ve got to be able to get that out to the right audience. What we probably don’t want to do, let me say that another way. What we definitely don’t want to do is just send it out as an e-mail attachment.
SharePoint and in particular, Power BI is going to give us a much more elegant way of sharing that information. Not only is it going to share the information in a very friendly way, but it’s going to provide some Q&A, the likes of which we haven’t had to this point. Then another thing to think about if you build this really nice visualization of your data, there is at least a chance that you’re going to try to present to the people that are going to want to look at it from a tablet or a phone. While Excel services do give me some ways to project onto those platforms, it’s probably not in a mobile friendly way to present the data. That’s another thing that we’re going to see in the combination of SharePoint and Power BI is a good story for mobile.
I will tell you that that story is not fully written yet, but it has begun and it’s got some promising turns ahead. Now that we kind of have our 30,000 foot flyby of the different parts, let’s begin looking at some particular parts. The first thing that we’re going to look at is a tool called Power Query. It is going to be our tool for discovering and scrubbing our data. What is Power Query about? Well, like several other things that we’re going to work with in this event, it is an Excel add in. What is its purpose? Well, its purpose first and foremost is to be able to connect to a wide variety of data sources. You’re going to see when I show you the demonstration, that there are quite a few choices here.
Traditional things like databases and non-traditional things like web pages. Then how do we use Power Query? Well, if we’re lucky enough to find the source of data that is exactly what we want, then that’s awesome, but in the case where that’s not true. We can use Power Query in a very interactive, iterative fashion to build the queries that we need, so that we omit data that we don’t need. We transform data that needs to be transformed or changed in some way. Again, I come at this more of the side from a technical person, because I’ve been a developer for years, but it’s really friendly. It’s really easy. If you have any command of Excel whatsoever, then you’re going to be able to do this without a whole lot of trouble.
Then another nice thing about this, once you’ve found the data that you’re looking for and you begin working with it, you’re going to get a consistent experience across all of those data sources. Whether it’s coming from a web page, or a data feed, or a database, or any other source, even another Excel spreadsheet. Once you get it into Power Query and once you’ve got the data read, the manipulating of that and the building of your queries is going to be the same no matter where the data came from. Another thing that’s really clever here and in fact, I’m going to demonstrate this for you, the ability to join and consolidate data across data sources.
Let me give you a real world example of that. I actually work with a volunteer organization with my son and daughter’s marching band. It’s all run by volunteers. As you might imagine data tends to get thrown across a lot of different spreadsheets in a lot of different systems. Recently I needed to put together a consolidated report that would tell me some information about students. That data existed in five different spreadsheets. Literally using Power Query I was able to stack that data together and consolidate it into one master view that would show me exactly what I needed. In the past literally I thought about this, when I started working on it, I’m like I’m going to have to bounce this into a database and do a bunch of joins.
I didn’t have to do that at all. Power Query gave me the way to do that, and it gave me a way to do that in a fairly friendly fashion. Now what are the data sources? Well, I’ll try to give you a nice little graphical representation of some of the things that are available to you here. Now depending on your technical or non-technical level and your level of experience, some of these look familiar. Some of them don’t. I do have a couple that I want to point out to you. One that I really like is the fact that you can use another Excel spreadsheet as a data source. That’s exactly what I did in the example that I just described you. Some other things to be aware of. Pretty much anything that serves up tabular markup can be used. Even to the point where when you begin to go through and discover these data sources, it’ll actually show you the different tables on the page and allow you to select one as a source of data.
I know sometimes Wikipedia is considered to be a good source of data. Other times it’s a little sketchy. I think it depends on the topic that you’re looking it, but regardless of whether it’s Wikipedia, or some kind of knowledge base, anything in your company or outside your company, that’s in an HTML tabular format is eligible to be a source of data for you. That’s pretty exciting. Then kind of at the other end of the technical scale, some of you are very familiar with the idea of web services, XML, JSON data. For those of you that aren’t, just understand it this way. Just as we have addresses on the Internet that let us find a particular document, there are also addresses that send back data in a more structured form, as opposed to an HTML page.
That’s what an O Data feed is about, JSON or Java Script Object Notation is a data format, as is XML. The reason I tell you all of this is just to kind of educate you if you are more on the user end of the spectrum, it may very well be that within your company, they would never permit you to connect to a database and read data out of that, but there may be a data feed, or a web service in your company that’s there just for the purpose, for you to consume that. The fact that you know know that exists, may give you an opportunity to get at some data that you wouldn’t otherwise get. In the demos that I’m going to be doing today, I’ll try to do a good variety of these, just so that you can see several different things in launching.
Let’s now switch over and do a demonstration of Power Query. All right, so I have switched over to my Excel 2013 Instance. It’s pretty easy to tell that I’ve got Power Query or the Power Query add in installed, because there’s a tab up here that says Power Query. Now how do I begin getting what I’m after? Well, there’s a couple of approaches that I could take. First of all, I’ve got to decide which kind of data that I’m working with, so you can see there. I can read from Excel. I can even read meta data from a folder and import that. Pretty much every major brand of database is supported. I also have other sources, like a SharePoint list. I can absolutely pull that into Power Query and then we talked about O data feeds. I’m actually going to demonstrate one of those for you in just a few minutes.
My opinion is pretty much any reasonable source of data that you’re going to come across, you’re going to find support for it here. Now in some cases, I may have this vague notion that the data that I seek is out there somewhere, either on our Intranet or on the World Wide Web, but I just don’t know exactly where it is. I could certainly go to a search engine and attempt to find it there. There’s nothing wrong with that, but what I think is pretty cool is that I can actually do that search from within this tool. If I click on online search, it’s going to bring up a little search window to the right. Then once that window opens, I can come right over here and type in a search.
Let’s try something that’s probably near and dear to at least some of my listeners today. Let’s say that I have some kind of desire to learn a little bit about World Cup winners. Now, notice that just by typing that in, I got back a significant number of results from the Internet. You can tell by the icons, some of these are coming from “data sources” some from Wikipedia, some from other places. What I think is pretty cool here is if you float your mouse over any one of these, you can actually see a preview of the tabular data that’s discovered in this source. Now, for the little demo that I’m going to do for you, this first one would be suitable. What I’m going to do now is just double click on it. By double clicking on it, it’ll actually go and read that data off of the Internet and then it’ll plop it in my spreadsheet here.
Now, you may find yourself in the fortunate circumstance, where the data that you were looking for was exactly what you needed, but I’m going to tell you that more often than not, you’re probably going to have to manipulate the data in some way or another. Let me give you a couple of examples. If as an example, I really wanted to break apart the winning and the runner-up scorer, I’m going to have to do something with that, because, it’s all in one cell. Not only that, just by nature of the way this thing was formatted on the web. If I want to do something with years, I don’t want 1930 space details. I just want the number, so that I can then format it. The way that we’re going to do that, is we’re going to take this query that we just created and we’re going to edit that query. I can either mouse over and use the edit menu at the bottom of the screen, or I can just simply double click on the query.
Then it puts me in the window where I can manipulate this query. I’ve scrolled to the top of the data here and you can see, it’s showing me the data that I saw before, but the difference now is that I actually have some interactivity where I can manipulate and change this thing. Why don’t I do a couple of things? First of all, I’m going to give it a name, World Cup Winners. I’m going to put Webinar after that, just so I can keep my demonstration straight. Then it may seem silly or needless to have a description on this, but you’re actually going to discover something a little later in the hour, that’ll help you see why that is important. Then from there, it’s just up to me to begin to manipulate this and change what I need to change.
First up in my little example, I don’t really need the host country. Notice that I can select that column and right click, then I can remove it. Then in addition to that, I don’t need any of the columns to the right of the score, so I’m just going to multi-select those with a shift click. Then I’ll right click and remove those as well. Now, you may or may not notice this, but if you’ve got a sharp eye and you glance over to the right side of the screen, you’ll see that in this applied steps section, everything that I’m doing actually stacks up there as a step, so if I’m going through this, and I realize oops, I didn’t need to do that. I can remove the step. I can reorder the steps. If I’ve got a lot of steps and I want to be able to keep them straight, I can even rename them.
I think that’s pretty cool how it just sort of builds that as a list and then you can retrace your steps as you go if you need to. Now, a couple of other things that I need to change here. One thing that I definitely need to change is I need to do something with this score. In particular, what I’d like to do is split it up based on the dash and just keep the score value. Even in this case, I don’t even need both of them. I just need the first one, because, I want the winner’s score. The way that I’m going to do that is by splitting the column. You’ll notice that there’s a split column by delimiter. Then there’s some common delimiters such as an equal sign or a comma. In my case, I’ve got a custom one and I’ll just paste in the dash. Then notice a couple of things I could do here. It tells me something interesting here. It basically says, based on what you’ve given me, you’re going to wind up with three columns.
Now the reason I’m going to wind up with three columns is there a row further down the screen, where it was a tie and they went to kicks. They show the scoring on the kicks. For my example I just want the first number every time, so an easy way to do that is just say, yes split it up, but I only want you to keep the first column. Then if I click okay, you can see now, I’ve got my numbers. It’s pretty cool. Then score, under score, 0.1 is probably not my best name here, so I’m just going to rename this. I’m going to call it winners score. One of the things I need to do to make this data a little friendlier is I need to turn this column into a number, in case I wanted to do some kind of calculation about the duration between years.
Well, that’s going to be easy enough. I’m going to do two steps to do that. The first thing I’m going to do is I’m going to replace some values. What I’m going to take is space details. I’m going to replace it with an empty string. By doing that now I got rid of the extraneous text there and I just had the digits. Then once I have just digits I can change the type and I can make it a number. There you go, even in the course of describing this to you, it really didn’t take me all that long to be able to pull that together and do something useful with it. Now, at some point I’m going to do something with this data. What is it I’m going to do with it? Well, that’s what we see in the lower right corner of the screen.
I’m either going to load the worksheet or load the data model. Now, I have a little chicken and egg problem here, in that I haven’t told you what a data model is yet. Think of it this way for now. If I say load the worksheet, it’s just going to plop the data right into its page on my Workbook. Fine, no problem, and then I use the data. If I load it to a data model, what you need to think about there is, it’s more like I’m storing that data in some kind of memory structure, so that I can use it for a later task. As you and I get to Power Pivot and Power View, we’ll learn a little bit more about what those mean. For now, I’m going to leave it as load the worksheet. I’m going to click file and close. Then you’ll notice it refreshes the data and it shows me the new view.
Now in order to demonstrate some of our join capabilities, let’s try one of the things here. I’m going to go back to Power Query for a second time, I’m going to do another online search and this time I’m going to search for population by country. It would be helpful if I spelled it correctly although I’m guessing it might work anyway. Turns out that there are a couple other sources out here that give me some information about world population so one is as good as the other for this demo, it might be in the real world I might do some research on these and make a scientific choice about which one I need but in order to show it in action I’m going to pick this one. Double click on it and you’ll notice the moment that I got this new source of data, you just add it to another sheet down here at the bottom so there’s the sheet where I’ve got my existing data and there’s the sheet with my new data so what I’m going to do now is fill in and [scrub 00:27:03] this just a little bit.
I’ll go in and edit the query and just like before I’m going to knock all the columns I don’t need so I certainly don’t need that one and I don’t need any of these as well. That’s about all you need to do for this one. I’ve got data now that’s going to give me some information about different countries and their population. Now how is it that I’m going to bring these two together? THere’s more than one way you can do this inside of a Power Query but in the end you do it with the same [verbage 00:27:40] and that is the word merged so here’s how I think about these two merge and a [pin 00:27:46]. Merge is somewhat equivalent of a join in a database. I got two queries, they have at least one column in common and we’re going to match rows based on those columns.
On the other hand a [pin 00:27:58] is something I use where if I’ve got two sources of data, they have all the same columns and I just want to take them and combine them top to bottom. In this example I’m going to do a merge and as you would expect, just going to say all right, how do you want to join these so I want to start with my world cup winners and then from there I’m going to join them to the population countries.
I’ll match this column and this column and notice they’ll tell me how many it matched and then for the sake of this demonstration let’s say that I only care about the ones that matched. I’ll simply turn that checkbox on and then I’ll click okay. What you now see is I have a third query that’s going to be the result of pulling these two existing queries together. Now you’ll notice when I get over to the far right column I kind of expected to see my population there but instead it kind of shows me this cryptic thing called a table. I guess you could make the argument that it would be okay if I just went ahead and auto expanded this but for whatever reason they choose not to.
I’ve got to do that. I’ve got to click on the little arrows right at the top of the new column and it will show me the fields that are being represented by that joint data so for my example, the population data is really the only one I’m interested in so I’ll just go ahead and click okay and now I’ve got my population. Then we’ll do something like this, merge winners by population. Then once we’ve done that we’ll go ahead and apply [inaudible 00:29:37]. Now you can see my worksheet has three tabs, a tab that shows me all the winners, a tab that shows me population data and then a tab that shows me the merging of this two so it’s not hard for you to project some different ways you might be able to use this inside your company to analyze data that’s either coming from your own data sources or data sources outside the company.
Just a little aside, I’m going to pull a [inaudible 00:30:06] here for something I’m going to show you a little bit later. Many of you may be thinking to yourselves all the things I would care about are going to come from within my company or to put that another way, how can I see data sources within my company? When we get to the very end of our presentation today, we’re going to see there is actually a mechanism for accomplishing that so you may find yourself in a place where those with more knowledge of data would be the ones that would build and publish the queries and then those that need to consume it can consume it regardless of their technical acumen. More to come about that in a few minutes
For now I’m just going to go ahead and say this. I will call this one query [data 00:30:55]. All right. Let’s go back to our slide presentation now and we’ll see a few more slides and then I’ll do another demonstration. Now using my diagram here we got through the part of discovering, scrubbing, cleaning, shaping up our data. Now we want to do some kind of analyses of it and the way we will do that is with Power Pivot. If you ever done a pivot table inside of a spreadsheet program, this is that same sort of thing, only it’s going to allow us to pull our data from a different, perhaps larger source. If you’re not as familiar with the concept of a pivot table let’s just say for now it’s a way for you to do a lot of multi-dimensional what if type analysis of data. What am I getting per region, what am I getting per region per product categories. Those kind of things.
Notice if you will in this first [inaudible 00:31:50], what is Power Pivot? It allows business users to create self-service [BI 00:31:56] data models in Excel. Interesting that I made the words data models a different color. That must mean they’re important in some way. It is really important so what you need to understand about a data model is really a couple of things. First of, when you build a data model, even though you described the data that you want to work with, it’s not going to just be plopped into your spreadsheet. Rather it’s going to be held in memory and then used for some other purpose such as a pivot table or visualizing. Another thing you need to know about data model is that by choosing this approach you actually broaden your horizons a bit on the amount of data you can process so you’ll remember a few minutes ago it talked about there being some industrial strength business intelligence tools you can get from Microsoft.
Those are generally referred to as analysis services. When you use a data model in Excel, what you’re actually using is kind of a junior version of that analysis services engine so in plain English, you can put a lot more data into a data model than you could by plopping it into a spread sheet so a moment ago when I was working with our world cup data, if I so chose I could’ve simply checked the check box at the bottom to put that into a data model and instead of seeing it in a page, it would be in memory and it would be available for whatever visualization or whatever analysis I needed to do. When you think about how big the data can be when you use this [sequel 00:33:33] server analysis services, there’s not a hard and fast rule. A big part of that is going to be how powerful is your laptop and so on but I can tell you there are things that would easily bog down Excel alone that will work just fine with this.
For me the rule of thumb is if I’m talking about 1000s of rows I feel pretty comfortable going down this path. If I’m talking about a half million rows, then I’d get somebody from IT to consult with me on that and see if that’s an appropriate use of the tool but bottom line, you can try this and it’s too much, it’s not like you’re going to be harming anyone else, it’ll just slow your laptop down and you’ll realize you need to go down a different path. Much like Power Query, Power Pivot is this thing where you go into a design of sorts, you tell it the kind of data you want and so on. We’re not going to be able to the kind of scrubbing and the manipulating we can do in Power Query so it may be in some points you can Power Query to build the model and then Power Pivot to do your [way 00:34:37]. That’s certainly a valid thing to do. One thing that we can do here, you may choose to do and that is calculations and formulas of various sorts.
Not only can you use the calculations to create new columns, you can also use the calculations and formulas to create key performance indicators and other things you would expect to find in a business scenario engine. The data is going to come mostly from the same source that was saw in Power Query. Corporate data base, data feed, an HTML page, whatever it is that you need.
Where is the end game on Power Pivot? If you look at the very last point here, ultimately we use Power Pivot to analyze the data and then provide us a base from which we’ll present the data. To say it another way, sometimes when you’re using Power Pivot, you’re building a data model so you can build a pivot table or a [pivot 00:35:35] chart. Other times you’re using Power Pivot to build a data model so you can feed into a power view report. Sometimes you may be doing all of this. Let’s see a demonstration of how you pull this data together you is it in Power Pivot.
Just as I observed a moment ago when I did my Power Query demo, it’s pretty easy to figure out if I’ve got the Power Query add-in installed by looking at the tab at the top. Same with Power Pivot and obviously the two product teams there were in disagreement about whether there should be a space between the words or not but that’s fine. For our example we’ll go to Power Pivot and you’ll notice there’s not a whole lot to do here. What we need to do is go into manage. Manage is just this add in, kind of the same way the query builder was on Power Query. That’s where you go in and start actually accomplishing things so I’ll click on manage and then probably the first thing I’m going to have to tell it is where is the data going to come from. Notice if you will that most of the data sources are similar databases, things such as that.
One thing that I do find interesting here is that we also have the ability to pull data in from analysis services so it could be that some IT folks with a lot of strength behind what they’re doing are able to put together a set of data for you and you can actually pull it in from that so I think that is very useful. For my example I’m going to use an [O 00:37:14] data feed that happens to be out on the internet. Those of you that have worked with sample databases, you know many years ago Microsoft created a sample database called NorthWind. It had products and orders and customers and all these different kinds of things so turns out now there is actually a read on link O data feed and the nice thing about this multi fold, number 1, is an interesting way to do demonstrations. It’s also a good way for us to practice and get used to using [O-data 00:37:44] and the source of data in our applications.
From my example I’m simply going to copy this URL and then I’ll go back to Power Pivot and then I’m going to tell it that I’ve got an O data feed I want to use so I’ll take the address of that feed, I’ll paste it in and click next. You can see it will actually go out and hit that address and make an analysis of what sets of data are available to me. I’m just going to choose a few of these. I care about categories, I care about customers and I also want to know somethings about orders and details that go with them and I always like to know about products and then I’ll go ahead and click finish.
Now something that I want you to think about as we got through this. When I did my demo a moment ago in Power Query, I only had a really vague notion of what I wanted, I was just searching for it and I found something that looked good and I acted upon it. What you’re seeing in this example is that I had to have a little more prior intimate knowledge of what I needed and where it was and how it was structured. Using a tool like this may require me to do a little bit more work up front. There are a few things that I just really don’t need so I’m going to eliminate those. One that I don’t need I’m not going to be using this picture so there’s really no reason to pull it down, I’ll simply delete that column.
Another thing I’d like to change here, if I go over and look at my order details, it might be nice to do the multiplication and subtraction on these numbers in order to have a cost column so if I click on the add column [inaudible 00:39:31] and I come up into this editor right here you can see this is somewhat like a formula editor. I can just say I want this to be equal to the unit price times the quantity minus the discount. Then if I hit enter it will plot that calculation into a new column and I can right click and rename that and we’ll simply call that cost.
Let’s think about where we’re going to go from here. The next thing that I probably want to do is I probably want to set up some relationships. If you know anything at all about pivot tables, you have multiple sets of data and you have to describe how those are inter-related. I’ll do that by clicking on the manage relationships button and then I’m going to create 4 relationships here. First of all from order details and I’ll pick order ID and we’ll match that with [inaudible 00:40:33]. I’m going to create a second relationship, once again from order details and this time we’re going to match the product. The next thing I want to o do is I want to go to orders and I want to map those to my customers.
The last one I want to do here is I want to go from my products to the various categories they belong to. I know I’m being repetitive here but it’s worth stating again. Obviously for me to make some good use of this I’ve got to go into this having done my research and knowing a little bit about where the data exists and what it’s going to do for me. What am I going to do with the data at this point? I really have two choices. If I simply want to use a data model to supply data to some reports which I’m going to teach you about in just a few moments. I would simply click on this little obscure button right up here and it’ll take me back to Excel. I’m going to show you that.
Notice when I get back to Excel there’s not any data here. Remember I told you, there’s not going to be any data here because we’re building a data model. The data is in memory and then I can do something to manipulate the data. That’s one approach. The other approach if I truly wanted to build a pivot table, well, there’s a pivot table right there so I’ll click on that and it’ll ask me where I want to put the table and I’ll click okay. I still don’t see the data yet but what I do see now is the mechanism for creating pivot tables and manipulating the data in the fashion that I want. Let’s do a couple of things here. First of all I want to go down to order details and I want to see some information about price and quantity and discount and cost.
You can see it’s already begun to fill out those values for me. In this case it’s simply summing all of them for me so even just with that I could draw some conclusions about the amount of money that we made at least at a gross level. I probably want to be able to slice and dice that in some different ways as well. I’m going to say I probably would like to look at this based on product. Now notice what’s happened, just by supplying this product’s name I have the ability to go in and have a look at the different products and how they’re performing. If I want to breakdown just a few products I can select just those and then I can see those in a little more detailed fashion and then if I like I can simply clear the filter and now I see everything again.
If I want to add a different dimension to this, let’s go one step further and say not only do we care about products but we care about the categories that they exist in. So I’ll go ahead and I’ll select category name and then what I’m going to do is take category name and drag it from here up to filters and what you see now if I want to be able to break it down and look at certain categories, I can de-select all of these and just say I’m interested in beverages and condiments and you can see it’s just showing me those items. If on the other hand I just want to show beverages I can easily do that.
What is happening here is the analysis of the data. I’m going in and looking at different things in different dimensions in order to decide what I need to do with my business. Going forward this data might be useful to me as is or I might want to report on it in some fashion. Remember when I created the data model it can either service this need to manipulate a pivot table or it can be used as a source of data for visualizing or reporting on it. Let’s go back to the slides now and learn just a little bit about that. I’ll call this my pivot demo and we’ll switch back to Powerpoint.
As we depart this topic let me point out something to you real quick. Those of you that are still using your on premise [inaudible 00:45:09] and you’re using SharePoint 2013, just FYI, there is a product called Power Pivot for SharePoint. The point of using that, no pun intended, is just the fact that it will allow you to do some browser based pivot where the processing is actually going on that server so the idea here is that somewhere behind the scenes, we’ve set this up inside of SharePoint and what it’s actually doing is taking advantage of analysis services on the server.
If you take advantage of this it does also have another capability where you can create a Power Pivot gallery and it’s a special type of library that lets you publish and browse and share your Power Pivot workbooks so I’m not going to demonstrate that one. We’re focusing more on Microsoft 365 here but for those of you that are in that world, that’s probably something you want to check out and in fact this is not a 100% true but it’s a little bit true. That is, given that power BI doesn’t work on [Premises 00:46:10], this might be an alternative that will let you accomplish some of the same sort of thing. Not all of them but some of them.
All right, now we’re into the third step of the visualizing and presenting the data and how are we going to do that? First up we’re going to do that with Power View. One again, we’re talking about an Excel add in. Probably, I should put a little asterix at the end of that sentence. Power View has existed to this point as an add in however Microsoft made a recent announcement that eventually it’s going to be folded into Excel and it will no longer be a separate add in so at some point in time you won’t download it anymore, it will just be part of the next Excel service pack or Office service pack and then it will just be there. What is Power View about?
It does have some overlap with Power Pivot in that it’s about exploration but the difference here is you’re not just seeing rows and columns. It’s really more of a visually pleasing presentation experience where we can do charts and other such things. Again sometimes we fall into this thinking “Rows and columns ought to be enough” but let’s face it, we live in a world where things are visual, we live in a world where people see television productions and web pages that are stimulating to the eye so that’s part of what we would get out of Power View. This would give us the ability to create things that are not only factually and scientifically correct but also desirable from a visual point of view and easy to consume.
That’s particularly going to be true when we’re trying to reach an audience that just wants to see what they want to see. They’re not real interested in the data or the science behind it so maybe an executive audience as an example. Someone that’s really going to benefit from a nicely done Power View report. Once we build the report it can be presented in a couple of ways. You’re going to see first of, it can be presented as a part of the spreadsheet and as we go on you’ll see there’s also a Power BI or a SharePoint aspect to this. Easy enough to use, just like the tools we used to this point and then how does it work? There are those two words again so I encourage you to remember that. We talked about data models. A moment ago I used the data model that’s a source of data for a pivot table.
Now I’m going to use a data model as a source for a Power View report and how is that data model come to be? Remember it can come to be in one of two ways. I might’ve used Power Pivot to create it or I might’ve used Power Query to create it. Either one would’ve been fine. In the end now that I have the data model I can report on it. Let’s see what that looks like. Given that I know that I have a source of data available to me that’s pre-scrubbed, I’m going to take advantage of that so just as there is a data feed for NorthWinds, there is also a data [inaudible 00:49:24] which is just another sample database from Microsoft. I’ll go in and I’ll use Power Pivot to build my model and this is going to be very straight-forward, it’s just going to be one set of data I’m interested in so paste in my feed and the one I want to look at here is the territory sales drill down.
I’ll finish that and I don’t really need to do any calculations, I don’t need to add any columns, I just want to take the data as it exists in the model. Remember what I said, when you want to get back to Excel and do some other things, if you’re creating a pivot table you click this. Otherwise you just click the full icon up here and I make a big deal about that because the first time I got into this tool I felt like I was trapped, kind of like goes back. That’s how you go back. Everything to this point has had a menu or a tab that says Power something. When you go to use Power View it’s actually on the insert tab so I’ll go over and click on insert then I’ll click on Power View and from there you’ll notice anything that is defined in the data model is immediately visible to me on the right side of the screen.
Then beginning the process of creating a report is pretty simple. I want to know about the employees and I want to know what territory they’re in and I want to know how much money they’re bringing home and I can put something up here to the effect of sales report and at the risk of oversimplifying things, I’ve got myself a report. Both in this view and other ways that I’m able to present things, I can filter as an example if I want to just see certain employees I can select just the one I’m interested in. I can do that with other bits of data as well. Another thing I can do that I think is kind of neat, I can actually do [tiling 00:51:23] and as an example, I can tile by territory name. Just drag that up there. Now you can see it lets me interactively go through the report and see different territories or different regions as I go and if I don’t want to see that I can just simply pack that out.
Another thing that might be compelling to know, sure I like the report but maybe I’m interested in doing this as a chart so I can go up here as an example and I can do this pie chart and I can see the breakdown of all my individuals or if I’d rather have my pie chart by territory I can go clear my names … That wasn’t exactly what I was looking for so let’s try that again. Sorry about that, I got a little click happy and it got away from me and you can see I did a map there, sorry about that. There we go, that’s what I was trying to get in the first place.
You see the idea there and then I can build multiples of these, I just got one here, if I wanted to have more and more and more I can simply click on insert and keep adding those. For this demo though, you get the idea so let me go ahead and save that and I’ll call this one new demo. Now that we learned about Power View, there is one other visualization tool we have available to us and that’s called Power Map. Power Map is going to be suitable when you’re dealing with data that’s geo-spacial data. It has the geographic element to it. Not only that you’re going to see, depending on the kind of reporting you’re doing, it’s also helpful when you’ve got data that has both a geography aspect and a time aspect. That’s really what Power Map is about, it’s about spotting trends over time or spotting patterns you didn’t know were there.
I’ll give you an example of this. I read this where a group at Microsoft was trying to analyze energy use and what they found is there is one building that consistently used more electricity for heating and cool than other buildings and what they eventually found out is unbeknownst to them in that particular building, not only were they heating and cooling the building, they were actually heating and cooling the parking garage as well, it was a closed garage so needless to say, they were able to put an end to that and save a good deal of money so we’re not trying to figure out that this data point is of an exact value, we’re just trying to look in an animated view of the data over time in order to spot some trends. Let me show you what that might look like.
For the other demonstrations that I’ve done here I’ve created the data from scratch just because I think it’s a little more interesting but for this one I do have a [pre-baked 00:54:32] spreadsheet that’s got some data in it that I want to take advantage of. In this example this is something I found by doing a little searching. It’s basically some usage data from a utility in Dallas, Texas and there’s a lot of interesting things in here but there’s two things I care about. I want to look at the period star, that’s basically the billing cycle, and then I want to look at the kilowatt hours that are used.
Very important and I learned this one the hard way, if you’re going to do data over time, you better make sure the data is sorted by time. Otherwise you’re going to get some very bizarre results that are going to be hard to understand. Another thing to encourage you about here, if you’re doing data over time it has to be a date. Even if it’s yearly data and it just says 2010, 2011, 2012, if you don’t convert that to a date the Power Map engine is not going to recognize it as a time value and it’s not going to allow you to work with it. Let me go in and see if I can make something of this. Once again we’re going to go to the insert tab and we’re going to launch Power Map and once Power Map launches and sometimes it can take a second for it to pull up, we’ll go in and we’ll begin describing the data in such a way that we can see it.
That is behaving just a little bit strangely but I think we can make it work. The first thing I’ve got to tell it is now is some information about addresses or cities or states or something so it can begin to get the X and Y that it needs of the location. I certainly could go by address but I got something a lot more precise here. I’m going to tell it the geography is going to be driven by latitude and longitude and notice I need to explicitly go in and tell it which is which and now that it has that notice that the map immediately jumps to that part of the globe. The next thing I want to do now is describe the data I’m interested I looking at and how I want to present it. The way I want to present it in this example is with a heat map and once I’ve got that, remember there were two bits of data I cared about.
The first of those was kilo watt hours so I’ll input that in and the other one I cared about is time aspect and you’ll remember the way I was going to get time aspect, was with period star so I’ll take that and drag it right into my time element and then once I’ve done that I’m going to dismiss that, I’m going to dismiss that. I’m going to zoom way in on the map and let’s rotate it out just a little bit and move it in a bit more. Then we’ll see what we can discover. One other little [gotcha 00:57:41] to be aware of here. Notice when you put a numerical value in here for whatever reasons, it decides to sum that automatically so if you were to show the animation now it’s going to show the summing of the kilo watt hours over time. That’s not what I’m trying to show here. I’ll say no aggregation and once I’ve done that, this is what happens if I run this animation.
As time passes I can see there are seasons of the year where more and less kilowatt hours is being used. What this map tells me is the rise and the fall of that is consistent across all areas. If I saw that there were certain areas on my map where it wasn’t following the trend, I can drill down on those and learn more about those areas. I hope what you see there is you got some very powerful tools there for doing a fly-over and discovering information that you might not otherwise be able to easily find. Even though I did demonstrate it, it’s worth noting you can actually capture those as videos and publish it. It’s an easy interesting way for you to be able to share data.
We’re in the home stretch here, I’m going to run just a little bit past the top of the hour but let’s go in and show you the rest of this quickly. As I leave this topic, just as we have some power tools on the Power Pivot side that worked for SharePoint and would let you do server side processing, Power View, also has this capability. This would be more appropriate if you were in an [inaudible 00:59:14] as opposed to something you would use in Microsoft 365. Let’s recap something I’ve said in the beginning of this presentation. If you’re doing self-service BI, most of the work happens in Excel and that’s really what you and I have experienced to this point. Now it’s only here at the end that we begin to talk about Power BI and what it’s for. More than anything, Power BI is designed to give you an effective powerful way to share the things we’ve been working on up to this point.
We do the construction in Excel and we’re going to roll that out to Power BI so people around our company can take advantage of it. Let’s look at a little bit more detail here. What is Power BI? It’s a special kind of SharePoint site that has unique capabilities for sharing, viewing, and even interacting with business intelligence data. It also has the concept of shared data sources. This was something I promised you early. If you have technical people or people who are business experts that can build sources of data in the form of queries, then we can take these queries and share them up with Power BI and other people in the company can consume those. I’m going to show you how that works in just a moment.
Another thing we have here is we have the ability to search that data. Think about a few moments ago when I did a search about population data and I’ve got all this stuff from the internet. If I’m enabled and I’m using Power BI, the search results will not only bring back things from the internet, they’ll also bring back things from my Power BI site and they’ll be noted in such a way that I’ll know that and I might even be able to rely on those a bit more. Another capability to be aware of in Power BI is data refresh. When I’ve been opening these spreadsheets, they’ve been pulling data in immediately and automatically refreshing but if you’re putting this in a more centralized place, it may be that IT would want to schedule those data refreshers so we can view that in a little more efficient fashion.
This is the thing I mentioned in the beginning that I’ll mention real quickly again. If you’re an IT person and you’re supporting people that are using Power BI there’s a good chance once they take these workbooks and they upload them to Power BI, that you’re going to have to set up one or more data management gateways in order to describe the path to that data and how you want it refreshed. In my example, mine are going to work fine because these are all publicly available sources of data that are on the internet. If they’re private or they’re required credentials or they’re inside of your company, that’s where the data management gateway is going to come in.
Another thing we get out of Power BI, we get another query engine on top of all of these models that we’ve built. What’s so nice about this is someone that’s in an executive role that wants to ask plain English questions about the data, they can do so without having to write in some obscure query language. Not only that, when we upload our models to Power BI we can actually flag them so they’re indexed accordingly and available to be used with this engine and then Power BI has a good story on mobile or at least a good beginning story.
What you see right now is that the reports are able to render an HTML file. They don’t do that by default but they can. That’s going to get you a good start on most mobile platforms. Not only that, there is a path for creating [native 01:02:52] mobile apps. Windows mobile is already supported, IOS is supposed to get its support in 2015. Given that more and more we have executives that are sitting in an airport lounge and they’ve got some kind of tablet on their lap, this is going to be really important because Power BI really targets them more than anyone else at their user-base.
Let me show you a little sample Power BI site that I’ve already got hooked up. In my case I’ll just tell you, I’m using the free [inaudible 01:03:21] copy that I got from Microsoft, it’s like a 60 [inaudible 01:03:27]. Obviously if you’re going to use this in the real world, you’ll have to make it a part of your office 365 license. At the risk of oversimplifying things, it’s pretty easy to figure out whether I’ve got Power BI or not. I see a Power BI menu there and I see one there. I obviously have Power BI. Conversely, when I first started reading about this I was always thinking I’ve got to go set up a site, I’ve got to use the right site template. That’s not really what’s going on here. It’s more like Microsoft is provisioning this site on your behalf and just integrating it into your [team 01:04:00] site.
If I [fill 01:04:03] over to the left and I click on Power BI what I’ll see is a site and notice the URL changes significantly so what’s going on here is I’m going to PowerBI.com and it’s hosting by data and exposing that out to authorized users and you can see there are some pre-built samples that came with the site. What I’d like to do is add a sample of my own so I’m going to click on add and choose upload a file and what I’ll do is go out and take the view demo I built just a moment ago and I’ll go ahead and I’ll plug that. Then it’ll take it a moment to process it. Notice when it’s uploading there’s some things I can tell it. I can add it to my favorites if I like. I can also make it a featured report which would put it at the top of the screen here.
Also if I wanted to be available for natural languages queries I can choose add to Q and A and that will cause it to get properly indexed. From the user’s perspective, I don’t want to be demeaning when I say this, it’s really the truth. They’re looking for pretty pictures that have an interesting title and once they find something compelling they’re prone to clicking on it and maybe discovering more about what’s going on there. In my case you can see this is actually going under render in Excel and while it’s loading you’ll also note that at the bottom right there’s a link for HTML 5 so if I want to view it in that format I can. One question I get asked a lot so let me go ahead and throw this out there to you. These ones that you see that have the beautiful pictures on the front, literally the way they’re doing that is they’re taking the first page of the workbook and they’re putting a picture on it and some [wordage 01:05:50].
In most of these if you were to drill down into them you would switch to other tabs to actually see the data. Two quick things I need to show you and then we’re going to wrap it up for the day. Number one, if I want to be able to ask some questions about this, I click on this ask link and it lets me do some natural language queries. Not only that, it’s got some samples pre-built so as an example, I care about metal count I can see … There’s a query that will give me some information and then I can go to different breakdowns. What you’re seeing here is a great way for a technical or at least a business knowledgeable audience to set up data that less sophisticated users could easier drill into.
When I say less sophisticated I don’t mean they’re dumb, I just mean it’s not their business to know this data but the whole idea with the business intelligence is we can take that data and present it to an audience in such a way that they can make important decisions so there’s a lot of opportunity there. The very last thing I want to show you here in the demo and then I’ll wrap up the presentation. This little note up here, Power BI. This is the section where you could do a couple of things. You can keep up with a personal list of favorites which I think is cool. The other thing and perhaps the more important one. This gives us some insight into the sharing of these connections. Let me put in my password there.
You’ll notice there are actually some connections that are already here so let me show you how those might get here and how they might get used. I’m going to go back to Excel one more time and I’m going to re-open the first demo I wrote for you. Over on the Power Query tab there’s actually a sign in button. If I sign in and I have to provide credentials again, you can see once I’m signed in there is now actually the ability to do two things. Number one, if I have built a query here that I really like, I can right click and choose share and it will actually push that up to Power BI. Once I’ve done that it’s going to be visible to other users. How’s it going to be visible to other users? Check this out. If I go in and create a new worksheet and pretend I’m someone else on the team, I go to Power Query, online search, and I search for world cup.
Notice what I see. Not only do I see the public ones I saw before but these organizational ones are actually being pulled from my Power BI site, that’s pretty cool. Not only that I can just simply go in here in the Power Query tab and click on shared and it’ll show me all of the shared ones that are available to me so that’s how you begin to get higher and higher quality sources of data that can be consumed by a lot of different people inside of your company. Let’s recap what we’ve covered here. The first step is locating and scrubbing data. Power Query is all about that, it will let you shape it, it will let you transform it, change the data type, everything you need to do.
The next thing we’re headed toward is a data model and in most cases what we want to do is build a data model that will let us explore the data via a pivot table or a pivot chart. That’s what Power Pivot is all about. Remember that if Power Pivot doesn’t bring anything to the table that you truly need you can also use Power Query to create a data model and once you created your data model you want to be able to report on it in some way. That’s what Power View and optionally Power Map are about. Then once you have a report that is useful and ready to be consumed by a broader audience, that’s what Power BI is about. Power BI is a kind of site and a set of tools that are available to you in office 365 so you can share this data with an audience.
One last thing I want to point out to you and this is really important. I think Power BI is cool and I think it has some amazing aspects. You do want to be careful when it comes to sensitive data. Once the data has been uploaded as a part of your spreadsheet, it’s not necessarily going to be able to project out the credentials of the current user and security trim the data like it might with some other kinds of data like databases or SharePoint so just be aware there. In fact I think you may have seen in one of demos when I said it gave me a caution about personal data. That’s the one case where when it comes to this, you definitely want to test it out and want to make sure you’re not accidentally exposing data that you don’t intend to.
Otherwise there’s a lot to offer here and a lot to gain by using these tools. That brings us to the end of the session. I want to thank you for giving me your time this afternoon, I hope you found it useful. Remember that you can find more information about the company at ThreeWill.com. You can also see our videos, I’ll be posting this one up, probably in the next couple of days. Vimeo.com/threewill. Sometimes as I’m presenting these, people will have popped a question or two into the question window. Looks like there was only one there and I already answered that one. For those of you that have got to hit the road and begin your commute, you can feel free to do that and for the others, I’ll stay on the line for just a moment in case there are other questions but again thank you for your time and I hope you enjoy the rest of your day.