Digital twin builder (preview) tutorial part 5: Create a Power BI report with digital twin builder data

This section of the tutorial is for creating a Power BI report using the Power BI desktop app to visualize features of your digital twin builder (preview) ontology.

You can create Power BI reports and dashboards with your digital twin builder ontology data by accessing the SQL endpoint of the lakehouse associated with your digital twin builder (preview) item. The SQL endpoint contains the ___domain layer of data, which exposes a set of views that directly reflect the logical structure and relationships defined in the ___domain ontology. You can use that ___domain layer as a data source for Power BI, which allows you to select entities and entity properties for use in reports.

In this tutorial section, you import digital twin builder ___domain layer data from the SQL endpoint to a new report in Power BI Desktop. Then you create a report with two pages: one with time series data visuals for the Condenser entities, and one that showcases relationships between MaintenanceRequests and their associated Distillers and Technicians.

Create report and import data

In this section, access your digital twin builder (preview) data through the ___domain layer in SQL, and import it into a new Power BI report.

1. In Fabric, open the SQL analytics endpoint for the lakehouse associated with your digital twin builder item. The SQL endpoint has the same name as your digital twin builder item with a dtdm extension, so for this tutorial it's called Contoso_Energydtdm.

   

2. In the SQL endpoint explorer, select the settings icon (shaped like a gear) from the left side of the ribbon across the top, and open the SQL endpoint tab.

   Copy the SQL connection string and the name of the SQL endpoint, Contoso_Energydtdm. You need these values to connect to the SQL endpoint in Power BI Desktop.

   

3. Open the Power BI Desktop app on your machine, and sign in with your Microsoft Entra ID. Make sure to select Microsoft account for the credential type.

   

   Note

   Troubleshooting: If you can't sign in or receive a permission error, try clearing out any prior data source permissions. To clear your permissions, open the File menu and select Options and settings, then select Data source settings. From the data source settings, you can clear any prior permissions. Then, retry signing in with your Microsoft Entra ID.

   

4. Once you're signed into Power BI Desktop, select the SQL Server data source to start configuring the connection.

   

5. In the Server field, paste the SQL connection string. In the Database field, paste the SQL endpoint name. Select Import and then OK.

   

6. In the Navigator pane that opens, select these tables: dom.Condenser_property, dom.Condenser_timeseries, dom.Distiller_property, dom.MaintenanceRequest_property, dom.relationships, and dom.Technician_property. Then select Load. This action creates connections to your lakehouse SQL endpoint.

   

7. Back in the main Power BI Desktop view, you see the Data pane populate with all the tables you imported.

   

Use the Save icon in the top left to save your progress so far as a .pbix file on your machine.

Prepare data with unique ID columns

Next, prepare the data for visualization by creating a unique ID column for each data table.

Remember that digital twin builder (preview) creates a unique ID for each entity instance by using both the EntityInstanceId1 and EntityInstanceId2 values together. In this section, you simulate this behavior in the Power BI report by creating a combined UID column in each table that concatenates the two values, making the data easier to work with later.

1. Using the view icons on the left side of the screen, select the Table view. Open the dom Condenser_property table.

   

2. Select New column, and enter the following column formula:

   UID = CONCATENATE('dom Condenser_property'[EntityInstanceId1], 'dom Condenser_property'[EntityInstanceId2])

   Save your formula with the check mark. This action adds your new column to the table.

   

3. Create similar new columns for the remaining tables, using these formulas.

   Table	New column formula(s)
   dom Condenser_timeseries	UID = CONCATENATE('dom Condenser_timeseries'[EntityInstanceId1], 'dom Condenser_timeseries'[EntityInstanceId2])
   dom Distiller_property	UID = CONCATENATE('dom Distiller_property'[EntityInstanceId1], 'dom Distiller_property'[EntityInstanceId2])
   dom MaintenanceRequest_property	UID = CONCATENATE('dom MaintenanceRequest_property'[EntityInstanceId1], 'dom MaintenanceRequest_property'[EntityInstanceId2])
   dom relationships (two new columns)	SourceUID = CONCATENATE('dom relationships'[FirstEntityInstanceId1],'dom relationships'[FirstEntityInstanceId2]) TargetUID = CONCATENATE('dom relationships'[SecondEntityInstanceId1],'dom relationships'[SecondEntityInstanceId2])
   dom Technician_property	UID = CONCATENATE('dom Technician_property'[EntityInstanceId1], 'dom Technician_property'[EntityInstanceId2])

4. Save the report and return to the Report view.

   

Create Page 1: Condenser time series data

The report defaults to Page 1 in the tabs across the bottom. In this section, you fill Page 1 with visuals for the Condenser time series data.

Create relationships

First, relate the Condenser property and time series tables together so that they're properly linked in visualizations.

1. From the top ribbon, select Modeling, followed by Manage relationships and New relationship.

   

2. In the From table, select dom Condenser_property and select the UID column that you created. In the To table, select dom Condenser_timeseries and also select the UID column.

   For Cardinality, keep the selections for One to many (1:*) and a Single direction. Check the box for Make this relationship active and select Save.

   

3. Select Close to close the Manage relationships modal.

4. Save your report.

Add visual for name filter

Next, add visuals to the report. The first visual is a filter based on the condenser's name.

1. From the Visualizations pane, select Build visual. Choose the Slicer icon from the visualization menu.

   

2. In the Data pane, select dom Condenser_property > DisplayName.

3. Change the slicer style to a dropdown menu by switching the Visualizations pane to the Format visual tab, expanding Slicer settings > Options, and selecting Dropdown.

   Resize and reposition the visual as needed so that it looks the way you want.

   

4. Select any blank spot in the report canvas to deselect the slicer before moving on to the next visual.

Add visual for time filter

