Connecting Revit to Power BI : An Introduction to Visualizing Model Data

hey everyone and welcome to another episode of bim after dark live my name is jeff also known as the revit kid if you've been here before and you've watched this show thank you for returning if this is your first time tuning in this is a now weekly this is actually episode 18 believe it or not weekly live stream that i do on usually thursday nights around this time 7 30 eastern and basically talking about all things revit and bim related sometimes it's just me sometimes we have guests but uh either way it's a fun um informal live session so feel free if it's your first time the chat i do try my best to keep up with the chat answer questions along the way i want this to be as interactive as possible without being on like zoom and seeing you know hundreds of faces on here i don't know about you guys but i'm a little zoom fatigued this week i did two two presentations um and each of those had like three or four virtual dry runs and they're all virtual presentations so i'm feeling it but i'm pretty excited about this topic so i'm excited to uh to uh talk about it tonight this is something um power bi uh and revit and visualizing data is something that um people have been asking for since i started running the show and i've heard about in past and um or i've got emails and stuff in the past and so it's something that i've been trying to figure out how i wanted to to to break into we had a session two weeks ago where um conrad um if you guys missed that check it out from two weeks ago um by the way all the episodes uh can be found either on my youtube channel so make sure you subscribe let me hit the subscribe overlay boom so make sure you subscribe hit the notifications um and all the live streams are there afterwards there's a playlist called bim after dark live um but you can also go to live.bimafterdark.com and see all the episodes so far um so what i'm saying is uh two weeks ago conrad um who is now from bim beats um came on and he he really started talking about visualizing data and some of the data analysis and stuff and so um i wasn't quite ready to to follow up with that so now uh now that i'm i've produced uh some content here and i'm ready to introduce you guys to to what power bi is in the connection with revit and what you can do with visualizing your data so super excited about it another thing i wanted to mention too is um if you're interested in any of the content that i have i do have a bunch of really cool free resources that um i think you should check out one of them is actually a full twin motion course but also a bunch of ebooks and stuff so check that out at free dot after and i'll uh i'll chat it down here free.bimafterdark.com so definitely check out all that stuff it says some some pretty cool content there all right and and if there's a little bit of delay in between um when you're chatting and when i'm speaking so bear with me a little bit i do like to try and read the chat sometimes go fast i do like to try and read a little bit so if i get silent for a second i'll let you know but if i'm looking over here that means i'm reading your chats um awesome so um how long was i in montreal did you ever go back sebastian asked me about montreal i'm not sure why he said that did i mention it before maybe i don't know well yes i was in montreal once and it was uh probably like 2013 or something like that it was for it was actually for a class trip for for um my graduate program and it was a great city we had a really good time and i haven't really been back since i've been to toronto once since then um in vancouver once since then um but i haven't been back to montreal i loved it so one day i'll get back there but i don't think they'll let me in right now unfortunately all right here we go uh and then carlos asks is this a brand new bad version for uh about data um currently no i don't have any courses on them after dark uh in related to this but um if there's enough need and want for this side of of what we do then i'm more willing to make it so what i was going to start out with with saying is that i sort of got introduced to power bi i want to say maybe two years now um for various different things but the first reason was there's an integration with assemble and so if you guys don't know what assemble systems is it's a a product by now by autodesk um but it's it was designed for um model based quantity takeoff and um it had an integration into power bi and so we we use now assemble for quantity takeoff as well as production tracking and a bunch of really different things really really cool things if you just search on the revitkid.com on my blog for assemble systems i do i think i even have a presentation an hour-long presentation about what it is but um super uh super valuable tool but one of the really cool things is that it had a direct connection to power bi so we would upload revit models and to assemble do all of our stuff in there whether it's quantity takeoff or production tracking and then we can have a live dynamic link where we'll just refresh it with power bi now what i'm going to show you today is is skipping the assemble piece of it and just showing you how you can start using power bi right now um and connecting to your models and pulling out data and visualizing things the one thing i want to mention is i'm going to show a bunch of different sort of ways that you could use it but what i really want you to take away from tonight is how this connection gets made the concepts behind it a little comfort with power bi as a tool microsoft power bi which i'll talk about a second um and then from there i'm hoping that you take this knowledge and you'll immediately have you know seven other ideas of how you would use it so i'm using it for very specific things but uh the the goal tonight is that you're comfortable starting to use it for whatever the heck it is that you think you'll find value from it somebody just asked for the link for the assemble presentation so uh since we're live why not i'll quickly look it up because i'll probably find it quicker than you guys something that's really funny um i've been if you haven't been following me for a long time i've been blogging um since 2009 so 11 years now on therevikid.com and there are over i think it's almost 1300 posts now out of those 11 years and i find myself searching the blog probably more than you do to find something that i did in like 2008 or 2016 or whatever and so it's kind of kind of interesting how that pans out but uh let me just quickly look for a symbol for you guys i'll post that link in there uh here we go so this was actually a webinar i did with assemble systems and so i'll post the link right here and then i i did do um if you guys uh happened to make it out to um built uh north america last year in seattle it was last year now it feels like such a long time ago and um i did an entire class there as well on assemble systems which is pretty cool um all right so let's get into this thing i'm just reading real quick some of the some of the things um awesome so we got some oh so somebody some people have tried revit to excel to power bi suite all right so yeah i'm going to talk about a couple of different ways that you connect and just just sort of show some recent things that i did um and what i wanted to do was kind of pull away um the the other software so like for example um one of the main reasons that i'm using this like i said before is with assemble and in that connection which means you're you're pumping um revit into um assemble and then you're using the connection to assemble to do all of the data visualization i know that db link is out there and so full disclosure if you guys are anyone out there is is comfortable with sql and microsoft database and access and stuff and that whole thing shoot me an email because i struggled for a few days to try and get that work to basically connect the whole database and it was it was tough um and i apologize if you hear my son crying he uh hates brushing his teeth he's almost two years old now and we are struggling with brushing your teeth anyways um so i i was i was i was messing around with that and i do know that i think nathan miller um of proving ground i believe um i think he has a an app that just came out called tracer yeah proving ground so if you if you guys are interested in that check it out and i do know that he's he's utilizing some of the um some of the connections that you can make there on the back end and that's through an add-in but what i want to show you is today without any any add-ins at all um how you can start connecting and visualizing your model data as simple as just using schedules that are already in revit to pulling out information and then being able to combine them in power bi okay as well as sort of showing you what power bi is because i'm some of you out there may not know what it is and actually maybe in the comments let me know real quick um if you've ever heard if if you feel like you know what power bi is and you're comfortable with it say yes and if you don't don't say anything because i'm curious to see out of 100 and some odd people that are on here right now how many yeses we get because i know for a while i had no idea what it was and it's perfectly comfortable doing that okay so neil just said he's spent years doing that connection so if you've done that yet shoot me an email at neil for sure and uh it looks like uh who is that oh that's alberto alberto said that he's still struggling with oh no he he's five years old and your son is still uh struggling first and steve you tell me i'm dealing with this for three years oh boy awesome so we got a few yeses in there which is great um i'm assuming that there there's a majority of you out there who don't which is great as well and that's um the whole point here is really to show you the connection but also introduce you to power bi so i wanna i i don't wanna go too fast and if you have questions feel free to to hit the hit the chat and let me know as i'm going through it because this can be some crazy stuff the concepts but at the end of the day it's actually extremely simple and i'm hoping that i can explain that to you so we got a bunch of yes it's awesome but that's still only about 10 or 11 yeses out of 115 people so uh that's all good so let us go to um my little head in the corner and little rabbit action so before i jump into revit and sort of talk about how you can pull data out um in the most efficient ways to pull data out and pull it into power bi i'm going to pull open power bi as well to show you some of the some of the dashboards that i'm going to be i'm going to be uh displaying today and so this is just a quick mock-up one this is the one that we're going to be using as our example to connecting directly to revit and so what you can see here is we have um these are this is these are the rooms in the project and you could say we have the level the number um the name of them and the area then we have the total area here but we also have things like you can see the total area by name or or even by percentage so you know what percentage of the area et cetera and obviously you could see this one's unique because um the first floor has a 67 of the total square footage of the building and the the basement only has 33 and that's because there's a whole the addition is actually a slab on grade and so it makes sense we added you know 30 to the building um but just just something right there um the the one that i'm really excited about which i'm going to show you is something i did recently and so if you remember last week i introduced a project that i'm working on myself which is my own house and i started playing with the idea of what if i what if i did a cost estimate like we do right now and assemble which is what we're doing model based and i built in unit pricing to my model and then how can i pull that out and effectively use power bi to analyze the cost and so i'm going to run through that a little bit it's really cool and i'll show you there's some unique unique features to how i had to approach that this one here is just something that you guys uh if you're if you're ever involved in the um coordination process 3d coordination process this one's a nice one where we actually just used um this is an excel sheet tied to a couple of pieces of information to track the progress of different pieces of coordination so you could see these are percentage complete by floor of the coordination model percentage complete is did they upload their model you know is it submitted or did they shop drawing submitted and so here we're kind of looking at the subcontractors and saying hey uh you know we're tracking you and you've got you know 10 submittals that are that are delinquent and stuff like that so that's cool one and then production tracking is another one and so this is actually a project um that as you can see we're tracking curtain wall here and this is where our assemble comes into play where we're actually putting the models up in the cloud um we're assigning statuses to them using parameters so like installed install date etc and then we're bringing them into power bi and we're connecting them and that's where we're looking at things like completion uh percentage uh you know current completion versus schedule completion versus pace you know how many pieces per day and stuff i'm not going to get into the assemble side of it today i just wanted to show you some examples um what i really want to do is just show you how you can just get started right the whole these are just some examples and my goal is i'm not hoping that uh at the end of today you've got a million ways that you can think of using this tool uh power bi and visualizing your data for your daily use whether it's design whether it's analyzing whether it's your bim manager maybe it's um you know kind of like a bim beats idea where you're looking at the health of a model how many errors there are how many refreshes whatever right there's all kinds of cool stuff you can do with it all right and so before i jump into revit and start i'm just going to check the chat real quick um uh there's a problem with axis in 64-bit yeah the i i like i said i tried so so before i started doing uh the example i'll show you with the cost estimate i um i attempted setting up a local access server or in a local sql server and then having the data the revit database export to that and then pull it in and i was just failing miserably i'm also doing it on on a work machine that i don't even know if there's back end stuff that may be stopping from happening but whatever the goal what i was planning on doing there was pulling out the entire model database which in the end at the end of the day probably would have been a lot harder to filter through in power bi than the process i'm going to show you now um neil neil asked who is building these dashboards well me and my team so the ones that you saw there for for for so the the construction call swan and the room one those are ones that i've created myself um the production tracking and the coordination tracking those are all ones that my myself or my vdc staff and team throughout the region create so we uh all of our staff uh on the virtual design and construction front of turner um are trained in power bi i say train but really it was just myself and dan who's actually here uh chatting away down there um kind of taught ourselves and then taught them so we would have really formally but that's how it works right why not okay so i'll keep an eye on the chat but i'm gonna jump in a little bit so i'm trying i'm using houses as an example just because they're smaller they're a lot easier to see on the screen and there's a lot less data coming out of them and so the very first thing i want to show you how to do is to take a schedule that you created in revit and be able to analyze it in power bi it's really simple really quick but it's extremely powerful okay so what i have here is a house as i as i mentioned before it's it's actually a renovation edition so on left hand side basically is the is the existing house on the right hand side is the addition as you can see um the addition was bigger than the existing house and so that's why you see that 60 30 percent sort of variation there but what i want to do is is i want to look at first because i think it's an easy concept to follow is i want to look at rooms and so if i look at the first floor plan here and i zoom in you can see i've got a bunch of rooms there's a kitchen foyer my existing rooms have an x on them etc but let's say i want to take my room schedule on i pump it into power bi for something i don't know what but we're gonna use it for something okay so the first thing you're gonna need and this is not the case for every example i'm showing but the first thing you're going to need right now is you're going to need a schedule okay so if you don't know how to make a schedule i'll show you right now you're going to go up to view you're going to go to schedules and you're going to say schedules and quantities and then you're going to pick your category or multi category which i'll talk about in a little while but i'm going to click rooms and you can see room schedule too because i already have one created but that's okay so i'm going to say rooms and then i'm going to give them some some information so i want to what parameters do i want to be in my schedule so the first parameter i'm going to want is going to be level you know what level these rooms on first floor basement second floor etc i'm probably going to want to do number and perhaps name and where's name let's see name and then perimeter because why not let's add some perimeter and then last but not least let's do area okay and then we'll click okay and just like that you know we have a schedule which now we can zoom into oh my god man it's uh it's one of those things that you're zooming into a schedule you're like that's a new feature but when you're teaching rabbit man it's great to have or when you're on a 4k monitor okay so as you can see here now we have levels numbers name parameter and area and it's called room schedule 2. so now right now if you wanted to export this and let's say pull it into excel what would you do you would go to file right let's go to file oops file export and then we roll all the way down and we'd say reports and then schedule don't ask me why it's 17 clicks deep i know autodesk likes to hide some stuff but man i don't know why schedule exports has to be so freaking deep um so then you click schedule and you go about your merry way um but what i want to do first i want to just explain some things that are going to make our lives easier on the power bi side to the schedule that we may not you may not want to see or you may want to see them in revit but you don't want to see them in power bi so what i suggest doing is if if you do use this process and you want to keep and you want to keep this um the schedule up to date and your visual up to date but you also want to use this schedule on like a set of drawings or something i suggest you make two schedules because one of them the power bi export one you're going to format for power bi the other one you're going to format for documents but the reality is it's in revit so they're always going to be up to date right so there's a couple things that we need to do the first thing we need to do is we need to get rid of our sf for the area because on the power bi side we want it to be able to add and subtract the square footage and if there's an sf there right it's and you've anyone who's tried to do this in excel you probably know um you know it's it's it's not going to add it's going to see it as text you can't make it a number parameter and so uh we could technically do that after the fact but i can tell you right now it's always easier to try and get the format of your data correct um during the export than it is in the power bi side and so i'll show you what i mean when we get in there because you can you can manipulate data when it comes in um but it's way easier if you have it already set the way you want okay and pognan just said he hates imperial units i do too but unfortunately we're forced to use them look at all those 256 of an inch huh nice okay so what i'm going to do first is i'm going to get rid of the sf so if you don't know how to do that i'm going to show you right now i'm going to go to fields and then i'm going to flip over to formatting i don't know why so so this is and this is just for some reason i'm on a soapbox tonight um for some reason i understand that all the tabs like if you see here the edit edit edit edit all these tabs or all these edits here are the same tabs as this and it makes sense that like if you want to jump right into that tab you click it but for whatever reason and i've been using revit obviously for a long time i always always go into fields first and then flip over to the tab i don't know why maybe it's just not intuitive the fact that this is the same as that but if you guys also do that then let me know because it drives me nuts but i can't i can't condition myself any other way okay so first i'm going to go to area and what i'm going to do is i'm going to go down to where it says conditional format i'm sorry not conditional i'm going to go down to field format so field format is going to let us override how revit explains an area so your project units are usually what say so you know if maybe it's um a square foot maybe it's cubic foot maybe it's cubic yard whatever right and so those are there those are those are project globally set but you can actually override them in any viewing schedule so i'm going to uncheck use project settings and then when it says unit symbol i'm going to say none okay the other thing too is square feet is always rounded i don't know if you notice that but by default i should say so if i want to say two decimal places or three decimal places i can also add some some accuracy i guess to it and i'm going to click ok and then the other thing i want to do is my perimeter so power bi if you really wanted to you can try and get it to recognize lengths like that but i would suggest making your life easier is to use decimal feed or some version of that for your perimeter so i'm going to go to perimeter now i'm going to go to field format i'm going to uncheck project settings and i'm actually going to say decimal feet and i'm going to round it to two places maybe i'm not going to have a unit symbol i'm going to click ok there we go so now we have perimeters and areas we know their feet and again thanks whoever said imperial sucks i know it um 153.3 is super awkward to figure out in feet but uh when we're looking at the analysis of it it's perfectly fine so we did there is we actually got rid of some junk that just won't read well in excel or power bi for that fact okay so that's funny it looks like a few people do exactly what i do isn't that bizarre that's i mean it's just like it's a you know user experience sort of thing that i just can't get over you know they actually don't need they should just have like something edit and then let us jump into it why give us all this option i mean come on okay so now this is pretty much good for power bi the only thing we need to pay attention to is during the export there's two settings we need to pay attention to okay so let's do that now the other thing too actually i should notice is or i should mention is um let me just go to uh so under appearance the blank row before data is something that you could leave it on because that's really easy to remove in power bi but i actually like it turned off the less crap you have to do in power bi the better okay so the more formatted your data is for the program the easier life's always going to be okay so i'm going to get rid of the blank row so now we got ourselves a really really simple sheet okay so now all you have to do is you have to go to file file's really slow today export reports schedule okay so now i'm going to throw this uh in a folder here i'm going to call it room schedule 2 because i already have a room schedule there and remember it's a txt it's a delineated file i'm going to click save but before i click ok here there's a couple things i want to do okay i want to get rid of export title and i want to make sure export column heads is there and that should be it so the reason i'm doing that is if you don't get rid of that your first row of your data is going to be room schedule 2 that big room schedule 2 and it just messes everything up in power bi because what you want is you want power bi to recognize level number name perimeter and area as your column headers and the second you put a piece of data in front of that they are no longer your column headers and you'll see visually what all this means in a second so you want to turn off export title apologize now i'm going to click ok all right and there it is it's gone so now if i if i go to that file in case if you guys have never done this before i guess it's probably a good lesson to open it so you can see so this is actually a text file that is a tab delineated so when you open it it's going to look like garbage right so i mean i guess this one's wrapped okay but sometimes you know it looks it looks crazy but you can see it's this format that that tells the computer what columns and rows are not much you can do with it there you know it's a text file so normally if you've gone through this process you probably would open excel dump it into excel and do something with it well luckily for us power bi reads these files without having to go through excel so we can actually keep things up to date we can actually connect directly to that txt file which is really cool so if you think about it so now i have that.txt file sitting there i have my model here once i link it to power bi if i make model changes all i have to do is is export to that txt and press refresh and power bi and i'm and i'm updating that if you wanted to you can definitely create some sort of a system where that automatically happens on its own to me it doesn't make a huge difference okay so now i'm gonna create a new power bi desktop and so uh for those of you not sure or familiar at all with power bi it's a microsoft project or microsoft software um i believe it stands for business intelligence power bi and i believe for most people um if you already have microsoft it's definitely free but i'm pretty sure it's free for most people in general there's a premium version for different things but i'm pretty sure you can use you can use it for most of the features for for free but um definitely check it out most of you probably haven't a license to office and i think you can get it through there um super valuable tool and i don't think you need to pay for the premium for most of what you'd be doing with it and so this is what it looks like and so whenever i try or whenever when i was first learning power bi um basically the way people would explain it is it's like a pivot table in excel okay i don't know about you but i'm not an excel wizard at all i can get around it sure but um i honestly cannot create a pivot table i've tried it and i've done it maybe once or twice that that it's been successful but i did i didn't really understand what the heck was going on um and so uh yeah i i don't like explaining it that way um to me it's more it's more of a really really easy way to make bar graphs and pie charts that you like that you would normally make in word or excel so because as a typical office microsoft office user that to me is way more easier to comprehend is you remember like whenever you created a a line graph in you know biology class something like that there would be a grid a table and you would have an x and y and that's how it filled in right and to me that is is is a little easier to start the concept to understand so what we're going to be doing is we're going to be bringing in data and really it's just columns and rows columns are going to be your essentially your parameters and the rows are going to be your values basically what it comes down to okay the nice thing about it is it can it can line it all up and then because it's it's it's its job is to visualize that data you can start slicing and dicing and filtering that data to get what you need out of it um so with all that being said um we're gonna bring in our data right now this is a brand new power bi desktop sheet um you can see in the middle is sort of your your work your work board on the right hand side you have fields your visualization so that would be where your line graphs bar charts uh tables whatever that's kind of where you would start and filter them and then they added in recently they added a page level filters here which is really cool and i'll show you how to use that um so before we even get into each one of those what i'm going to do here is i'm going to link our data so the very first thing we're going to do is we're going to say get data okay and so right away there's a whole bunch of file types that you could use okay and so you can see it has a whole bunch of file types and a lot of these are actually um you can see azure sql pdf you know they're they're mailchimp i mean there's a lot of connections that can be made and the really cool thing about these connections with applications is that usually they're dynamic connections so for example quickbooks online or or mailchimp you know those type of programs those aren't you know exporting data and refreshing those are literally when you press refresh on here it's refreshing data from the cloud immediately so you do have a truly dynamic and up-to-date dashboard which is pretty sweet alright so let's do text.csv and click connect and then room schedule 2 is the one i'm going to use so these are my txt files and i'm going to click open now you'll notice right away look at this it already looks like how i want it to be and even because i got rid of that blank row and the header power bi is already recognizing the first row as our column headers and that's key because if you didn't have column headers for that then the parameter name so to speak for these would be column one column two column three column four and you'd have to make power bi recognize that and then it's also recognizing parameter and area so i'm gonna say uh load and now it's there look at oh what happened nothing what you will notice is all the way on the right hand side room schedule 2 is right here and we have area level name number parameter and so i didn't i didn't modify the data because i knew it was going to come in the way i wanted to if you need to modify the data you go up to where it says edit queries and you click edit queries of course it opens up on the wrong screen sorry about that and all of your connected data will be here on the left hand side these are your data tables and you'll notice that because of those settings that i told you about in the in revit when exporting pretty much it's accepting the data the way we want it to so it's it's recognizing all of these as column headers and it's recognizing our our um our text as text and it's recognizing our numbers as numbers okay so that's that's the key here too is if you're using dates if you're using numbers if you're using text you need to make sure that in this environment here power bi knows what type of essentially print i'm going to use the word parameter because to me as a revit user that makes the most sense right i know they call them columns i think this is all they call them but um i i like using the word parameter it makes so much more sense to me but anyways um we're basically setting the type so if it's a date you need to make sure that power bi knows it's a date if it's a location you need to make sure power bi knows the location so you have to set those things if it doesn't automatically do it and honestly if you have your data going incorrectly in the very beginning it will most likely automatically do it i can tell you from experience that if you set up and you're trying to use this with a template or a data that needs to be manipulated when you bring it in every time or new formats of it it's a pain in the butt i have a sheet that we use for for tracking um our resources uh within the department and i unfortunately didn't know power bi when i started the sheet and so we've got like 47 of these excel sheets that are for every single project and it's got to be like you know 30 minutes of me doing a bunch of column and row and modifications in this piece of it in order to get it to work in power bi because i didn't know how i should format it to make my life easier okay and it looks like people are confirming that power bi is free so um yeah definitely just look into it i'm i'm almost 100 sure that there's a free version there may be some premium versions but look around and i think you'll find it okay so i just clicked apply and close so now what happened so if i just wanted to recreate the schedule real quick i'm going to place a table so under visualization i click table and i can quickly recreate the schedule so the schedule was level and what i'm doing is i'm dragging from my data area which is our our fields that's my data i'm going to drag level and i'm going to put it under values and you'll notice over here uh it says basement and first floor so it's starting to recreate the schedule a little bit if i wanted to put number next i'll put number down now you'll notice what's happening is it's making it longer obviously first floor there's the room number and there's the the level first floor basement etc right so now i can also put in the name the area and the perimeter actually i think i did a perimeter first and let's go just okay so now notice what it's doing and this is without any settings it's totaling those which is great so we know the perimeter of all the rooms in the project is 1300 or 1032.69 feet the area is 3144.92 okay pretty cool and we just created you know we created our schedule so now obviously we can do a lot more with this but i just wanted you to see that connection that was made there so we we basically took this data from revit from the schedule we exported it and now we're connecting it to power bi so now if i was to make a change and you know maybe i should let's let's put a few more visuals in here and then we'll make a change so you can see it so what's something else that we might want to see let me put in a donut chart because everyone loves donut charts even though um my buddy dan who's actually on here today would argue that they don't tell you uh or they're a little misleading and i i do agree with that they're actually kind of a little misleading okay so let's just say we want to see now the area per level so what percentage of the area um is on the first floor what percentage of the areas on the second floor or basement whatever it is um so what i'm going to do is for for this guy i'm going to click it and my legend which is going to be sort of the pieces of the pie is going to be level so i'm just dragging things into the data fields and you can see now it's already making a legend basement first floor and my value is going to be area so i put that under values so just like that i know that my base you know based on just the information i have here or just the information i brought out i'll make it a little bigger if you guys can't read it it says here uh my first floor is uh 100 1.3 you know i can format that afterwards uh so 33 of my area is on the first floor for the basement and then uh 68 of my area is on the um on the first floor which makes sense so then i could also let's let's look at perimeter too to see just for fun um what the difference is there so i'm going to throw another um donut chart i'm going to do a level for legend and then i'm going to do perimeter for my values so the perimeter you can see we have uh 814 feet for the for the first floor and uh 217 feet so so i don't know what you're going to do with this data but it's there right which is kind of cool and then what's really neat about power bi and this is what i like you know for those of you looking at this you may be like well i just made a graph you know it's a graph like any other graph you can make in in excel right but the really cool thing about it is filtering and slicing the data is extremely dynamic and it looks really cool but it's easy to do so like if i only want to see first floor now in any of these graphs i can click those those values and you notice every visual on this page is being filtered by what i'm clicking you can also put in what are called filters so so over here on the visuals there's something called the slicer so i'm going to place the slicer and if i want to slice it by level you notice i can put in a slicer and i can say i only want to see all the rooms on first floor no i want to see how learners on basement right so now you can quickly quickly analyze and start filtering and slicing and dicing your values which is super or your visuals which is super super powerful right um last but not least let's throw in a bar chart because what's a graph without a bar chart and let's say maybe for this one we'll do room name um room name by area or something like that so now we can start looking at this and seeing uh what rooms are taking up the most area okay notice the existing basement is taking up the most area so and this is where the slicing comes into place so if for some reason you wanted to see how room areas are comparing to each other um you know you may want to take out the basement real quick to look at that visual and how it changes so i can just click first floor and now you'll notice now we get a better sense of the existing bedroom versus living versus master versus blah blah you know you can start looking at this way if we want to look at it by number uh by room number for example that may be even more valuable so let me go back in here and let's do let's do room number instead of area i mean instead of name oh yeah just the wrong thing that is area i'm gonna do room number as my that's my axis there we go so now you can start looking at it and saying okay room 103 is is is 253 square feet below and you start comparing the room sizes you want to know what they are you can click them or you can even add the data to the to the tool hub so right away you could see and again i'm just giving you examples of how it could be used i have no idea how you may find it valuable everyone has different uses for this stuff but the reality is once you have the power to do it is when it starts taking off as you saw i just showed you four dashboards i've got you know dozens more from all kinds of different things from uh you know resource management to um i have all of my financial stuff through quickbooks and mint coming into power bi it's just such an easier way to visualize your data okay so quickly let's make a change and show you how that process looks so let me go back into uh revit here let me go to my first floor and then i'm a little i know that i saved ads with this file but man when you take a final uh construction document like this and you move it you never know what's gonna happen but really i'm just gonna i'm just gonna move there we go elements will be deleted that's what's gonna happen i had this joint on let me try again so i'm just gonna select this wall i'm gonna move it i'm not gonna have this joint on i'm gonna move it three and a half feet over there whatever so i just pulled the wall this way now obviously the rooms change so what i'm gonna do and based on based on the process that i've set up right now is i go over to my schedule i go to file wait a second because the file button in revit is annoying go to export go down to here and then we'll go to reports schedule because there's 17 clicks to get to it and then you just overwrite room schedule 2 but but you have to realize that revit doesn't save your settings in the csv export so notice export titles are still checked so you've got to make sure that you uncheck that or else you're gonna have all kinds of issues okay so now i'm gonna uncheck that click ok jump back into power bi and all i have to do in power bi is click refresh refresh maybe not oh i guess it did change i didn't see it change no maybe i just didn't see a change let me try again export report schedule room schedule 2 save yes uncheck oh you know what the rooms might not have actually changed size so that won't be good so export column headers click ok all right i gotta go back to rabbit anyways uh you get the idea of the process itself see the roof i i was messing around with some floors and stuff here so as you can see the uh the rooms actually didn't change size so let's do instead of instead of dealing with that let me uh be nice if the actual data changed right let me take this wall too and stretch it and see if the rooms change size there that one should work ignore some warnings yep ignore warnings everywhere okay so now these rooms should have got a little bit bigger i got something going on this model when i was messing around with it before so you guys should see how many warnings i'm i'm ignoring right now you guys would be driving yourself nuts okay so now the room levels the room area has changed so now if i jump back into my schedule over here and i go file wait 30 seconds export reports schedule room schedule to save overwrite uncheck export title click ok and now we go back into power bi and refresh it there we go there's quick but it changed okay so that right there the file menu is so painful yes i know my god the file line this was painful i i'm even on the vpn because i was afraid of that but it doesn't it doesn't matter okay so notice that process now so it makes it really really easy so whatever you want to do with your data um if you have an existing schedule or you have this schedule set up you're freaking good to go this is fantastic okay um so that's how you connect them that that's that's as easy as it is you set up a schedule you can export it and you connect it so now i want to talk about a little bit of a different process that i think um some of you may find valuable because it overcomes a limitation of revit when it comes to schedules um and so if there's not any questions i think i saw a couple just reading on here neil said replication was scheduled to start yes neil definitely replicate the schedule i think i mentioned that before if you're going to do this process and you want to use a schedule on your documents but you also want to schedule to pump out to here you may want to create schedules that are for export just like you would do like a navis view or something like that or lumion view make dedicated schedules for that okay the data will always be updated right okay so now let me jump over to this guy which is one of my newest and latest creations okay so right now take the money take the uh my head is kind of in the way there huh guys move my head over a little bit there's really no there's really no place to put my head that's not in the way here that's kind of okay um uh somebody so juan just asked what about a key combination for export the schedule um i think you're asking is there a key can you do a keyboard shortcut for schedule exports i actually don't think so um i'll check right now i'm gonna i'm gonna so for those of you that don't know if if you go under view and over to user interface you can check out all of your keyword shortcuts and uh let's see i want to say there aren't any ways to do that but let's see oh right here let's see schedule application menu reports let's give it a try so it looks like we may actually be able to do it so i don't know if s let's do schedule sx not only sx is anything right oh it is oh it's the points okay so let's do scx okay scx so now if i type scx or i overwrite a level there or you gotta use shift you have to use shift or something but yeah i guess you could uh you could you could assign it to your shortcut great great point there okay so now let me jump into this guy so story about this one so as i mentioned last week i'm in the process of designing an edition for my personal house and um and we're really early on part of is obviously figuring out what what the budget even needs to be and exploring costs and stuff like that and so um what i did here and take the numbers what they are i'm in connecticut but i'm also using rs means for now i haven't verified any of this information but uh what i wanted to just play around with was the idea of building unit pricing into the model and how we can utilize that moving forward so as i mentioned before at turner construction where we do cost estimates all the time we're using assemble as our tool assemble is phenomenal for it um what's great about it one of the biggest advantages of using assemble is that it's not like revit where it has restrictions when it comes to scheduling so assemble has an inventory that's basically a schedule but you can have every single cat category that exists in your model in that inventory so it makes it really easy to see everything if you guys haven't tried uh connecting or creating a schedule for for like something like this with cost estimating one thing that you'll realize immediately is that a multi-category schedule cannot put system families in it so what does that mean that means that a multi-category schedule which should be the kind of like end-all be-all of schedules for revit it's moldy category you can't have uh walls doors oh sorry doors you can't have walls floors roofs um whatever is a system family not a loadable fan which means if you want to do a full cost estimate within the revit environment you need to have schedules for every single one of those things so if i jump over to to the uh split level redux this is the edition you guys probably saw it last week it might have changed a little bit we're sort of again really early on messing with designs um but if you go down here you'll notice that i have uh floor quantities roof quantities wall quantities door quantities and then up here i have somewhere and you have a multi category construction estimate so this was the multi category one okay so if you've ever tried this before you may think yes i found a multi-category schedule i can put my walls doors windows i can make it to calculate totals i can build in unit pricing and i have a takeoff boom right there no there is no way in revit as far as i know if you guys know of a way let me know to create a schedule that has every single category and a model in a schedule okay that's kind of cool all right so i sort of farted around with how to do this like how can i play with this idea so the first thing i did just so you know is is uh trying to simplify things and again i'm just working through a process here and this is my own project so i don't really care and you know anyone who's innovated and sort of tried new things you may know um you know that that you know the mock-up is is rough right so if this was ever to move forward you know i do different different different ideas of how to approach something like this the parameters etc neil i know you said dynamo yes dynamo yes i got it so what i did real quick is i made a pri a project parameter that's an instance parameter and it's called unit price and takeoff unit and the reason i did that is um the takeoff unit is going to be valuable on the power bi side for me to tell power bi um you know what what i'm multiplying my unit price by so what i'm going to do here is i'm actually going to pump out the information and i'm going to pump out whatever exists so for every element that i'm exporting and using in power bi i'm going to export a perimeter an area or sorry just an area and a length i believe so that would be you know linear foot and square footage right now i'm doing kind of i know it says unit price but it's kind of an uh assembly price so you know i guess that's not the right term but whatever um and so and then each right so number of doors number windows and so so what i did here is and i wanted to use a uh an instance parameter just because remember i'm plugging away and doing all kinds of crazy stuff so it's just easier to select elements and pump in a parameter so anyway so if you look at this what i'm saying here is this this exterior wall construction is 9.89 a square foot okay that's what i'm saying right here and that and for anyone who's familiar with pricing that right now is including the the drywall the the studs it's the whole assembly of the wall um anyways okay so so i added that in and as i was building this or after the fact i went around and i selected items like for example if i'm going to do my deck as a square foot estimate you know i'm i click the deck because i know that has the square footage that i want to use and i i typed in the information 40 dollars a square foot take off unit square footage same thing with windows same thing with doors etc right so i plugged in that information as i was building it after the fact right so then yeah in reality i could jump in and i could say okay i've got my floors here i have an area a perimeter and a unit price i could do a conditional or a calculated column within revit and i get my my total for floors right and then i could do a calculated column in um in my roofs and i could do a total cost for roofs and yes i know i could do that but i want to see it all the same damn place right and so the only way to do that is to get outside of rev unfortunately so a couple things i could do i could go through and do file export and i guess now i could do a keyboard shortcut like control shift e or something like that i don't know um and i could export 17 schedules and then try to combine them in in um in power bi and i could tell you from experience it works but it is very difficult to i mean it's not very difficult it's just it's a different concept to to bridge data together from different sources one of the great things about power bi is that you can pull in data from different sources so i have we have we have um dashboards that are pulling from assemble pulling from excel and pulling from like procore all in the same place which is super cool but um i didn't want to get into that i want to say how can i get all this data and as i'm modeling and as i'm designing this i can just pump out a new a new version of this refresh my dashboard in in power bi and get the new updated cost that's what i wanted to do okay um carlos just mentioned pi revit has a one click button to export excel yes so i know that but i again this is sort of a learning process and hopefully showing you guys an understanding so what i did is i use dynamo because whenever i get stuck on anything in revit i immediately say let me go to dynamo to see if i can overcome it so what i all i did in dynamo is i created using a very simple export to excel type of script i combined essentially um all of the uh system families and categories that i wanted and i pumped out to an excel file and i formatted it using dynamo so that i don't have to touch the excel file i'm just exporting it and then bring it into power bi so let me show you what that looks like so i have it here it's called oh now my head's in the way right [Music] it's called export to excel multi category now let me go to a view so what i did um i know there's a thousand ways you can you can uh there's always a thousand ways that you can do things in dynamo um what i decided to do was just use a node that allows you to export everything that's in everything that's visible in your active view so i just made a view that showed only new and and you know it's only showing what i want to show so it's actually kind of funny this right here you can see this floor and this roof you know planning on refinishing the roof and planning on refinishing the floor so i gave them their own you know their own elements and put them on new and so everything's in new construction but anyways let me jump into dynamo now uh so and this isn't a dynamo class i can do plenty of sessions on dynamo and i have lots of information on dyno so but i do want to sort of walk through what's what's happening here because i think it's just one of those things that can show you um the value and power of all this and then also how it all comes together in the power bi side super cool so what i did here is uh this note here is just gathering all of the elements in the active view so whatever you see in that view that's not hidden is here okay then i'm just getting parameter values so all of the all of the elements now i want to pull off the parameters i'm pulling off area length unit price and takeoff unit that's it okay then i need to remember i'm i'm also um i need to know the i need to pull the the name of the element so the element type and then i need to pull the category out of there right because if i want to recreate this and i really want value to it i want to be able to see floors dolls folders walls windows the categories and then i also want to be able to see the family type so what's the family called etc and those you can't just say get parameter value by you need to use nodes so these two nodes are getting the categories of all those elements and then these are getting the family types of all those elements then all this crap right here is just combining those lists and formatting them into one succinct thing that looks good in revit i want to thank john pearson i don't know if he's here tonight but maybe he'll look at this in the in the future i was struggling hard on this last piece which was getting the headers so uh so out of all this remember i said i said you want to format your data as as best possible outside of power bi so what i'm going to do i'm going to create a new excel file because i don't want to write the other one and for those of you who ever who have ever done in export to excel usually when using the default nodes you have to have an excel file to pump into so i'm just making a blank excel file that i'm going to call live cost estimate okay so remember how we exported the rooms we wanted headers and then values and nothing else so if i if i didn't do if i didn't create the headers i would have had to put those in excel or put them in power bi it would have been extremely extremely annoying so all of this right here notice how i'm taking all these lists that create lists.flat and blah blah i'm just making a list um but the one thing that uh that john pearson's 60-second reference if you guys don't know i added this dumb code block here and so this is for those of you that don't aren't comfortable dynamo aren't familiar with it i'm just going to go get off and geek out for a second now i'll come back um so just by putting this code block in between and and making making this list um actually you know i should run it so you guys can see all this crap that comes out of it because i do know we got some dynamic geeks out there so let's do it so i just ran it really quickly and you'll notice so this list here are basically my headers so i combined my parameter names and then i just manually typed in category family type so now this is these are my headers so i want these to be the first piece of the pie at the top of this list okay and so notice how these are all here right so the first list is actually category family type area length and so that's going to be a row in in uh in excel so my my uh first i could do here you know this is going to be category and it's going to go from left to right so that was important i needed that to happen well if i didn't put this dumb code block in here look what happens let me unconnect this if i just connect it directly to list dot add item to front and i click run of course now it does it actually refresh but either way what it was doing is it wasn't it wasn't allowing the data or the it was placing the category as the first in every single list for some reason just by putting a code block in there calling it an x and passing it through it groups that together and it makes dino put them in front freaking awesome anyway so thank you john appreciate it so now i'm going to go i'm going to browse i'm just going to hit the uh where's that one live cost estimate so remember what this is doing it's taking all the elements in my active view it's grabbing their parameters their family types their family categories and it's putting them in excel so if i click run it's gonna yell at me uh am i in the file i probably edited the file try it again all right now it's not working and i don't know why that that x thing works i i couldn't tell you i couldn't tell you i don't know why uh oh is there no sheet one in this one stupid thing yeah sheet one of those things so sorry so if you guys are ever trying to to uh to use excel with with dynamo or if you use excel in general they decided to go depending on what version of excel you use there's a space between sheet one or not those things so now as you can see this is what was pumped out so i'll zoom in so you guys can see it notice the very top category family type area okay length unit price everything we possibly need but what you'll also notice is check this out there's walls there's curtain panels there's floors there's columns well guess what there's furniture there's doors there's windows this is a truly multi-category schedule freaking a man all right so that's it so now i pumped it out now this is basically your csv export like you would like you would have used in the in the beginning so now i can go into power bi and i can load it in and you see what you have here so now the key thing here what's really cool about this is because because i brought in categories and family types etc um i can filter by that so if i want to see how much just the windows are the percentage of cost etc but remember how i said you know creating the unit price i would have had to do in every single schedule well in power bi you can create measures just like you could in excel it's using dax format um and so i gotta pull open all this stuff now pull this down here and so if i go over to hard costs notice there's this uh there's this column here called total cost i don't know if i can scroll into this oh i can whoa look at that um so the the cool thing um and i don't wanna say the cool thing because anyone who's dealt with dax in this format um knows that it can be extremely frustrating but if anyone out there has made a formula in in revit this is not all that different okay and so what you'll see here is i'm using those units those takeoff units that i plugged in there and i'm saying if the takeoff unit says sf i want you to multiply the unit price by the area if the takeoff unit says lf i want you to multiply the unit price by the length if the takeoff unit says each ea i want you to multiply the unit price or just use the unit price okay and then everything else zero because if i didn't plug in a unit price i don't want it to calculate right now okay so check that out so right there instead of doing it in 17 schedules and figuring all out now in this one dashboard i basically i've got my total cost and it's multi category so if i go back over here you'll notice i have walls doors windows everything in the exact same piece and the other side of it which is pretty neat notice on the right hand side i have total soft cost remember i said you could you could add multiple pieces of data so i'm bringing an excel sheet that comes from revit so let's just say we are using db link or something else like that either way it's coming from revit well guess what if i go under queries i also have an excel sheet here called soft cost and that's just a blank excel sheet that i created um so that one there if i open it up this just piece of text i typed in all the soft costs i can think of i typed in some values but now i can actually combine and and add and multiply and utilize these two pieces of data together which is super super powerful all right so all that being said um yeah i was sorry i'm i was reading a couple comments there sorry looks like we got some some dax lovers out there which is pretty sweet so yeah just some examples of how you can utilize it um so i'm right there you know the one the first piece of it is exporting a schedule um to to a csv and then using that directly in in power bi um the other piece of it is using like dynamo to pump it into excel directly um i think you can actually export to a specific file format too from dynamo but and then combining it all together here and so again these are these are super cool you can filter through them they're really dynamic and the other part of it too is if i modify something um all i have to do is run that dynamo script and press refresh and i'm good to go that's it so all that's to say you know this this isn't necessarily the the best solution for a an efficient cost estimating tool i could tell you that for a fact but um it was a it's a really cool it's it's a simple technique you set up it doesn't take any extra software and uh and it can be extremely valuable like i said if i was to do this um using assemble it would be done in five seconds but all right uh neil said can you trust the direct quantities purposes yes of course you can trust the direct quantity purposes i'm built the model bro uh yeah of course i i mean anyone who has seen any of my talks about model based takeoff and i don't and again i don't want to rant i feel like i've been on a lot of uh a lot of rants today but anyone who's seen any of my any of my uh talks about model based takeoff if you see the non-model-based takeoff happen which i've seen many times i would personally much rather trust the model so i mean the typical program whether it's a bluebeam pdf or like an on-screen take-off or something like that you're scaling 2d drawings and then you're having a human being click and count lights or draw polygons that make rooms right and and go through the list and that's that's that's how it's happening right um if i was to ask anyone out there who's a revit user right now which i'm assuming a lot of you are if i said hey how many toilets are in the project you're working on right now what are you going to do you're going to two things you're either going to right click the toilet and say select all in project or you're going to create a schedule and you're going to count them you are not going to go through and count every toilet manually are you no so why on earth would you want an estimator to do that right you're introducing human error into a process that doesn't need to be human error the big difference so the the thing that you'll see here um which to me the value of something like an assembler a costex or one of those programs is that those keep a connection to the model so here i'm detaching it a little bit right um in in the better 3d model takeoff programs um you can look at that data and say well these doors don't sound right the cost doesn't sound right you can click them and it highlights them in the model and you can verify oh i'm missing some doors or the parameters are wrong so that to me you can't do model-based takeoff without the connection to the model personally all right i saw a bunch of chats coming i knew i was going to how did you get the visual to interact over the images oh great question i was hoping someone would ask that uh hoping but maybe not hoping all right so let me let me jump over and share my screen so anyone who's who's interested in that um it's unfortunately there's not an easy way to do it and i brought up in the very beginning nathan miller's got a thing called tracer which is um uh i don't know a lot about it yet it's pretty new but um it looks like he's taking the database information and sort of redrawing some floor plans using that information and then and then having interaction but we've done it what we do is a little a little more work in the front end but it works perfectly fine so what you're seeing here this this is actually a visual um that is let me pull it up it's a custom visual and it's called synoptic panel so s-y-n-o-p-t-i-c panel and so synoptic panel the way it works is it just brings in images so these are images but they're svg images and so what we've done is in illustrator we actually have to manually draw what you're seeing here the shapes and then you give them a property that you can connect and so the process of it is really just creating the shapes you're seeing here even when i go to this one so each one of these is a shape that has information whether it's a level name a room number whatever it is but what's really cool about it is these are just images that you can zoom in and out of right which is kind of cool um but they're layered images that have data so like the the the polygon has level lo9 on it or room 106. what's super cool about it is as long as that room name number or piece of data is identical to the one in revit so you could use you know usually we're using something uh you usually we're using a custom parameter to do it if we need to but we've done it with rooms before so if your room number is x100 and you name that colored uh box in your your illustrator file as the layer as x100 in power bi that's how it's connecting it's looking for that piece of data and you're saying this equals that which is freaking cool i don't have the example here but we did a renovation that we're working on and um we basically have power bi uh talking with revit assemble and procore for the whole process of punch list and walk throughs and stuff and the visual is exactly what you heard me say is it's all the room numbers and then it's connecting to the rev room numbers and then procore has the same room numbers for their locations and so we've got a fully dynamic connection between all these pieces uh skyward yeah i believe you can do this using any vector program that you can name a layer i think we tried in bluebeam and we weren't having success i believe our the easiest program for us to do it in was was illustrator but i think anything that can make an svg that has layered named layers should work because that's really all they need they don't need you know they don't need a uh uh illustrator file they just need the the svg um what we got here a lot of chat sweet guys awesome for the most part quantum reports and rabbit are trustable [Music] so yeah uh mario just mentioned you know quantity take off the revit and overlaps i mean it's all about yeah obviously you have to you know the garbage in garbage out with anything right and so um to me like i said before the biggest key to that is is being able to connect your your quantities to the model so if you're getting an exterior wall ratio that seems wonky right i mean it's really easy to just measure the perimeter of the building and the height and know that the area should be seven hundred square feet or seven hundred thousand square feet but then your takeoff is showing five hundred thousand that's when you look at it and you say oh it's actually not pulling the curtain walls correctly right so you can't just blindly export data and take it for quantities that's just wrong um which i'm kind of doing here but of course i'm the only working on him personally you know i'm making the model i have that connection back and forth the other thing i guess you could do too is you could also bring out the uh guids into assemble and then just uh use that to select elements in there but whatever um there's a a website that can x convert dwg to svg yeah i think dw there's a way multiple ways to do that with dwgs i think as long as the layers stay the same you know you're uh you're good as long as they're naming awesome well guys thanks for hanging out i know it's 8 30 i actually thought i was going to get through even more than this so maybe i'll do another one in the future um i hope this at least gives you an introduction to it again if you like if you like this channel if you like this show if you like what i'm doing here make sure you subscribe subscribe to me check out uh therebykid.com or bim after dark i do have um a private online community called bim after dark so if you go over to community.bimafterdark um i'm there with a hundred other people right now which is freaking awesome we have a hundred members isn't that crazy or just just oh just under a hundred i'm gonna celebrate when we hit 100. um and i also have four full-length courses within that community so check that out too let's see anything else any other questions before i head out i know there was a there's a lot of questions which is pretty awesome no awesome well thank you guys so much i hope this is a good introduction uh for those of you who maybe don't know what power bi is don't understand that connection or now at least you understand sort of what power bi is how you can quickly connect to it and i urge you to try it out uh start messing around with it and i think you're going to find the value in being able to pull your data out of your projects and visualize it i'm sure everyone has a different way that they can use it mine are just some of the ones i'm demonstrating here but i hope that's what you take away with today so the the replay for this uh will be live right on the channel and it'll also be live at um the revit kid so just go to live.bimafterdot.com and uh with that i want to bid you all adieu uh i'm supposed to have a guest next week i'm just confirming with him it's gonna be an exciting one so make sure you check that out and i think it's actually going to be at a different time so pay attention to the channel as well as the website to make sure so with all that being said i want to bid you all to do thank you so much feel free to reach out to me uh at the revit kid on twitter um just go to therebykit.com you can find my email and contact there and uh yeah everyone stay safe and enjoy yourselves and go have some fun with data have a good night you

Channel: TheRevitKid

Views: 49,232

Keywords: revit, revit design, architecture, architecture design, autodesk revit, revit tutorial, lumion, lumion 3D, lumion architecture, architect, residential architect, revit tip, revit tutorials, revit architecture, BIM, building information modeling, autodesk revit tutorials, architecture tutorials, revit 2022, revit 2021, revit 2020, bimafterdark, bimafterdarklive, Connecting Revit to Power BI : An Introduction to Visualizing Model Data, revit to power bi, revit power bi

Id: 051WFw52C5g

Length: 70min 25sec (4225 seconds)

Published: Fri Jul 31 2020