Tracking Field Production with Assemble System and Power BI

JON LA PLANTE: Welcome to tracking Field and Production with Assemble Systems and Power BI. This is the safe harbor statement that we have. That's it.

Meet the speakers-- so my name is John La Plante, I've been working with PCL Construction for 17 years. I'm the construction volume manager. I've been working in VDC since 2009.

SEAN ZOOK: Hello, everybody. My name is Sean Zook. Happy to be teaching this class today. Really excited about this topic, really excited about what an easy solution that it is to implement, what a great solution it is to implement because it builds upon a product that a lot of people already have. So it extends that functionality.

And then, again, you know, it's helping push the growth of BIM use in the field, which I'm very passionate about and excited to share with you today.

JON LA PLANTE: So today, you're going to learn about Assemble Systems, how we track progress in there. How we can use Assemble Class Editor to manipulate the data, and then also use Power BI to display that data. And then we can also see how our trades performing or underperforming.

So site tracking-- what's the old, what's new? So the old way-- we use the old style quantification where we track on-site, using quantities using a highlighter or a printed drawing, or a CAD file that we mark up with dates. Or just a PDF with coloring. And also track our quantities using Excel.

The current way, we are using Assemble systems. Using Revit or Navisworks model. Also using the iPad app on-site. And then using this app allows us to do track real time, field production for the site office to view, as well.

So here are some examples of what we have done. So on the top left, we have a mechanical system, just piping being tracked. You can see that the trade's slightly underperforming there, and they're going to be running later than the scheduled date. And then, on the bottom left, we have steel tracking progress-- you can see they're slightly late by 10 days.

And then, we can track that, as well. You can see how much progress they've done. And also section the building up.

So how do we get to this new process? So we use programs such as Revit, and Revit add-ons, Tracer program, by Proving Ground. And also Assemble add on. And then we use Assemble Class Editor, Microsoft Excel, and Power BI Pro.

So in Revit is our main hub for the export data. So we export that data to Assemble into a database file using Tracer. And Assemble actually hosts all our data from the field and from Revit. And then, we use Assemble Class Editor to manipulate that data and to fill in our custom properties that we have. And then, Excel is used to track all the activity, and Power BI is to display all that activity.

Next is the Revit-- this is where we start off. So we'll export the data using Assemble Systems, the add-on. And then we'll export the file using Tracer. With Tracer, you've got to make sure you're into a 3D view, and you are exporting the view as a filter active view. And then, that field gives us a SQLite 3 database file.

Assemble Systems-- Assemble, so here, we'll need to create some new custom groups with properties, view to export to Excel. And for filtering and data conditioning the model, so the property can actually be exported from Assemble into our Excel sheets. And then, this data conditioning is also used to tailor our views in Assemble.

So we'll start off by creating a new project and clicking on the Edit Project. And then, we move on to Manage Assemble Properties. Then here, we'll create a couple of groups and some properties. What we need to create is one called Progress Tracking, one will be Installation Status. So we usually do that by text, and then installation is by date and time.

And then we move to the Power BI parameters. We track our own quantities-- so we have a separate PCL quantity that we do. And the units. And then we also use Revit IDs. So Revit ID is so you can link your model file and your Excel data together with the symbol, as well.

And then, your building level, so you can sort your model by level. Same with location and element. And if you are doing a self-perform concrete work, we like to use port numbers so we can segregate the views per pour. It just makes it easier for someone tracking on-site later with the iPad app.

So here, we can see on the right-hand side, me creating a pour number one view. So we can actually go to the visibility settings, and we can set it so we can sort by pour number or one.

Selected, and updates. Now you only see whatever's in that pour one. And it gives you save that view. And from there, you go to your main screen of your project. And you can see the pour one view is set there.

So on the iPad app, you can go right to that pour one, you select that, and you can select all the elements in that pour and give it the quick, fast installation date, and status, as well.

Next, we can move on to the columns. So in this inventory view, we want to make sure that we can actually see the custom properties that we created.

So from there, you go to the columns, you take everything out on the right. And we just add in all the ones we created here.

