Skip to main content
Microsoft Idea

Power BI

Needs Votes

Simple Semantic Layer Support for Schema in Custom Connectors

Vote (2) Share
Paul Cooper's profile image

Paul Cooper on 06 Nov 2019 09:31:46

A simple Semantic Layer support could be incorporated in Power BI by having Power Query use a shared function in the Connector of:

getLocalization(, , Optional ) returns text

Power Query would check if the function exists and if it does then for the Navigation Table the localized version of the Entity names would be got using getLocalization(null, ) and names of fields in the types would be got by using getLocalization(, ). If the function does not exist or it returns null or an empty string then use the unlocalized version of the name.

The name of entities and fields as seen in the Connector dialog could then be localized using this function.

Once the data is selected the initial M code would be created as normal. The important point is that the "Source" entry in the M code would use the unlocalized names but an additional "Renamed Columns" line would be added after the "Source" that renames the columns to the localized versions of the names. (Power Query would add that second line for you.)

Unexpanded tables added as columns should also need to support this renaming of field names by providing the localized names in the Table.ExpandRecordColumn arguments. If a prefix is used then that should be localized too.

It is important that the unlocalized names are actually still used in the Source to support changes to localizations without breaking the query. A table once connected to will get the localized names at that time. Changing the localizations would not change the localized name in the query without deleting and re-adding.

The connector would implement the getLocalization function by having a hardcoded localization data or load the data from files or accessed from a resource such as a REST API call (Web.Contents). It would use the two argument values and the report developer's locale (Culture.Current) to select the correct localized text. If the locale is "fr-CA" (Canadian French) It would make sense to check for localized text with the locale "fr-CA" and failing that then with the locale "fr" and then finally "" (base locale) if all fail then return null. The exact method is the responsibility of the Connector Developer.

If the Connector Developer loads the data rather than hardcoding it then that means that the user could update localizations by editing the files or entries in the resource.

The getLocalization function could also be made available to the report developer for use in localizing data, assuming the localization files/resource have the appropriate data. In this situation the function is directly used in the report so the user running the report's locale would be used unless directly specified in function's optional third argument. (Some thought would need to go into what locale is used by the Power BI service for scheduled reports)

Please note this system is not an automatic translation tool but requires pre-populated data for the data sources' schema. It is up to the Connector Developer or manager of the resource used to create the localized/translated text in the number of locales they wish to support.