In part two of this tutorial we will look at utilising the MS Excel workbook that was exported from Revit 2021 in Part 1. If you did not work through part one of the tutorial, here is the link:
The primary part of this tutorial will be looking at Microsoft Power BI and importing the MS Excel workbook. Obviously, you can present the data directly in MS Excel by using the inbuilt tables but Microsoft Power BI has better tools for presentation and data visualisations and allows data tables to be referenced to each other via unique keys. Another advantage is that the dashboard can be shared via a weblink to anyone that wants to consume the data.
Just as a quick recap, when exporting Revit model data via ODBC, Revit creates a data table for each model category. Additionally, the tables are differentiated by instance and type.
In the image below you can see the worksheet for the floor instance.
Below is the table representing the floor types
Working with Microsoft Power BI
Once Microsoft Power BI is running the interface should look like the image below. In this tutorial I am using the desktop version.
The first step is to connect to a data source. In this case we will connect to our Microsoft Excel workbook. On the Home ribbon click Excel.
You then browse to your MS Excel file. Power BI will then connect to the data and present the Navigator. In this example we will select the following tables:
- Floors
- FloorTypes
- Levels
- StructuralFraming
- StructuralFramingTypes
Click the Load button. This will take a few moments to load in the data from MS Excel.
Before we start working on the visualisations, we need to create some relationships between the type and instance properties. Revit creates unique keys to enable this linking.
In Power BI click the model icon.
You will then see the selected tables presented. You can move and drag these around on screen to fit everything into view.
Click the Manage Relationships icon as shown below. This will enable the linking of data so we can visualise both the type and instance properties.
In the Manage Relationships dialog click the new button in the bottom left of the dialog.
Configure the dialog box as shown in the image below. Note that the data columns are selected (TypeId and Id) These are the unique keys that tie the instances to types.
Click OK and then close. The tables now have a relationship.
We can now start to visualise the data. Click the Report Icon as shown below.
On the Field panel click the Type Name as shown below. You will see a table presented on the page.
Now open the Floors table and select volume. Notice that this new field is added to the same table. You can now select a visualisation for the data.
This is just a simple example of a chart, you can continue to make additional relationships with other tables and create a dashboard to better understand material quantities of your structural elements.
Hope this helps.
LawrenceH
Autodesk Revit Structure 