Next, we can create another view and actually separate the stuff we don't want in it, as well. So if you just like for expert view, for instance, we want to take out all the stuff that's not being tracked. So that's that visibility settings. And then, updating the parameters.

Next, we can actually go ahead and update assemble, so we can click on let's say the slab, and then we can give it a building level, building location, building element, and pour numbers. This is just data conditioning your model, so we can say later on what things are what, and which port they belong to in what part of the building.

Once that's done, we can move on to Assemble Class Editor. From here, we put our site address, and then we would say which project it's part of. And then we got to do that custom view that we created. And then from there, we connect to it.

And it brings up this window. And from here, number one, you have to select only one element for number two to be available to select. So once that happens, you can select that, then this properties window shows up in the middle. And then also, the little green icon at number three, we can actually click that and it gives us a calculate property window, number four.

And from there we can select the property you want to fill in. So for instance, this is Revit ID. So we'll click on that. And then we go down to number five, where it says Source ID or Instance ID. And then we can import that into the equation line in front of the equation. By clicking there, and then hit the little purple icon number six, and it brings that instance ID to there.

And we can double check that pushing the green check mark. And if it goes ahead, it works, it's good. And that means we can hit the little blue icon, which is the calculator. And that goes ahead and updates everything in that Assemble view, to update that Revit ID.

So once that's done, we go back to Assemble the double check. So we've done that. So we can see that Revit ID here is all filled in. We can see that the little comment boxes, meaning that there's multiple items with different numbers, different values.

So from there, we can go ahead and data condition the rest of the model. And we can figure out the rest of our quantities, we can do that in Assemble Class Editor. So we can grab different calculations, for instance, slab on deck. We can't use that quantity from-- the concrete quantity from Revit, because it's wrong. Because it's taking up the whole queue deck, so we have to make sure we take half the queue deck, plus the concrete on top. So that's why we have our own quantities the way that we do that.

So once we do that, we can update the rest of the data conditioning for building levels, and stuff like that.

So once that's done, we can go ahead and export the data to Excel. So just click on the Export, and then we go to Export Microsoft Excel.

So in Microsoft Excel, we need to create a tracking log. So we will create a workbook which has six sheets in it. One will have a tracking log. Assemble exports is what you just exported, gets linked into this one. And then, we use quantity per day calculation from the Assemble export and the tracking log for the dates.

Then we make one for projected final date, to figure out exactly what we're going to be heading to. And then, we need to create one with holidays with descriptions, to understand which those days are. And when we do that, Power BI does not like dates with text, so we have to create another one called holidays without descriptions. And just copies the dates over, basically. And we use that to create our final actual working days in Power BI.

So track and log-- here we like to track the date. So we take the dates from our schedule, plunk them in here. And then, we take our planned quantities per day, which is the quantities per day calculation sheet. And then, we actual track the actual quantities per day, which is calculated from the Assemble export. And then we take the percent complete, as well.

So to get our model data into here now, sorry, our Excel data into it, we need to go to data, and our input or workbook from Excel. And we need to hit Transform Data before we hit Load. We need to take anything out in the data that has multiple values blank spaces, or duplicate or Revit IDs in there. Because that'll just mess up our view in our Power BI.

Also to note, lessons learned, if you have a-- steel, for instance, has only one item of H-beam in there. It will count that as two. So we got to go to the source ID, and remove those source IDs individually every time.

So once that's imported, we take that tracking log and we go the far right out of the import, and we create a date and count column. So this basically summarizes the info from the import. So the dates come from the tracking log, just like an equal sign. And then, the count is actually counting the installation date with the status and the quantity.

So we use a SUMIFS statement in this occasion.

Next, our quantity per day. So we take the start and finish dates from our log, and we calculate how many days are in there, using the networking days. And then we take the quantities from the exported Excel, and plug into here as a summary. Then we just take the number of days-- sorry, the quantity divided by number of days, to give us our actual quantity.

And then, the units come from the units from the tracking log.