Next, add a filter for time.

1. From the Visualizations pane, return to the Build visual tab and choose another Slicer visualization.

2. In the Data pane, select dom Condenser_timeseries > Timestamp.

   Resize and reposition the visual as needed.

   

3. Select any blank spot in the report canvas to deselect the slicer before moving on to the next visual.

Add visual for time series chart

Finally, add a time series visualization.

1. From the Visualizations pane's Build visual tab, choose a Line chart visual.

2. For the X-axis, drag over dom Condenser_timeseries > Timestamp.

3. For the Y-axis, drag over dom Condenser_timeseries > Pressure. Use the down arrow next to the field to change Sum of Pressure to Average of Pressure.

   Resize and reposition the visual so that it looks the way you want.

   

4. Save your report.

Now, you have a basic report with which you can interact and explore time series data for the Condenser entities. You can verify it works by selecting a condenser from the dropdown menu, and changing the date and time values. Experiment with different selections in the DisplayName and Timestamp slicers and observe how the time series visual changes.

Create Page 2: Maintenance requests, related to distiller and technician data

In this section, you add a page to the report for visuals illustrating MaintenanceRequest entities and their relationships.

Recall from part three of the tutorial, Define semantic relationships between entities, that MaintenanceRequest entities are related to Distiller and Technician entities. A Distiller has MaintenanceRequest and a Technician performs MaintenanceRequest.

The visuals on Page 2 reflect these relationships.

Select + at the bottom of the report to create the new page.

Create relationships

First, indicate how the relationships table connects to the Distiller, Technician, and MaintenanceRequest property tables, so that they're properly linked in visualizations.

The relationships table holds information about each relationship, including its source entity instance and target entity instance. You can reference these entities with the SourceUID and TargetUID columns that you created earlier. You need to create a Power BI relationship between those columns and the UIDs in each of the dom Distiller_property, dom Technician_property, and dom MaintenanceRequest_property tables, so that the ontology relationships are properly linked to their source and target entities.

1. From the top ribbon of Power BI, select Modeling, followed by Manage relationships and New relationship.

   

2. The first Power BI relationship you make indicates that some ontology relationships have Distiller entity instances as their source.

   In the From table, select dom relationships, and select the SourceUID column. In the To table, select dom Distiller_property, and select the UID column.

   For Cardinality, choose Many to one (*:1). Set the Cross-filter direction to Both. Check the box for Make this relationship active and select Save.

   

3. Select New relationship again to create another relationship. The next Power BI relationship indicates that some ontology relationships have Technician entity instances as their source.

   In the From table, select dom relationships, and select the SourceUID column. In the To table, select dom Technician_property, and select the UID column.

   For Cardinality, choose Many to one (*:1). Set the Cross-filter direction to Both. Check the box for Make this relationship active and select Save.

   

4. Select New relationship again to create another relationship. The next Power BI relationship indicates that some ontology relationships have MaintenanceRequest entity instances as their target.

   In the From table, select dom relationships, and select the TargetUID column. In the To table, select dom MaintenanceRequest_property, and select the UID column.

   For Cardinality, choose Many to one (*:1). Set the Cross-filter direction to Both. Check the box for Make this relationship active and select Save.

   

5. Select Close to close the Manage relationships modal.

6. Save your report.

Add visual for relationships list

Next, add visuals to this page. The first visual on Page 2 lists all the relationship types in the relationships table.

1. From the Visualizations pane, choose a Table visual.

2. In the Data pane, expand dom relationships and select RelationshipName, SourceEntityType, and TargetEntityType.

   Resize and reposition the visual as needed.

   

3. Select any blank spot in the report canvas to deselect the table before moving on to the next visual.

Add distiller filter

Next, add a filter based on the distiller's name.

1. From the Visualizations pane, choose a Slicer visual.

2. In the Data pane, select dom Distiller_property > DisplayName.

   Resize and reposition the visual as needed.

   

3. Select any blank spot in the report canvas to deselect the slicer before moving on to the next visual.

Add technician filter

Next, add a filter based on the technician's name.

1. From the Visualizations pane, choose a Slicer visual.

2. In the Data pane, select dom Technician_property > DisplayName.

   Resize and reposition the visual as needed.

   

3. Select any blank spot in the report canvas to deselect the slicer before moving on to the next visual.

Add technician data

Next, add a table that shows technician contact information, filterable by the technician slicer.

1. From the Visualizations pane, choose a Table visual.

2. In the Data pane, expand dom Technician_property and select DisplayName, Email, and TechnicianId.

   Resize and reposition the visual as needed.

   

3. Select any blank spot in the report canvas to deselect the table before moving on to the next visual.

Add visual for maintenance requests

Finally, add a table that shows maintenance requests, filterable by the distiller and technician slicers.

1. From the Visualizations pane, choose a Table visual.

2. In the Data pane, expand dom MaintenanceRequest_property and select EquipmentId, SiteId, Status, and TechnicianId.

   Resize and reposition the visual as needed.

   

Now, you have a second page of the report where you can interact with maintenance request entities, based on their relationships with distillers and technicians.

Experiment with selecting different distillers and technicians from the slicers, and observe how the tables update. The slicers allow you to see all the maintenance requests that are associated with a particular distiller, or assigned to a particular technician.

Conclusion

Now you have a Power BI report with two pages, each showing a different type of data for Contoso Energy. If you want to share your report with others, you can publish the report from Power BI Desktop to your Fabric workspace. Once a report is in your Fabric workspace, it's available to share.

In the tutorial scenario, the fictional Contoso Energy company can use and continue to expand on this report to relate data from multiple sources, and make data-driven decisions across sites.

This Power BI report completes the tutorial of creating a digital twin builder solution for Contoso Energy.

Next step