Managing Q&A Synonyms in Power BI for Office 365

***Note this post applies to Power BI for Office 365 (deprecated as of 12/31/2015). Though some concepts remain the same or similar, all details are not necessarily applicable to the new Power BI V2 service.***

Before diving into details, I have a question for you reader:  What do you expect to happen with this sequence of events?

  1. Upload an Excel workbook to Power BI for Office 365. Enable it for Q&A.
  2. In the Q&A optimizations area of the Power BI Site Settings (in the web), set up some synonyms & refine the auto-generated synonyms. Perhaps you also create & refine phrasings.
  3. Re-upload the Excel workbook after some modifications were made to the Excel file.

Will the work you did on synonyms in the web still be there after the Excel workbook has been modified? Nope!  There's a way to handle this so you don't lose any work though.

Note that this information is based on what I experienced with a client project in January 2015 using Power BI for Office 365. This does not currently apply to the new Power BI Preview & I don't know to what extent Q&A will be similar in the new Power BI Preview platform. With Power BI constantly evolving and improving, this information will become out of date so be sure to do your own testing.

Viewing Synonyms in the Excel Client vs. in Power BI Site on the web

The diagram above depicts that you can create synonyms in two places:

Inside of Excel you can only see user-generated synonyms - i.e., the synonyms you set up. You cannot see phrasings inside of Excel at all currently, nor can you see the system-generated synonyms. And there a *lot* of system-generated synonyms!

In the Q&A optimizations area of the Power BI site, you can view all synonyms, both user-generated and system-generated. Here's where the system uses its learning algorithms and attempts to add synonyms and phrasings on your behalf. This week we had a few chuckles with Meagan Longoria at a client site - she had a stock price type of column & the system-generated synonyms added things like cows and sheep based on the word stock. Clearly, the system-generated synonyms need some refinement (sidenote: a great future feature would be to have a setting which tells Q&A if it should try to generate synonyms automatically or if the user prefers it doesn't do so). Note that even after you refine the system-generated synonyms, it continues to learn so clearing out synonyms it sets up that aren't actually relevant will take a few iterations.

Since you can view everything related to Q&A in the website and not the Excel client, one way to handle things is to have a process where you use the web for all Q&A optimizations. Let's talk about how that might work...

Managing Q&A Optimizations - Export File

The above diagram highlights "XLSX File 1" but also illustrates how Q&A optimizations are handled on an individual file basis, and that the export files would be done on a per-file basis.

This technique presumes that you have a file share, folder, or source control somewhere where the Q&A optimizations export file would reside. The flow would look like this:

  1. Upload an Excel workbook to Power BI for Office 365. Enable it for Q&A.
  2. In the Q&A optimizations area of the Power BI Site Settings (in the web), set up some synonyms & refine the auto-generated synonyms. Perhaps you also create & refine phrasings.
  3. Export the Q&A optimizations to the designated location.
  4. Re-upload the Excel workbook after some modifications were made to the Excel file.
  5. Import the Q&A optimizations from the TXT file stored in the designated location.

As long as the person doing Q&A optimizations is diligent about steps #3 and #5 (especially #3), this should work.

Sidenote: Having versioning set on your Power BI document library is a great idea in case you ever need to revert to an older copy of the Excel workbook (be sure to set a limit on them though; 3-5 versions is usually enough). You might be inclined to think that if you "lost" your Q&A optimizations you could restore a previous version and regain them, then re-upload the latest workbook again. I wish I could say this works but it doesn't - we tried that this past week when a project team member lost hours of Q&A work. I can't explain why restoring an older version of the workbook doesn't bring back the Q&A optimizations at that point in time, but it didn't from my experience. I mention this so you know how very important the export file really is.

Re-Downloading the Excel File from Power BI Site to See User-Defined Optimizations

If you do want to re-download the file after Q&A optimizations have been done in the web, it appears that you need to wait a few minutes first for everything to get finished processing. On a client project we tested were the Q&A optimizations were done in the web, then the workbook was immediately downloaded. The new Q&A synonyms done in the web were not contained in the downloaded file. After a few minutes we tried the download again, and then the work done on synonyms was visible in the Excel file. So I can't say what's happening from the back-end perspective but there does appear to be a bit of a time delay. 

Handling Multiple Power BI Sites - Such as UAT and Production

If the Power BI environment is being managed by IT (as is the case fairly often from what I've seen), you may end up with two Power BI sites for your subject area: one for User Acceptance Testing (UAT) and one Production. That's a great way to roll out new changes for review & approval before impacting the 'live' version of the workbook. However, it introduces a few complications when it comes to Q&A.

Keep in mind that the 'scope' of Q&A optimizations is per file. The emphasis or focus will be different between the two sites:

  • In UAT, you would initially be working on refining synonyms and phrasings based on questions you initially believe will be asked of the model.
  • In Production, you would be focused on handling flagged items (i.e., items users flagged to indicate the Q&A results weren't helpful), handling unrecognized words, evaluating usage stats, as well as continuing to refine synonyms and phrasings as needed based on real-life usage.

So what does this mean for Q&A optimizations? Ideally, your flow in a two-tier environment would look like this:

  1. Upload an Excel workbook to Power BI for Office 365 (UAT). Enable it for Q&A.
  2. In the Q&A optimizations area of the Power BI Site Settings (UAT in the web), set up some synonyms & refine the auto-generated synonyms. Perhaps you also create & refine phrasings.
  3. Export the Q&A optimizations from UAT to the designated location.
  4. Import the Q&A optimizations to Production from the file stored in the designated location.
  5. Continue making Q&A optimizations regularly in the Production Power BI Site Settings.
  6. Export the Q&A optimizations from Production to the designated location.
  7. Import the Q&A optimizations back into UAT from the file stored in the designated location.

For this to work well:

  • Q&A optimizations cannot be occurring simultaneously in UAT and Production. If that ends up happening and you don't want to lose work from either environment, you could try to manually merge the data in the export file.
  • The export file in the designated location should be treated as "the file" to be shared by both sites. This means anyone involved needs to understand the process carefully.
  • It presumes you want Q&A to function the same in UAT as it does in Production. If that isn't a need, then do Q&A optimizations in Production only and ignore it for the UAT site. Whether you can simplify like this depends on your needs for the UAT environment.

Multiple Users Involved with Q&A Optimizations

We learned this one the hard way. It seemed like a good idea to go into the site, and have team member A set up synonyms for table 1 and team member B set up synonyms for table 2. At this point it does not seem like multi-user support is there. So, the way I'm thinking about it now is:  We know in the Excel client only one person can be editing the Excel file at a time. Treat it the same way in the web.

Summary of Q&A Export File

In conclusion, the big message here is to be sure and export any work done in Q&A each time it's complete. The export file can serve these purposes:

  • Ability to re-import Q&A optimizations in the event of a file overwrite that loses the Q&A optimizations done in the web.
  • Promotion of Q&A optimizations from one site to another (such as UAT to Production, or vice versa).
  • Import Q&A optimizations to other workbooks if the same file structure exists (for example: you have the same workbook structure in 10 sites with different data contained in each file, all secured separately for different user bases - I don't recommend this as it creates issues with keeping all the separate files in sync, but it can be done).

You Might Also Like...

Where to Find Q&A Optimizations in Power BI

Getting New Power Pivot Features