Upgrading Revit Power BI Reports to VIM SQL v5.6.0.c

This guide covers how to upgrade a customized Revit Power BI report for compatibility with VIM SQL v5.6.0.c.

Read this before you start

1. The steps below assume your custom report is based on the template:

BimAudit-RVT-SQL5.6.0-U5.1.0-DM0.14.0.pbix

2. The order of the steps below is important. Deviating from this order may break the connections between the semantic model and the report pages.

Need help?

If you need help upgrading, please give us a shout!

Initial Setup

Create a backup of your customized Power BI report.
If you have not already done so, install VIM Enterprise v1.2.141.17 or later.

If you are targeting Revit projects from ACC, please ensure your ACC app bundles are correctly updated before running your VIM Enterprise projects.
Open the new Revit template which targets "VIM SQL v5.6.0.c" in one instance of Power BI Desktop:
```
BimAudit-RVT-SQL5.6.0.c-U5.1.0-DM0.15.0.pbix
```
Run your VIM Enterprise Revit or ACC project to update the project's database to VIM SQL v5.6.0.c.
Open your customized Revit report in another instance of Power BI Desktop.
Ensure your report's data source is set to the newly updated database via "Transform Data" > "Data source settings"
Press "Apply changes" to load the data from your newly updated database.

Model View Tables

In the left navigation bar, click on Model View.

In the right table list, rename the following tables.

Previous Table Name		New Table Name
FamilyInstance-Parameter	👉	Element-Parameter
Instance-Workset	👉	Workset
Instance-Level	👉	Level
Element-PhaseFilter	👉	PhaseFilter
Element-Room	👉	Room
Element-View	👉	View
Element-CompoundStructure	👉	FamilyType-CompoundStructure
Instance-LevelReferences	👉	Element-Level-Info
UI-SpecialCharacter	👉	SpecialCharacter-UI

Delete the following tables as they are no longer required.

Table Name to Delete
Element-Level-Elevation ❌
Material-Node ❌
Instance-Room ❌
Click on the measure "UI-FieldParameter-Model-Workset-ElevationLevel-RoomNumberName-Group-GroupRevitID" and replace "'Instance-Room'[Number Name]" with "'Room'[Number Name]"
Save your changes.

Table Name to Delete
Element-Level-Elevation	❌
Material-Node	❌
Instance-Room	❌

Transform Data Updates

Click on Transform Data
Rename the top "Primary" group folder to "Core"
Rename the "Other Tables" group folder to "Helpers"

Click and drag your tables to re-order them in the following manner. This ordering will help with the instructions below.

Core/
  VIM
  BimDocument
  Element
  Element-Hierarchy
  Element-Geometry
  Element-Parameter
  FamilyType
  FamilyType-Parameter
  FamilyType-CompoundStructure
  Family
  Family-Parameter
  Category
  Group
  Room
  Level
  Element-Level-Info
  Element-In-System
  Element-In-Warning
  Material-In-Element
  View
  ViewSheet
  View-In-ViewSheet
  ViewSheetSet
  View-In-ViewSheetSet
  ViewSheet-In-ViewSheetSet
  Workset
  Phases
  PhaseFilter
  Unit
  __EFMigrationHistory
  Element-Join

Helpers/
  Element-BasePoint
  BuiltInCategoryMap
  Category-Discipline
  Camera
  CompoundStructureLayer
  DisplayUnit
  Family-OmniClass
  FamilyType-Uniformat
  Material-Join
  SpecialCharacter-Family
  SpecialCharacter-FamilyType
  SpecialCharacter-Material
  SpecialCharacter-UI
  System
  Warning
  WarningType
  INT-Warnings

PromotedParameters/
  <empty>

Not Loaded/
  AreaScheme
  BimDocument-PhaseOrder
  CompoundStructure
  Element-Area
  Element-BimDocument
  Element-DesignOption
  Element-Grid
  Element-Group
  Element-Level
  Element-Material
  Element-Phase
  Element-PhaseFilter-Parameter
  Element-Base
  Geometry
  Geometry-Node
  Instance-Group
  Instance-Level-Unique
  Instance-Room-1
  Level-Reference
  Node
  Phase
  Unique Instance Host
Other Queries
  MeasureTable

