Hello everybody, and welcome to Creating Better Spreadsheets with Microsoft Excel. Just one note about using ReadyTalk. If you have any questions today, you can go ahead and chat those into the chat pane. If you lose your Internet connection at any time, you can reconnect using the link that was emailed to you, so just the way you did right now. And I did put a number in there into the chat pane if you need to call in. It should be playing through your mic and speakers automatically. And just as a reminder, we are going to be recording today's session, and the archive of the recording will be up via a Creative Commons license later, probably next week. But we will be sending out the recording to you later today along with the presentation and materials and links. And if you do want to go ahead and talk about this via Twitter, you can do so on the Twitter hashtag. So again, welcome to Creating Better Spreadsheets with Microsoft Excel. I'm Kyla Hunt. I'm going to be your facilitator today. I'm the Webinar Program Manager with TechSoup Global. And with us today is Mark Liu also from TechSoup who is going to be talking with us a little bit about keyboard shortcuts and pivot tables. And we also from Microsoft have Kate Lieberstein who is going to be talking about the organization Feet First and a little bit about how she used Excel to help them with their strategic score card. So again, first up we are going to have keyboard shortcuts from Mark Liu. And so in just one second I'm going to go ahead and give it over to him to get started. Again, if you do have any questions, go ahead, type those into the chat pane, and we will go ahead and respond. And for some reason we do not get to your question, never fear. We will be following up with any unanswered questions today. And in your confirmation email or reminder email, we did send out the link for the captioning of this webinar. I will go ahead and put that link into the chat pane if for some reason you do not have access to that email. Okay, thanks. And at this point I'm going to give it over to Mark. Hello, can you hear me? Okay, so hi everybody. Thank you Kyla. I'm Mark Liu. I'm with TechSoup and I will be talking about keyboard shortcuts and pivot tables. And let's see. So I'll talk about what are the advantages and disadvantages of keyboard shortcuts and how to learn about them. And when I do pivot tables I will talk about when to use them and then do a demo of the usage of pivot tables. Now Kyla has inserted a survey in here and I just wanted to ask people how often do you use keyboard shortcuts in Microsoft Excel? Frequently, occasionally, or never? And so I see we are getting responses. They are up to a hundred already. How do we do this Kyla? Kyla, do you want to meet yourself really quick Mark? All right, so it does look like we are getting a lot of responses. I'm going to go ahead and close the poll in 5, 4, 3, 2, 1.  So that's really good for us to know. And 22% of you use it frequently. So that's really great I think for us to know for Mark going into his keyboard shortcuts section. So thanks guys. Okay, so I see a lot of you use them already. So maybe you are here to hear about pivot tables because there is pretty straightforward with keyboard shortcuts. The idea is that instead of using the mouse and menus, like mouse in over to the edit menu and then clicking on edit and then mouse down to copy and clicking on copy, then you just use a shortcut like you hold down the control key and press C. That's the copy shortcut. Or alternatively if you want to open a file you go to the file menu and then click on that and then go to open and click on that. The keyboard shortcut is Alt-T, hold that down and then the letter F and then hold the Alt-T and then the letter O. So here the next page are some examples of very commonly used shortcuts. So the most common editing commands are copy, paste, and cut. So those are set up as Control-X, Control-C, and Control-V. Another one that is very useful in Excel is Select All, which is Control-A. That will select every cell in your sheet. Another very useful one is Undo the last operation that you did, the last editing operation which is Control-Z. And then some other basic ones are Open File, Save File, and Close File. And those are Alt-F, Alt-O, not zero, letter O, Alt-F, Alt-S, and Alt-F, Alt-C. And you can see there's a pattern. They try to use letters that make sense. So Alt-F is for File and then O, S, and C are for Open, Save, and Close. It doesn't always follow that convention because I think there's limits to the alphabet but it's pretty common to have hints like that to help you remember. So what are the advantages of using keyboard shortcuts? I use Excel very heavily myself. I spend probably a couple hours a day in Excel. And I find that using keyboard shortcuts for me is very valuable because it's easily twice as fast as using the mouse. And why is that? It's because rather than using, moving the mouse and pressing several keys like move the mouse press, move the mouse press, you just click one or two keys. And you also don't have to move your hand back and forth. I'm a touch type. This is probably many of you are. And I like to keep my hand on the keyboard so I don't move the home placement of my fingers. So if I'm moving my hand back and forth from one to the other, then I can lose it. And it just simply takes time to move my hand to the mouse and back. It takes a whole second. But if I'm spending hours in Excel, those seconds add up. And that's why I think it's twice as fast. And also, it just simply takes less precision. If your hand is already in the touch typing position, you can find the keys very reliably without even thinking about it. You're sort of programmed into your muscles by them.  You actually have to have some eye-hand coordination. And for some people like me, that's a little bit of demanding. So I'd rather not have to do that. So that's the basic idea. What are the disadvantages? Well, the main disadvantage why a lot of people don't use keyboard shortcuts is that you simply need to learn and remember the letter combinations. And very often people just don't want to bother. They've already got too much stuff in their brains and this doesn't seem worth it. But the good thing about it is it's not all or nothing. You don't have to always use keyboard shortcuts. You can use them when you feel like it, and you can not use them when you don't feel like it. So if you're just casually getting into Excel for a few minutes, you don't have to bother. But if you're going to spend an hour in Excel, the next hour in Excel, it might be worth it to refresh your memory and start using the keyboard shortcuts during that session because you could be spending a lot of time. And you don't have to worry about whether you'll remember tomorrow. All you have to do is learn it for today. So how do we learn about them? I'm going to show you a spreadsheet. And before I open it up, this is an example spreadsheet. I just happened to be working with this data myself, so I used it. It's the IRS Business Master File of Nonprofit Organizations. So this is publicly available data. As nonprofits, all of our organizations, this information that the IRS makes available to anybody. And I, in fact, went to this URL that I've got finished here and downloaded the data myself as if I was somebody in the public. I didn't need to log in or anything like that. There's 1.5 over 1.5 million nonprofit organizations. I just pulled a random sample of a thousand records. It's not even random. Like I grabbed the first thousand rows or something like that. And I'll show you how you get to see key tips. So let's go to share it. Oh, it works. Okay, so this is my spreadsheet. This is my example. So like here's the names of some organizations. Here is their employer identifier number. It's a unique number for every organization, every business, or nonprofit has a number like that. And if I scroll down, and I'm using keyboard shortcuts, that's why you don't see the mouse moving. I have a thousand, I'm in row 1001, so there's a thousand records in here. And this file has a whole bunch of different fields describing, let's see, it goes all the way over to column AF, so there's about 30 columns of data. And it has assets and income. It has a name, doing business as, address, city, state, zip code. Column I is the 501C number, so the subcategorization. So these are 3s which is public charities. And then down here I've got a 501C4, and a 501C5. So there's different kinds of nonprofits and not all charities. So this is an example of just a spreadsheet. So key tips tell you what the keyboard shortcuts are. So the way to see the key tips is to press down the Alt key, and you can see up at the top there are these little icons, squares with letters inside. So F is F, home is H, insert is N, and there's an example where it's not I. Maybe you probably had I reserved for something else, page layout as T. So if I hit F now, I'm holding the Alt key. It goes to the file command, instead of commands. And here again I've got key tips. S is for save, save as is A, open is O, and C is closed. And so I've been holding the Alt key this entire time. Now if I hit A, it will bring up the save as dialog. Not that I really want to save it, I just wanted to show you. So that's key tips. So you can learn your keyboard shortcuts just by holding down the Alt key, and it tells you, hey, get to them. So you can do Alt N for insert. And now, for example, if I want to do a line graph, it's going to be another N. If I want to do a column graph, it's going to be a C. If I want to insert a picture, it's a T. So you can learn what they are just by doing this and paying attention to those little key tips, and then you can reuse it. So that's how you see the key tips. I've got to double-click here. I mentioned earlier that there are some shortcuts which are used to control key. Those are faster because you only need one letter usually. Those are still supported by Excel. These have been around in Excel for a long time, but they are not visible to the key tips. The key tips only show you the Alt keyboard shortcuts. So you can get this information from the Golden Excel Help. To get to Excel Help, you can use the F1 function key, or there's a help icon. It's a little circle with a question mark inside. Search for keyboard shortcuts, and there's an article called Keyboard Shortcuts in Excel. And we're planning to make a URL for this article available to you. And it's like a five-page article that tells you all the different shortcuts and gives you a lot more information. So this is just an introduction. I wanted to show you what they are and how they can speed things up. But I'm not going to try and teach you the shortcuts because there are literally hundreds of them. So now I'm going to move on to pivot tables. And so we have another survey. And the question is, how often do you use pivot tables in Microsoft Excel? Frequently, occasionally, or never? And somehow I guess that the answers will be different this time than they were for keyboard shortcuts because pivot tables are a relatively advanced function. And we're waiting to see people's responses that's still coming in. Do you want to do this, Kyla? Kyla Sure. All right, we're going to go ahead and close it in 5, 4, 3, 2, 1. And so it looks like 84% of you have never used pivot tables. And so that's really great for us to know. 14% of you occasionally use it. And only 2% of you, about 2% of you frequently use it. So that's really great for us to know that you are mostly all beginners at this. All right, let's go ahead and check. Kyla Okay, so we had 4 people who said they frequently use them. If I had voted myself, it would be 5 because I use them constantly. I use them all the time. So what are they? What you need is a tabular array of data. I mean, you could use it with 2 rows of data, but I don't think people would find it very helpful in that case. So you would be talking about hundreds of rows of data up to a million rows because Excel will allow a million rows in an Excel spreadsheet.  Those are the menu options. And when do you use them? So that's why I brought up this example of the IRS business master file. You use it when you have lots of data. You have data fields to summarize, things that you might want to sum up or average. So an example of a data field in an IRS is simply like the one in the field of income, another one was assets. You have categorization rows for columns and rows and filters. So these are things like the state or the 501C subgroup. They're good for presentation because in that example of the IRS business master file, you just have this massive spreadsheet with many, many, many rows of data. And it's hard to see anything in it because there's just so much data. A tabular view, which is like a two-way table, is very intuitive. People are very used to looking at them. And that's what a pivot table produces for you. And it's very well integrated with Excel graphs. And it's really good for exploration. So like in that example, you might be wondering, are the nonprofits in Maine different from the modern nonprofits in Florida? Or which subsection like IRS 501C3 versus 4 has the highest income? These are questions you might be wondering about and you can use the pivot table to answer them. So let's go into it. So the type of data you have. So typically in a pivot table, each row represents a different instance of the data event. So the kind of data I work with at TechSoup Global is I looked at we have a big product donation program and I'll get tables where each row in the data represents one donation. So I will say who is donated to, what the product was, how many copies of the product, what was the retail value of the product, the date that the donation was given. So that's what I use. I know the kind of things that we do at TechSoup is not that typical of nonprofits, but the kind of things that you might see yourselves are things like you might have one row representing a client who is using your services and his use of the services, his or her. Or it might be one donation to your organization. Or one row might be one volunteer performing an activity for your organization. Or it might be one client participating in an activity being offered by your organization. So that's the kind of information you have in each row. Then the columns, you have different fields to describe the event. So typically you'll have something like a date and a time. You have identification information is who is the client or donor or volunteer or participant, maybe address information, telephone number, email address, contact information, things describing a participant. You might have information about the type of service. In my example it was what is the product? Probably what is the product name and what is the catalog number we have for it? But it might be different services or activities that your organization is involved in. And then there will be quantities. For me, number of products requested. For you it might be the number of services used or the size of the donation or the amount of time the volunteer gave, or the number of activities the volunteer was involved in. And each column must have a heading. Okay, so what I'm going to do is, and so the result is a tabular summary of the data. The text with global, for me it's like donations by country by month. Or you might be interested if your organization has multiple locations, how many services were offered in each location, or the number of donations by different donation type by week, or the number of volunteers from an affiliate by month. These are the kinds of reports you might get with a pivot table. So I'm going to go into Excel and here's my data. So the first thing I'm going to do is select all my data. So I'll do Ctrl A. Now everything has been selected. And I'll go Alt A for data. Where was it? I don't usually use this one. V I think. That's the wrong one. Alright, I'll start over. I'll do it the way I usually do it. D P. No, I doubled it. D P. Okay, so I've had my data selected. And here it's set me up for a pivot table. The pivot table is going to appear on the left. And over on the right is sort of a wizard type of a price of things. And so what I'm going to do is just take this ill name and drop it down into values. And it says count of name. So this is a piece of data I'm going to get in this pivot table. So I have a thousand different organizations. And then maybe I'll go to Income, just to try another thing. And you can see I can put in two different things. So sum of income. So the total income of these thousand organizations was, these are dollars. So this is like $3,526. So they have an average income of $3.5. So this is a pretty low income group of organizations. Let's see what it says for assets. So I had assets of $3,000. I can also change these here. If I click on this little down arrow, then I got this menu and it says value field settings. I click on that. And you can see these are different kinds of computations that the pivot table can do for you. So I've already had some. I've already had count. So I can show average. Let's click on okay. So now I've got an average assets of $3. This is awesome money. So this is $3.82. So you can see you can put different values into your pivot table. And then it gives you an ability to do this by rows and columns. So let's say, let's pull up state and put that into rows. And we can see in my data, I have the states of California, Colorado, District of Columbia, Florida, and so on. And it just so happens out of my 1,000 organizations, this group was very heavily oriented towards the state of Maine. That was just the random collection I got. That doesn't mean that Maine is the nonprofit capital of the United States. It's just the collection I pulled up. And let's go to that subgroup number. So let's do, let's put that here. So I see I have names. I have different subgroups, 1, 2, 3, 4, 5, 6, up through 92. Right here, this column is subgroup 3. So these are the charities. And out of the 1,589 were charities. Let's see. I don't even know what subgroup 7 is, but there were 90 of those. So that was relatively common. If I look at this cell right there, there are 549 501c3s in the state of Maine out of the 1,000. It's almost a little over half of all 1,000 were there. And over here in this set of columns and continuing, these are the income for the nonprofits in those states and subgroups. And over here we have the average assets. Okay, and so the number formatting is not very good, but I could fix that. That looks a little bit more reasonable. But this is a very complicated sort of messy pivot table. I was just trying to show you some features. This is not a likely combination. Let me just get rid of some of these things. I'll get rid of income, and I'll get rid of assets. So that's a more typical kind of view that you would have. So you can see you can set things in different places. I can move, I can switch. I can put the subgroups in the rows and the states in the columns, and it took me one second to do that. So I can adjust the display to be something that works for me that's more intuitive. I'm going to go back now. I can add more rows. So for example, I can put city under state, and I can see for California I actually have 1, 2, 3, 4, 5, 6 different cities, Los Alamitos, Los Angeles, Rimboland, San Diego, San Mateo, and Soledad. So there are only 7 organizations in this data set of 1,000 from California, and they were spread over 6 different cities. Now of course if I go down to Maine, I'm going to see a lot of cities because I had a lot of data from Maine. Those are all Maine cities. So you can have any number of row labels down here in the lower right. I can have state, state city. I can add zip code that's getting carried away. Another thing you can use these for is you can use them for filters. So for example, I can put the state as a report filter, and up here in the upper left is the state all, and that's a drop-down menu. And if I pull that menu down, I can see the list of states. And suppose I wanted my table to only show Maine. Okay, I click on ME for Maine. And now all I've got is the data for Maine. And whoa, there's a lot of cities for Maine. And there were 934 of the 1,000 rows were in the state of Maine, and these are all the different cities. So I could change this to show only California, and I should see only 6 cities. So you have tremendous flexibility in your display and your pivot tables. And this allows you to pull up different kinds of data and organize it in different ways. And this is a small data set. It's only 1,000 rows by 30 columns. So it's really fast. You can see it's instantaneous.  But that's one of the features. It's so easy to manipulate, and to organize the data in ways that are interesting to you. Another thing it does is it's very easy to, it's very well integrated with graphs. So I could like, select this data, and then I can go Alt, N for Insert, C for Column, and get a column graph, and there. I have my 6 rows, and it's showing, let's change this to a line graph because it's a little confusing. It pulled up 2 columns of data. No, no, no, that's not what I wanted to do. Change chart tape. Let's make this be a line graph. Oh, there's a lot of blanks. So type 3 and type 5. Now I'll show you something interesting. Let's go into the data. Let's go to the top. Let's change second batches of church of Palmer, New York to Palmer, California. And I still have my 7. Now I've just changed the data underneath this pivot table so I need to refresh the data. So I do Alt, Alt for Data, Alt for Refresh All, A for Refresh All, and suddenly I see Palmer, California. I've just added it to my data, and I now have 8 nonprofits in the state of California. And Palmer appears in the graph. I didn't have a group number, I guess. So you can see how everything is tied together and it's very highly integrated. So I think that's it. That was everything I wanted to show you about pivot tables. So it's very intuitive. If you can get the pivot table to come up and show on the screen, you can just poke around and you can figure out how to make it work. So are we going to open up this floor for questions? Yeah, we do have a couple of questions that have come in, or quite a few questions that have come in. I'll go ahead and ask a couple now, and then we'll go ahead and let Kate do her thing and then see what questions we can get to at the end as well. One thing that we were asked was if you could show how to insert a pivot table without the keyboard shortcut, so using a different route than using an actual keyboard shortcut to insert the pivot table. That's a good question because I'm so used to using keyboard shortcuts, I can't remember the routes. That's what I was trying to show you earlier. Let's select the data again. It was data, oh it's needed to insert, insert to the table. Yeah, so there's insert to the table. There you go. That's what it is. Okay, I knew it was someplace. I've been using shortcuts for so long that I've never used it, so I had forgotten what it was. So I learned all this stuff in Office 2003, and the user interface has changed. So I'm still using 2003 commands very often instead of Office 2010. Yet another question? We did get a couple of questions. Susan was asking, how are you putting the fields in each box below? Were you doing that as a drag and drop? Yeah, I was dragging. There's various ways you can do it, but I was just like dragging. So I grab it, click down, and drag it over. But you could just double click I think, or click, and it automatically puts it someplace, or you can drag it from this corner. I'm dragging it now to another corner. So there's various ways you can do it, very typical kind of user interface. Okay, great. And then we had another question that was just about Excel in general. And if we have time, you can go ahead and try to show this. And if not, I can go ahead and try to find an answer to send this person later. But Susan was wondering how to copy spreadsheets and preserve the formatting or apply formatting from one sheet or call them to another. I'm fiddling with my mute button. So suppose I, let's go over here, assets. I think on any assets that are worth seeing. Let's turn this into a dollar. Okay, so I've got this formatted. These are as money. I could then copy this. I'm using a shortcut, I'm copying. I could come over here. Now I want to set this one up to the same format. And let's see, home, coffee, taste, is that the taste options formatting? So here like this paste formatting, okay? Let's see what happens. You see, it tasted, it didn't change the values. This one stayed a one, but it gave it the formatting that I pasted in. So under paste, there are all these different other paste options, formatting, a link, pictures, so there's special ways of pasting. You can just paste the values. So I actually use that quite a bit. I use taste value sometimes. I use taste formatting sometimes. There's both useful commands. Okay, great. So I think that the rest of the questions, the rest of the questions we'll go ahead and get to after Kate's section, but I do want to make sure that Kate has enough time for her. So I'm going to go ahead and jump to her first slide. And if the operator could go ahead and unmute Kate at this point, that would be great. Please go ahead. Thank you. Hello everyone. My name is Kate Libersky. I'm a finance manager at Microsoft. And I also sit on the board of directors of Seed First, which is a Seattle-based non-thoughts organization. And they were the beneficiary of the strategic support card, but which we will talk a little bit more in-depth. Let me move to the next slide. So I will give you a little bit of an overview of what Seed First does, which will help understand the support card metrics a little bit better. So Seed First is a pedestrian advocacy group based in Seattle. And we focus on issues such as transportation, public health, neighborhood design, as well as environment and social justice. And there are a number of projects that Seed First gets involved in, among which are something called Safe Routes to Schools, through which we help local middle schools and local schools help design the premises around the school in a way that keeps the kids safe and allows them to walk around the area without parents having to worry as much. We also have something called community maps, walking maps for various neighborhoods that allow folks to explore different neighborhoods on foot, as well as use it for transportation. We also get involved in a variety of pedestrian advocacy issues that take in place where we either provide our support or offer dissenting opinions on various policy decisions. But that's kind of an overarching view. So just to give a general sense of the mission, the organization is to promote walkable communities because we believe that walking every day is good for your health, transportation, environment, community, and pleasure. We're a very small organization. We currently have 4 full staff members and a few part-time staff members and 10 board members. So my involvement with Seed First began actually as part of a consulting project. I first learned of Seed First in 2009 and in December of that year Seed First undertook a two-day strategic planning forum with the board and staff to help them identify their new mission, vision, values, and goals. And once they have done that, they have invited Taproot Foundation, which is the consultancy nonprofit, to help them create a strategic scorecard which would then provide a consistent and effective way to communicate with the board on a quarterly basis. It would also help facilitate annual tune-ups of the strategic plan to make sure that all of the different aspects that were identified during the planning forum are still relevant, depending on developments, as well as help make sure that the plan stays alive, that all the things that have been identified that we keep a focus on those areas to make sure that all the good things that we as an organization want to accomplish we keep our eye on. So the next slide gives you the six goals that were identified during the strategic planning process that organizations took on. And as we, when I was part of the consulting team, had to kind of take in and figure out how do we use them to create a scorecard. So just high-level, among these goals were identifying and empowering community leaders to take action that would help promote walkable communities, informant and motivating policy makers to prioritize funding that would support walkability efforts. Energizing, informing community about walkability issues, et cetera, et cetera. So kind of taking these high-level goals and then figuring out how do you break them down into various objectives, how do you measure them, what kind of targets you set, and how you then take that and come up with initiatives to stay on top of the plan. So I'll just look at the next slide here in a second. So what this scorecard really allows an organization to do is it provides a summary of progress toward reaching a strategic goal. And it is geared toward a review by the board on a quarterly basis. So it would allow the board to measure and monitor strategic execution across four performance perspectives that would then provide holistic and balance the other organizational strategic progress. So the four perspectives that we focused on were financial, internal processes, stakeholder, and learning and growth. This would then help organization keep the strategic plan on top of their mind, allow them to measure their progress quarter over quarter, as well as against the targets that were set. Also focus on drawout goals that are not all necessarily explicit but that the organization lives by, which is important. It also gives a common language to discuss organizational progress. And in a way, Excel plays an important role here because there are a lot of visualization features that make it much easier to understand where their organization is in terms of where to the where it wants to be. Let's see. It also gives a way to – there are a lot of different aspects that each nonprofit is involved in, but you want to focus on a few that are big picture and that helps you figure out how healthy your organization is. And then finally, it allows – it provides clarity to the organization how their efforts, how the efforts, day-to-day work that the staff does actually helps them toward the long-range plan that they set. So the next slide will give you sort of an example of what this discord card looked like. And you can see that it's broken out into the four objectives that I mentioned, so financial, stakeholder, internal process, and learning growth. Then within each of those objectives, there are a variety of measures that we wanted to drill into. So what I'll do right now is actually share my screen with you so that you can see – I can kind of walk you through the different line items. So you all should be seeing the Excel file itself that we used to create the strategic scorecard. So what the view that you currently see is the output file. And really, this is what we would then present to the board and what the Executive Director of Seed First would take over and then present the board on a quarterly basis after the consulting project goes over. So again, you can see the financial objective. Within it there are a couple of measures that we focused on. For example, increase unrestricted funding sources and achieve revenue goals, or better, track and control costs. Within each of those we then had several measures which are, for example, memberships or unrestricted grants or fee-for-service dollars. And then you can see the targets that we set, working with the organization first. So for example, Feed First helped identify what is the high target for member fees for each quarter and what is the low target. And then they would be able to pull in what the actual number was in terms of the membership dollars. So we here show two quarters for comparison purposes. And then Excel, there is a feature called Conditional Formatting which allows you to then use these color coding to see, okay, relative to the target, how are we doing, and relative to the prior quarter, how are we doing. So for example, we said, okay, if our member fees in particular quarter were above our high target, then the circle would be green. If it were below the low target, the circle would be red. And if it was in between, it would be yellow. So in this case, it would be yellow in both quarters. And then here we set up a simple formula that allows you to say, compare a quarter over quarter and say, okay, if current quarter is above the prior quarter, then show an up arrow. If it's below, then show a down pointing arrow. And if it's the same, then show an equal sign. But again, when the board looks at it, they don't need to think through all these details. They can just take a look and see, okay, so it looks like our membership fees have gone down in a particular quarter. So what kind of decision should we make based on that? And first of all, we can drill down into potential reasons. And then what are some of the initiatives that would fall out of that particular takeaway? And the same is done for the year today so that you can see not only how we're doing on a quarterly basis, but how you're doing on an annual basis, how you're progressing towards the annual goal. And so again, we chose to do this in Excel because we felt that this is a relatively simple way of doing it, and it would be easy for their organization with small staff and limited resources to maintain. And it would be easy for the board then to take a look and see where their organization is and what kind of actions need to be taken. Now as I mentioned before, this is an output page, so there's a lot of data that supports it. And so I'll just briefly walk you through how we set it up.  For example, for the financial section, a lot of the data would be capped either in QuickBooks, Accounting System, or in Salesforce, for example. So that's when staff fill this out on a quarterly basis. They can easily find where the data is housed. And then I could go to each tab here. It corresponds to a particular measure that then feeds into the scorecard. So for example, if you look at the financial tab, these are the different measures that we looked at. Membership fees, unrestricted grants, and fee-for-service. So on a monthly or quarterly basis, staff or someone in an organization would fill out this, just do a data dump from another system that an organization uses without having to do a lot of work. And then there's a formula set up to say, okay, pool, for example, here it will say look up for the X which indicates what time period we're in, and then pool the number that corresponds to that time period. So here you see 4900. So if we go back to the scorecard, you see that 4900 here. And of course, if I were to move the X to a different time period, it would adjust, which makes updating this scorecard very simple. So this is a high level of how we approach this project. And then of course each corresponding tab does a very similar thing depending on what measure we were looking at. And all of these are outlined in the output stage, which is the scorecard. And to this day the organization uses it. We've changed some of these parameters, some of the measures, because again if we see for example that quarter over quarter the circles are red, then either we are not focusing on some of the areas because something else is a higher priority and maybe it shouldn't be one of our strategic goals, or we need to invest a lot more time and resources into making sure that we are in organization putting staff time into improving how we're doing against those targets. Or another reason to be that the targets are not reasonable. For example, maybe our high target is too high, or maybe it's too low. So this is the kind of thought process that the board can go through just looking at something as simple as this that Excel allows us to. So this is a high level overview. Please let me know if there are questions that I can answer. But I think this is a good illustration of how Excel can be a really useful tool for both the big picture thinking as well as drilling into specific data sets and understanding who causes of any problem, or understanding reasons for success. Kate, I know we had at least one question that was just wanting you to go a little bit more into the process that was used to create it. And the same person was wondering if you could include a screenshot or a sample even without data for the score card categories? Sure, one second. She was wondering how you develop the four parts and the subsections under each more specifically. So in terms of figuring out the four categories, we've used certain literature that sort of helped us think through how the different measures their organization focuses on. What are the different four objectives? And really stakeholder is the outcome of the mission of their organization. So if our mission is to promote walkable communities, then stakeholder, for example, generating enthusiasm for walking would be an important one. Expanding relationships with community leaders and elected officials would be an important one because they are the folks that provide funding that can support a lot of the initiatives that we'd like to take on and see happen in our community. Expanding membership is important because the more members we have in our organization, the more support we have, the more the issues that are being talked about, the more they show up in the press. And I guess the higher the rate of change that we will see or improvements. And for another one we had here is increased diversity of members and communities. Again, it was actually interesting to discuss who the stakeholders are because being a pedestrian is kind of an interesting thing because everybody is a pedestrian. A biker is something you choose to be, but a pedestrian is something that we all are at some point in our day or in our week or month. At some point we do have to walk places. And so it actually opens up a lot of opportunities of not necessarily segmenting the markets to who the biker is only, for example, but it's really understanding what's important to different people. So when you think about parents, safe routes to school would be very important to them because they worry about the safety of their children. When you think about people who commute to work a lot, having a good transit system and having walking maps that allow them to find ways to get where they need to be without using the car is important to them. In terms of policies, again, the more support we have from the community about some of these issues, the more elected officials would pay attention to the areas of concern. So it's really taking those four objectives, financial, stakeholder, internal process, and learning growth, which we came up with from just reading and understanding how non-probabilization operates and then kind of within each working with feet first to understand what are the important measures that need to be addressed. And then drilling in a little more and saying, okay, what are the different sub-measures, if you will, that help support each of those layers? If that helps. In terms of, you mentioned something about a snapshot. What kind of snapshot today is awesome? I think she was just wondering if there was a screenshot or sample of the scorecard categories, and if you could even email that to me later because I know we're getting low on time. And I do want to make sure that we, yeah, and that way I can pull it out of the mall. But on one of the slides that's a Seedbird Strategic Scorecard, all of the different measures that we cared about at the time that we created this scorecard are listed. We had more, but those are the ones that we narrowed down and kept. Okay, great. Thanks. And then if you want to go ahead and get back to the slide deck by just pressing the green arrow at the top, it's at the very top of the screen. Okay, she got you. Yeah, here we go. And if you have any closing, yeah. And so this last slide was just an example of the model structure which I walked you through and modeled itself. So you can see the different tabs that we had which correspond to the different measures that you see on slide 31. And kind of how we organized it to feed into the output page that then is used for presentation purposes. Okay, great. Thanks. And I'm going to go ahead and take a look to see what other questions have come in. We probably won't get to all of the questions that have come in today, but never fear I will be forwarding those questions to the presenters after the fact and we will get you an answer at some point. We did have some questions that I wanted to bring up that were unrelated to either pivot tables or to the strategic scorecard that Kay just talked about. And I thought it would be good to talk a little bit about those. We did have a question about how to create specific formulas. So for example, we had a question asking, what do you do to subtract the number from the total such as you do an autosum for various fundraising items, then you have a corresponding autosum for expenses and you want to have the net amount. I'm not sure maybe if Mark wants to try to take that one. I can repeat that if you need me to. Sure. So he's asking what do you do to subtract a number from the total for a formula? And his example is if you do an autosum for various fundraising items, then you have a corresponding autosum for expenses, but then you want to have the net amount. I'm not sure I really understand the question. That's the problem. Could we possibly just get this in written form and I could communicate with them through email or something like that? I'm not quite certain. I understand what he's asking. That's completely fine. And then Paige was wondering if somebody could say something about setting up conditional formatting. Sure, I can talk about it. I'm not an expert. If you're better at it, you can take it. I can show for example how we've done it for the scorecards. Let me share this next screen again. So for example, we're here and we want to set up one of these circles here. So you would go to home, and then you would go to conditional formatting right here. And then you see there's several options. The highlight cells rule. For example, here you can choose highlight a cell in particular color, or font in particular color if that cell is above a certain number, if it's less than a certain number, if it's the same certain number. So for example, if we choose this one, and we say format this cell with say yellow and dark text. Wait one second. Let's do this again. So the home conditional formatting highlight So we say they're greater than this number with this text. So what you would then need to do is copy this format. And you can see that this number is greater than 50, 7, 18. And so it highlighted it in a format that I selected. So that's just one example of how you use it. But really you just have to go in here and explore the different options. So data bar is another really cool way where you can, for example, if you have, let's say we had membership fees from different categories of members. We wanted to see the percentage of the different categories that make up that total revenue number. If I had those listed I could then use this. You can see this very well in here, but you can kind of have a visual representation of what proportion came from each category. So it's really a visualization tool. And it's pretty simple. I would say just play around with it. This is where the icons come from. So for example, these circles that you see, this is where that comes from. I hope that's helpful. That's very helpful. Thank you, Kate. But that I do want to go ahead and close the webinar up. Again, if your question did not get answered I will be going ahead and forwarding those questions to the speakers. And I will try to somehow get the content of the answered questions to everybody so that way we can all benefit from those answers. So a little bit about who TechSoup is. We are a 501c3 nonprofit organization just like so many of you. And we do try to enhance or help your organization's mission by providing technology and technology resources that you need to do that. And if you go to the TechSoup.org website you can go ahead and go to our Learning Center or a blog to find some great written content. You can go ahead and try to find products and find product section on the right for our donation programs. And don't forget to sign up for our By the Cup and new product donation of our newsletters also on the right there. And I do want to take a moment just to really thank all of our presenters today. And I want to thank Microsoft for being so great in helping us put these webinars together. And then I also want to thank ReadyTalk who is our webinar sponsor for providing the tool that we had our webinar on today. So again, thank you everybody. And thank you Kevin and Meg who are helping us on the back end today. And I hope everybody has a wonderful day. Again, when you exit you are going to get a survey and please make sure to fill that out because that does help us in creating our future webinars. And the recording and additional materials should be going out later today. So thank you everybody.


Good morning, everyone. My name is Peter Gagan. I work for Heroku and this is my talk. It's called Upsert Use Cases. Let's talk about a new feature in the just released PostgreSQL 9.5. This particular feature has been, I would say, a long time coming. And I'm quite relieved that it was in PostgreSQL 9.5. So let's get started. What is it? What is Upsert? Upsert, incidentally, is sort of an important item to update or insert. It's a way that we casually refer to the feature. So the idea is that you, as you don't know, you insert a row or on the basis of an existing conflicting version of that row being present, you instead go to update that existing row. PostgreSQL 9.5 also added a similar insert ignore type feature that rather than updating would have the insert statement, which we'll see the syntax of in a moment, not insert anything at all on the basis of there being an existing conflicting row. And the big picture here is that you don't have to worry about concurrency. You quite simply write the Upsert statement and you leave it at that. You don't have to give too much further consideration to race conditions concurrency. As we'll see, this is important. Thank you. It alleviates the burden of writing what can be fairly subtle code. So what I specifically mean is that there are various errors that may occur in similar features in other systems without you perhaps expecting them to occur that we have voided. So sometimes I've used this more formal terminology that I myself invented as jargon, the so-called fundamental Upsert property. And these are the errors I specifically refer to, what I called unprincipled deadlocks, which means you have two sessions that are Upserting at the same time and they deadlock with each other but in a rather unconventional way. Specifically, there's no user-visible mutual dependency. You as an end user cannot very well say, oh, I know, I'll just reorder the statements within my transaction and that'll make that go away. So it wouldn't have done if you simply had to live with deadlocking of that nature even though you had no such conventional mutual dependency. You didn't have two updates that you could reorder to make them consistent in every transaction because there's two locks involved in this one statement. So that doesn't happen and it's known to happen with other systems which we will have a little bit more on shortly. Also no spurious, unique violations. The whole point of an Upsert is you insert on basis of that there being an existing row by which I specifically mean a row that has the same value in a constraint that you specify then you ought to go update or ignore whatever it is. But you certainly don't want to have a unique violation in that unique constraint or index. The whole point is that you're avoiding that. If that happens, the feature in my view is broken. So that also doesn't happen. This may seem almost obvious that this should not occur and yet, as we'll see, there are examples from other systems as these things do regularly occur in the event of concurrency. So the syntax is sort of driven by an insert. I'll show that shortly. Like I said, this is a very widely requested feature, mostly for all OTP and web app sort of use cases. Now the syntax. So what you see here is that I've extended the existing insert statement. I've added a new clause that makes all this possible. So in fact, yes, I'm afraid not there was an AV guy and actually I think he said, don't turn off the lights during the presentation. So he specifically told me not to. I think it's being recorded, that's why. Okay, so the syntax you see here. First of all, we have a fairly basic example of an absurd, a conventional example. So here there is an insert of two rows. The one that says gizmo translobal and then another that says associated computing. So this is a simple case where we will either insert one or both of those or zero or one or two of those or update them alternatively on the basis of specifically a unique violation in, this is probably the primary key. See the way it says on conflict, did. That did is the primary key. We're saying that's the constraint. We are taking this alternative update path on. The second example is he shows what I call before the ignore variant where rather than updating, we're simply doing nothing on the basis of an existing version of that row. We're leaving it as it is. The third example is a slightly more worked out, absurd example where we are inserting or updating one row and we have an additional constraint on what we update the row on. We're also signifying that in order to go ahead with that update the zip code is not equal to 21201 and if it doesn't pass the update doesn't go ahead, of course. The update will still lock the row with this variant with absurd rather which is not true of conventional updates. They won't lock rows that were not affected otherwise you'd lock almost every row in the table whereas this will reliably lock the row in the event of you not updating. I'm highlighting the excluded pseudo table here and this is a way that we can reference rows originally proposed for insertion so it will be inconvenient and possibly not even possible to repeat the values from the insert because the first one certainly is a multi-row insert. Having the excluded pseudo table allows us to, you know, it feels like a join almost on what you originally proposed for insertion so it's just a convenient way of referencing what you have there in the first place when they're repeating it and obviously because this is, as I said, a multi-row insert statement would be rather inconvenient to do it any other way. This incidentally does carry forward the effects of before insert triggers which may have contributed to the rows taking the alternative path being excluded from insertion so the update statement in Postgres in general supports an update from where you're joining a target table you mean to update against another table and you are updating one from the other so it kind of feels like the excluded table it sort of feels like that in an update except it's implicit that the excluded table is available and obviously it's a special sort of table a magic implicitly defined table this highlighted syntax that did column is the this is used to infer, as I say, or as we say now the unique index that you are interested in using to arbitrate whether or not you take this alternative update path this will figure out which of any of the available unique indexes you had in mind dealing with various subtleties makes your intent clear so specifically what it does is if you happen to get a duplicate violation and any other defined unique indexes that you did not infer here then you will in fact get a conventional duplicate violation this is a problem with still with the MySQL implementation where you just better make sure that anywhere you get a duplicate violation is where you have expected to get one if, for example, for whatever reason you end up getting a duplicate violation in some other unique index then that implementation will spuriously update possibly causing something you might describe as logical corruption so we avoid all that by offering this and requiring you to use it requiring you to be explicit about exactly what you mean to update this is much better than naming an index or a constraint directly it's more robust than that columns can be in any order this can be a composite index certainly you could have, you know, you could be inconsistent in the ordering that would just work fine there are other sorts of advanced abilities which we'll see in a little while so it's going to figure out which unique index you want to use and implicitly which ones you don't these incidentally these unique indexes that you infer will appear in explain output if you attempt to explain or if you could explain the insert or update statement then you'll see precisely what actually was inferred and other details like that so insert on conflict do nothing also supports exclusion constraints exclusion constraints are sometimes called a generalization of unique constraints that means that if a unique constraint is a constraint that enforces equality that is that there can be no two equal things in a row or set of rows at the same time exclusion constraints generalize that to other operators for example the overlaps operator so this could be useful in a domain like event management we're in this room right now we are occupying this room if we wanted to have not fixed time thoughts but time thoughts over continuous ranges arbitrary slices of time that things could happen in a room an overlaps operator would determine if something overlapped with something else but by having that represented as an exclusion constraint we can enforce much like a unique index that no one can use the room at the same time and have that work in a robust way and so this will work with the do nothing variant but you may want to spell out the name of the UD constraint yourself directly by using a syntax which I have not shown which is basically on conflict on constraint constraint name do nothing okay one question I get a lot is why did I not go about implementing merge for those of you that don't know merge is a feature in a number of other major database systems described by the SQL standard it's a DML statement which is often thought to be comparable to what I'm talking about but I would suggest that isn't really the case at all it's kind of a way of combining a bunch of insert update or even delete statements so you're sort of it's defined as joining two tables together and inserting updated or deleting one of them a target from source useful certainly in data warehousing that kind of thing where you want to reconcile two tables but importantly merge as implemented and as described by the standard there's no counter example does not provide guarantees around definitely getting an insert or an update there's plenty of evidence that people expect this and are often disappointed when in production it doesn't turn out that way so I was able to add some amount of merge like flexibility to what I came up with but it's not equivalent to merge merge has all these various problems and I think on the whole I was happy that I was able to offer something that I feel gave people what they really wanted okay this is can I get a show of hands does anyone recognize what this is anyone oh sorry visibility is not so good here what you're seeing here this will jog some memories when I describe what it is yeah that's pretty bad yes it's showing you how you ought to do in previous versions how you ought to go about correctly implementing more or less what I've described that you definitely get either an insert or an update and what you can see here is that this is like a PL, PG SQL function that involves a sub-transaction so initially you attempt an update in the event of that not affecting any row you go and insert in a sub-transaction which may itself have a dupf violation which you can catch in the sub-transaction and if that happens then you try from the start so this is pretty ugly but it does work it does offer that useful set of guarantees so this is what you would have been required to do previously and a lot of people weren't doing this anyway they were doing other things that maybe seemed like they were safe but in fact were not but this would be the safe way of doing it that is correct according to my own definition prior to 9.5 they're prone you can have problems for example with getting a unique violation in the wrong unique constraint I already talked about why I think that's important in general you could even have say a trigger defined on the table that affects some other table which may itself raise a duplicate violation so perhaps the spurious violation is the same on the same table there are a number of subtleties like this that must not be missed when using this particular approach so I mentioned just a moment ago how merge doesn't offer these guarantees I'll now show you how to make merge do just this to make it more or less offer these guarantees and this is an example from Stack Overflow this is an oracle merge statement again this is rather difficult to read here so I'll tell you what it says it's an example of someone had a merge statement and essentially said why doesn't it do what I just said it ought to do that is why do I sometimes not why do I sometimes get a spurious violation unique violation I'm not expecting that isn't that the point and someone here proposes a solution that was well received on the Stack Overflow page where they do what I showed in the last slide was Postgres they have a subtransaction they put the merge in and they have to handle all the errors the catching errors there at the bottom in that exception block so in fact all they've accomplished over and above what Postgres was doing in earlier versions was to combine an insert and an update into one statement which is really in the main not what people are interested in people are interested in simple guarantees so I guess this illustrates in my mind what the problems are with merge it hasn't really as we see here hasn't really saved this person any trouble at all so in summary on merge versus absurd absurd is there guaranteeing a certain outcome insert or update whereas merge quite a different thing it operates according to the same rules as individual insert update or delete statements just combined together in order to make absurd have these various guarantees I had to essentially create new exceptions to the rules about how MVCC a core mechanism for transaction isolation in Postgres works I had to to suit the purposes of this statement I had to create special cases there where merge does no such thing so and that's sort of what I'm getting at here when I say there's a fundamental difference because it merge doesn't make any guarantees at all it's good for bulk loading it's good for data warehousing when you're going to drive everything with a big join because maybe it's a conventional join certainly nothing is required to go through in fact maybe there isn't even a unique index defined on the table I mean after all in data warehousing that could be very common if you're doing a bulk load before you might then only create the index afterwards so quite a different thing this is a MongoDB bug I'm not in the business of bashing MongoDB already in my style and in any case I don't know very much about it I don't know how qualified to do that but I'm pointing at that and this bug is current today I did check it again so this bug still exists and has been acknowledged by MongoDB as being a valid bug concerned a regression in the latest version of MongoDB due to their presumably due to their wired tiger storage engine having more granular locking than before so what we see here is all of a sudden getting duplicates violations where they're supposed to not have that happen that's the point that you get an update not a dupe violation so this is a regression that they have which they're working on MySQL I think similarly had problems of this nature in the past I believe that those are sorted out now but I guess when I was working on the feature I looked into problems people were having with other systems with you to avoiding those so it's rather subtle to get these details right all at once Yes No it's not, no Merge is in the SQL standard this is it tends to be the case that when people when vendors, when implementers implement something like this they'll use their own flavor of your own syntax so MySQL has its own one we have our own one now Terrig data has its own one there's a number of other I think is it 5A so there's a tendency perhaps because of these sort of subtleties for them to do it in each case their own way so yet another non-standard syntax I don't see that the SQL standards committee would I mean they don't even the SQL standard doesn't even acknowledge indexes it doesn't mention indexes because they would consider that to be an implementation detail and so if they don't mention indexes it's not they don't concern themselves with not unreasonably they don't concern themselves with that stuff so it's the kind of thing that I think they would never do basically I know more general summary of Upsert it offers what I would call a very flexible syntax which I'm very happy with we got all the edge cases we fixed them so some of the things I've talked about here I've occasionally maybe sounded a bit complicated but ultimately it's all about guarantees that you as an app developer can leverage making it easy to use correctly hard to use incorrectly we may still do merge in the future I'm not opposed to that it does have its uses as I've acknowledged and yeah I think that's about all I have to say on merge in general or on Upsert in general okay so this point I will take any questions on Upsert in general if anyone has them does anyone have any questions about anything I've said so far so I'll now discuss new use cases some less obvious uses of the feature in some cases using things that are unique to Postgres which I think are notable for data integration what is data integration for our purposes it's sort of a requirement to integrate data from diverse sources these are often autonomous or distributed sources which is to say that we don't control how they might be formatted it might be that a vendor does that and it's not practical to get them to do it the way we would prefer so maybe there's inconsistencies across these various sources that kind of thing this is very common this is just the way of the world and how those are represented might be maybe for example it could be that they're using different actual character encoding but it might also be something like maybe they're using metric units because it's a Canadian or European supplier that kind of thing so these are all inconsistencies we must account for maybe this is something like a common separated values file or something terrible like that so these are sort of practical real world scenarios not especially glamorous but I think common now in this sort of complicated landscape it's important that we have or sometimes it's important that we have idents which is to say in this case the absurd statement can be executed multiple times without changing the result beyond the first application so typically you'll be able to write the absurd statement such that it doesn't especially matter if it's executed multiple times that's something that can happen from time to time so that'll be just fine if you write the statement correctly if we need to reconcile the table in a live system I mentioned before special visibility rules how I've changed in very specific cases how MVCC works that could be necessary here that could be particularly necessary here because you could have multiple concurrent processes doing this sort of thing and you can't really sort of tense the tables make sure nothing is happening from any other process at the same time that's really not practical and there's probably other various messiness as there always is so my perspective here is when you're dealing with all this complexity just be careful with bad plan it's certainly not going to scale all too well ETL is always complicated but meshing all this complexity together can be sometimes very difficult, very challenging so by having all these guarantees you can sort of leverage them to the hilt we've got a few basic good primitives that you can use and you can get a lot of mileage out of them and you don't have to be an expert to get all this benefit and because it's easy you sort of get it automatically so I feel this is a very important goal for Postgres in general so I think I've done that here now getting a bit more into the example this is something calling an accidental distributed system which is it's not something like involving Apache, Kafka or some kind of message bus system something fancy like that it's more for day-to-day kind of example so this particular example involves retail systems with disparate suppliers with ad hoc text file formats some of these may sell the same product without necessarily being all from independent manufacturers so they could be selling multiple suppliers could be selling the same product essentially this is a standard boring sort of business information system and this requires periodic syncing with regional regional suppliers these are basically autonomous systems so an end retailer needs to maintain details of each product with autonomous systems of non-obvious it's not obvious that one is started in respect of what values for example a product description so there's very subtleties because of that so this is an example a little more worked out I'm creating a products table here a supplier prices table so this suppliers perhaps don't have a particular interest in making this easy for you for all they would have you buy everything from them when for practical reasons, economic reasons you're not inclined to they don't care about distinctions that the end user the retailer naturally cares about they're not especially inclined to accommodate the requirements that that person they have so we need to work around that okay so this is an example absurd statement so what you're seeing here is we're downloading new comma separated values file inserting some rows into a product and then potentially updating but on the basis of the existing data that being less than the new timestamp that you see there so it's kind of like your updates will only go ahead if that data is considered authoritative on the basis of on the basis of it being the most recent according to that timestamp so it's sort of like a technical the update sort of has its own snapshot which is the timestamp that you specify that's going over people's head but again the whole point here is that you just there's semantics it provides they're not especially complicated although the details of how you might avoid problems are more complicated you get that facility and you use it and you don't have to be an expert if you're trying to do it the other way there is significant potential for problems to arise so you know as I said if we make all this easy if we make it just happen then even people who are you know not liable to you just automatically get all these sort of benefits and they will accrue I guess alright so you've seen how UPSR can selectively update a row we'll look at more advanced capabilities now this is where a lot of the detail work went your favorite existing feature should work seamlessly there's a principle in post-crescue development that all features must play nice with each other so you'll have a hard time finding any case where it fails to make the new UPSR statement work with an existing feature okay so I'm going a little bit back to the example in making this further point so consider that you might have a system where products need to be or other objects that represent the database need to be logically deleted but not actually deleted maybe you have historic sales records that need to continue to refer to the products you have line items that are sold years in past even though those products have logically been deleted they still need to be in reports and so on and so forth and yet you probably don't want to display them when you do a generic search for the product however you can't very well delete the product and expect to have them in sorry, excuse me in the first place you can't delete them because they are simply not going to be available for the report but it's also true that if you don't set a difficulty with constraints in that if you have a flag that represents the Boolean column is deleted there's a difficulty there with making sure that you still enforce constraints the SKU the bar code whatever it is the supplier code if you still have the product record the old product record in the table it can still happen that you need to create it again because you need a new representation of that same product for whatever reason it does happen so you cannot very well have both records in the same table if you naively have a unique constraint however Postgres is a good way of working around this you could use a partial unique index you could have it say create a unique index on column where is active and so when you set is active to false thus logically deleting the product record at that point you will you will then you'll then it won't matter when you go to create a new version of it because uniqueness will only be enforced among those rows that are currently active so you get everything works basically but the question then arises well where does this leave absurd and that too is supported so this is slightly I don't know how visible that is so you see here the where is active that is distinct from a predicate on the update notice before the update not after there could also be a predicate afterwards so this is saying when we go to look for or infer unique index it's okay if it only covers those rows where is active so that partial unique index will work just fine as you would expect because we spelt that out again this just works I also already mentioned this phrase I was fond of making it easy to use correctly and hard to use incorrectly I think I've accomplished that we've accomplished that it's a little bit unusual that the presence or absence of a constraint affects the semantics of a query but that just needs to be the way it needs to work so we sort of end over backwards to make it safe having offered this inference capability we ought then to make sure that we don't add further ways to yourself in the foot with the inference capability so having made people use it we better be very sure that we haven't given them more problems than we've prevented the cure if the cure was worse than the disease that would be pretty bad so there are further edge cases that I'll talk a little bit about I mentioned earlier you can explain well in general everyone realizes you can explain an insert or an update this is an example of explaining an absurd showing the novel aspects of it these are highlighted so you can see the arbiter index in this case it's called absurd peak key the primary key this of course is what determines whether or not we had a would-be-dupe violation which is grounds for taking the update path so it's specifically limited to that one here it is possible sometimes that you would have multiple such equivalent unique indexes for example you're doing a migration maybe you are replacing an index because it's bloated something like that these things do happen and we need to worry about the rare cases a lot because when it does come up even if it's very rare it's surprising if you have a spurious unique violation so you can create unique index concurrently and end up for a short time with two exactly equivalent unique indexes and a number of other possibilities but then if that happens this will figure out both of them you'll use both of them of course usually that won't happen but if it does happen you certainly don't want to have in-production all of a sudden so again easy to use correctly this is a partial index example it's a similar example so I'm just showing two arbiter indexes here I'm going to talk more about advanced features of inference I haven't said so yet but this also supports expression indexes Postgres will allow you to create indexes including unique indexes on arbitrary expressions for example you could do that to create a lower column name expression index so everything in the index will be lower case and if it was a unique index you'd thereby be enforcing uniqueness over lower case versions you'd thereby avoid not recognizing well you would recognize as equivalent cases where the only differences were in the case of the text you were inserting so I mentioned forgiving of ordering redundancy predicate works on unique indexes that satisfy it as we say so you could still have a non-partial unique indexes and if it happened to be if it happened to be satisfied or it happened to satisfy the inference investigation every other way it would work fine because it would cover everything which is sufficient it doesn't just cover some subset that you spell out it covers everything so that's good too again if you added a partial index, if you're doing a migration and production you have both at the same time for a little while it would just work so the inference should be used in preference to as I said earlier the on conflict on constraint constraint name variant that's really kind of an escape hatch we don't recommend you use that and as I mentioned earlier exclusion constraints require it because there's no way of inferring an exclusion constraint you have to spell out exactly which one you mean but in general best avoid doing that so I'm going to talk a little bit other advanced features so Postgres foreign data wrapper who knows what Postgres foreign data wrapper is I guess a half people for those of you who don't know it's a way of an extension that Postgres offers that allows you to create foreign tables that more or less appear as tables in your local Postgres QL instance but in fact are maintained by another Postgres QL instance that you connect to so it's creating the illusion of having those be local tables with a high degree of accuracy so this will itself work with absurd but it only support to do nothing variant and actually that's simply because you're required to spell out what you mean or constraint you have in mind when you use the do update absurd so because foreign tables in general have no concept of a constraint for that reason alone it does not yet work with Postgres foreign data wrapper but they do nothing variant when you don't ask for a specific constraint when you don't require that it be limited to that when it's everything which is probably the common case with do nothing then that will work just fine with Postgres foreign data wrapper anyone heard much about ORLS the row level security feature also a new to 9.5 feature can I get a show of hands for that perhaps so people know about that about the same proportion the existing permission system in Postgres is basically column oriented or table orientated where you have permissions on columns can I can I see the data can I update the data etc ORLS basically offers similar although somewhat different capability with row at the row level where you can also limit which rows are updated so this is something that is a major item in the 9.5 release which this will also play nice with more or less transparently there are some there is a little special consideration to how this must work with ORLS but not too much so it's nice that we were able to offer those both at the same time with each other updateable views as the name suggests way of defining a view and having an update on that view automatically be rewritten such that it updates the underlying reference table if the system can determine that it's equivalent to such an update that can happen transparently also will work just fine here library it's not PostgreSQL 9.5 it's part of the core release it's not a library as such no no it's part of the core system it basically works by typically what you do is you have a column that has the name of the user connecting to the database it doesn't have to work that way and you create a policy on the table and that forces the query against that table to have an extra bit on the predicate essentially limiting what it can see or what it can update it could be that an update is not allowed to see rows it could also be that upon attempting to it throws an error there's a lot of complexity and variation there depending on exact requirements the possibilities are considerable core distribution logical decoding is a big feature in Postgres 9.4 it's a way that we can do logical replication a logical decoding plugin can potentially support for any arbitrary purpose it can produce it can consume rather logical change set records that could be used for any number of purposes the way this will be reported if you do an upsert it'll be reported reliably as an insert or an update to the row it's not possible to reconstruct that it was an upsert so it looks like inserts and updates if you were consuming these change set records from your plugin it might matter that you're not able to reconstruct but probably not certainly not for logical replication so what you didn't see also is that the on conflict do update statement the syntax is totally unrestricted in its structure merge won't allow you to put sub selects in any of the handler clauses that it has whereas this has virtually no restrictions at all you can for example have subqueries in the update you could be correlated subqueries they may reference the excluded table so it's really very flexible in terms of what you can in terms of what you can throw in there there's an awful lot of flexibility there over and above what yes sir yeah I mean if you you could certainly you're certainly not restricted in that you must use the values statement you could for example insert into whatever conflict do update and then you could select star whatever so you can select an arbitrary thing in general you can select an arbitrary number of rows to insert and so it follows that you can select an arbitrary number of rows to insert or update because that's just an extra clause on insert well that's one possibility certainly yes there's also a new 9.5 feature it'll let you for updates in general are now updating multiple columns from a subselect so in other words you could do update table set in parentheses abc equals select 1 2 3 from some other table so you've got a subselect with multiple columns updating multiple columns all at once as opposed to for example having multiple such subselects which is messy I can combine it to one nice little one this will of course work just as well with insert on conflict do update that's pretty good there's also some of you will know that in general insert or update statements in postgres support a non-standard returning syntax although in fact oracle has a similar syntax this allows you to show rows after they were inserted updated or before they were deleted in the case of delete you can insert something and then you can see what you actually inserted what would you want to do that well maybe you have for example a serial an auto increment sequence and it's not you can't really determine ahead of time what the primary key value will be well that will be right there if you do return you can immediately this will work with an insert on conflict do update statement the same with inserted rows of course but with updated rows it will show you those as well so you can see inserted or updated rows returned to the client as if they were selected more or less again no reason why I ought not to just work which it does I guess we spend some time discussing whether or not we should show the updated rows as well and the decision was that you ought to and so you do okay so because of that you can does anyone know what a writable CTE is in postgres like a with statement okay so that's just some of you will perhaps know that in other systems there's always been or as an ability to have what is essentially like a temporary table that exists for the duration of a statement so you do a with something as and then you define it could be it's another statement selecting something from something else and then there's a main statement that appears underneath that that can reference the CTE as if it were a table this is a nice way of breaking up the logic of certain complicated things by sort of pipelining them so anyway returning which I mentioned just now can work with this in postgres which is a unique postgres thing it's not in any of the other systems that do have with statements so you can pipeline things you're inserting or updating across CTEs as well so again immense flexibility very useful for data integration I'm really looking forward to seeing how people use it partitioning mostly works as before that's not significantly different probably not going to specifically note that I guess that's all I have to say on the subject that's any questions any further questions yes sir yeah I always get that question which is the answer is no but let me just explain why that is basically if you do an upsert what if so that did work so that was offered what would happen if both were violated at the same time that's where you get the complexity of that you know if they were both violated at the same time then you can't really do both alternative actions at the same time right now it is true as I mentioned that you can technically although it's not really intended to be used in a fancy way you can technically use two unique indexes as arbiters at the same time I guess they're more or less equivalent in the event of migration that kind of thing