Creating a QTO report in Power BI, based on your Revit models.

In this tutorial you will learn how to export material takeoff information from Revit and how to import and visualize that data in an interactive Power BI report. The tools needed to achieve this are:

• Autodesk® Revit® as the BIM application.
• 3DBI for Revit version 1.5 or newer, as the custom addin that is able to export Revit geometry and data to a format readable by our 3DBI visual. 
• Microsoft® Power BI®
• 3DBI, the custom visual we developed for Power BI, available on Microsoft AppSource and Microsoft certified.

1 – Export geometry and data from Revit

Create the 3DBI-Context View.

One of the key features of 3DBI is its ability to define contextual geometry. These elements, while not part of your core project and not requiring detailed data, significantly aid in spatial orientation. Common examples of contextual geometry include topography, roads, plots, and neighboring buildings. Create a new (or duplicate an existing) 3D view and name it “3DBI-Context”. In this new view, only make those elements visible that are contextual elements.

Create the Exporting View.

3DBI, when exporting, will only export geometry and data of elements that are visible in the 3D view that is active during the export process. Create a new 3D view, or duplicate an existing one, and configure it to display only the elements that are part of your project.

Activate the Exporting View and Export.

As previously mentioned, 3DBI exports only the geometry and data of visible elements in the active 3D view.

Once you’ve activated the Exporting View, follow these steps:

1. Open the 3DBI dialog by clicking the 3DBI button in the “Add-ins” ribbon tab.
2. Check the “Export Context” option to export all geometry available in the 3DBI-Context View as static contextual geometry.
3. Additionally, select the “json single elements array” option to generate a .json file containing all relevant information found in the Revit model.
4. Click the “Export” button and specify a storage location for the generated geometry and data.

Upon successful export, 3DBI will create a .3dbi file containing geometric information and a .json file containing the associated data.

2 – Getting the data in Power BI

After completing the export, the next step is to import the data into Power BI in a structured manner:

1. Launch Power BI and start a new blank report.
2. Instruct Power BI to load data from a .json file using the JSON connector.
3. Power BI will automatically perform some steps. However, to better structure the import, undo all automatically applied steps. This initial query will serve as a reference for three additional queries.

Now, create three new queries:

• Objects: Reference the initial query, select the “objects list,” convert it into a table, and expand its columns. You can select the specific columns you need for your report. Ensure to include the “Id” column, which contains the unique identifier assigned by 3DBI to each element.
• Materials: Reference the initial query again, select the “materials list,” convert it into a table, and expand its columns. You can cherry-pick the columns relevant to your dashboard. Include the “Id” column, which contains the identifier for each material processed by 3DBI. Notice that this list of materials is only available in exports created with 3DBI for Revit 1.5 or newer.
• Object-Material Link: Reference the original query, select the “ObjectMaterialUsages list,” convert it into a table, and expand its columns. This table should contain ObjectId, MaterialId, and the quantity information. Currently, in version 1.5, both area and volume are exported in internal units (square feet and cubic feet) as well as in the display units active in Revit during the export. The selected display units are listed in the VolumeCalculatedUnit and AreaCalculateUnit columns. Notice that this list of object material usages is only available in exports created with 3DBI for Revit 1.5 or newer.

Ensure all numeric columns are correctly marked as numeric instead of text and finalize the import process.

3 – Display data in Power BI

After importing the data from the .json file, you can visualize it in Power BI using various graph visuals and tables. In this tutorial, we’ve crafted two graphs: one showcasing the total area of material used by each element, and another for the total volume.

It’s crucial to note that for Power BI to understand the relationship between objects, materials, and object-material usages, you’ll need to explicitly establish these relationships in the relationship view.

4 – The interactive 3D model

While valuable insights can certainly be derived from data alone and utilizing out-of-the-box Power BI visuals, the true power lies in the seamless integration of this data with a 3D model. This integration enables a deeper understanding of spatial relationships, contextualizes data within its physical environment, and enhances visualization by providing a more immersive experience. Moreover, connecting data to a 3D model facilitates better decision-making by offering a comprehensive view of the project, identifying correlations between data points, and allowing stakeholders to explore data in a more intuitive and interactive manner. This holistic approach not only enhances analysis and reporting capabilities but also fosters collaboration among project teams, ultimately leading to more informed and effective outcomes.

The 3DBI custom visual is Microsoft certified and is available directly on AppSource. To incorporate this visual into your report, follow these steps:

1. Click on the “Get more visuals” button and navigate to AppSource to locate 3DBI.
2. Download 3DBI and add an instance of the visual to your report.
3. Upon accepting the license agreement, establish a connection by linking the Id column of the objects table with the “Object Identifiers” field within the visual.
4. Finally, select the “Insert model” button and specify the location of the .3dbi file exported from Revit.