BIM variance – Track and analyze alterations in your model over time

In this tutorial you will learn how to bring and compare the geometry and data of 2 SketchUp (or Revit or .ifc) models in Microsoft® Power BI®. The goal is to be able to compare what has changed in your model over time. 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. 
• Microsoft® Power BI®
• 3DBI, our Microsoft certified custom Power BI visual, available on AppSource.

The same can also be acomplished with Autodesk® Revit® models, using 3DBI for Revit, or, with .ifc models, using 3DBI for IFC.

1 – The first model variant

In this example we created 1 SketchUp model and exported this first variant to 3DBI using 3DBI for SketchUp. We have chosen to use “persistent ids” for the “Id Provider” option and have also selected to export data in a .json file. The result of this export will be a .3dbi file containing the model geometry and one .json file containing various quantities that 3DBI is able to destill from the model.

If you are following this tutorial using 3DBI for Revit or 3DBI for IFC, you do not have the option to select a specific “ID provider”. In this case your .3dbi file and .json file will contain automatically generated IDs.

2 – The second variant

Over time, your model will change. Elements will be deleted, added and/or modified. For every moment in time that you want to compare what has been altered in comparison to the previous variant, you can create a new 3DBI export using the exact settings as used previously. This will result in a second (or third, fourth, …) .3dbi file and associated .json file

3 – Import data of 2 variants into Power BI

3DBI exports data in a .json file, which is a text-based data format that organizes information in a clear and human-readable way. It consists of key-value pairs and arrays, making it versatile for representing complex data structures. The good news: Power BI can connect to .json files right out of the box, using the JSON data connector. The first thing to do would be to connect to the 2 .json files that were generated in the previous steps. This will result in 2 separate queries in Power BI. Now, 3DBI exports more information than you will probably need for comparison. To speed up the process it is advised to filter out those columns that are not needed. In this tutorial we only keep the “3dbiModelId”, “Material” and “Volume” column. The first one is mandatory, all the others are optional and depending on which fields you want to use for comparison.

For Revit based exports, you will notice that there is no “3dbiModelId” column. For Revit you should take the “ID” column, since that is the ID that 3DBI uses internally.

4 – Merge data of 2 variants into 1 new table

Once the data of the 2 separate variants have been loaded into Power BI, you can merge both tables into a new, third table. In order to merge 2 queries, Power BI provides the “merge queries” functionality. In the dialog that is presented you have to select the 2 separate queries you set up previously (containing the model data) and specify that you want to merge based on the “3dbiModelId” column (or “ID” in case of a Revit export). Also, you want to perform a “Full Outer” join, which makes sure that all rows from both queries will make it into the new table, even if a match can not be found. This can happen when elements are either deleted and thus only exist in the first model’s data, or, when elements have been added and only exist in the second model’s data.

5 – Create a custom label column

Once the 2 original queries have been merged you can add 1 (or more) calculated columns to that new table. In this tutorial we want to assign a label to each row specifying if the element in that row is either “unchanged”, “changed”, “new” or “deleted”. En element is deleted if it only exists in the first model’s data, but not in the second, resulting in null values for those columns that are created based on the second model’s data. Vice versa, en element is new when it only has information associated with the second model’s data, but not the first one. Further, an element is changed if one of the other columns (Material and Volume) have different values in both models’ data. Finally, if all values remain the same, the element is considered to not have been changed.

Aditionally, you can also add an extra column to already calculate a color for each status, so that you can later use that column to colorize the 3DBI model in the 3DBI visual.

5 – Display in 3D

Once the data is set up it is time to work on the visual aspect of your dashboard. Since the goal of the dashboard is to analyze differences between 2 3DBI exports, you need to add 2 instances of the 3DBI visual to your dashboard. If you are not sure where to find the 3DBI visual, it is available directly from AppSource and is certified by Microsoft.

Depending on which visual you are setting up, you either bind the 3dbiModelId of you first model or you second model to the “Object Identifiers” field of the 3DBI visual. Aditionally, you can also assign the Color By column (if you created that one in the previous steps) to the Color By field of the visual.

After binding the data, you will need to press the “insert model” button of each visual and point to the correct .3dbi file.

6 – Add other visuals and data

Finally, you are free to add any other type of visual to your dashboard. In our demo dashboard we decided to add a table that shows all information we have in our Merge query as well as some charts to show the number of elements by their status.

Since Power BI alows you to also bring in information from other data sources, you could extend this dashboard with some price information (from Excel, for example) and not only see which elements have been changed, but also what the financial consequences are of those changes.