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!!

Parametric Holiday Tree from Charles Lee

Well, working for an Architectural firm definitely brings me closer to some strange and interesting people. One such interesting person I get to work with regularly is Mr. Charles Lee.

Charles was named this year's "Young Architect of the Year 2010" here in San Francisco and really loves the BIM stuff. Check out his biosarch blog based on BIO Design!

Here's Charlie's Revit model of a Holiday Tree... Thanks Charles! Maybe next time we can set this up as a shared model where everyone in the office could model their own ornaments to help decorate it! Next time we'll have to get that setup for our users...

Download

Wait for IT....

I know this is the worst blog post you will ever read in your human existence... but I just wanted you to know that I have a post on its way that is going to really bring the house down!!! Just waiting on the OK to post it!... Trust me... it's HUGE!

If I can't post it for some reason this week... it will have to wait until March (I wonder why)...

10 Pick up Lines All BIM Managers Must Know

If you lack a sense of humor or are easily offended then click an ad to the right and avoid reading this post! There will be other posts later that actually have a point!

As you can imagine, I have a lot of friends (pause) that poke fun at my line of work (I'm from Austin Texas). So I got into a bit of a challenge in comparing how my profession can just as easily be compared to theirs in terms of pick up lines to use at a bar. So here's the top ten pick up lines that ALL BIM managers need to know:

10.) These hands aren't just for constraining forms

9.) Have you ever been with a BIM Manager?

8.) All my models make it to stardom!

7.) Your forms are constrained quite efficiently

6.) I'm an expert at starting complex families...

5.) You have eyes that shine like the most elaborate curtainwall on the most perfect summer day

4.) You're more beautiful than any building I've ever modeled

3.) Speaking of Models, you're one of the best I've ever managed

2.) Has anyone ever 3D modeled you before?

1.) Wanna come back to my place and check out some sweet renderings?

Schedule Accumulated Road Centerline Lengths in Revit

Another NON API Post!!! Don't worry, I haven't converted...

Have you ever needed to schedule some sort of element's accumulated linear distance?... Say like a whole bunch of road centerlines for curb costing?... Here's a quick tip on how you can do just that?...

First create a new wall type and name it something like "Roads_CenterLine" and set it's construction width to something very skinny... say 0.05mm

Set the "Type Mark" for this new wall type to "RoadCenterline"... we'll use this to filter our schedule later.

Next draw or trace all of your road centerlines using this new wall type. Make sure that all of the walls that you draw using this new wall type are no taller than your cut plane in your view (very short). A wall height of 1mm works great.

Create a view filter based on the name of this wall and apply it to your current view. We'll use this to override the display of the walls so that when plotted they will look like a real centerline.

Apply the filter to your view and override the filter's display to whatever you want. My example modifies the linetype to a centerline...

Your centerline walls should look something like this one now:

So you obviously need to schedule the accumulated lengths of these centerlines, so create a new wall schedule and add the "Length" and "Family Type" fields.

Filter walls by "Type Mark" using contains "RoadCenterline" as the filter means.

Set the Grand Totals checkbox in the "Sorting/Grouping" tab so you get the totals.

Now hide the "Type Mark" field since you're only using it as a quantification filter.

Now check the "Calculate totals" box for the "Length" field... this is how we calculate the totals.

You're done! Your schedule should resemble something like this one:

BIM is easy...

Revit MEP Reuseable System Layouts Across Multiple Projects

Before anyone boo's me... this is a NON API post...

This one's for you HVAC guys out there struggling with the speed of Revit MEP. I know it can be a little painful at times, but boy is it nice to have an intelligent model as a finished product that you can really make use of throughout the CA and construction phase to help avoid change orders and SNAFU's in the field.

I'll have to warn you first about the performance hits that you may encounter if you fail to "UNGROUP" these systems after you place them into your model... PLEASE remember to ungroup all placements after they dropped into your model as these groups will really leave a mark in terms of performance if left as live groups.

OK... let's get started...

