DIY FM: Bridging the AEC/FM Gap Using Revit DB Link, ASP.NET, HTML, VB and Java

DIY FM: Bridging the AEC/FM Gap using Revit DB Link, ASP.NET, HTML, VB and Java

DIY FM - Bridging the AEC/FM Gap with COBie using Revit DB Link, ASP.NET, HTML, VB and Java

Brandon Pike - NELSON
(Assistant/Co-presenter optional)

Building owners constantly express a passionate desire for why BIM products drastically improve their day-to-day operations but technological and knowledge based roadblocks prevent realistic AEC/FM collaboration. We aim to share how building data added during all phases of the design life cycle can be leveraged using various application programming interfaces in the AEC/FM handover and Facility Management phases to create on-going AEC/Owner relationships long after design and construction.

Learning Objectives

At the end of this class, you will be able to:

• Identify the current means and methods of capturing and managing COBie facilities data.
• Identify gaps in the COBie process and layout solutions of make the COBie process more efficient.
• Understand the nuts and bolts of COBie data input, containment and management within a Revit model.
• Leverage applications such as Revit, Revit DB Link and Design Review Plugins as well as programming languages such as asp.net, html, VB and Java to create customizable web-based platforms to broadcast SQL Data and allow for web-based data modification and streamline model update processes.

About the Speaker

As NELSON’s Design Technology Manager and Strategic Manager for BIM Services, Brandon is responsible for direct firm-wide BIM, CAD and other design technology vision implementation and ongoing support. He has developed a strategic Building Information Modeling Vision as well as developed appropriate implementation roadmaps, processes and timelines.

Brandon’s goal for NELSON is to “Use state-of-the-art technology to break down personal and professional barriers, bring people and project teams together and pass the benefits of a streamlined design ecosystem onto our clients.”

Introduction

Building off of IPD and COBie principles, we will explore how gathering and maintaining building information data during the design life cycle of a building model can greatly improve the time spent and the efficiency of the workflow when transitioning from design/construction to on-going model management.

Using Revit DB Link to distribute this data from our Revit models into a SQL Server environment allows us to create a bi-directional link between the Revit model and the SQL database. Broadcasting this database information in a custom coded (using programming languages such as ASP.NET, HTML, VB and Java), lightweight, online web platform creates an interactive experience for the end user where they can view, edit and manipulate their building information in real time, meanwhile, allowing the AEC Firm to streamline the update and delivery process. Creating a custom coded, ground up web interface allows the AEC firm to economically tailor to each and every one of their clients specific requirements. Building owners can make changes/modifications within the web platform and data changes are directly filtered back up into the Revit model.

Originating, containing, maintaining and broadcasting building information within a Revit model allows the AEC Firm to maintain long-term, ongoing relationships with their clients while bridging the gap between AEC and FM workflows.

Definitions

Integrated Project Delivery

Definition as stated by the Wikipedia:

Integrated project delivery (IPD), is a collaborative alliance of people, systems, business structures and practices into a process that harnesses the talents and insights of all participants to optimize project results, increase value to the owner, reduce waste, and maximize efficiency through all phases of design, fabrication, and construction.

COBie

Definition as stated by the Whole Building Design Guide:

Today, most contracts require the handover of paper documents containing equipment lists, product data sheets, warranties, spare part lists, preventive maintenance schedules, and other information. This information is essential to support the operations, maintenance, and the management of the facilities assets by the owner and/or property manager.

Gathering this information at the end of the job, today's standard practice, is expensive, since most of the information has to be recreated from information created earlier. COBie simplifies the work required to capture and record project handover data.

The COBie approach is to enter the data as it is created during design, construction, and commissioning, see Figure 1. Designers provide floor, space, and equipment layouts. Contractors provide make, model, and serial numbers of installed equipment. Much of the data provided by contractors comes directly from product manufacturers who can also participate in COBie.

SQL Server

Definition as stated by entry on Wikipedia:

Microsoft SQL Server is a relational database server, developed by Microsoft: it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet).

ASP.NET

Definition as stated by entry on Wikipedia:

ASP.NET is a Web application framework developed and marketed by Microsoft to allow programmers to build dynamic Web sites, Web applications and Web services.

Visual Basic

Definition as stated by entry on Webopedia:

A programming language and environment developed by Microsoft. Based on the BASIC language, Visual Basic was one of the first products to provide a graphical programming environment and a paint metaphor for developing user interfaces. Instead of worrying about syntax details, the Visual Basic programmer can add a substantial amount of code simply by dragging and dropping controls, such as buttons and dialog boxes, and then defining their appearance and behaviour.

Software

