skip to Main Content

by Avichal Singh

Now that Rob has acquainted us all with the Power BI family, I wanted to take a closer look at one of the relatively new members: Natural Language Question & Answer or simply Q&A.

Rob pointed out in his last post how Microsoft seems to be targeting large enterprises; perhaps as a result we have seen many an impressive demo (watch Amir Netz at WPC 2013 and James Phillips at WPC 2014). But you have to pause and consider whether the features demonstrated are eye-candy or are indeed useful in practical settings.

Q&A is one such feature, which after my first look I had labeled as eye-candy and set aside (speaking with others, I find many are in the same place). All that changed for me when I attended Adam Wilson’s Q&A session at the PASS BACON. Adam not only outlined the workings of Q&A, but also shared tips and tricks and real world implementation stories. After my change of heart, I rushed back to work and cobbled together our first Q&A workbook. Q&A articles on the Power BI MSDN Blog were an immense help as well. Here is what I learnt from implementing Q&A:

  1. One Q&A workbook per subject area
  2. Eat your own dog food
  3. Optimize for Q&A (Cloud Modeling)

Read on or watch the video version.

One Q&A Workbook per Subject Area

Power BI Q&A Rule 1: One Q&A Workbook per Subject areaI had imagined a Power BI world, where everyone was uploading workbooks with their own data models, after all that is what Power BI is for. And figured that Q&A and it’s users would struggle to make sense of all these workbooks.

The advice I received was to only enable one Q&A workbook per subject area (users can still upload as many as they like, just not enable them for Q&A). For example if you have Sales and Inventory data sets, only enable one workbook for each of these areas. If you have seen Jet Li in The One, you know what I am talking about. If you haven’t just click the image 🙂


Eat your own dog food: Use Q&A to respond to ad-hoc queries

We are a small BI team but have built impressively large Power Pivot model and now cater to ~600 users. We routinely prepare user training videos, conduct brown bags, training sessions in order to teach our users to fish, i.e. to be able to pull their own reports. However we have realized that there would always be users that need help and reach out to us with ad-hoc queries.

Q&A is a wonderful way to answer such ad-hoc queries! This is true even if you are a one man BI army catering just to the whims and demands of your manager 🙂

  1. Just pop the question in Power BI Q&A (tweak till you get desired result)
  2. Grab the URL and send it to the requestor
  3. When user opens the URL they are presented with the exact same view that you had queried in step 1
  4. User can then alter the question if they like (dig deeper) or play with the settings to alter the result

This is a great way to bring your users along. Send them something baked and let them play with it a little bit till they get more and more comfortable using Q&A for themselves. Who knows, they may stop sending you ad-hoc requests altogether!

Use Power BI Q&A to answer ad-hoc queries that you recieve


Optimize for Q&A (Cloud Modeling) is a godsend!

With a recent update, it is now possible to optimize your Q&A workbooks on the cloud itself. You can check:-

  • Overview: Summary Q&A information
  • Synonyms: Review automatically generated synonyms and create new synonyms manually
  • Phrasings: Bit advanced, but if some questions are not being answered correctly this lets you fine tune your Q&A
  • Usage: my favorite by far! Let’s you see exactly who is asking what questions, the questions flagged as unhelpful (the Flag is easy to miss, so teach your users to use that)Power BI: Optimize for Q&A (Cloud Modeling)

    Out of the four options (Overview, Synonyms, Phrasings, Usage) only Synonyms is available within the Power Pivot window as well, the rest are only available via the Cloud Modeling interface. Any changes made via Cloud Modeling are saved back to your workbook.

    Power Pivot also has the Synonyms Feature

    Other Tips

    Here are some other tips I have gathered from Power BI blog and my personal experience. The blog is much more detailed, so certainly give it a good read.

  • Give your tables and columns friendly names (Customer is better than DimCustomer or CustInfo)
  • Best not to use abbreviations (PreviousYearSales is better than PrevYrSales)
  • Always add some featured questions to guide users new to Q&A
  • Cleaner relationship and tables produces better results (closer you can get to a Star Schema the better)
  • Do Not Summarize: Where needed, set numeric columns as ‘Do Not Summarize’. Example the ‘Year’ column in your Date table. The default behavior for numeric columns is to SUM the values, but for some columns that is not the correct behavior. Just turn that off by changing Summarize By (on Power Pivot > Advanced Tab) to ‘Do Not Summarize’ for such columns
  • Hide all columns that are not relevant (consider deleting if they aren’t needed). This goes a long way in reducing clutter and making it easy to query and manage your model for Q&A
  • Users can pin a specific model to send all their queries against that model. This minimizes the chances of them being bounced around in different models as they phrase and rephrase their questions. However the ‘One Q&A workbook per Subject Area’ rule should minimize this issue

    It is all right if you’re still a skeptic. I would say, just give Q&A a fair try and then make up your mind.

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 3 Comments
  1. Thanks for the post Avichal,

    I strongly believe that Q&A has a great future…Specially when the new features presented at WPC roll out with the ipad app. The ability to create your own dashbord & KPIs and then swipe to combine/change graphs and dimensions….Say goodbye to ad hoc reporting..(at least the most common & simple ones).

    Imagine if in a couple of years (or more) Q&A combined with Cortana (microsoft’s siri) and Azure Machine learning…

    But Avichal can answer better as is one of the few that deployed Q&A in a big enterprise…

    P.S. Also thanks for the proof that an excel pro can make fantastic work as BI pro( at least with Power BI)

Leave a Comment or Question