Have you ever worked on a building that uses repeatable VAV layouts or has numerous zones that for the most part are all basically the same in terms of elements placed to design it? This method can be used for just about anything you can imagine that utilizes system families, tags, text, and well... you get the idea.

I'm going to show you a way that you can build a warehouse of reusable system layouts complete with equipment, ductwork, and air terminals that you can reuse on all of your projects.

First let's start by drawing a full system complete with a VAV unit, all necessary ductwork, terminals and tags (you really could add anything you want to this).

Then let's select the items and create a group. Notice how you get two options for naming the groups? Tags will be placed into a nested group that you can name independently from the model group.

Now that you've got the group created, verify that your project browser displays the model group with a nested detail group beneath it like the image below.

Now to test if this thing works, right-click the model group from the above mentioned model group in the project browser and chose "Create Instance" and place one in your model (anywhere). It should look like the one below (without the tags... don't worry...).

Did you notice in the image above that you have an option button named "Attached Detail Group" when you select the model group that you just placed? Pick it and select the nested detail group name in the resulting dialog box.

Now your group should look a little more complete like the one below.

Now you can export your preconfigured system layout groups to a server where they can be shared across projects...

Use the "Load as Group" button in the "Insert" tab of the ribbon to add an externally saved group to a new project...

Human Readable Double Values for Parameters in the Revit API

This is a follow up or addition if you like to a previous post entitled A Classy Way to Handle Revit Parameters where I demonstrate a simple class for working with parameter objects in the Revit API.

This particular example is interesting in that we actually have a carnivorous way to get and set double formatted data by decimal as well as a more generic and simple way as string. It is obviously more difficult to calculate or aggregate values from a string representation value such as 6'-4" compared to 76.00 but sometimes the string representation is exactly what you need.

The sample code below demonstrates ho a variable initiated as a DB.Parameter is used to get and set the value from or to a string. The get will retrieve a value of 7'-0" or similarly you can set a value by entering 7'-0"...

Add the following snippet to the clsPara class to access the string values of a double formatted parameter:

''' <summary>
    ''' This property will return the string structured value for a double rather than a decimal
    ''' </summary>
    ''' <value></value>
    ''' <returns>A String</returns>
    ''' <remarks></remarks>
    Public Property DoubleValueAsString As String
        Get
            If m_parameter.StorageType = DB.StorageType.Double Then
                Try
                    ' Returns the human readable string representation of a double
                    Return m_parameter.AsValueString
                Catch ex As Exception
                    Return ""
                End Try
            Else
                Return ""
            End If
        End Get
        Set(ByVal value As String)
            If m_parameter.StorageType = DB.StorageType.Double Then
                Try
                    ' Sets the human readable string representation of a double (7'-0" or 150mm)
                    m_parameter.SetValueString(value)
                Catch
                End Try
            End If
        End Set
    End Property

AU2010 CP333-1 .... 10001110100001011010100001101010001 1110100

Well I'm finally back and unwound from the trip to Las Vegas where I taught my first class for Autodesk University.

From what I could tell, about 140 (93 people completed a survey for me - THANKS) of the 186 that signed up for the class actually showed up and surprisingly, my speaker rating is an overall 4.469 out of 5! The comments people left were mostly right on point and next time I'll avoid the live code scrolling and slap the key stuff on some super duper slides and maybe even add some sound FX to keep people's senses going.

I just wished I had more than a tiny 60 minutes to present all the madness... I left a ton of information out of the presentation due to timing constraints... Oh well (enough complaining)...

William Lopez Campo mentioned my class as his favorite (I think he's just being nice ;) in his recently famous blog post entitled "AU 2010: My top 5 lists"... I have to say I read the whole post and couldn't agree more with what he wrote in there... right on point.

It was also really cool to meet all the industry badasses in regards to BIM in person (too many of them to name... you know who you are).

So the class was entitled "Leveraging the Tail End of the BIM Life Cycle with APIs" and was centered around how to build a powerful web environment where people could directly interact with data in a BIM model and even synchronize modified data back into the model if they so chose.

I'll be elaborating on this topic in future posts so don't go anywhere!!