Synonyms, the secret behind Power BI Q&A and Natural Language Query

Natural language query was probably the most unused feature in Power BI, until they introduced the new Q&A visual in the end of 2019. I’m a huge fan of this feature, but it is sometimes a bit challenging to implement. Especially working with end-users from different background in different countries.

Back in September 2019, I wrote a blog about let your end-users ask questions to their data, as part of my Storytelling with Power BI blog series. This blog will take it one step further.

Q&A visual use case

Natural Language Query functionality, which is covered in the Q&A visual, is a powerful way of interacting with your dataset. Working with big groups of report consumers, you simply can’t speak to every single person to get their requirements clear and for sure not cover every single need for information in your report. In this case, the Q&A visual can be very useful! Report consumers can interact with the dataset without doing any technical complex stuff and get their questions answered based upon the dataset.

Since the visual is not hidden behind a button anymore, it is better discoverable and more user friendly to interact with the dataset from an end-user perspective. Also, as a report author, the Q&A visual can help you to get a better understanding of the need for information by reviewing the questions asked. For example, noticing that one specific question is asked very frequent, can lead to new insights and visuals to implement in the next release of your report. 

Before I start going into every detail, have a look at the video below posted by Guy in a Cube. Adam Saxton, made a great video about the new Q&A visual together with Justyna Lucznik, Program manager in the Power BI team.

Working with synonyms

Because I’m not going to tell you the same story as in the video above, I want to focus on what is only quickly highlighted in this video, but really adding more power to the Q&A functionality. Halfway the video, Justyna and Adam talk about synonyms which are added to make the Q&A interactions work even better. Synonyms can be very useful to let people from different background interact with your dataset.

The added value of synonyms

Many times, I see data models build in Power BI where the table and column names are technical names, as they are imported from the data source. Very unreadable and not user friendly at all. First thing I always do after importing the data, is changing names to be more user friendly. Changing the names is the first step in making your data model more user friendly for self-service re-use by end users, but also empowering the natural language query functionalities.

As noticed before, working with large groups of report consumers, it can be challenging to find the right name for tables and columns in order to let everybody understand where it is about. Especially when you are working with people from different background or even different languages, it is not very easy for them to interact with the Q&A functionality. Although translations are not supported (yet?) in Power BI, the use of synonyms can be of help here!

Adding synonyms in the model view

Synonyms can be added in the Power BI model view. Every field (e.g. measure, column, table) can have one or multiple synonyms. Multiple synonyms can be listed comma separated.

Synonyms1

The fun thing is, using the training functionality of the new Q&A visual, adds synonyms to your data model as well. So, adding synonyms yourself, can be a shortcut for training the Q&A functionality in your data model.

Are synonyms in the model view a new feature? No, it is not. The option to add descriptions and synonyms to objects in Power BI was introduced way back, but became better discover able at the introduction of the new model view back in November 2018.

Synonym ambiguity

Though, please do not add synonyms everywhere by default. You don’t want to introduce ambiguity in your model. This can occur when you add the same synonym to multiple columns or even tables in your model. Q&A will always try to find the best match related to the full context of your question, with taking relationships between different objects into account. However, it can happen that Q&A will give a different answer than expected when multiple items have the same synonyms.

But what about table names and column names with duplicate names? In previous versions of Q&A (before the Q&A visual was introduced) this was an issue, where it was required to rename one of both. In the current version of Q&A this issue is sorted.

Reverse engineer your synonyms?

While teaching Q&A in the interface, as Justyna explains in the video (at minute 3.45 and onwards) there will be synonyms added to the columns and measures in your model. In a separate section of the Q&A interface, there is an overview of terms and definitions you have taught Q&A.

Synonyms2

Keep in mind that it does not work the other way around. Every synonym you have added to your model, will not show up in the manage terms section in Q&A.

I have tried to find the synonyms in the metadata (Data Management Views) of the data model by using tools such as Tabular Editor and DAX Studio to query the data management views, but I could not find an easy way to get an overview of all your defined terms and synonyms with these tools. But there is more! The Linguistic schema can help out here.

Linguistic schema

However, there is an option to export all the synonyms from both entry options (tipped by Mike Carlo, owner of PowerBI.Tips). By exporting the linguistic schema from your Power BI data model, you will get a YAML file which includes all synonyms. You can export this file in the top ribbon of Power BI Desktop.

In the below example you can see the terms listed in the YAML file, which matches the synonyms in the data model as well as the terms managed by the Q&A setup. Notice that the synonym added by the Q&A setup also includes a last modified date.

Besides all the synonyms added by us, the report author, there is also a full list of suggested synonyms with State: Suggested which are automatically generated by Power BI and also having a weight, representing a score that the suggested term is the correct one.

Synonyms3_LinguisticSchema

The linguistic schema YAML file can also be imported back in Power BI again. So, editing the YAML file, makes it much easier to add a lot of synonyms in one go. Mike also sent me a link to one of his tools, Power BI Lingo, which helps you to get a better overview of the linguistic schema.

Just adding one more row to the terms section in the YAML file, will add the synonym to the data model, but it will not show up in the manage terms section of the Q&A setup. But by adding the LastModified section, like what you can see in above screenshot, the term will actually show up in the Q&A setup.

Find more about what you can do with the linguistic schema in the Microsoft documentation.

Wrap-up

In my opinion, natural language query is a very powerful and innovative way to interact with your report consumers. To let your users adopt this functionality and use the full potential, it is key to tweak the model, so it matches the background and frame of reference of your users.

Using synonyms can really empower the Q&A functionality to let it match more report consumers backgrounds. These synonyms can be added by using the training functionality in the new Q&A visual, manually adding synonyms in the model view or by editing the linguistic schema. Keep in mind, there is no central place to get an overview of all synonyms, other than go through all the objects one-by-one in the Power BI model view or exporting the YAML file. It would be a great addition if it is possible to read the synonyms with tools like Tabular Editor or DAX Studio.

Finally, Microsoft published some great documentation about the Q&A functionality with some best practices. Want to get the full potential out of it? Have a look at the below documentation:

Send your users on an adventure to let them explore the data themselves with Q&A!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s