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.
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.
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.
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.
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
There are many options here but to keep things simple, select the Microsoft Excel Driver option as shown below.
Click the Finish button. You should then be presented with the ODBC Microsoft Excel Setup dialog box as shown below.
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
Click OK
You will now have your 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.
We are now ready to extract the Data. From the File Ribbon select Export and then ODBC Database as shown below.
From the Select Data Source Dialog click the Machine Data Source tab as shown below.
Once in the Machine Data Source Tab, select the Machine Data Source that we just set, ‘DataExtractionFromRevit’.
Click OK
Select your workbook and click OK
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.
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
Autodesk Revit Structure 