Tableau Users Group November 2020 Meeting: Mapping with Tableau

Sarah Murphy: Wants to on today’s Tableau user group we’re going to focus on mapping today and Zach Lockwood from facilities has graciously agreed to share with us what he’s currently doing and some tips and tricks and how to get it to work with ArcGIS data, things like that And I’m going to just very briefly share How to integrate Mapbox maps with Tableau, but because our TUG is a community and we’re all stuck at home, staring at screens I’m going to encourage everyone to turn on your video really quick, and I’m going to ask Brian to throw us into some small breakout rooms for just three or four minutes so that we can all say “hello, I’m alive” Just share what unit you work with, how long you’ve been using Tableau, and one of your favorite things about Tableau, and then we’ll just come right back together and then we’ll then dive into our program So Brian if you could throw us in breakout rooms, that would be great Thank you Brian Frueh: Sure thing It’ll take me just a second Sarah Murphy: Hey Brian, can you just give me a heads up when everybody’s back because I can’t see it Yeah Brian Frueh: All right, everyone should be back now, Sarah Sarah Murphy: Thank you so much Okay, well I enjoyed our little group Try and find ways to humanize Zoom, it can be very like, the wall with face, right, but we’re going to dive right in and I’m going to hand over I’m going to mute myself and hand over the whole Zoom to Zach, who’s going to share his, his world with mapping with Tableau, and then we’ll come back to me in the last about 10 minutes and I’ll share how to do a Mapbox integration and a little bit about our dataset for The Iron Viz in January Okay All right Zach, take it away Zachary Lockwood: All right Yeah So Hi, I’m Zach I work in Facilities, Operations and Development and Operations Business Administration So, like, our group has a lot of titles to get down there But, I worked for Matt Sear under P calamari on doing a lot of data with Tte work order management system that we use is my primary focus So, anytime a light goes out in your office and you get on s to s and submit a request those work orders are typically the data that I’m working with And that’s what’s in the workbook that I’m going to show you today Let me Okay, here we go So, I’m going to first show the two ways, can everyone see this? Alright, so I’m going to first go through the two ways that I know of to Get the GIS data into Tableau One of which has always worked or for since at least like 2019.1 or something or 2018.1, and then the other of which is a new feature for like 2020.2 So first, this is gismaps.osu.edu The basis, you know, like the foundation if you want to like look at the OSU campus data So, when you load it up, you’ll usually be somewhere around here But if you come down to this little export button, you can click to draw an area of interest I never really bother You set your download format to shape file and then I usually only just focus on the building If you come down here and hit an extract data Well now It’s not letting me not draw a shape I don’t know what I did wrong Oh, well, let’s draw a polygon David Martin: Click the draw button Zachary Lockwood: Yay Oh yeah, thank you Sometimes when I click and drag No, I blew it

