Revit 2021 Tutorial – Structural Data Extraction Part 2

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:

https://revitstructureblog.wordpress.com/2020/05/21/revit-2021-tutorial-structural-data-extraction-part-1/

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.

Revit Floor Instance

Below is the table representing the floor types

Revit 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.

Microsoft Power Bi Interface

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.

Power Bi Connect to MS 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.

Power Bi Model

You will then see the selected tables presented. You can move and drag these around on screen to fit everything into view.

Power Bi Tables

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.

Power Bi - Manage relationships

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.

Power Bi - Manage Relationships Dialog

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.

Power Bi - Report Icon

On the Field panel click the Type Name as shown below. You will see a table presented on the page.

Power Bi - Field Tab

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.

Power Bi - Revit Floor Table

Power Bi - Revit Floor Volumn as Chart

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s