powerbiqueryYou’ve built an elegant data model in PowerPivot, and you want to expose it to your users for natural language querying with Power BI Q&A. Even if your data model is perfectly well designed, there are several things you should do to help produce the best possible results in Q&A. Here are 5 things that are well worth the time spent to improve the answers that Q&A provides.

 #1.  Separate Workbooks For Each Topic/Domain

If your workbooks contain overlapping subject matter, it will be more difficult for Q&A to choose the correct workbook to answer a question – leading to unhelpful answers, and confused users. As a very simplified example, imagine that one of your workbooks represents bicycle sales data, and another represents skateboard sales: when a user asks “Total Sales by Year” which workbook should Q&A use? You will be better off to combine workbooks with overlapping subject matter, or disable one of them for Q&A.

 #2.  Clean Up Your Tables & Columns

If there are any tables or columns in your workbook that aren’t needed, delete them. If the tables/columns are needed to support relationships, but don’t need to be exposed for querying, hide them. Then clean up the names of remaining tables and columns. A table named “CurrCust” might represent current customers. For Q&A purposes, it would be far more useful to rename this table “Customers”. Even when you add synonyms (see below) Q&A will use the actual table or column name to make suggestions to the users… so the more understandable these names are, the easier time your users will have.

 #3.  Set Up Synonyms

Synonyms can be set up in the workbook (in the PowerPivot window, Advanced tab), or with the new “Cloud Modeling” interface within SharePoint Online. These are very useful because different people will ask the same question in different ways. Without synonyms, Q&A only knows how to reference things by table or column name. Consider a case where you have a measure named “Total Opportunities” and another called “Total Sales”… things are going to get confusing when someone asks for “Total Sales Opportunities” unless you set up a synonym for this.

 #4.  Add Custom Phrasings

This is a newly added feature in Power BI: the ability to create custom phrasings. This happens only in the Cloud Modeling interface, and not in the workbook (at this time). Custom phrasings are a bit complex to work with, as might be expected considering their power: phrasings capture the relationships between things and therefore phrasings are the real engine behind Q&A… these are so important, that they deserve a blog post of their own. Until then, suffice it to say that phrasings will allow you to define new terms, resolve ambiguity, and improve the way that Q&A restates the users’ questions.

#5.  Pay Attention To Usage Data

No matter how much time you spend in advance, optimizing your workbook for Q&A… in the end the users will ask unexpected questions in unexpected ways. The Cloud Modeling interface provides access to actual usage data, allowing you to rework and refine your synonyms, phrasings, etc. Pay special attention to flagged questions – these are the questions your users have flagged as having unhelpful answers.

Time spent in these 5 areas of Q&A optimization will be time well spent: your end users will not only get better results, they’ll also generate usage data that will help you to further refine and improve your workbooks – an ongoing cycle of improvement.