Taking a deep dive into the Trimble Autobid SQL Database with Power Bi

First and foremost, this article is an advanced guide that expands on the topics introduced originally here:

Even if you don't work with the Trimble Autobid software, there are some more common SQL processes that will be highlighted in this article that hopefully will add value to your current workload.

Specifically for Trimble Autobid, these processes offer you the ability to view and share your estimating data much quicker and more efficiently without having to navigate through the software and the reports it currently generates.

To get started, these are the things you will need.

YouTube Video: Link

(Many of the steps for this guide are illustrated here. So, I suggest having this open as a play along in another window while you are going through the rest of the article)

Files: (Includes PowerBi Template file, SQL Scripts, and Buttons)

Folder Download

Software: (all free)

• Microsoft PowerBi Desktop

• Microsoft SQL Server Management Studio (SSMS)

• Microsoft SQL Server 2017 or 2019 (Developer Edition)

Experience:

• A working knowledge of SSMS and how to perform backups and create local instances of network SQL Databases.
• General familiarity with SQL and T-SQL programming languages.
• Creating views for SQL databases and the GUI in SSMS to do so.

Additional Tools:

• Microsoft Visual Code and Visual Studio Code, specifically the SQL data tools and extensions to help with development of additional queries.

Getting Started:

Step 1: Open SSMS and connect to your Network Trimble SQL Server and your Local Server on your machine. (Usually is the same name as your Computer) if you don't have one. You'll need to install a new SQL Server instance. (The free developer version is the one I have has the most success with)

Step 2: Select your Network Database and create a copy using "Export Data Tier Application"

Step 3: Expand your Local Server tree and right click the "databases" folder and import your .BACPAC file from your Export in Step 2 and follow the prompts.

Step 4: Name this database as "ABMC_Local" or what ever you prefer. Just remember that you will be referencing this in the PowerBi template later on. This database will stored on your local hard drive and will have better performance then your Network version where it is limited by Network speeds.

• For this example I created a database copy named "ABMC_Demo". I also suggest having multiple local copies for development, testing, and Production. This will offer piece of mind and it will make your life easier when it comes to building tools and queries.

Step 5: Perform a backup of the database so you can return to its original state at import if you make a mistake.

Step 6: Go to "File" and open the 2 queries included in the file download.

Step 7: Navigate to the "ABMC_ReplaceWeblemMulti.sql" file and Select the database you created from the drop-down list.

• The reason why we are executing this query is to update the "LabDef1.Description" field with the proper values. Specifically it is updating MCAA Weblem Site links by replacing "www." with "old." The update will give us the ability to verify a labor value in our dashboard by selecting the link and opening the correct MCAA Weblem page in the web browser. Trimble hasn't yet updated this table to reflect the changes with the New Table ID's. So, If we use the current default values in the "LabDef1.Description" field, it will give us a 404 error.

• The new Weblem site is organized differently and has different Table ID's that are tagged differently (shown below). This is the reflective table from above (Pipe, Carbon Steel, Butt Weld)

• This naming convention change does have some issues which I won't dive into but, I'm assuming that the MCAA and Trimble are working on this in a forthcoming update.

Step 8: Navigate to the "View" folder of your database and right click. Select "New View"

Step 9: Copy from "Select...." to the end of the "Where" statement in the ABMC_PipeLookupView.sql file

Step 10: Paste this into the SQL pane of the View Designer, then right click and select "Execute". This generates a table that combines all of the Labor Values per each MCAA Weblem Table along with the respective price.

• Once Executed, save the View as "ABMC_PipeLookupView". This is now an Object in the database and can be imported directly into PowerBi

Step 11: Open the a blank PowerBi File and Navigate to File->Import->PowerBi Template. Select the "ABMC_PipeLookupDB_Demo-Template" and import it.

Step 12: Ignore the prompts and open the Power Query Editor, it is here that we will enter our Server and Database info into the respective parameters. Once entered, select your values in the "Current Value" drop down for both the Server and Database parameters.

Step 13: Select the "Buttons" query and Navigate to where your file download is. This will update the Buttons in the first slicer of the dashboard for sorting our Values based on Item Category. Select "Close and Apply".

Now we have a dashboard that shows us all of the MCAA Component Labor and Pricing for the Item Categories represented by the buttons. We also have the ability to verify the labor with the updated MCAA Weblem Link (You will need to login first to pass the authorization).

In conclusion, I will be following up to this post with additional detail on how the SQL views are generated and why this method offers better query performance and stability. I'll also be sharing more examples of what additional information you can extract out of the Trimble SQL Database.

Including:

• Updating fabrication part pricing with your Trimble data
• Generating error reports per estimate
• Viewing all of your assemblies and historical takeoffs
• Creating measures and KPI's based off of Job Type, total Lf, and other metrics I've tried investigating.
• And many more that I still have yet to look into

Hopefully this opens the discussion for what other capabilities we have with the existing Microsoft platform as we are really just scratching the surface here. At the end of the day there are many ways I believe that we can add value to our current processes by leveraging the tools and data that we already have. So, getting as much knowledge out there for everyone is my goal.

And as always don't hesitate to ask with questions, I love the feedback!