Add the new table Element-Measure-Info
- Right-click on the Core group folder > New Query > Blank query
- Right-click on the new blank query > Rename > Set its name to "Element-Measure-Info"
- Select the table and press "Advanced Editor".
- Set the table definition to the following, replacing the Sql.Database fields with your data source.
```
let
    Source = Sql.Database("__your_server_name__", "__your_database_name__"),
    dbo_ElementMeasureInfo = Source{[Schema = "dbo", Item = "ElementMeasureInfo"]}[Data]
in
    dbo_ElementMeasureInfo
```
Add the new table Element-Ifc-Info
- Right-click on the Core group folder > New Query > Blank query
- Right-click on the new blank query > Rename > Set its name to "Element-Ifc-Info"
- Select the table and press "Advanced Editor".
- Set the table definition to the following, replacing the Sql.Database fields with your data source.
```
let
    Source = Sql.Database("__your_server_name__", "__your_database_name__"),
    dbo_ElementIfcInfo = Source{[Schema = "dbo", Item = "ElementIfcInfo"]}[Data]
in
    dbo_ElementIfcInfo
```
Add the new table LevelInfo
- Right-click on the Helpers folder > New Query > Blank query
- Right-click on the new blank query > Rename > Set its name to "LevelInfo"
- Select the table then press "Advanced Editor".
- Set the table definition to the following, replacing the Sql.Database fields with your data source.
```
let
    Source = Sql.Database("__your_server_name__", "__your_database_name__"),),
    dbo_LevelInfo = Source{[Schema = "dbo", Item = "LevelInfo"]}[Data]
in
    dbo_LevelInfo
```
Rename the following tables

Group Folder Previous Table Name New Table Name
Helpers Material-Join 👉 Material
Helpers Element-BasePoint 👉 BasePoint
Helpers INT-Warnings 👉 WarningsCustomized

Group Folder	Previous Table Name		New Table Name
Helpers	Material-Join	👉	Material
Helpers	Element-BasePoint	👉	BasePoint
Helpers	INT-Warnings	👉	WarningsCustomized

For each table below, copy the "Advanced Editor" table definition from the new Revit template (BimAudit-RVT-SQL5.6.0.c-U5.1.0-DM0.15.0.pbix) which is opened in a separate Power BI Desktop instance.

when you are copying...

Replace the text value "localhost\SQLExpress" with your database server name.
Replace the text value "vim-empty-v5.6.0.c" with your database name.

Group Folder	Table Name		Notes
Core	Element	🔹	Source updated to "vw_Element_v4"
Core	Element-Parameter	🔹	Source updated to "vw_Parameter_v3"
Core	FamilyType-Parameter	🔹	Source updated to "vw_FamilyParameter_v3"
Core	Family-CompoundStructure	🧹	Now joins with the "Element" table instead of "Element-Base"
Core	Family-Parameter	🔹	Source updated to "vw_FamilyParameter_v3"
Core	Category	🔹	The column "NameFull" has been added for convenience, which joins the parent category name with the current category name.
Core	Group	🧹	Simplified table definition. Now joins with the "Element" table instead of "Element-Base"
Core	Room	🧹	Simplified table definition. Now joins with the "Element" table instead of "Element-Base"
Core	Level	🔹	Joins with the new LevelInfo table for additional information
Core	Element-Level-Info	🔹	Defines the primary level of each element as well as the geometry containment between building stories
Core	Element-In-System	🧹	Now joins with the "Element" table instead of "Element-Base"
Core	Element-In-Warning	🧹	Now joins with the "Element" table instead of "Element-Base"
Core	Material-In-Element	🧹	Now joins with the "Material" table
Core	View	🧹	Now joins with the "Element" table instead of "Element-Base"
Core	System	🧹	Now joins with the "Element" table instead of "Element-Base"

Delete the tables below as they are no longer required.

Group Folder	Table Name
Core	Element-Join	❌
Not Loaded	Element-Area	❌
Not Loaded	AreaScheme	❌
Not Loaded	Element-Phase	❌
Not Loaded	BimDocument-PhaseOrder	❌
Not Loaded	CompoundStructure	❌
Not Loaded	Element-BimDocument	❌
Not Loaded	Element-DesignOption	❌
Not Loaded	Element-Grid	❌
Not Loaded	Element-Group	❌
Not Loaded	Element-Level	❌
Not Loaded	Element-Material	❌
Not Loaded	Element-PhaseFilter-Parameter	❌
Not Loaded	Geometry	❌
Not Loaded	Geometry-Node	❌
Not Loaded	Instance-Group	❌
Not Loaded	Instance-Level-Unique	❌
Not Loaded	Instance-Room-1	❌
Not Loaded	Level-Reference	❌
Not Loaded	Node	❌
Not Loaded	Phase	❌
Not Loaded	Unique Instance Host	❌
Not Loaded	Element-Base	❌