It’s double click the end of it Yep Yep Thank you If you extract data It will process your request Click Here you get this download, so then that goes into your downloads folder and you probably can’t see it on my downloads folder, but that’s okay So, I will change my share here in one second to Tableau New share So, this is a blank Tableau workbook, you come over here to a file I didn’t need to do that Spatial file So, we got our output folder And so, you see it filled in all of that building data for everything that was in our circled area So, you got building number, fancy name, a link number again, the more common name with the four-digit building number attached, address I don’t need to read every column, but you just get like all of GIS’s data basically How they describe the building, you see building maintenance for someone like me, that’s pretty important, because obviously I need all the buildings FOD maintains But, and then at the end, you get this geometry field, which just says the word polygon over and over But if you go to worksheet and you drag your geometry measure to it, onto the thing, the thing, the dashboard, then it populates all of the shape files So, the shape of every building in the place where it goes on the map That’s what the geometry field populates And then you’ll probably want to do something like put I always put building number on color or you can put it on detail so that way each building is individually its own mark on the dashboard My favorite is always the tunnels, because the tunnels are building number 9999 go or re-run across campus And so that’s the one way to get this GIS building data in The other way is to use the Esri ArcGIS connector So, that is Ersi ArcGIS server in the new data source And then it’s going to ask for a URL And so, our URL I have to do a new share again I’m just going to switch to that Perfect Can you see Chrome again, GIS maps? Perfect Alright So, so this is the standard GIS maps URL I have up here is how you get to the ArcGIS directory So, it’s GIS maps of osu.edu/arcGIS/rest/services I can go ahead and put this in Chat Some Yes, there we go Put that in the chat So, this long thing gets you specifically to the buildings layer of the ArcGIS services For best performance if you’re going to use this connector, you want to get as specific as possible So, if you stop your URL directory Oh, a few maps, you can access each one of these tables individually in Tableau, but it slows down your data connection like immensely So even just clicking into here You’ve got all of these different data layers that are all going to pull into separate tables in Tableau Which then, again, it just like gets really, really like slows down your processing and everything So, I always I always suggest getting the specific as possible if you’re going to use this So, if you use this link, go back to Tableau put this in here And so, this is going to actually pull the exact same data as that shape file we exported The only thing is that this is going to be a live connection So, if the team over FITS does make updates for buildings or like they just open the new dental clinics, there’s a new building on campus, they close an old building like old Fontana those updates will be auto at automatically like brought in But again, like those updates aren’t something that I, they’re not really that common because, you know, whole buildings don’t just change all willy-nilly So personally, I always David Martin: I should point out real quick The building names change fairly often

Yeah, so, and if you’re if you’re going to match on say SIMS data to say start representing space data for buildings, you know, how you show buildings and how many offices are per building, whatever, you’re going to use that common name or comm name in the GIS data to match on to the SIMS data and you have to be wary about those building names changing So, it does pay to connect live for that reason, but, just a heads up Zachary Lockwood: Yeah, no, I’m glad you jumped in on that because I, yeah, I always joined on the building number I just like doing a numbers versus strength personal preference So, like, to me, Just having like the data extracted just help speed things up But of course, same thing can say, you can do it either way Like I said, this is all the same data, you find your it’s gone shape instead of geometry let me find your shape And then you’ve got This, this gives you the bonus of not having to draw your shape, your polygon and for the export so you can see we’ve got Mansfield campus, Wooster campus I’m assuming Yeah, I’m pretty sure All of the buildings that have been put into though the OSU GIS maps, whether or not they’re like specifically our campus buildings So, then you can do something like just throw a filter So, you only want Columbus campus Maybe you want to add in the Medical Center, that’s pretty normal Then, again, I would put something like building number on the color I add all members and then if there’s, then eventually you end up like filter out some buildings So, then you can see we’ve got off campus again here It’s pretty ugly That looks a little bit more like campus So, this is the other way Those are the two ways that I know, certainly, there may be other ways, but those are the two easy ways to get this map data, the campus building shapes and everything into Tableau so that you can use marks from other data from other sources to then visualize things So, I will go ahead and close this out and pull up the data we’re actually using So, this one This is the workbook that we actually used for some work order volume and project identification stuff Answer that question on the way That’s the best, the answer to the question is, is it a true live connection Zachary Lockwood: No, Once you would publish something to the server, it’s a live connection to, a live connection to an extract is not a live connection until you refresh the actor It’s confusing, but you can’t, the Ersi ArcGIS connector cannot be a live connection on server just like nothing else has a live connection So, when you publish the server, it would have like a refresh time that it would query against ArcGIS And be like, hey, what’s the fresh data I always just create extracts locally, and then I like to publish those to the server sometime, just to keep the books from breaking so that we seem to break network books So, this one I actually have three data sources and this is that building extract and you’ll see that it does come with a gross area for building, but I have a SIMS pool here that has a building number and size because FOD doesn’t, is not responsible for maintaining or cleaning every square foot of every building on campus, necessarily, there are some buildings that are split just some spaces that we don’t include in our metrics So, this is just a sense, a sense extract where I filtered out some of the square footage that we didn’t need, and then I have all of our work orders So, this is what our map looks like To do blending of multiple data sources, you have to use your shape file or the Ersi connected data source as your primary data source So, the first thing you have to do is drag the geometry onto the, onto the dashboard like so, and then you have to have fields on these other data sources that you can do your joins or create your linked relationship on, whatever they’re called nowadays And so that does present some limitations in the how you can filter and use the data, because they do have to be blended like that So, that is one thing to keep in mind when doing this kind of mapping, if you have multiple

data sources There might be a way around that, I haven’t found it I haven’t looked terribly hard, but as far as I know, like there are some things that we can’t filter on because the primary data source is the geometry And so, if you wanted to do, if I just wanted to do building as a detail make this the primary and then link, I found it does not like, it will not necessarily map well, with, if the shape file is not the primary data sources You see, it’s just not doing anything But the moment the prime, this is the primary data source It does exactly what you want So, this is our campus map This map can actually show eight metrics, it will change the color of the building based on the metric So, I created a parameter the different metrics that we have available And so, based on that there is a calculated field, that cases on the parameter when “blank” then perform some calculation And so, you can see we can do number of work orders, labor hours, material costs, or total cost And then we also do the same versus that log, size of the building so that we can see on a per square foot basis, what buildings are higher and higher volume work orders So, you see this really dark, this is Post Hall we have a really high volume of work orders by raw count But if I go per log square foot, that’s going to be about the same because Post Hall, yeah The, the genuine side would be a bigger building has more work orders, but the variance in size of building on campus is like extremely high So, I would suggest to anyone who wants to do like a per square foot using this ArcGIS data, I would suggest using the log, the natural log, of building size so that you don’t, you’re never going to get a result from like a Post Hall, RPAC, Thompson Library, all those buildings that are just huge like those small buildings are just going to kind of dominate in on a per square foot basis and so log adjusting is something that I found that really helps, especially on campus because like we’ve got like teeny tiny buildings I don’t know if this applies to anyone, to everyone, if you might be in a more specific area of campus where the buildings are more like in size versus FOD what we do, we have like kind of looking at every building on campus and there’s definitely a huge size area So, We also have filters, like you can do like kind of see like over time how this changes if you want to change this just last four months If the Post Halls, no longer, but the darkest one we’ve got Prior Hall as a higher volume work orders here for us And this was just something this is something that we use for our planners to identify buildings where certain things might be coming up more often than not So, people in the offices, is that we try and identify something what might be causing those like long term problems that keep coming up And so, what this dashboard helped us do with look like okay, which buildings have the most work orders or which buildings have the highest cost So, to say so like work that’s being done in this building is very expensive for FOD, which then may also be expensive for the customer, and is there something that we can look at in that building that may just need replaces there Is there, HVAC unit that needs more attention more regular servicing or just these needs replace is it Pressey Hall, which is expensive for some reason So yeah, this is, this is the main work word that we’re using right now, the main workbook The word work to anything Um, this is the main workbook that we’re using right now that really utilizes

the mapping data Especially doing like on a square foot basis like not just seeing like where our work orders on the map, but also comparing to the size data that SIMS and ArcGIS has to identify what, like just different metrics and bringing in more data, and just blend it all together So, like I said, like this is three separate data sources And especially like the ArcGIS having all these different fields available, it’s, it’s very easy to blend in multiple data sources to do analysis in different ways It’s kind of the end of my spiel So, if anyone has any questions, I would love to answer those It’s kind of a straightforward dashboard So, at this like right now David Martin: If I could jump in I want to let everybody know coming so I’m over in FITS and I work right alongside the GIS team I managed SIMS for those that didn’t know and where I’m actually working, with the RAY team right now to move this GIS data set or copy it over to the RAY And as soon as that happens, we will be making these shape files, both the building and the parking lot shape file, excuse me, of actual Tableau data sources on the server And so, you’ll be able to find those and link directly to them via server connection out of the FITS project data sources, and soon as soon as those are live I’ll be sure to email, Sarah, so we’ll get that information out to everybody That might help with some of your issues like having to have the shape file be the primary data source Maybe once it’s a data source on this Tableau server that might help, that might resolve that hopefully, we’ll see Zachary Lockwood: Sarah We can’t hear you David Martin: I can’t hear you, Sarah Zachary Lockwood: No, not yet Brian Frueh: No We can’t hear you So, you might want you to check the carrot again next to your Zoom microphone just to make sure anything’s being detected David Martin: Shows you as unmuted Maybe try muting and unmuted again to see if Brian Frueh: No Can’t hear you Brian Frueh: Not quite Interesting Dale Harris: Zoom microphone plugged in? David Martin: Heard you for a second David Martin: Well, you have a bad wire or something Brian Frueh: No Sarah Murphy: I learned a lot I don’t know about the Ersi Connection and haven’t seen it in action So that’s great I see in chat We did have a few questions Let’s see One was from Jeff Welcome back to OSU I think that you covered that Is it truly a live connections data source page and it should be an extra, I think you did a cover that It needed to be an extract with you upload it to server, is that correct Zach? Yeah yeah Okay, and then Beverly, and I did, I shared this question on there’s just some terminology for some of us who are outside of FOB and I worked in the Libraries What is FITS and what is SIMS Can you just share real quick? Zachary Lockwood: I’m going to let David answer that David Martin: Okay So, FITS is Facilities Information and Technology Services David Martin: We are housed within PARE which is Planning, Architecture and Real Estate, which is a part of AMP with along with Zach and FOD And then SIMS is the space data management system for the university So, we have a system that tracks every indoor space on campus and all the associated attributes that go with that Room usage capacities, who the owner org is for that space like So, who gets bills, bills for utilities minutes and whatnot And I manage the SIMS database for campus Zachary Lockwood: There you go You want to talk me through I can show a little bit of SIMS David Martin: Yeah, sure So, while you I mean, you’re in the perfect place So, we have, you have the different levels tiers of the space data, campus, building floor, spaces

You also have the ability to track occupants in a space Once you click on the space tab, that’s probably going be the most interesting I’m sorry to the right Yep And then throw a percent sign into space ID and hit apply Zachary Lockwood: Oh wow, I’m so glad you said that I Wild Card I always wondered if there were the wild card David Martin: Yeah, so you can see right now We currently have 114,000 plus spaces tracked on campus David Martin: Every individual space has a space ID That space ID is the building the floor and then the room number, along with some extra characters And everything’s in there for the assignable square footage, the capacities, what building floor applies to of course, whether you pay POM on those spaces, who the owner award is All that data is available for every space on campus These the space SIMS space data is actually one of the first certified data sets on the Tableau server It’s already available for you if you guys want this data for any reason It’s all right there in the physical space project And then if you could go to the facility information module Sorry, we’re kind of coming during the meeting here a little bit, but just, just open any building floor, just so people can see what kind- Zachary Lockwood: Do you want the floor plans? David Martin: Yeah, and then they can, we can show them the fact that there’s based data attached to the drawings So once this opens This will open a smart floor plan render and every space has all its attributes tied to it So, as you hover over the spaces, you see, it highlights just yeah then double click on any of them really doesn’t matter And then you can see all the attributes assigned to that space are available to you there, and the dialogue We also, there’s probably no 360 photo of this once you jump over to Cavalli real quick And ground floor Zachary Lockwood: Oh yeah, I got ahead of myself David Martin: Slow Zachary Lockwood: It’s my internet I’m asking it to do a lot right now David Martin: Open the other main floor there And then go to pictures And it should open up Let this render for a second So, we go around and we take 360 photos of as many spaces as we can get The high priorities, places like classrooms, operating rooms at the hospital, and whatnot And once this actually renders and opens you’ll see it’s a full 360 photo that you can move around and look around the space So, this becomes valuable for like the FOD team if they want to see what the space looks like before they get into it for maintenance reasons, a mechanical space they can see what the, what the mechanic plant looks like try it If it’s not opening tried double click on the first photo there Zachary Lockwood: First one Okay David Martin: Looks like yeah it looks like it’s just still be rendering and we don’t Zachary Lockwood: Oh, wait, wait Zachary Lockwood: No, my bad I have to David Martin: Oh, it’s shared in the other window Zachary Lockwood: Yeah, I had, I was playing with it as you were talking, I was like, wait David Martin: I like going to maximize that Yeah, there you go So now you can see so we try and get around and we give me 360 photos of campus as we can So that people can actually get into a space So, we’re wedding We’ve gone We’ve taken a bunch of Sarah’s time Sorry, but Zachary Lockwood: No, I think that’s David Martin: That’s SIMS Zachary Lockwood: I sat there a SIMS presentation I definitely think the people in this group were interested in that So, Yeah Sarah Murphy: Oh, I understand there’s a bunch of static when I’m unmuted Is that still the case or should I call on my phone? Are we good? David Martin: Sounds good Now, Zachary Lockwood: Know that was good Sarah Murphy: I turned my computer Maybe I’m getting wind or something and distracting Great I really, that was really cool seeing that SIMS data and I saw the floor plans Can you download those soon? Yes David Martin: There the other places to download the owner plans If you guys need high quality owner plans There’s PDFs that are high enough resolution for 11 by 17 But there’s also Sarah Murphy: I’m thinking for future TUG, because there’s a way you can use background images Are things like blueprints interest to our group, but if your TUG talks we can we can look at that later David Martin: Yeah, let me know if you want to set up and if anybody if anybody wants a tutorial on SIMS reach out to me So, Martin.1537