And next, the project final dates. So we only use the two out of all these columns that we have here, is the two final ones. The projected final finish date gets put in there, and then the days overdue, or under schedule go into there, as well. Then the holidays, just that sheet, like I said, it just tracks the holidays being used, and minuses from the working days, gives the actual.

So how do we get this new process into Power BI? First, you'll need to connect to the Power BI template into Assemble. So what you do is you put your Assemble site there. And the project name is your site.

And then, the active view for models versions, we want to make sure that's always set to true. If it's set to false, it's going to grab every version that you have of them all uploaded. We don't want to do that because then it makes the upload very difficult and it takes forever. So we just set it to true, and it does the current one.

And when you do upload the new version every time, it transfers that data to the new version, as well. So you don't have to worry about transferring that data back and forth.

And then, you link all your tracking log with all the sheets, as well, by transforming the data.

And then, to bring the properties from Assemble into Power BI, we actually need to go the Assemble Properties Editor, and we need to actually import what it says here on the available properties. So in here, we say, so PCL quantity, we want to put the PCL quantity underscore assemble property. No spaces, just with the word the way it is, how I've written here.

So what this does, it brings all your custom properties into Power BI and you can view it.

Next thing, we want to bring our model element, or mesh data into Power BI. To do this, we've got to use an SQLite 3 database file, which was exported from Revit at the beginning. Then we need to say, always have database equals. Then we have to say the folder location where we stored it, plus the file name, plus dot dB.

And once you do that, another window pops up, and it'll ask you for login credentials. You don't need to do anything, you just have to select the default custom. And then, just enter nothing, hit OK. And it logs you into it and uploads it.

And this window comes up after that happens. So from here, we need to grab the document, document parameter, element, element integer, element to number, element Revit ID, and element text.

So we use these to actually link our model data to our Excel file, and to our sample file-- our project.

So in here, you see the red is all the assembled information that's in our model in Power BI. In the blue is our Excel tracking log, and green is our model data from Tracer. So from here, we're linking basically all the Revit IDs to each other, and also anything that matches. So the more probabilities you have matching, the better-- which makes your data talk a lot better when you're actually manipulating your data in Power BI for views.

Also to note-- lessons learned that you have the active view set, like for instance, Revit ID we're doing here. So you have that one set all the time as active. Then your view for Tracer will work a lot better inside Power BI.

So in Power BI, the next thing we need to do-- you have tracking log on the right hand side-- we need to create custom calculations using DAX formulas. So in here, we need to calculate the actual running total dates, working rates, work rate with units, and stuff like that. And days elapsed remaining, and dates. Just to fill out these columns in the middle here.

It's basically an overview of the dashboard, what it looks like, where the data comes from. So the top, we have assembled data, anything red. It all comes from Assemble. Blue is anything that's Excel, and that's whatever we put into Excel, this basically just updates from there.

And then, green is just the Tracer that we exported. It's just a viewer.

Next, we get to the new process by updating the dashboard. So this is a short video of how we do it.

Here, we're just showing the data is not updated yet. Now, we're going to Assemble and just updating the data.

Do you know what here, don't put any spaces in your data-- in your dates. Won't work. Here, you can see all the elements updated for status and dates, as well.

Also in naming the file, make sure it's always named the same thing. Here's that export file. Need to make sure everything is not grouped together.

Here, we see the data is still not updated here. So we need to refresh our Excel sheet to update.

Here, we can see the data is now entered and refreshed.

So what you're seeing here is in real time, the video is not sped up, so it's just refreshing now.

Now, we can see the data is all updated, view's changed to green, this has been installed. You can see quantities installed for dates, and how much they're ahead of schedule. Now, we have the option to select per day, what's installed, and gives us total quantities for that total. Those are meters cubed, as well.

And also adjust the date timeline, and adjust our graphs.

Next, we can actually just see by pour, if you wanted. So pour one, for instance. You can also manipulate models, or whatever we want here. So just slabs, or level one slabs. Or just a wing.

SEAN ZOOK: Really awesome, Jon.

JON LA PLANTE: Thanks.

