Creating an interactive 4D construction scheduling dashboard, in Power BI.

In this tutorial you will learn how to bring your SketchUp model’s geometry as well as external scheduling information into Microsoft® Power BI® so that you can create interactive 4D dashboards. The tools we use are:

• Trimble® SketchUp® Pro as the 3D modelling application.
• 3DBI for SketchUp as the custom extension that is able to convert a SketchUp model to a format readable by our 3DBI visual. 
• A Google® Sheets® spreadsheet containing scheduling information as well as relationships between objects and their associated tasks.
• Microsoft® Power BI®
• 3DBI, our Microsoft certified custom Power BI visual, available on AppSource.

1 – The SketchUp model

In this example we use a SketchUp model and convert it to a .3dbi file suitable for the 3DBI visual for Power BI. We also provide the tools needed to get started from either a Revit model, or an IFC file. If you are a Revit user, you might want to check out our 3DBI for Revit solution. In case you prefer to get started from IFC, we also provide a 3DBI for IFC solution.

The model should contain every part that is tied to a task in our schedule. If the schedule will contain groundworks, it is important to also model the ground volume that will be excavated. If at some point during contruction temporary elements will be placed on the construction site these items should also be made part of the model.

2 – Export geometry and data with 3DBI for SketchUp

3DBI for SketchUp is certified by the SketchUp team and can be downloaded directly from Extension Warehouse.

Once installed, open the export dialog found under Extensions > KG-dev – 3DBI.

The export settings used for this use case are:

• Only export visible geometry
• Use “Persistent ID” as the ID provider. This will let SketchUp automatically assign a unique id to each exported object in the resulting .3dbi model file.
• Leave “Must have DC property value” blank. There are use cases where only elements that have a specific DC property need to be handled as unique geometry. For this use case this is not the case.
• Set concatenate to “yes”. This will concatenate all unique IDs of eventual parent elements into the ID of each individual element. This is especially useful when you have multiple copies of components that have child components. This will ensure that each child element will receive a unique identifier.
• You are free to use which character to use as a separator. In this example we use a dash (-).
• Setting “Single Table Json Data Generator” to true will ensure to also export a .json file containing quantities and other meta data of exported elements. Otherwise, only a .3dbi file containing geometry will be exported, no data.

3 – The scheduling data

For this example, a Google Sheets spreadsheet has been created containing the essential information. Every type of information has been added in a separate tab.

3.1 – Task Types

Every Task in a construction schedule can be linked to a limited set of Task Types. Such a Task Type defines some global information that is inherited by every Task linked to it. In this example we add information that specifies if elements should be visible before the Task has started, when the Task is active or when the Task has ended. For example, when doing excavation work, typically, the ground’s volume that needs to be excavated will be visible when the task has not started yet, since the soil will physically still be there on site. It should become invisible when the task has ended and the soil has been excavated. Or, a construction crane, should only be visible when the Task linked to that crane is active, since it will not be physically on site when the task has not been started or has ended. For construction work, on the other hand, it should only be visible when the Task is active or has ended.

An extra Color column has been added so that later on we can assign a color per Task Type to each element in the 3DBI visual.

3.2 – Tasks

Tasks, in their most essential form, have a name and start- and end date. Each Task has also been given a TypeID column so they can be linked to their specific Task Type.

3.3 – Task Object link

Finally, a third table links every object to its associated task. The object ids come from the SketchUp model and are part of the exported .3dbi file exported in step 2.

4 – Get model 3d and data into Power BI

During step 2, not only a .3dbi file got exported, but also a .json file containing information about the exported model. Using Power BI’s default json connector you can load in the information contained in that file. Using this data you have a set of 3DBI model ids, which contains the exact same identifiers that are also used in the exported .3dbi file. This set of identifiers will be needed in order to link Power BI’s data model with the 3DBI visual. 

After the data has been loaded you can place the 3DBI visual, which can be downloaded directly from AppSource, on your dashboard, assign the 3dbiModelId column to the Object Identifiers field and finally insert the exported .3dbi file in the visual.

5 – Get schedule information into Power BI

Since the data used for this example is created in a Google Sheets spreadsheet you can use the default Google Sheets connector that ships with Power BI out of the box.

Using this schedule information you can already get started adding some tables and (gantt) charts to your dashboard.

6 – Specifying the active date

6.1 – The calendar table

At some point you want to be able to select a specific date and see the expected status of your project. To do so, you will need a calendar table that is based on the start- and end date of your tasks. You can create a custom table using the following DAX: Calendar = CALENDAR(MIN(Tasks[Start]) – 5,MAX(Tasks[end]) + 5)

6.2 – Slicing the calendar

Using a standard slicer you can filter out dates depending on a chosen value. Since you are interested in having a list of all passed days up to a specific date, make sure to setup the slicer’s style to be “Before”. 

6.3 – Calculate the active date using a measure.

While the slicer from the previous step will filter the calendar table to only contain the chosen date and all dates before you are actually only interested in knowing what the selected date it. It is this date that will then be used to calculate if a task is active or not. To do so, you can create a measure that picks the latest date in the currently sliced calendar table, using the following DAX: SelectedDate = MAX(‘Calendar'[Date])

7 – Bringing it all together

7.1 – Merge schedule data with model data

While it is possible to setup relationships between your schedule data and model data, we recommend to merge your schedule data with your model data up front using Power Query. The reason is that this will speed up the responsiveness of Power BI. Additionally, it will make it easier to setup measures for calculating if objects should be visible or not and which color should be used to colorize them, if all required information is part of the same table.

Using the following steps you can create such a combined query that will result in one table containing a row for each object, populated with information from the Task it is linked to as well as information of the Task’s Type:

1. Select your Model Data query and perform a Left Outer join with the table containing your Task Objects. The result will be a new table that not only contains information about each object, but also about also the ID of each task that it is linked to.
2. Using the new Task ID, you can perform a second Left Outer join to merge it with information contained in your Tasks table. Again, the result will be that information contained in the Tasks table, such as the Task’s name, start- and end date as well as the Task Type ID will now also be part of this table.
3. Finally, you perform a third Left Outer join with the Task Types table using the matching Task Type ID, so that  information of the Task Type, such as its visibility properties are now also part of this table.

The end result will be one single table containing all scheduling information for each object.

7.2 – Create measures to calculate an object’s visibility

Once a table exists that contains all information for each object you can create 2 measures. In order to calculate if an object should be visible, you should compare the Active Date (6.3) with a Task’s start- and end date. If the active date lies in between, the task is active. If the Active Date lies before the task’s start date, the task has not been started yet, etc..

Since you also have information of each Task Type available in this table, you can also use the Task Type’s Visibility Before, During and After columns to decide if the object should be visible. The following DAX will perform this calculation for you:

ObjectVisible = IF(FIRSTNONBLANK(model_data[TaskObjects.TaskID], TRUE()), IF(IF([SelectedDate] < MAX(model_data[Tasks.start]), FIRSTNONBLANK(model_data[TaskTypes.VisibleBefore], TRUE()), IF([SelectedDate] > MAX(model_data[Tasks.end]), FIRSTNONBLANK(model_data[TaskTypes.VisibleAfter], TRUE()),  FIRSTNONBLANK(model_data[TaskTypes.VisibleDuring], TRUE()))), “true”, “false”), “true”)

The previous code first checks if a task has been associated with the current iterated object. If not, it will yield “true” (we want objects not linked to a task to always be visible). If there is an associated task, it will check if the task has been started already. If not, it will yield the value contained in the VisibleBefore column (either true of false). If the current Active Date is after the task’s end date, meaning that the task is finished, it will yield the value contained in the VisibleAfter column (also either true of false). In the other case, the task must be active, and it will yield the value contained in the VisibileDuring column (also either true or false).

The measure can them be used as a filter on the 3DBI visual to only display objects that have a “true” value for this measure.

7.3 – Create measures to calculate an object’s color

Using the same logic as the previous step, you can calculate the color of each object based on the fact that its linked Task is active or not and using the color information defined by its associated Task Type. The following measure will perform this calculation for you:

If a task is active (meaning the Active Date lies between its start- and end date, it will yield the color found in the Color column, else it will yield a gray (#808080) color.

Finally, using condional formatting on the 3DBI visual, the value yielded by the ObjectColor measure can be used to give each object in the visual a calculated color (also see New Feature – Conditional Formatting to lean more about this topic).

8 – Wrapping up

Add some charts and finishing touches and see your 4D model come to live. As an extra step you can also link in cost and quantities information, so that you can see on a daily basis how much material will be consumed, what the cost is, … and so much more. Power BI is able to link with basically any kind of data, which you in return can link with the data you already have in your dashboard.