How to Translate Dynamics 365 Field Values to Power BI

Colin Maitland, 23 January 2020

In this article, I demonstrate how to dynamically translate the Physical Name field values stored in the ‘Applicable From’ and ‘Related Field’ (i.e. SLA KPI) fields for Service Level Agreements (SLAs) and related Service Level Agreement Items (SLA Items) in Dynamics 365 to their equivalent Display Names for use in a Power BI data model.

The following image shows the configuration of an SLA and related SLA Items in Dynamics 365. In Dynamics 365, the Display Names rather than the Physical Names of the fields selected for the ‘Applicable From’ and ‘SLA KPI Field’ configurations are displayed.

In this demonstration:

•    the SLA is for Cases.
•    the standard ‘Created On’ field from Cases is selected for the configuration of the SLA ‘Applicable From’ field.
•    the standard ‘First Response By KPI’ and ‘Resolve By KPI’ fields for Cases have been selected for the configuration of the ‘SLA KPI Field’ field for two of the SLA Items; i.e. First Response By and Resolve By SLA KPI.
•    a custom field has been selected for the configuration of the ‘SLA KPI Field’ for one of the SLA Items; i.e. Assigned By SLA KPI.

In Power BI, the Physical Names of the fields selected in these configurations are retrieved by Power Query Dynamics 365 stores the Physical Names rather than the Display Names.

Here, the retrieved value for the SLA ‘Applicable From’ field is shown.

Here, the retrieved values for the SLA Item ‘SLA KPI Field’ field are shown.

In a Power BI data model, where these values are to be displayed in visualisations such as Charts, Tables, Matrixes and Slicers, or used in Filters, it is preferable that the Display Names rather than the Physical Names are used to provide a common user experience between Dynamics 365 and Power BI.

In Power BI, it is possible to retrieve the Logical Name, Display Name, Attribute Type and any other property for all the attributes of any Dynamics 365 entity.

The SLA ‘Applicable From’ values are Date/Time attributes and the SLA Item ‘SLA KPI Field’ values are Lookup attributes. In a Case centric Power BI data model these attributes only need to be retrieved for the Case entity. The Power BI data model does not know which have been used in the configuration of  the ‘Applicable From’ and ‘SLA KPI Field’ fields for any SLA or SLA Items. To ensure the Power BI data model is dynamic, all the Date/Time and all the relevant Lookup attributes need to be retrieved for the Case entity.  The relevant Lookup fields are only the 1:N Lookup fields from SLA Item to Case.

The following image shows the retrieved Date/Time and Lookup attributes for Cases. To reduce the number of queries required to retrieve this information all the Lookup fields, in addition to the Date/Time fields, for the Case entity have been retrieved. The attributes used by the configuration demonstrated in this article are highlighted. However, any of the relevant attributes might be used in the configuration of Case related SLAs and SLA Items.

Here is the query used to retrieve these Case entity attributes from Dynamics 365.

This query retrieves, filters and transforms the attributes metadata for the Cases, i.e. incident, entity from Dynamics 365. The ‘EntityDefinitions’ portion of the Source step retrieves the metadata for the Case entity attributes. Refer to my “How to configure Data Source Setting Parameters for a Power BI Data Model” article for details on the configuration and use of the “1 Web API URL Prefix”, “2 Web API URL Organisation” and “3 Web API URL Suffix” parameters.

Because this is an intermediate query that is not loaded into the Power BI data model the ‘Enable Load’ option for this query has been disabled. The use of the underscore prefix in the name for the query is my personal naming convention to indicate that the query is not loaded into the Power BI data model.

It is now simply a matter of replacing the retrieved Physical Name values retrieved by the SLA and SLA Item queries with the corresponding Display Name values from this query.

The following images show how the Case Attributes query has been merged with the SLA query via a Left Outer Join from SLA ‘applicablefrom’ to Case Attributes ‘LogicalName’. The original ‘applicablefrom’ field has then been removed,  the merged Case Attributes table has been expanded to select the DisplayName field and this has been renamed to ‘Applicable From’.

The following images show how the Case Attributes query has been merged with the SLA Item query via a Left Outer Join from SLA Item ‘relatedfield’ to Case Attributes ‘LogicalName’. The original ‘relatedfield’ field has then been removed,  the merged Case Attributes table has been expanded to select the DisplayName field and this has been renamed to ‘SLA KPI’. I have chosen ‘SLA KPI’ rather than ‘SLA KPI Field’ because this is the named displayed for this field on the form for SLA Items in Dynamics 365.

Having completed these steps, my remaining steps would be to ensure that the Power BI data model is transformed into a Star Schema based on dimensions such as Customer, Date, Owner, Queue, SLA, SLA KPI (renamed from SLA Item) dimensions and facts of the such as Cases and SLA KPI Instances.