SEAN ZOOK: Yeah, really awesome, exciting. What I'm going to show is a little bit more of the picks and clicks about how to use the mobile app in the field, and also a different workflow as far as getting your model information into Assemble. And what sort of pros and cons and opportunities you have related to that.

If you could change to the next slide for me, please.

So Jon covered a lot of the model prep stuff, as well. He also touched on some of the dashboard and dashboard publishing. I'm really sort of skipping some of the early Assemble setup stuff. I will show a bit of it as I walk through some of the demonstration videos, but primarily focused on using the iPad app in the field-- how we then sync that back, refresh our dashboard in Power BI, and publish that for the team.

Next slide.

So like Jon, we start with Power BI-- I'm sorry, an Assemble project site-- you'll have to hit Play, I think, on the video for me. Each one of these-- so typically, we'll build a separate Assemble project for our team that's just built around tracking. That way, you can control team members that are invited to that project. And then, also, as Jon was talking about, not have a large depth of model data within the project, from pre-construction phase, really just focused on making it as easy as possible for the operations team.

And part of that is building out, like Jon showed, the series of views, and sort of filtered views of the quantities that you intend to track in the field, to help the team focus on the work that they're immediately tracking in the field. And also help, performance-wise, on the iPad app.

One great part of Assemble is your ability to build specific filters in the inventory list, which you're seeing here. So here, we've built a specific view in the inventory based on the tracking. There you can see your members based on their erection status.

You're also showing how we've built that group, similar to Jon, for field tracking, and the parameters that the field team is going to populate. But we've also taken the workflow through Tekla, and so we have some access to some additional properties from our fabrication model that we're utilizing, as well.

The views are colored by their status in Assemble. So as the team is making changes in the iPad, and syncing back to the web platform, they can visualize those changes as they work.

Here, we're going to just quickly select an object. Here, you can see how we've built a data tree to limit the number of responses that a field team member can choose on a given object. And then, the desktop app, you have to enter a structured date for the erection. And that's listed there with the type-in.

You'll get an added benefit, which will show where you can use the current date and time in the desktop, or in the mobile app, which is really fabulous. And again, teams are able to summarize their quantities and sort of status, and see those quickly. And if you control your columns in your inventory filter.

Next slide.

So this one, we're going to-- back out a little bit, if you could hit Play for me-- so this is sort of your overall view of total status. Here, we're going to go into one of the filtered views that was developed for the field team to help them with the data collection in the field, and isolate the amount of model content that they're looking at.

In this case, we're focused on steel quantification, but the concrete structure is quite integral to the basement of this building. So you can include secondary models, like the foundations, if it helps the team visualize the context which they might be seeing in the field.

Here, it shows how we built those filters. We got us down to just erection sequence 51, or 52. And that 52 is read directly from Tekla. And so there's ways, depending on the content that you get, to leverage information that a subcontractor's provided you, or have built intelligence into the data in a model that they've shared.

And so particularly, with steel and the erection sequences, you know, we've worked to leverage all that work that they've put in. Because there's very specific selection of elements, and it's difficult to maybe rebuild that logic into a design model.

Next slide.

So this shows our team. This project had an opportunity to get a bird's eye view on the project site to aid in the tracking.

Next slide.

And this shows the clicks and picks within Assemble, and how easy it is for a field team member to isolate a particular erection sequence by choosing the view in your Assemble project. Here, he's selecting a few objects, clicking on the Properties button, confirming the pieces that he has selected here. And then, he would change their status.

Next slide.

So this video will walk you through what that looks like in the iPad screen capture. So again, here's same views that you've preset up for your field team. You can enter into one of these views. They can sync offline and make those models available offline in the field.

Again, you see that inventory grouping that we did to help the team maybe locate a specific element that's erected or not erected, as well as you can just pick it in the model and immediately get into your properties. So here, you can see that data tree of predefined choices. He's chosen that that beam's erected.

And by default, it pulls up the current date and time. So if you're out in the field tracking the steel erection daily, it's pre-populating, basically, the response that you want to include, which is very fast and speeds up the process.

