Real Estate Dashboard🏙️ with Revit and Excel Data

Introduction to Power BI and Speckle

In this section, Bilal introduces himself as a Solutions Architect from Speckle and discusses the features of Power BI, a business intelligence and data visualization tool.

Features of Power BI

• Power BI allows you to connect to various data sources such as databases, cloud services, and online datasets.

• You can transform your raw data using Power Query for cleanup and preparation.

• The drag-and-drop interface in Power BI makes it easy to create interactive reports with visualizations like charts, graphs, maps, and tables.

• Real-time insights can be obtained from Power BI reports.

Connecting AEC Applications with Power BI

In this section, Bilal explains that while Power BI has extensive data connections, it does not directly connect to AEC applications. He highlights how Speckle bridges this gap by connecting various applications.

Limitations of Power BI with AEC Applications

• Despite its powerful data visualization capabilities, Power BI does not natively connect to AEC applications.

• BIM (Building Information Modeling) and GIS (Geographic Information System) data are often trapped within host applications.

Speckle's Role in Connecting AEC Applications with Power BI

• Speckle acts as a bridge between different applications, enabling the exchange of data between them.

• The focus of this session is on the connection between Revit (an AEC application) and Power BI using Speckle.

Revit-Power BI Connection Workflow

This section outlines the workflow for connecting Revit with Power BI using Speckle. It covers sending models from Revit to Speckle, receiving them in Power BI for visualization, and extending the data with external sources.

Workflow Steps

1. Send the Revit model to Speckle.

2. Receive the model in Power BI and view it in 3D within the Power BI environment.

3. Extract metadata and properties from the Revit model to enhance visualizations in Power BI.

4. Highlight the link aspect of the connection by making changes in Revit and seeing them reflected in Power BI.

5. Extend the received Speckle data with an external data source, such as an Excel table, to create a relationship between different data sources in Power BI.

Scenario: Creating a 3D Real Estate Dashboard

In this section, Bilal presents a scenario where a 3D real estate dashboard is created for analyzing a building's potential revenue using Power BI.

Scenario Description

• The goal is to create a 3D real estate dashboard for a client who owns the Snowden Towers building.

• The client wants to understand how much money they can make from this building.

• The output of this session will be a comprehensive dashboard combining 3D visualization with built-in visual cards, graphs, and charts.

Sending Model from Revit to Speckle

This section focuses on sending specific elements (rooms) from Revit to Speckle using filters.

Sending Process

• Create a project in Speckle for organizing the data exchange.

• Filter out specific elements (e.g., rooms) in Revit using category selection.

• Use the Speckle connector to convert selected elements into a platform-neutral format compatible with Speckle.

• Share the generated link for others to view or access the converted model in Speckle.

Receiving Model in Power BI

In this section, Bilal demonstrates how to receive the model sent from Revit in Power BI using the Speckle connector.

Receiving Process

• Open Power BI and click on "Get Data."

• Paste the shared URL of the Speckle model into the dialog box.

• Authenticate and establish a secure connection to the server.

• Load the received data from Revit into Power BI as a tabular format.

Viewing Model in 3D within Power BI

This section focuses on visualizing the received Revit model in 3D within the Power BI environment.

Visualization Process

• Rename the query to "Rooms" for clarity.

• Utilize built-in 3D viewer functionality in Power BI to view and interact with the Revit model's geometry and elements.

Extracting Data from Revit to Power BI

In this section, the speaker explains how to extract data from Revit and import it into Power BI using Speckle. The process involves generating columns for commit object ID and object ID automatically, without the need for manual extraction in Power Query.

Generating Columns for Commit Object ID and Object ID

• When receiving data from Revit in Power BI, Speckle automatically generates columns for commit object ID and object ID.

• This eliminates the need to manually extract these columns using Power Query.

• To import the data, simply drag and drop the stream URL into the stream URL inputs.

Viewing the Model in Power BI

After importing the data from Revit into Power BI, the speaker demonstrates how to view the model inside Power BI. This step is considered magical as it allows users to visualize their Revit model directly within Power BI.

Opening Power Query

• To view the imported model in a tabular format, open Power Query by clicking on "Transform Data" button.

Extracting Useful Information from Received Data

Once the Revit data is imported into Power BI, it is structured in a tabular format. The speaker explains that certain columns are automatically generated during this process, including a column called "speckle type" which indicates the type of object. The focus is on extracting information related to room objects.

Understanding Speckle Type Column

• The "speckle type" column indicates the type of object received from Revit.

• Mesh objects represent geometry elements such as rooms.

• However, for analysis purposes, we are interested in extracting information about room objects themselves.

Selecting Room Objects

• To extract information about room objects, select "object.buildelements.room" column.

• This column contains records that represent the room objects.

Understanding Records in Power BI

• Complex structures like elements in CAD, beam applications, or GIS applications cannot be easily represented as lists or tables in Power BI.

• In Power BI, the data type that corresponds to these complex structures is "records".

• Therefore, room objects are received as record objects.

Extracting Key Properties from Room Objects

• Extract key properties of room objects such as name, number, area, and level from the received data.

• Use the "Add Column" -> "Custom Column" option to extract each property individually.

Adjusting Data Types and Removing Errors

After extracting key properties from the received data, adjustments are made to ensure correct data types. Additionally, any errors related to keys that do not exist in mesh geometry are removed.

Adjusting Data Type for Area

• Change the data type of the "area" column to decimal by selecting it and changing its type manually.

Removing Errors

• Some keys (name, area, level) do not exist in mesh geometry and result in errors.

• Remove these errors by highlighting the red lines indicating errors and clicking on "Remove Errors".

Loading Cleaned Data into Power BI

Once the data has been cleaned and adjusted, it can be loaded back into Power BI. This step involves extending the data with extracted metadata and waiting for it to update.

Loading Cleaned Data

• Load the cleaned data back into Power BI.

• The cleaned data includes extracted metadata about room objects.

Coloring Elements Based on Levels

With the extracted metadata available in Power BI, it becomes possible to color elements based on their levels. The speaker demonstrates how this can be achieved using the 3D viewer visual in Power BI.

Using Color By Functionality

• Drag and drop the "level" column into the color by input of the 3D viewer visual.

• This will color elements based on their levels.

Adding Slicer and Tooltip Data

In this section, the speaker adds a slicer to filter levels and includes tooltip data to provide additional information when selecting items.

Adding a Slicer

• Add a slicer to filter levels.

• The slicer allows users to select specific levels for visualization.

Adding Tooltip Data

• Include tooltip data for visuals.

• When selecting an item, the tooltip displays its name, number, and level.

Renaming Tooltip Data

The speaker demonstrates how to rename tooltip data for better clarity and understanding.

Renaming Name Tooltip

• Edit the query by clicking on the gear icon next to the step that extracts name from room objects.

• Rename it as "name" instead of "first name".

Renaming Number and Level Tooltips

• Similarly, rename tooltips for number and level columns accordingly.

Interacting with Other Elements

The connection between different elements in Power BI is highlighted. Filtering out levels in one visual also affects the 3D viewer visual, creating an interactive experience.

Interacting with Filtered Levels

• When filtering out levels in one visual (e.g., slicer), those filtered levels are isolated in the 3D viewer visual as well.

• This interaction allows users to explore specific levels more effectively.

Changing Room Names in Revit and Reflecting Changes in Speckle

In this section, the speaker demonstrates how to change a room name in Revit, send it to Speckle, and see the changes reflected. The process involves updating the room name in Revit, sending it to Speckle, refreshing Power BI to fetch the new version from Revit, and observing the changes in the data type and 3D viewer visual.

• The speaker navigates to level five in Revit.

• The office unit room is selected and its name is changed to "green roof".

• After applying the change, it is sent back to Speckle by uploading it to the Speckle server.

• In Power BI, clicking on refresh fetches the new version from Revit.

• The updated room name is now reflected as "green roof" instead of an office unit.

Extending Revit Data with an External Data Source

This section focuses on extending Revit data with an external data source using an Excel sheet containing rent prices. The goal is to calculate how much money can be made from different spaces in a building. The process involves importing the Excel sheet into Power BI, creating a relationship between tables based on room names, and calculating total rent based on room area.

• An Excel sheet with rent prices per square meter is prepared.

• In Power BI, the Excel workbook is opened and loaded into the environment.

• A table named "rent prices" is created from the imported Excel data.

• A relationship between this table and the existing rooms table is established using room names as a common attribute.

• A new column named "total rent" is created by duplicating rooms' area multiplied by related data from the rent prices table.

• Visualizations are added to display total rent and total area by room names and levels.

• The speaker demonstrates how to analyze the distribution of rent and area across different levels.

Conclusion and Additional Resources

In this final section, the speaker concludes by highlighting the benefits of freeing data from proprietary file formats and leveraging external data sources in Power BI. They encourage viewers to explore more tutorials and resources available on speckledot systems for further learning.

• Freeing data from host applications' proprietary file formats allows for various possibilities in environments like Power BI.

• Data can be cleaned, visualized, and customized according to specific needs.

• External data sources can be integrated to extend Revit data.

• Viewers are directed to visit speckledot systems/tutorials for additional resources and tutorials.