Data Warehouse (DWH)

A data warehouse is a solution that aggregates all enterprise data–schedules and process information documents for example–into one intelligence platform that allows executives to customize reports unique to their organizational needs. A data warehouse saves time and delivers insights in a format that’s transparent and easy to understand. It’s a valuable business analytics tool that moves at the speed of business and that has a whole host of additional advantages as outlined below.

The Data Warehouse is an add-on. To learn more about pricing and bundles check e-Builder's Data Warehouse, data sheet, or talk to your Account Manager.

Connection Guide Overview

The purpose of this document is to provide connection information specific to your organization’s Business Intelligence (BI) reporting tool (or a database management tool) to assist with the setup and some considerations to be aware of.

Documented here is the most common platform from an industry standard perspective.

Trimble does not recommend any specific third-party BI tool.

Navigating the e-Builder Credentials e-Mail

e-Builder will send two e-mails once the instance has been established for your Data Warehouse. The e-mails provide the credentials that will be required when accessing the Data Warehouse and a breakdown of the e-mails are provided below.

e-Mail 1 - Server and Credentials Part 1

Server: prefix of jdbc:redshift: and the blue highlighted text (ebuilder-bi-us1.cel5p5ysuhuo.us-east-1.redshift.amazonaws.com:5439/production_us1) is really a combination of the following:

  • Driver: jdbc:redshift:// (only required if using a BI tool that does not have preloaded connection option for Amazon Redshift)
  • Server URL: ebuilder-bi-us1.cel5p5ysuhuo.us-east-1.redshift.amazonaws.com
  • Port: 5439
  • Database: production_us1

The Port is separated from the Server URL by a colon (eg. aws.com:5439) and the Database is separated from the Port by a forward slash (eg. 5439/production_us1).

For tools that do not have standard Amazon Redshift connectors as an option, the URL in its entirety will be used. In most cases, the BI tool will have an Amazon Redshift option and simply requires decomposing the elements of the URL and putting them in the corresponding dialogs within the BI tool. This guide will cover the majority of the industry standard platforms.

Schema: in the e-mail, the Schema is provided with dashes, but the correct syntax for the Schema requires underscores in their place.

e.g. 1cxxX8d7-7XXb-12pc-80f0-b1bXXX551d should be 1cxxX8d7_7XXb_12pc_80f0_b1bXXX551d

You will need to ensure that you update the Schema when entering into your BI tool

Username: Used to login into the Data Warehouse, this user is tied specifically to the Schema provided and no other Schemas are accessible although visible to the user (see the specific section for your tool for additional context on Schemas and initial view of the data)

Password: This is provided in a separate e-mail as shown below, the password is case-sensitive

Be conscious of copying and pasting to not add additional spaces before or after

e-Mail 2 - Credentials Part 2

Data Warehouse Credentials Sent to User by email

Password: This is provided in a separate e-mail as shown, password is case sensitive.

Connecting with a BI Tool

Trimble does not recommend any specific third-party Business Intelligence tool.

  1. Open PowerBI.
  2. Select the Get Data option (it will either pop up at the start of PowerBI or is an option in the File menu).

    Get Data option on the Power BI Desktop start page

  3. From Get Data pop-up window, enter Amazon Redshift into the search (or scroll and select Amazon Redshift).

    Search for Amazon Redshift on the Get Data Window

  4. Once Amazon Redshift is selected, click Connect.

    The first step will ask for the following:

    1. Server: This is the e-Builder Data Warehouse Server URL with the Port Included.

      e.g. ebuilder-bi-us1.cel5p5ysuhuo.us-east-1.redshift.amazonaws.com:5439

      Note: In some BI tools, there is a specific field for the Port, if that is your case, put the Port in the corresponding field and remove it from the server one.

    2. Database: This is the e-Builder Database.

      e.g. production_us1

    3. Click OK.

  5. The next step will ask for the following:
    1. Username: this is the username provided in the Credentials e-Mail 1.

      e.g. dw_clientname_user1

    2. Password: this is the password provided in the Credentials e-Mail 2.

      e.g. abc123-AB1-12ab

    3. Click Save.

  6. Once the credentials are saved, a Navigator pop-up will appear, here you will need to select the Schema provided to you in Credentials e-Mail 1.

    • If you select the correct Schema, you should see a preview.

    • If you select the incorrect Schema, you will see the following error:

For additional details on the data set, please refer to the DWH Data Dictionary posted in the Community under the “Data Warehouse Tips & Tricks group” and the “Getting Started Guide”. To reach the Community, log into e-Builder with your regular credentials. Then select "Community" from the Resource Center dropdown menu under the “?” icon.

Troubleshooting Connectivity

If you have checked the Trimble Unity Construct Community under the 'Data Warehouse Tips & Tricks' group and found no outage or issue messages posted, it is worth troubleshooting before opening a ticket with Support.

  • Check the firewall: Ensure the fully qualified domain name (FQDN) is in the allowed list instead of the IP address. The IP address of the Data Warehouse (DWH) can change, so using the FQDN is recommended.
  • VPN Off: Try disconnecting from your VPN and attempting to connect to DWH again.
  • Validate Credentials: Use a database management tool (e.g., DBeaver) to validate your credentials.

If these steps are unsuccessful, please open a ticket with Support for further investigation.