There's other features that the team will become very adept at using. Key 1 is the multi-select, so if you walk out on site at the end of the day, you can visualize quite a few beams have been installed, you can work through and select those and add those to your selection group, collectively. And then adjust their completion status, and the erection date, in one fell swoop.

And it refreshes in real time, so they can see that change that they've made in the field. There is one additional option, as well, which is the boxing. Which is your icons up at the top of the screen. If you have a large area that was completed in a given day, and you're able to isolate, you can save yourself even more time with the box select.

Continue to add to that multi-selection. And then, again, enter the properties. Choose your status from the data tree options. And pick the current date and time.

Then that'll refresh in real time.

Right there, we were in the sequence two view. If we then move into the overall view, you can see that information that you just collected in that overall tracking view-- that can be useful in a project meeting or communicating to the client the overall status of the job.

So the diagram here sort of illustrates the differences between the workflow that we've been using on our projects with what Jon was showing. And that involves being able to integrate subcontractor's model, and working with assemble through the Navisworks add-on. In our case, the majority of our steel models on our projects come via an IFC export from Tekla.

We pull that into Navisworks in order to regenerate that. And use the Assemble add-on to publish that to the Assemble platform. We then build our dashboard around the data that's collected in Assemble, and as well as some DAX analysis and DAX calculations, similar to the way Jon was working.

Next slide.

And so why are teams working this way? What are the pros and cons to this workflow? For us, there's quite a few pros. One that's really important for the teams in the field collecting the information is being able to understand what the piece mark for an individual steel element might be, understanding in the model and ensuring that they're tracking the right piece in the field. And having and working from the subcontractor's fabrication model assists with that.

Additionally, as I showed, we're able to leverage the steel erection sequence information that can be included in the Tekla export. And that helps us set up those filters-- those view filters that isolate portions of the building for field tracking. On a small building, maybe not too difficult to recreate that. But if you have a significantly large building with dozens and dozens of erection sequence zones, being able to leverage that information that the subcontractors already built into their model is a huge benefit.

And then, the other aspect is if we work with the Tekla model, and if we work with our subcontractor model, we're getting a model that's been developed to LOD 400. So when somebody's standing in the field with the mobile app, they're going to get a greater semblance to the field conditions that they're seeing, if they're using a fabrication model, as opposed to a design model.

Now, the cons related to this is, in my experience, and folks that attend the class and come up afterwards I'd love to hear your experience, but typically, we've been stuck with really the original IFC import into the system. The way the IFCs are made, Assemble can get lost, Navisworks can get lost in changes to that model over time.

And if you had assigned some information date, or erection status, et cetera, it might get lost if you swap and try to refresh your model. And so the con really is choosing the right time to start this process. Is your fabrication model far enough along that you can effectively track it and perform the tasks that you want to do with it?

Another one is, as Jon showed, being able to visualize the model inside of the dashboard with the Tracer application is really powerful. For really large steel projects, the level of detail that this workflow is going to push into Assemble, or try to push into really the Tracer app, is going to be too great. And so you probably want to stick with a design model base where you can hold a course level, or work closely with your fabricator on a solution for that.

And then, also, the details that are going to come from your fabricator, again, very heavy. You're going to have every nut, every bolt, every flange connection, et cetera. Those typically aren't tracked individually. We're really looking at beams and columns, braces, et cetera. And so there is some redaction of LOD 400 content might be necessary, in order to get a performance within Assemble. But then, also, just be tracking the items that you want to track and reducing the number of picks and clicks that you have on your project.

So those are the aspects that I've learned from implementing this process, and really trying to leverage that data from the subcontractors. Again, in the whole-- this process has been very effective and very efficient of our field teams. It's been very beneficial to leverage the content that other team members have put so much effort in and be able to use those downstream, and lean our processes to deliver amazing projects for our teams and our clients.

JON LA PLANTE: Our field teams keep asking for more and more of these. They're really seeing the benefits. They can keep track on their trades, if they're below or above the line.