I do either one on one or group sessions on training people on SIMS all the time So, feel free to reach out I’ll be happy to show to you Sarah Murphy: Do you have to contact someone to get permission to get into that system? David Martin: Yeah, just email me, it’s it any OSU employee can have access, but you don’t have it By default, I have to activate your profile So just email me Sarah Murphy: Great to know Thank you All right Well on for the next portion of our meeting, I was going, my plan is to share a little bit about Mapbox integration with Tableau which won’t take too much time So, let me share my screen just going to share my entire desktop here and jump into my Chrome So, can you see Chrome here and you see mapbox.com Thumbs up Good Alright, so this is mapbox.com It’s a way to add some different colors and layers to your maps in Tableau This is just their main website I have, you can sign up for an account for free to play with it There’s fees if you go crazy and embedded in your website and it’s getting all these hits on a daily basis I’ve never really run into that But again, I just play with it So, I’m going to go to my account Actually, I didn’t want to go in my account Hang on a second I Yeah, that works on All right, so you have to make them at first So actually, what I wanted to do was go to my studio, we go and I’m going to, second herem and I made a couple maps I don’t, I don’t really think it’s a I don’t know enough about Mapbox right now to share a lot about how to create the layers and, you know, we’ll just jump into quickly and your style But like say you wanted a monochrome map or needed to do something with All of the topos are different kind of map You can use some of the templates in Mapbox or you can tweak templates and make your own stuff So, I’m just going to randomly choose monochrome and hang on I got to move my file here, going to say customize it and it brings me into this environment Again, I think for another day or maybe even another program outside of the Tableau User Group, we could spend a lot of time on how to play with this system, but OSU branding colors are always of interest to people We can add some layers and we can manage the colors of the layer So, I could say, hey, for the base I you know, I want this to be a different color, click on it, it opens up, eventually and I do this, a Very low the RGB values So, I could turn this scarlet by changing that to 18700 so remember you can always go to brand.osu.edu to get the whole palette of branding colors if that’s of interest to you Or if you love the color green, you can just make it green right now So earlier today, I made some maps And here I have one I was just playing around And made the Medical Center red and the rest of the campus grey and thrown some green spaces on this one didn’t actually translate into Tableau that surprised me I have another one I made back in August, that’s black This one did translate to Tableau So, once you have a style, you can come up here to share And what you do is you click here on third party Scroll down a bit and right under third party, we’re going to change this to Tableau And then you just copy your integration URL right here and pop into a Workbook So, I have downloaded a while ago, the ArcGIS file for all the trees on campus And so, here’s all the trees around the Medical Center If I go up here to map and they go to background maps I come down to add a Mapbox map, and I can give this map a name I’m going to call this test two and then you just paste that URL hit ok and then it, we’re not going to see anything different, because this, this map, really Oh yeah, actually, we did Okay I have a couple Mapbox maps and I was playing with earlier today