1. SQL Server Express instance (free) or use a slice of current corporate SQL Server
2. SQL Server Management Studio 2008r2 (free)
3. Microsoft Visual Web Developer 2010 Express (free)
4. Revit Architecture 2012 (not so free)
5. Revit DBLink Add-in (free with subscription)
6. Autodesk COBie Toolkit for Revit (free)
7. Standard Web Browser (free)

**See the “Resources” section of this document for links to software download sites**

Part 1 – Data Input and Exchange

What we’ll cover:

• How have we gotten where we are today?
• Crash course in COBie data models and data exchange
• A look into the next generation of data exchange

A Blast from the Past - Legacy Workflows

Before we look to the future of data exchange, let’s take a look back at some of the work flows (the good, the bad and the “educational”) of the “Tech Revolution” that got us to where we are today…

1. 1982 – Autodesk Founded, Releases AutoCAD v1.0
2. AutoCAD
3. Paper Deliverables
4. 1989 – Microsoft releases Office v1.0 for Mac
5. AutoCAD, Microsoft Excel
6. Paper Deliverables
7. 1998 – Autodesk Releases AutoCAD Architectural Desktop v1.0
8. AutoCAD Architectural Desktop, Microsoft Excel
9. Paper Deliverables
10. 2000’s – Revit Technology Corporation releases Revit v1.0
11. Revit, Microsoft Excel
12. Paper Deliverables
13. Revit, Microsoft Excel
14. Electronic Deliverables
15. 2010 – Autodesk releases Revit DBLink
16. Revit, SQL Server Database
17. Electronic Deliverables, Bi-directional relationships
18. 2014 – Autodesk releases the COBie Extension

Data input, containment and management within a Revit Model using a COBie Data Model

COBie is the industry’s first attempt at an open standard for Building Information Model data parameters and data exchange means and methods.

COBie building data is populated as the information becomes available by the appropriate project stake holders during design, construction, and commissioning, see figure 1. Designers provide floor, space, and equipment layouts. Contractors provide make, model, and serial numbers of installed equipment.

Figure 1 - COBie Process Overview

Traditionally, a COBie data model is broken out into 16 COBie worksheets within a COBie-formatted Microsoft Excel workbook, see figure 2.

Figure 2 - COBie Data Structure

Data within a COBie workflow can essentially be categorized into two data types, see figure 3.

Model Data

Model data is data that we can directly attribute to an object within a Building Information Model.

Examples:

• Furniture Components
• Mechanical Equipment
• Windows and Doors

Non-model data

Non-model is data that cannot natively be compiled or tracked within a Building Information Model.

Examples:

• Supporting Documents (warranty documents, cut sheets, submittals)
• Project Stakeholder Contact Information
• Facilities Operations onsite job plans, replacement parts and tools and training

Figure 3 - COBie data Structure – Model data vs. non-model data

COBie Data Exchange

In the current rendition of COBie, data compiled in the Revit model is exported (via the Autodesk COBie Extension, manual copy/paste or IFC export) to Microsoft Excel worksheets. This Microsoft Excel deliverable is then used by CAFM, IWMS and O&M applications to populate their databases with the building information. In this workflow, Revit can (via the Autodesk COBieExtension) natively compile, track and provide to the COBie model data for all 7 of the model data worksheets, see figure 4.

Figure 4 - COBie data Structure – Tables outlined in black indicate data provided by Revit using the export to Microsoft Excel method

By synchronizing this data with a database application such as SQL Server, we can take advantage of ALL the data that is housed within a Revit model rather than just the data we can schedule. Using this method, we can compile, track and provide COBie model data for ALL model data worksheets, see figure 5.

The copy/paste to Microsoft Excel method also falls prey to the ever so dreaded “linear data collaboration workflow.” As soon as COBie data is exported from a Building Information Model to an outside application such as Microsoft Excel (while there are workarounds and involved methods to bring data back into the model), the design model essentially becomes instantly obsolete, see figure 6.

Figure 6 - COBie data exchange using current workflows

Synchronizing model data with a server application both opens the door for data “round tripping” as well as opens doors for broadcasting data into a web application format to allow for non-technical stakeholders to contribute information LIVE to the database application and, in turn, to the design model, see figure 7.

Figure 7 - COBie data exchange using a database application

Part 2 – Let’s do it!

What we’ll cover:

• Creating a new database within SQL Server
• Use the Autodesk DBLink Add-in to synchronize model data with the SQL Server database
• Create a web application in Visual Web Developer to broadcast SQL Server data across the web
• Make data changes flow round trip through the system

Figure 1 – Data Exchange Model using SQL Server

Linking a Revit model database to a SQL Server using Revit DBLink

1. Create a new database within SQL Server
2. Create a new DSN file to connect Revit to the SQL database
3. Export Revit model data to the new SQL database

Create a new database within SQL Server

1. Open SQL Server Management Studio 2008r2
2. Enter the Server name to connect to the appropriate server, select Connect

1. Right click on the Databases category in the Object Explorer and choose New Database
2. Enter the name of your new database in the Database name: field
3. Select OK

Using DBLink to synchronize Revit model data to the SQL database

1. Open up your Revit model
2. Open the DBLink add-in
3. From within the ODBC tab, Choose [Select a new connection] and Export

1. Go to the File Data Source tab and select New

1. Choose SQL Server Native Client 10.0, select Next
2. Browse to a location to save the new DSN file, give it a name and select Save, then Next, then Finish
3. Under “Which SQL Server do you want to connect to?” Enter your server name, select Next

1. Depending on you server permissions, choose With Integrated Windows authentication or With SQL Server authentication using a login ID and password, select Next
2. Check the Change the default database to: checkbox and select the appropriate database that we created in the previous section, select Next, then Finish

1. Test Data Source… and then select OK

1. Highlight your newly created DSN file and select OK to sync the Revit model with the SQL database

**You can leave the Server SPN field blank***

**Helpful Hint: Instead of creating a new DSN file from scratch every time you need a new DSN connection, you can copy, paste and rename DSN files. You can edit the DSN file within Notepad and just change the database name.**

Creating a Web-Application to broadcast SQL Server data across the web

Creating the Data View

1. Open up Microsoft Visual Web Developer 2010 Express
2. Select New Project, choose ASP.NET Web Application
3. Enter a name for your new application
4. Define a location to save your new application
5. Select OK

1. It will open Default.aspx right away, switch to Design View and open your Toolbox

**Helpful Hint: If the Toolbox is unavailable, go to the View drop down menu>Other Windows>Toolbox**

1. Under the Data category, drag and drop the Grid View entity in to your web application

1. In the GridView Tasks Menu, choose Data Source>New Data Source

1. Select SQL Database, select OK
2. Choose New Connection…

1. Enter your server name or server address
2. Select the appropriate database from the selection list select OK, then Next, then Next again

1. Select the appropriate data table from the database
2. Select which fields you would like to include in your Grid View
3. Make sure the “Id” field the selected

**Helpful hint: The order you check the boxes, will be the default order they are displayed in the Grid View**

1. Under Advanced… check:
2. Generate INSERT, UPDATE and DELETE statements
3. Use optimistic concurrency
4. Select OK

**This is where the magic happens, this will allow for the data to be edited form within in web application**

1. Select Next, and then select Test Query…
2. Select Finish

Formatting the Data View

1. Enter the Grid View Tasks Menu
2. Enable
3. Paging
4. Sorting
5. Editing

**This will add the Edit button in the Grid View**

1. Edit Columns
2. Under Selected Fields, you can rearrange and format each column as needed

Adding Inner Joins

By nature, and the way the Revit database is designed, sometimes not all relevant information may be included in one single data table. The following steps will outline how to use Inner Joins to add additional information from another data table such as a Revit Type Name or Level Name to your current data grid view.

1. Enter the Grid View Tasks Menu, choose ‘Configure Data Source’
2. Choose Next
3. With the current Data Table (andits statement selected), select the ‘Specify a custom SQL statement or stored procedure’ radio button, choose Next.

**This will copy the statements we have already generated via the wizard and allow us to further customize.

1. Choose ‘Query Builder’

1. Right click in the top windows and choose ‘Add Table’
2. Select the tables you wish to add to the statement. In this case, we will choose Furniture Types and Levels.
3. Once added, select the attributes you wish to add to your grid view. In this case, we will choose FamilyName and TypeMark from the FurnitureTypes data table and Name for the Levels data table. These attributes will be added to the attributes list below. These can be dragged and dropped to reorder the columns.
4. Click OK
5. Select Next, and then select Test Query…
6. Select Finish

Take it for a Spin!

1. Save the default.aspx page
2. In the Solution Explorer, Right Click>View in Browser
3. This will launch the application locally in your web browser

1. In your web browser, select a line item and select Edit and make a change or addition to a data field
2. Select Update

**This will push the change to the SQL Server**

1. Open up the Revit model and re-sync the model with the SQL database
2. In the ODBC tab, choose [Select a new connection]or choose existing DSN connection
3. Choose Edit and Import

1. Browse to and select the DSN file we created earlier, Select OK

**This will pull the data from the SQL Server**

1. Either view the change in a Revit Schedule View or go to the Manage Tab and choose Select by Id
2. Enter the Id of the referenced object and select Show

Information Sources: