AU2016 SD20868

Autodesk University 2016 has come to a close for us now and it was very enlightening and entertaining. I enjoyed catching up with old friends and making a few new ones as well.

I taught a class entitled "SD20868 - Revit Usage and Model Data Reporting Simplified with C#"

It was a full solution style overview class showing how to get verb data out of Revit and into a graphical dashboard showing various Model and User metrics.

The purpose was to show and share how this can be done using ASP.NET coupled with a few other modern(ish) web development techniques. I want to thank the folks that provide the Gentelella web template and hope everyone enjoyed the class.

Relax Dataset Constraints for TableAdapters in .NET

This is a very common issue that people run into as they begin to use complex dataset objects in their .NET development work. This applies to ASP.NET as well as Winforms development work... even WPF.

If you've ever put together a complex join query in one of your TableAdapter objects and then in your code attempted to apply it to a DataTable object, you may have seen this warning and had no idea what to do about it:

The solution is simple. Open your XSD in design view and select on the background area (do not select a query or a TableAdapter).

Then in the properties pallet you will see a property named "EnforceConstraints". Set this property to False.

This option gives you the ability to provide dataset level constraints which are usually not necessary if your database backend has its own constraints.

Supplemental BIM Database Porn Part 2, Reporting Multiple Categories Together

This is part 2 of the highly anticipated and exciting mini series on supplemental BIM database design!

This post will focus on how data from two entirely different tables can be UNION queried together to aggregate counts and cost SUM's in a single report. The really cool part about how this works is that the fields don't have to match names or even data types between the two tables... I know, SUPER EXCITING~!!
The examples in this mini series have been built in MS Access 2007 in case you haven't noticed but could very easily be used with minor adjustments on SQL or my new favorite PostgreSQL.

A quick snapshot showing the 5 tables that we'll be working with is shown below. The type properties are in the category tables prefixed with "type" and the instance properties in the tables prefixed with "inst"...

UNION queries can only be built in MS Access 2007 using pure SQL syntax. This shouldn't be any big deal at all to a seasoned SQL DB Admin. To create a UNION query in MS Access 2007, click the "UNION" button in the query Design tab of the ribbon.

Union queries will show up in your MS Access 2007 Object Browser with a Union icon as shown below.

It is important to note that while the data types and names of the fields in each table can be entirely different when used in a UNION query, the quantity selected between each table must be equal. Select 5 fields in one table, then you better select 5 in the other. There is no limit to the amount of UNION queries you can run together, just remember to select the same quantity of fields in each of your table selections.


Another super important key is the order of the fields in each of your selection queries. That is to say that the first field in the first selection will list directly with the first field of the second selection query and so on, make sense? Great!


So what would the query look like? The example below shows the selection of all "Furniture" and "Furniture Systems" elements from four tables named "inst Furniture", "type Furniture", "inst Furniture Systems" and "type Furniture Systems." Each category holds a relationship between two tables for instance and type properties respectively. Yet another relationship to complicate matters further but also show the flexibility of such a query is the "ProjectInfo" table that holds the project information useful in populating report headers and footers.

The resulting query results (partial) looks like this (Quantity and TotalCost are aggregated across the two UNION queries in totally separate table groups):



Supplemental BIM Database Porn Part 1, Introduction and Table Schema

Now that I've got your attention...

Have you ever wanted to know a little more about databases in the context of your everyday BIM related tasks but weren't quite sure where you should turn? Then this post is for you and if you're by yourself, nobody will be there to laugh at you! This is part 1 of what will more than likely be an entire action packed mini super series on how databases can help the BIM process and lead to your ability to conquer the world.

It is fairly common to manage design specifications for repetitive yet varying design elements for us in the AEC industry. Some example categories that are typically handled in such a way are Furniture, Furniture Systems, Door Hardware, Light Fixtures, and Casework to name a few. These items are commonly only tagged with a type value in the model and then synced with an external database where they can be further elaborated on.

So what's a good schema to use for a database design to solve such a task when your BIM application is say... Revit? Well, Revit stores data for it's elements in two basic forms that are basically entirely separate. Type data is stored in a "Family Symbol" where all of an element's "Type Parameters" live. The rest of the data describing an element in the model is stored as "Instance Parameters" within each instance of a placed symbol. So to mirror this in the database world, it makes sense to have one table to store an element's type properties and another related table to store the instance properties. It also makes good sense to have a matching pair of these tables for each Revit category that your elements belong to that you are interested in synchronizing.

A consistent naming strategy for these category based tables will also help you succeed. I like to use a simple prefix of "inst" for the instance properties and a prefix of "type" for the type property tables. I then just name each table with a suffix matching the Revit category name.

You may have also noticed in the image up above that the same "inst" and "type" prefixes are used for the primary keys of each table. This also helps with the relationships between the database and Revit. I prefer to use the "UniqueID" property for all elements as their primary key when synchronizing with a database. These values are pretty much guaranteed to never duplicate even across project models. The value returned by UniqueID is a hugungous GUID. I then use the UniqueID from the type element as a foreign key relationship to the instance tables. This is possible since the type element (family symbol) and the instances of these symbols as they are placed throughout the model are all unique! That is to say that the type element is entirely a separate object behind the scenes in the Revit API thus making a handy way to join them in a database schema.

I also generally use the name of the Revit parameter or property to name the corresponding database field name. String based parameters and properties should have a string formatted database field. Double formatted Revit parameters or properties should have their database fields formatted as decimal. Be careful with Revit parameters that are named using what are referred to as "Reserved Names" as these may result in strange unwanted features during synchronization. A list of these so called reserved words can be found at http://support.microsoft.com/kb/286335.

Stay tuned for future episodes of this action packed and exciting topic!!! We'll also get into how these tables and databases can be setup programmatic!!! Enormously exciting and guaranteed to help you pick up chicks at a bar!!