Creating an interactive 3D quantity take-off dashboard, in Power BI.

In this tutorial you will learn how to bring your SketchUp model’s geometry and quantity information into Microsoft® Power BI® so that you can create interactive 3D quantity takeoff 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 as well as generate an open-source data format containing quantities and other meta data about your 3D model. 
• Microsoft® Power BI®
• 3DBI, the custom visual we developed for Power BI, shipped with the SketchUp extension.

1 – The SketchUp model

The SketchUp model created for this demo represents a concrete structure of a 5 story office building, where:

• Each individual element is modelled as a separate SketchUp component.
• Also, each component has a tag assigned to it to represent the level it is placed on (-1 to 5).
• An IFC classification (IfcFooting, IfcSlab and IfcColumn) has been assigned to each component as well.
• Finally, the model uses 2 materials to illustrate in-situ vs. prefab concrete. Each component has been assigned one of both materials.

2 – Export geometry and data with 3DBI for SketchUp

To open the export dialog go to 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 assign an automatically generated unique ID to each element that will be persistent between exports.
• 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 – Import quantities and meta-data into Power BI

By using the standard JSON data connector that comes shipped with Power BI you should be able to load in the .json file that got exported during the previous step. Once loaded in you can fine-tune the import to only import columns that are actually needed in your dashboard. For this tutorial we will go with:

1. 3dbiModelId – this column contains the same IDs that should also be present in the generated .3dbi file
2. Material – the name of the material assigned to each component in SketchUp
3. Tag – the name of the assigned SketchUp Tag. We have used tags to indicate levels in this tutorial.
4. Volume.ModelUnits – the volume of each component instance in model units, m3 in this case.
5. Classification.IFC 2X3 – the assigned IFC classification for each element.

4 – Create dashboard and load in 3DBI model

All those quantities and other fields imported during the previous step can already be visualized using the default visuals Power BI comes with out of the box. In a few clicks you can add a table, a chart and a few filters to your dashboard. However, the actual power of 3DBI lies in the fact that you can also bring a 3D model to your dashboard so that you always see the link between your data and the 3D elements that are resposible for the data that you see. For that, we created the custom 3DBI visual for Power BI, which comes shipped with 3DBI for SketchUp. Once that visual is placed on your dashboard, loading in the exported .3dbi file during step 2 should be straightforward.

5 – Link in extra information

The data shown in the dashboard is not limited to what we imported during step #3. With Power BI you can load in lots of other information and link it all together. For example, we could link to a pricing database containing pricing information for each material we use. To illustrate we created a very basic Excel sheet containing 2 rows (one per material used in our dashboard) and 2 columns: a name and a price per unit.

Once imported we have set up a relationship between this freshly imported data and the previously imported model information. Finally, we have also added a custom column to the element table where we multiply its volume by the unit price of its related material so that we can calculate a price per element. This new column can now also be used in the dashboard to dynamically display a price per element or a total price.