Managing Q&A Synonyms in Power BI for Office 365

February 1, 2015

***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?

Upload an Excel workbook to Power BI for Office 365. Enable it for Q&A.
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.
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:

Power Pivot in Excel - visible on the Advanced menu if you have the click-to run installation of Office.
Office 365 - visible in Power BI Site Settings (also known as cloud modeling in Power BI although I admit that I don't love that term for it since the functionality isn't modeling of data in a traditional sense).

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:

Upload an Excel workbook to Power BI for Office 365. Enable it for Q&A.
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.
Export the Q&A optimizations to the designated location.
Re-upload the Excel workbook after some modifications were made to the Excel file.
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:

Upload an Excel workbook to Power BI for Office 365 (UAT). Enable it for Q&A.
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.
Export the Q&A optimizations from UAT to the designated location.
Import the Q&A optimizations to Production from the file stored in the designated location.
Continue making Q&A optimizations regularly in the Production Power BI Site Settings.
Export the Q&A optimizations from Production to the designated location.
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

Where to Find Q&A Optimizations in Power BI

July 21, 2014

Just a quick tip about where to locate the options in Power BI for doing Q&A optimizations. In the Power BI site, these are not where you might first think to look.

What is Q&A?

First, just a quick bit on what Q&A is and why you should care. Q&A is also referred to as natural language querying, and essentially amounts to using a Bing-like search box in order to return a report. This is interesting and important for your report consumers because it means the report author doesn't have to publish every single permeation of a report.

This behavior of constructing reports on the fly relies upon certain things in the underlying data model (Power Pivot) for it to return results properly. So the first thing you can do for Q&A is to have a proper data model and use Synonyms in the Power Pivot model. (See the link below for Part 2 for more info.)

In addition to proper modeling techniques, there are some optimizations that are now available in Power BI - i.e., available in the browser in the Power BI site. When done in the browser, these optimizations are written back to the original workbook. However, where to locate Q&A optimizations isn't quite as obvious as you might think.

Finding Q&A Optimizations in the Power BI Site

1. Go to the main page of Power BI.

2. Select the Q&A page within Power BI Site Settings.

3. Locate the workbook you wish to optimize, click the ellipses, and choose "Optimize for Q&A."

At this point, you have 4 options: the Overview, Synonyms, Phrasings, and Usage.

Synonyms can be done in either the Power Pivot workbook (assuming you have the correct version of Excel, that is...I discuss that more in this blog entry: Getting New Power Pivot Features), or in the Power BI site. If you start doing Q&A optimizations in the Power BI site, make sure you re-download a new copy of the workbook before making edits and re-uploading it! (If this becomes an issue, you might think about turning on versioning in the document library to keep 1, or maybe 2, versions in change history.)

Phrasings, however, can only be done currently in the Power BI site. I would say that the Q&A functionality available in the cloud only, along with the new licensing model for Power Map, is one of Microsoft's ways of encouraging customers to purchase Office 365 and Power BI licenses.

Who Has Access to Q&A Optimization?

From what I understand, you will see the Q&A optimization page if (a) you are a Power BI Administrator, or (b) you are the one who uploaded the file. Now that doesn't cover a situation where Suzie uploads the file but John is taking over management of it. I figure in that case John can download it, delete it, and re-upload it. However, I'm hoping the permissions for allowing optimization become a bit more granular. In my mind, the person who developed the model is the best person for optimizing it, but that of course depends on the optimizations.

Cloud Modeling?

In the documentation for Q&A from Office.com, they refer to Q&A optimizations also as cloud modeling. I personally have a very different definition of modeling. However, it's helpful to know what the lingo is if you are searching for help. Usage of the term cloud modeling makes me wonder if that means something more along those lines is coming at some point? Hmmm.

Finding More Information

Introduction to Power BI Q&A (and Cloud Modeling)

Demystifying Power BI Q&A - Part 1 < An introduction

Demystifying Power BI Q&A - Part 2 < Discusses good modeling techniques & synonyms

Demystifying Power BI Q&A - Part 3 < Discusses phrasings

How to Interact with a PivotTable in Power BI for Office 365

July 10, 2014

Being able to make a few quick changes to a PivotTable in the browser, without having to download the workbook, is a great feature.

Initially when you view an Excel workbook in Power BI (and by this I mean in Office 365 via the browser rather than the Excel client or the mobile app), the PivotTable field list does not display. Here is an example where I have my cursor placed in the PivotTable, but the pane on the right that I'm looking for does not pop up by default:

My guess is the field list doesn't appear by default in order to save screen space and/or not get in the way of the most straightforward report consumption experience.

View Full-Size Workbook

To get the PivotTable field list to appear, we need to click the small icon at the bottom right. When you hover on it, the tooltip for the button says "View full-size workbook."

After you've clicked the button, Power BI will launch the workbook a new window. You might need to re-navigate to the worksheet where your PivotTable is located because the new window will display the first worksheet in the workbook.

You'll notice there's now a green menu bar across the top which displays the standard Excel Services options. And, when you place a cursor inside the PivotTable, the PivotTable Fields pane on the right appears so you can interact with it in the browser.

URL String

There is one other way to do this besides using the icon at the bottom right and that's to modify the &action parameter in the URL string to be "&action=view" instead of "&action=embedview".

Default URL - does not display the menu bar or field list:

https://TenantName.sharepoint.com/_layouts/15/WopiFrame.aspx?sourcedoc={GUID}&action=embedview

Updated URL - displays the menu bar and field list:

https://TenantName.sharepoint.com/_layouts/15/WopiFrame.aspx?sourcedoc={GUID}&action=view

With Power BI being a young product, its behavior will continue to change and evolve. This information is current as of July 2014.

Why Bother Renaming Charts and Tables in Excel?

Ways to View Content Published to a Power BI Site

How to Create a Q&A Featured Question with a Custom Image in Power BI

June 17, 2014

In Power BI, the Q&A Natural Language (Bing-like search) capability is pretty cool for being able to render Power View visualizations in the browser "on the fly" quickly. I admit when Q&A was first announced, I was pretty darn skeptical. but now I see that one of the values of this capability is that every possible version of a report doesn't have to be created. If you've ever ended up with several versions of a report that are just a little different, you'll understand what I mean. Q&A also gives users a way to execute reports in the browser if they're on the go and not somewhere with a full-fledged version of Office handy. Q&A is also useful for users who aren't familiar with creating Power View reports from the ground up.

In this post I want to focus on a feature called Featured Questions (pun intended). Here's what a few of my Featured Questions (with custom images) look like on the Power BI home page:

Featured Questions displayed on the Power BI site

Featured Questions are useful to help users quickly render a report for popular questions that may be asked frequently. It also helps users get acquainted with Q&A initially.

At this time, there can be a maximum of 50 Featured Questions in a Power BI site.

Enable the Workbook for Q&A

Before using Q&A or setting up any Featured Questions, you want to make sure that the workbook(s) you want to query are Q&A-enabled. This can be done from the ellipsis next to the report in the Power BI site.

Enabling the workbook causes the Power BI engine to index the metadata such as all of the column names. This helps the natural language engine work its magic.

Synonyms and Other Q&A Optimizations

It's really important to make sure the workbook is optimized to handle Q&A. There are more features around this coming soon, but right now one of the biggest Q&A-related optimizations is Synonyms. These are done in the Power Pivot model, and can help the natural language engine understand that Unit is the same as Division, or Month Name is the same as Month.

The Power BI team posted a great reference of optimizations to do for Q&A here: Demystifying Power BI Q&A.

If you don't see Synonyms in your Power Pivot menu, it could be because you aren't yet using the Click-to-Run version of Office. More info on that here: Getting New Power Pivot Features.

Custom Image

You'll want to have your custom image(s) ready if you plan to use them for the background of any Featured Questions. Personally I think they are a nice touch. If you do use a custom image, make sure it is landscape shaped (rather than portrait) and at least 250 pixels wide.

I prefer to store images for use with Featured Questions in a SharePoint Online document library called Site Collection Images, but you can use whichever document library you prefer (as long as it's allowed that content type).

Once you have identified the document library to store your images in, go ahead and upload the image(s) there.

Once the image is uploaded, click on it so that one it is shown in the browser. Copy the URL from the browser address box so you have it - we'll need the URL in just a few moments.

Creating a Q&A Featured Question

To add a new Featured Question, go to the Q&A page by following the "Ask with Power BI Q&A" hyperlink at the top right of the Power BI home page.

Try your question out first in the search box at the top to make sure it returns the answer you expect. If the resulting visualization meets your expectations, then click the button to the right of the search box to Show Featured Questions.

Click the plus sign on the page to create a new Featured Question.

In the "Type a Question" box, input your natural language query that you just verified.

Select the checkbox if you wish for it to appear on the home page. If it's not selected, it'll appear on the Q&A page only.

Choose a tile size. The background color and background icon won't matter if you are using a custom image.

For the background image, input the URL that you copied from the images document library. After you click Save, it will be able to render a preview with that image and the Featured Question will be ready to use.

You Might Also Like...

Why Bother Renaming Charts and Tables in Excel?

Organizing a Power BI Site