So, you’re already up here so I could change that background map to a couple of other ones that I played around with but really that’s just the ins and outs and this is my segue into the what I hope you guys can all give me feedback if this is the craziest idea ever I had one of those realizations this morning there., Oh yeah, I said I was we were going to introduce the data set for the Iron Viz and then oh yeah, I hadn’t even thought about that yet So, but I’m on a different project and playing with this for a while So, if I’m just a couple things So, I don’t know if you know, but there’s a there was a big project, a few years ago and it’s if you look, it’s with the Chadwick Arboretum And there’s some context and background information about it But there’s a database of the where all the trees are on campus And you can search for trees and it’s all in the ArcGIS space for OSU So here let me move my new bar because it’s getting in the way Alright, so here we go ArcGIS so, this is great and timely that’s actually one way to get the data out I also have this data in an extract file If you can share that with you too But where all the trees are, I just have the trees on So, here’s some ideas because, you know, one of the cool things with Tableau is you can always join data sets together So, I’ve been looking around and now if you go to the open data page for the city of Columbus and you search for trees This is a popular thing to know where all the trees are right There’s a data set for all the publicly owned trees in Columbus So, you could blend with the OSU tree data set I did notice that the sort of my desktop like totally crashed Right for getting started So, I have to recover some documents but yeah I was looking at the publicly on tree dataset noticed all the tree codes are in there Which is great if you know what tree codes are, but I don’t think most of us know what the four letter tree codes are so then, here they are Since They are here on So, we have our treat code or species code So, um, there’s some like on the on the US National Oceanic Atmosphere Association has all of the, you can download all of the names for those three codes There’s some cool things you can do with the plants database at the USDA You can search by those tree codes And since you can do that Like, I think this is a maple tree Sarah Murphy: You see right here Here’s your tree code right there So, you can use actions to add information to a dashboard or enhance the dashboard What else can I do, there’s the sky’s the limit on what you can do and I can, I could even give bonus points if you can incorporate some things about trees or something into a dashboard I know there was a big there’s all like in 2015 was a whole album that won a Grammy Award, and it was for kids and it was all about trees and my dog is having the little puppy nightmare right now, it’s kind of cute behind me So, I thought, you know, why not we can try to do our Iron Viz on trees this year, and if it works out really well, maybe we’ll have a, we can have an Arbor Day exhibit online or something and make it work So, we’ve done it on bees before, So, we can do it on trees Bees and Trees So, that’s really all I have Do you have any feedback for me? If that’s the stupidest idea ever let me know, and if it’s great, I will, we’re not going to meet in December But our next meeting will be in January and it will be our Iron Viz, and I’ll put together some documentation with some of these suggestions and also I need to sit down with our planning team to really kind of figure out the particulars on how we’re going to do a virtual Iron Viz this year So, I’m going to stop sharing and that’s it Any thoughts

Does anybody need help with anything Tableau? Have any great ideas for future program are always looking for that I hope you had a good TUG today and I hope you have a great rest of your day Future meetings, the TUG is on the third Thursday of the month from 12:00 – 1:00 p.m working through the Research Commons this year in the Libraries, my colleague Holly is handling, Brian handling scheduling and getting everyone the links and we’re recording all of these sessions and making them available afterwards So, we can honor accessibility requirements and Yeah, so we’re just not meeting in December, but we will be meeting the third Thursday of the month through June Any other questions Okay, well we got some thank yous for great information I will send a reminder out to the TUG with me on the link to on where to sign up, but it is I do need to ask you to sign up because you’re just going, we’re just using this new system automatically sends the link to you Great Alright, well, if anyone has any other questions, you can always email them to me directly It’s Murphy.465 and we’ll be in touch Have a great couple weeks Okay