Weekly Workflow: Connecting Data Sources in Power BI

Posted on by Nicole Mater

Business Intelligence tools like Microsoft Power BI make it easy to overlay data from multiple different sources and draw connections across large volumes of data. As a storytelling tool, these analyses communicate to project stakeholders that the project team is tracking the metrics that mean the most to their success. Insights gathered through this process can also be gained and enable teams to make well-informed decisions that lead to better outcomes.

Power BI is compatible with many different data sources types, allowing for a wide range of use cases. For example, here are a few types of data you could link to Revit, IFC, or Rhino model data to support data-driven decision making:

  • Financial proforma
  • Bidding data for building materials or FF&E items
  • Construction sequencing by date or phase
  • Sustainability and building health
  • Post-occupancy monitoring
  • Facilities Maintenance tickets
  • Asset management
  • etc…

To create relationships between databases, users first need to load data into Power BI using the Get Data tab, then navigate to the Data Model tab to manage the relationships between datasources. Relationships are represented as lines which connect tables. When you first open the model view, you may notice that Power BI has created relationships for you. Power BI identifies similar fields in each data source and attempts to make connections for users. Generally, it is a good idea to remove these relationships and create intentional relationships instead.

Creating a new relationship is simple. Click and hold down your cursor on one field and drag the cursor to a compatible field in another table. Then, right click on the relationship line and select “Edit Relationship” to open a relationship settings window.

From here, you can change the cardinality of a relationship to One-to-one, One-to-many, or Many-to-Many in reference to the number of identical values in each of the selected fields. You can also select the cross-filtering direction for the relationship to either “Single”, where the connection is only active in one direction, or “Both”, where it is active in both directions. We generally recommend setting this to “Both”.

Check out the tutorial below for a demonstration. Here are a few links to related tutorials that can help you get started!

What now?