Delete the Not Loaded group folder.
Save your changes and select "Apply Later"
Press Close & Apply
When the "Set storage mode" dialog appears for the tables "Element-Measure-Info", "Element-Ifc-Info", and "LevelInfo", select "Import" and press OK.

Model View Connections

Open the Model View once again to modify the table connections.
Right-click on the connection between "Element" and "Level" and press "delete"
Right-click on the connection between "Element" and "Element-Level-Info" and press "delete"
Click and drag the "Element-Level-Info" > "Element" field onto the "Element" > "_key" field to create a relationship
- Set the "Cardinality" to "One to one (1:1)"
- Set the "Cross-filter direction" to "Both"
- Enable "Make this relationship active"
- Press "Save"
Click and drag the "Element-Level-Info" > "PrimaryLevel" field onto the "Level" > "_key" field to create a relationship
- Set the "Cardinality" to "Many to one (*:1)"
- Set the "Cross-filter direction" to "Both"
- Enable "Make this relationship active"
- Press "Save"
Click and drag the "LevelInfo" > "Level" field onto the "Level" > "_key" field to create a relationship
- Set the "Cardinality" to "One to one (1:1)"
- Set the "Cross-filter direction" to "Both"
- Enable "Make this relationship active"
- Press "Save"
Click and drag the "Group" > "Group-Element._key" field onto the "Element" > "_key" field to create a relationship
- Set the "Cardinality" to "One to one (1:1)"
- Set the "Cross-filter direction" to "Both"
- Enable "Make this relationship active"
- Press "Save"
Click and drag the "Element-Measure-Info" > "Element" field onto the "Element" > "_key" field to create a relationship
- Set the "Cardinality" to "One to one (1:1)"
- Set the "Cross-filter direction" to "Both"
- Enable "Make this relationship active"
- Press "Save"
Click and drag the "Element-Ifc-Info" > "Element" field onto the "Element" > "_key" field to create a relationship
- Set the "Cardinality" to "One to one (1:1)"
- Set the "Cross-filter direction" to "Both"
- Enable "Make this relationship active"
- Press "Save"

Update the measure "Level" > Story Above to:

Story Above = 
VAR CurrentElevation = 'Level'[Elevation]
VAR CurrentFile = 'Level'[Building]
RETURN
CALCULATE(
    MINX(
        FILTER(
            ALL('Level'), 
            'Level'[IsBuildingStory] &&
            'Level'[Elevation] > CurrentElevation &&
            'Level'[Building] = CurrentFile
        ),
        'Level'[Level.Name]
    )
)

Delete the measure "Level" > Elevation - Story Above
Select the measure "UI-FieldParameter-Model-CategoryTag-Workset-Level-Category"
- Set line 5 to
```
("Level", NAMEOF('Level'[NameWithElevationFeetDecimal]), 3),
```
- Press the check button to accept your changes.
Select the measure "UI-FieldParameter-Model-Workset-Level-Category"
- Set line 4 to
```
("Level", NAMEOF('Level'[NameWithElevationFeetDecimal]), 2),
```
- Press the check button to accept your changes.
Select the measure "Ul-FieldParameter-Model-Workset-ElevationLevel-RoomNumberName-Group-GroupRevitID"
- Set line 4 to
```
("Level", NAMEOF('Level'[NameWithElevationFeetDecimal]), 2),
```
- Press the check button to accept your changes.
Update the measures under "MeasureTable" > "Parameter Template" to match the template's revised definitions.
Save your changes

Report Pages

Open the Report view
Cycle through the report pages to update each one. Use the provided Power BI report template to identify the fields which need to be updated and copy the settings over.

note
If some widgets do not correctly update after modifying their settings, try deleting them and re-creating them.
Copy the contents of the newly added report pages into your customized report, if desired:
- "Element - Measure"
- "Levels - Elevations"
- "Levels - Elements"
Save your changes and validate that everything is working well.

Still stumped?

If things didn't go to plan, please reach out!

Initial Setup​

Model View Tables​

Transform Data Updates​

Model View Connections​

Report Pages​

Initial Setup

Model View Tables

Transform Data Updates

Model View Connections

Report Pages