Introduction

This document provides an overview of the Power BI Data Model leveraged for VIM’s PowerBI Reports. Having an understanding of the Power BI Data Model will enable VIM users to:

Better understand how VIM’s Power BI reports work
Have the knowledge to add customizations to VIM Power BI reports such as new visuals and calculations

To understand the Power BI Data Model, it is important to understand some important definitions:

Power BI is an analytics platform for self-service and enterprise business intelligence (BI). It provides nontechnical business users with tools for aggregating, analyzing, visualizing and sharing data
Each Power BI report is made of a Data Model, which is a logical representation of all the data and relationships contained in the report
The data model in our case is a representation of data coming from Revit through the VIM conversion process. This data has been transformed via the VIM Object Model into a format that is suitable for Reporting

Ultimately, combining Power BI with the VIM platform enables advanced data analytics for architectural designs in Revit, helping to unlock insights and drive decision making.

Data Flow: How Data goes from Revit to Power BI

This article will delve into the VIM Power BI Data Model, which consists of the following components:

Tables - used to store different types of data, such as information on Families, Types, and Instances
Transformations - used to manipulate data to make it easier to perform reporting
Relationships - define how tables are related to each other, such as Instances being related to Family Types
Calculations and Measures - allow users to add new data points using formulas and expressions

The VIM Power BI Data Model

Tables

Tables are the fundamental building blocks of any data model and in our case represent data from the VIM Object Model. The underlying data source for all tables in the Power BI Data Model originates from a SQL Server Database which houses the VIM Object Model that is extracted from the originating project BIM file.

After the data is loaded into Power BI, it is prepared for visualizations using both SQL queries and Power Query transformations. This helps aggregate, append, and modify the data before loading it into Power BI. We use this approach to ensure efficient performance and reduced load times.

Sample List of Data Model Tables

There are over 50 tables in the Data Model, which have been organized in an easy-to-understand manner.

Table Naming Convention

Tables are named in a manner that explains their underlying usage:

The Element naming prefix denotes tables related to the Element table such as Element-In-System and Element-In-View
The Instance naming prefix denotes tables that are related to Instances, such as the Instance-Host and Instance-Level tables
The UI naming Prefix denotes tables that are used to simplify the user interface and visualizations

Column Naming Convention

Columns have been named in a way that explains their underlying meaning:

The _key column represents the unique identifier for that table
Columns deriving from other related tables have period separators, such as the Element.Name column in the Instance-Room table
The main Identifier Descriptions in tables columns have a Card icon beside them (see screenshot below)

Column Naming Convention for the Instance-Room Tables

Hidden Columns

Certain columns in the Data Model are hidden from the report view but are available to see in the Table View or Model View. These columns have a hidden icon and can be unhidden if needed:

Below is a list of all Tables in the Data Model as well as their description:

Table Name	Description
__EFMigrationsHistory	Used for automatic data schema version detection; this table is used for internal processes and can be ignored
BimDocument	Contains model metadata
Category	Represents the category to which an Element may belong (ex: Door, Floor, Ceiling, etc...)
Element	Represents an object which can be associated to a collection of Parameters and attributes
Element-CompoundStructure	Represents the collection of material layers which compose walls, ceilings, floors, etc
Element-In-System	Represents a collection of Elements which compose a System. These may be mechanical systems, piping systems, electrical systems, curtain walls, stairs, etc.
Element-In-View	An associative table binding an Element to a View; this can be a 2d or 3d view
Element-In-Warning	Represents a textual Warning in a BimDocument. Warnings designate whether there are any problematic authoring issues among Elements in a BimDocument
Element-PhaseFilter	Represents a categorization of elements based on their phase status: new, existing, demolished, or temporary
Element-View	Contains data on all Views
Family	Helps define elements and their behaviours by grouping them into Families. Families also represents a collection of FamilyTypes, for example an 'I Beam' Family
FamilyInstance-Parameter	Represents attributes of the element
Family-OmniClass	OmniClass is an industry-standard classification system in construction projects. It Contains comprehensive classification information translating OmniClass codes into its representing description
Family-Parameter	Represents broad attributes which apply to all the elements that belong to a family
FamilyType	Like Family, but the groupings are more defined and less broad. A single Family can consist of multiple Family Types. FamilyType represents the template by which a FamilyInstance is created
FamilyType-Parameter	Represents broad attributes which apply to all the elements that belong to that family type
FamilyType-Uniformat	Assembly Code and Assembly Description type properties based on the hierarchical list of Uniformat codes
Geometry-Node	Represents an instance in the G3D buffer of the VIM file. The ordering and the number of Nodes matches the ordering and the number of instances in the G3D buffer. This serves to bridge the gap between the Element entities and their corresponding instance geometry
Instance-Constraint-Base	Stores wall base constraint information, these are levels on which walls are placed on
Instance-Constraint-Top	Stores wall top constraint information, these are levels on which the top of walls is constrained by
Instance-Geometry	Stores geometric information for elements including their bounding box and geometry box details
Instance-Host	Maps the elements that can receive or provide structure for other model elements
Instance-Level	An associative table that binds elements to a level
Instance-Level-Base	An associative table that binds elements to a base level; mainly applicable for staircases and objects that connect between multiple levels
Instance-Level-Base-Parameter	Attributes associated with base level
Instance-Level-Parameter	Attributes associated with level
Instance-Level-Reference	An associative table that binds elements with the level that family is associated with
Instance-Level-Reference-Parameter	Attributes associated with reference level
Instance-Level-Schedule	Provides information for levels on the offset or where elevation is referenced from
Instance-Level-Schedule-Parameter	Attributes associated with schedule level
Instance-Level-Top	An associative table that binds elements to a top level; mainly applicable for staircases and objects that connect between multiple levels
Instance-Level-Top-Parameter	Attributes associated with top level
Instance-Phase-Created	An associative table that binds elements to the phase that it was created
Instance-Phase-Created-Parameter	Attributes associated with phase created
Instance-Phase-Demolished	An associative table that binds elements to the phase that it was demolished
Instance-Phase-Demolished-Parameter	Attributes associated with phase demolished
Instance-Room	An associative table that binds elements to a room
Instance-RoomFrom	An associative table that binds door elements to an originating room
Instance-RoomFrom-Parameter	Attributes associated with originating room information
Instance-Room-Parameter	Attributes associated with room information
Instance-RoomTo	An associative table that binds door elements to a connecting room
Instance-RoomTo-Parameter	Attributes associated with connecting room information
Instance-WorkPlane	Displays all Workplanes, which are virtual 2-dimensional surface information used as the origin for a view
Instance-Workset	Displays all Worksets
Material-In-Element	An associative table showing which Materials belong to which Element
Material-Node	An associative table binding a Material to a Node
MeasureTable	Table used to store measures being utilized in the report
Schedule-Data	Comprehensive scheduling information
Ul-SpecialCharacter	Stores special character information
Ul-View-CategoryDiscipline	Maps category labels to discipline classifications
Unit	Simplifies unit labeling data into human readable Labels and Specs
VIM	Contains VIM file related metadata

Transformations

Transformations to data in Power BI Data Model are performed using Power Query and enable us to clean up and reformat the data so that it becomes more suitable for reporting.

Examples of transformation steps include the following:

Renaming columns to be human readable
Removing unnecessary columns to keep the data model lean
Adding custom formula columns
Merging multiple tables to simplify the data model

Sample transformations for the Element Table:

Relationships

A relationship shows how Tables associate with one another. As a result of these relationships, the shape of the Vim Data Model is that of a Star Schema with Element being the center table:

The tables that are related to Element contain vital details about elements, such as their category, model, level, room, and so on.

Defining relationships is an essential part of the Data Model. It allows us to ask questions that span multiple tables, such as:

What are the instance parameters of all doors on Level 1?
What elements belong to the Pipe Category?

One example of a relationship in the Data Model is that between Element, FamilyType, and FamilyType-Parameter. This relationship shows that each Element may have at most one Family Type (many to one relationship) and that each Family Type may have multiple parameters (one to many relationship). The symbols for the type of relationship are shown below using a star “*” for the many side of a relationship and a “1” for the one side of a relationship.

Calculated Columns and Measures

In addition to utilizing the base data model for reporting, the data model can be further enhanced by adding custom measures and calculated columns to create new reports and visuals. All measures in the Data Model are contained in table MeasuresTable:

Calculated Columns

Calculated Columns are custom columns added to a table using Data Analysis Expressions (DAX) formulas. Unlike regular columns from the original data source, calculated columns are calculated only when you load or refresh your data in Power BI. These columns allow you to derive new data by performing calculations on existing columns within the same table. Calculated columns are useful for tasks like combining text, performing mathematical operations, or creating conditional statements, enhancing your dataset with tailored information for analysis and visualization.

For example, the below column was added to the Element table and concatenates the Category and Element Name of each row:

Measures

A measure is a dynamically calculated value used for data analysis. Unlike calculated columns, measures are not stored in the data model; instead, they are calculated on the fly based on the context of your report. Measures are created using Data Analysis Expressions (DAX) and can perform various calculations such as sums, averages, counts, and more. They are especially useful for aggregating and analyzing data, providing insights and metrics without adding unnecessary data to the model. Measures are commonly used in charts, tables, and other visualizations to enable interactive and meaningful reporting in Power BI.

Example

The below measure was created and provides the unique number of elements available in the Element table: