Revit 2021 Tutorial – Structural Data Extraction Part 1

It has been a while since my last post, and surprisingly, I have been very busy working from home with all sorts of projects, software development and online training/consultancy engagements. It is good to know the world is still turning!

Anyway, perhaps some of you have less demands on your time and something I wanted to share was the ‘easy’ method of extracting data and metrics from your Revit models without expensive software or Dynamo skills. When we think/talk about BIMs the important part is really the information that is held within your very expensive Revit models. Only a select few have the privilege of looking into this sea of data. In this tutorial we will take a look at how you can extract some basic information from Revit and present this in meaningful ways using MS Power Bi. You could do some of these things just with MS Excel, but MS Power Bi is much better a presenting and sharing data visualisations.

Part 1 of this tutorial writes data to MS Excel and Part 2 will show some basic operations to read this into MS Power BI and create some data visuals.

The only software you will need is Revit and MS Excel/MS Power BI.

However, if you do not have the MS Excel drivers you can download and install the AccessDatabaseEngine_X64.exe from the link below,
https://www.microsoft.com/en-us/download/details.aspx?id=13255
Then run ODBC Data Sources (64-bit) and Microsoft Excel Drive (xls, xlsx, ….) can be found.
Make sure MS Office installed is 64-bit.

Below is an extraction of an automated report showing the Revit categories and total material consumption. So much easier to understand the data when presented in a graphical form.

Structural Materials from REvit

So, let us start by looking at ODBC. Some of you may have seen the option to export to ODBC within the Export options of Revit. If you have not seen this, then the image below shows how you can navigate to this option.

Revit ODBC

ODBC is an acronym for Open Database Connectivity and it essentially is a tool to connect databases together. Most of us will know that Revit is fundamentally a database with a graphics engine for the visualisation of the building elements.

The ODBC option gives us a simple platform to extract data from a Revit project and view the data in applications such as MS Excel, traditional databases, or software tools such as MS Power BI.

Lets first create a new MS Excel file to store our data. Open Windows Explorer and create a new Microsoft Excel Worksheet by right clicking and selecting the New option.

Create New MS Excel Workbook

Name the workbook RevitDataExtraction.xlsx

Lets now configure the ODBC Datasource!

The first step to export some Revit data is to configure a data source, I am assuming we are all using Windows 10 so this will be for 64-bit systems. To access the ODBC Data Source Administrator, type ODBC at the search bar.

ODBC Data Source Administrator Search

You will then see the ODBC Data Source Administrator dialog box. Here you will see data sources that are already configured. We are going to Add a new Data Source. Click Add

ODBC Data Source Administrator

There are many options here but to keep things simple, select the Microsoft Excel Driver option as shown below.

Create New Data Source

Click the Finish button. You should then be presented with the ODBC Microsoft Excel Setup dialog box as shown below.

ODBC Microsoft Excel Setup Dialog

Click the Select Workbook button and browse to the MS Excel document you just created. The file navigation is very old so be warned!

Once you have located the Excel workbook you need to give the Data Source a name. In the below example I am naming this DataExtractionFromRevit

ODBC MS Excel DataExtractionFromRevit

Click OK

You will now have your data source configured.

ODBC Data Source Configured

Click OK to the ODBC Data Source Administrator dialog.

Extracting Data from a Revit Model

Now that the ODBC driver is configured, we can use this data source to extract information from a Revit model. To begin, lets extract data from a sample file that ships with Revit. Click the File Ribbon, then Pause over the Open command and select Sample Files from the menu.

 

From the Open dialog select rst_advanced_sample_project.rvt. This is a basic structural model with a variety of elements that will serve as a tutorial.

The model should look like the example shown below.

rst_advanced_sample_project Tutorial

We are now ready to extract the Data. From the File Ribbon select Export and then ODBC Database as shown below.

Revit ODBC

From the Select Data Source Dialog click the Machine Data Source tab as shown below.

Machine Data Source

Once in the Machine Data Source Tab, select the Machine Data Source that we just set, ‘DataExtractionFromRevit’.

Revit Select Machine Data Source

Click OK

Select your workbook and click OK

Select Workbook

There should now be a slight pause while Revit writes the data to our MS Excel document.

Open your Excel file and you will notice that the categories are arranged as work sheets. Scroll to the Structural Columns and you should see the following data. Each column has a separate row with all the data displayed.

Excel - Structural Coulmns

It is worth noting that if additional shared parameters are added, these will also appear in the workbook.

In the next session we will use Microsoft Power BI to present this data!

Hope this was useful,

 

LawrenceH