I had a problem on my hands.
I was working on a large project. I mean REALLY large – multiple buildings, millions of square feet and lots and lots of information. One of my tasks was to keep track of all the room specific requirements. The owners had their requirements, the engineers had theirs, and we had our own as well. It was a lot of data – we’re talking well over a thousand rooms.
In order to keep my head above the water, I needed a better way to keep track of all this information. Excel wasn’t going to cut it anymore.On a project this size, it’s really difficult to keep track of everything. There are lots of cracks important information can fall into.
A better way to manage project data
As we all know, designing a building is an information intensive activity. There are thousands of decisions that need to be made. Hundreds of products need to be evaluated. I’m pretty sure there’s more wood in all the paper that goes into designing that paper than there is in the building itself. Storing and retrieving this data is a major challenge on a day-to-day basis, let alone 5 – 10 years down the road.
Additionally, how can we leverage this data to inform future projects? Stuffing a bunch of files into a folder on a server somewhere isn’t going to cut it. We need some real tools to keep control of our data and to put it at our fingertips. This data is the lifeblood of our firms.
This is where databases come in.
What is a database?
A database is a structured collection of information that is stored in a computer. We interact with databases everyday though most of the time, we aren’t aware of it. Most large websites such as Amazon, Google, and Facebook are powered by databases.
Every database has a structure. The structure determines how all of the data relates to each other. The most common model used today is the relational model. Relational databases break data into named tables and establish relationships between these tables using a common key or identifier.
Software known as a “database management system” (DBMS) is used to implement the database structure. Microsoft Access, Filemaker Pro, mySQL, and SQL Server are all database management software applications. Most database software only stores data. Microsoft Access and Filemaker, however, provide tools for building a custom “front-end” for entering, retrieving, and presenting data.
Why use a database instead of Excel?
I’ve written a lot about Excel in previous posts. You can’t beat Excel’s ability to crunch numbers and present small amounts of data. However, if you’re dealing with a lot of data, you quickly run up against Excel’s limitations. Here are 5 major advantages of a database over a spreadsheet:
- Databases can easily handle large amounts of data. Even entry-level databases can handle a TON of data.
- Databases are inherently multi-user so multiple people can enter and edit data simultaneously.
- Databases are relational so you can link tables to ensure the data is accurate and to minimize duplication.
- Data can be formatted multiple ways by using reports. The data is separate from its presentation so you can create a variety of reports from the same data.
- Specific data entry forms can be created for entering information into the database. You can even configure your forms to exclude certain types of data.
When not to use a database
While they are very powerful, databases do have their limitation. Here are four instances when you wouldn’t want to use a database:
- You need to calculate a lot of numbers.
- You need to track a simple list of data.
- You want to create charts and graphs.
- You want to create “what-if” scenarios.
In these cases, a spreadsheet application like Excel would work much better than a database.
Database uses
I’ve built a number of databases over the years. As I mentioned at the beginning of the post, I was tasked with gathering all the room specific requirements on a large multi-building project. I quickly realized that Excel wasn’t up for the task so I built a database to manage all the information.
Each room had an entry in the database and there were fields for the owner, engineering, and architectural requirements. There were close to seventy fields in all. We had a large team working on the project so multiple people could enter data simultaneously. In the end, we produced a series of detailed reports outlining all the requirements.
I have also built databases to track drawing issues and field observation reports. One of the real benefits to using a database was the ability to output the data in multiple formats. For example, I could generate a detailed field observation report showing pictures for each observation or I could generate a brief summary of all the observations. Both reports used the same data but in a different format.
What’s next?
This is just a basic introduction to databases. Hopefully you have a better understanding of what they do and why they’re useful. In the next installment, we’ll look at the elements that make up a database, starting with tables for storing data. Stay tuned!
Thank you Michael, although I’m somewhat familiar with databases this is one of the best explanations I’ve read into the why’s and how’s for AEC.
I’m building a 3D model for a demo of a large site (50+ acres) with existing streets, a few existing buildings, but the rest is all speculation. My intent is to do massing studies for potential office buildings. I’d like to be able to change parameters like building size/density, and see the affects that would have on planning (Tax income, traffic increase, etc.) visually.
Would it make sense to use an outside database that would change the model as well as other associated data? Meaning you choose a taller office building in the database, then the massing study gets updated in Revit, along with parking requirements, and other content gets changed as well (graphs showing tax revenue for example)?
Thanks again, I always look forward to reading your content!
Thanks Dennis. Sounds like a really interesting project. How many points of data do you anticipate incorporating into the model? A database would make sense if you’re going to track a lot of varied data. You could create a table for each type of data (building related, tax income, traffic, etc. . . ) then use look-up tables to link it all together. One thing to note – you many need to use another application like Excel or Tableau to do any analysis. Most database applications, like MS Access, output data using reports but have limited graphing and charting capabilities. If you’re not anticipating a huge amount of data, I’d recommend Excel. You can link it directly to your model and use its built-in analysis and reporting features. You could even create an Excel dashboard so you can quickly visualize the project parameters. Good luck!
Hello Michael
This might sound a little stupid but how would substract any data from a model into excel files? Also, how can you link a revit file with ms access?
Great question. I’m actually working on a blog post on this topic. You can extract data from your model one of four ways:
1. Export a schedule to Excel using Export > Reports from the “Revit” button.
2. Use an add-in such as BIMLink or Exceler8.
3. Use Dynamo to extract specified parameters to Excel.
4. Use a custom macro.
As for exporting data to a database, you can use the Autodesk Revit “DB Link” tool. It’s available as a subscription add-in. I’ll look and see if there are some other database specific add-ins available.
I agree to the value of using databases for building information – after all that’s basically how BIM storage work. Working on healthcare buildings in the past using spreadsheets to store the necessary information could be a pain, the sheer quantity of information and inflexibility over how it could be managed was frustrating. Detailing finishes room-by-room for instance – 90% of the rooms would have, say, 4 basic finishes (floor, joinery, walls, ceilings) so you could have a nice concise spreadsheet with four columns for finishes. Except that you’d get some rooms with specialist finishes that that structure couldn’t accommodate. Relational databases can be set up to have any number of finishes associated with one room, it’s not necessary to consider in advance the maximum number likely.
I think it’d be worth clarifying the ‘When not to use a database’ list a bit. Certainly the second and fourth on the list (track a simple list of data and create “what-if” scenarios) are worth doing in a spreadsheet if there’s not too much information to work with – spreadsheets are invaluable for providing a quick answer to problems, the same could be achived with a database but at the cost of the length of time it would take to set the database up. The inability of databases to ‘create charts and graphs’ is probably just an inability to do it easily though – I’m not familiar with Access so I don’t know if that has any such tools but they’re certainly not impossible. Unless you need to create charts and graphs from the same set of data very frequently and at a moment’s notice I’d again agree it’s easier to copy the data from a database to a spreadsheet app with the capability built in than to set up some means of generating them direct from the database.
‘1: You need to calculate a lot of numbers.’ I feel this is actually somewhere databases can be much stronger at than spreadsheets, except that it’s not the database itself with that strength but rather the querying and reporting ability. Generally with spreadsheets the formulae for undertaking calculations are stored cell-by-cell – you end up with a lot of copies of the same calculation. There’s a big risk with that that you might end up with one cell with a slightly different calculation (or ‘referencing’ the wrong cell) than others, which can be difficult to spot and have disastrous consequences for the final output from the spreadsheet. Also, if you need to tweak the calculation used then it needs to be tweaked multiple times. In databases, the data is stored in one place and the calculations another (whether as a stored procedure in the database or externally as part of the query collating information for reports). Therefore it doesn’t matter how many bits of data you’re working with, there is just one copy of the formula that does the calculation to check and, if necessary, modify.
Is Access, with its ability to create front-ends for data input, querying and reporting, capable of being used with a variety of databases providing the storage? I know the database part of LibreOffice can, although it’s not something I’ve used yet. I thought it worth mentioning as a free way of seeing the potential that the similar Access has.
Chris – Sorry for the delay in getting back to you and thanks for the great comment. You make a good point about calculating values via a database report. One of the most frustrating things about spreadsheets is having to copy a single formula to all the rows of data. Yes, a report handles this much better. As for using Access as a front-end for other databases, I”m not sure. I’ll definitely take a look. If it can, I think LibreOffice would still be a better bet since you could pair it with an open-source back-end database like mySQL.
Hello Michael,
I’m a bit late to the party maybe, still, lately I’ve been trying to figure the best way to populate a given Excel file (provided with a certain formatting) with datas extracted from Revit.
On one side the provided Excel file, on the other Revit datas from BimLink Excel file.
I’m not an expert at Excel or at Data base so I’m just looking for a few hints to choose the right direction to make my spreadsheet looking nice without manual work.
Is spreadsheet enough or should I explore DB solution (which sounds totally exciting/scary stuff to me)?
Vincent,
The Revit data needs to fit into a specific Excel file, am I understanding correctly? You might be better off exporting the Revit data to its own Excel file then using a macro or links to transfer the data to the provided spreadsheet. I think that would be best. Databases are powerful but there’s a definite learning curve. It largely depends on how much data you have and what you need to do with it after. If it’s a lot of data and you need to share it with other applications, a database would be a good option. I hope this helps.
Michael