Where to Find Q&A Optimizations in Power BI

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


Creating a SharePoint Server Farm in Azure - Part 2

In Part 1 of Creating a SharePoint Server Farm in Azure, we went through the screen shots and steps available for using the gallery resources available in Azure for setting up a SharePoint Farm. In Part 2, we're going to look at the next steps to determine the state of this new environment.

When we create the farm in Part 1, there were not any options for what services, apps, or functionality to be pre-configured. That makes sense since this is an IaaS environment, not PaaS. So what we have at this point is SQL Server 2014 installed on the SQL VM which is running Windows Server 2012 R2.  A SharePoint site ready to be configured on the SharePoint VM which is running Windows Server 2012. 

As a reminder, this is a demo environment for me so some of the settings are reflective of that and may not be consistent with what you would want to do in a production environment.

Pin the Resource Group to the Startboard

The first thing you'll want to do is pin your new resource group to the Startboard.  When it's done being configured, you should see a Notification.  Selecting the notification will open the resource group. Right-click the tile on the Startboard and pin it for easy access later.

Reviewing Details of the Resource Group

On the blade for the Resource Group, select the 2 more ... ellipses. That'll expand out to another blade which lists each individual resource.

Let's take a look further at the SharePoint VM.  Selecting that resource displays another blade out with more details about it + links to take further actions.

Connecting to the Virtual Machine

Now I'm ready to connect to one of the virtual machines. To connect, there's a button at the top of the blade for the virtual machine. (Note this is also where you can shut down, start, & restart the services. Since this is a demo environment for me, I'll be shutting down the VMs when they're not in use.)


After clicking the Connect button you'll be presented with a Open/Save/Cancel dialog from the browser.

What I usually do, because I'm far too lazy to come into the Azure portal and connect every time, is do a Save.  Where I choose to save it is a folder on my desktop called RDP which has various connections with their preferences saved.

In Windows Explorer, right-click the RDP file you just saved and choose Edit. There's 2 main things I'm interested in changing.  One is the pixel size for the monitor (on the Display tab). The other is under Local Resources; click the More button and choose all the checkboxes - especially drives. It's really helpful to get to your local drive when in a VM.  (Sidenote: I often need to edit the file with a text editor instead to get the pixel size exactly right. I talked about that a bit more here:  How to Perfect the Resolution of a Remote Desktop Session.)

When you're done, before you hit Connect, don't forget to click the Save button on the General tab. That'll save these preferences for the next time you launch the RDP session from your saved file.


Now let's connect to the virtual machine. When prompted, use the administrator ID and PW (i.e., the you provided on the "Create a SharePoint Farm" blade during setup).

Add Central Admin Shortcut to Desktop

Call me finicky, but the first thing I really want to do is get the shortcut to Central Admin placed on the desktop of my SharePoint VM. So let's bring up the Start menu from the lower left hand corner.

On the Start menu, Central Admin isn't set to appear there yet. So let's right-click and go to All Apps.

Locate the Central Administration tile.  Right-click once and choose Pin to Start.  Then right-click again and choose Open File Location.

Now we have a window where the shortcut is located. Let's right-click to copy it, and then paste it on the Desktop so it's handy for future use.


Now after all that we are ready to launch Central Admin and see what we've got!  It'll take it a bit of time to fire up because it's doing its just-in-time compiling thing.

In Central Admin, let's take a look at the web applications.  Click the "Manage web applications" hyperlink under Application Management.

On the web applications page, we'll see two addresses. One is our default URL, and the other is for Central Admin.


When we launch the default address, we are presented with a template selector. Your choice will depend, of course, on what you want to do with the site.  I'm going to choose the BI Center template.


The other thing you can do to check out the SharePoint environment initially is to go to Site Contents. There's a link for it on the left-side menu.

At this point you are ready to install and configure the SharePoint services and database instances you need for this environment. If you're interested in a BI environment, check out some of the suggestions here:  Resources for Installing and Configuring BI with SharePoint 2013 and SQL Server 2012.  (Even though the links in that blog entry all relate to on-premises installations, the majority of the information will still be relevant in Azure since this is an IaaS type of situation.) I also highly recommend Rob Kerr's SharePoint 2013 series of videos on MSBIAcademy.

Creating a SharePoint Server Farm in Azure

This morning at the Worldwide Partner Conference, the new SharePoint Server Farm in Azure was announced. As a BI developer with limited experience with administration and configuration, this option to fire up a multi-server SharePoint Farm in Azure piqued my interest.

Below is Part 1 my experience setting up the farm in Azure. (Perhaps this will prove to my husband that I'm not really playing Farmville as he seems to suspect!)  These steps assume you already have an Azure account created - if you don't, start here https://account.windowsazure.com/Home/Index first.

Part 2 is continued here:  Creating a SharePoint Server Farm in Azure - Part 2.

Finding the Link to Create a SharePoint Farm in Azure

Initially I thought maybe it wasn't available publicly yet because I could not find the correct choice when choosing New > Virtual Machines in Azure. That's because it's available in the new Azure Portal that's in preview mode currently.  Turns out that's because, among other things, the new Azure Portal has new capabilities to manage multi-tier applications using a Resource Group

First, click your ID at the top right of the browser window and choose "Switch to new portal." 

This launches a window for the new Azure Portal which is addressed like this: https://portal.azure.com/<YourAccount> and you will be logged out of the old portal.

Now we want to click the New button at the bottom left of the main page.

At this point since it's new, the SharePoint Server Farm is listed right away. However, let's go "the long way" just for fun.  Next click the Everything arrow.

Now we're looking at the gallery of resources that can be created in Azure. (Note that if you have the Gallery pinned to your Startboard, i.e., the home page in the new Azure Portal, you could have gotten to this point that way too.)

Select Virtual machines from the Gallery menu and locate SharePoint Server Farm in the list.

Creating a New SharePoint Farm in Azure

After you've clicked on the link for SharePoint Server Farm, its "blade" slides out to the right. From here you can take a look at the useful links they've posted and when you're ready hit Create.

At this point we get one more blade to slide out to the right which is where all the options are.

Options in Azure When Creating a SharePoint Farm

Resource Group

This creates a new Resource Group which will contain all related resources so they can be managed together. I'll name mine SharePointResourceGroup.

Choose this name carefully especially if you're not going to be using a custom domain name. Because of this choice, the URL address for my default web application is SharePointResourceGroup.cloudapp.net/. There are, however, options to manage the domain name in Azure.

User Name and Password

This will be your initial domain & local administrator. This is not your Microsoft account or Organizational account; it's just a user ID without the @company.com.

Domain Controllers

Here you set a Host Name Prefix (default = SharePoint), your Forest Root Domain Name (default = contoso.com), and choose a Pricing Tier (default = Standard A1) for the domain controllers.

I have left the Host Name Prefix at its default of SharePoint. I've also set a Domain Name and adjusted the pricing tier for the DC down to Basic A1 because this is just a demo for my personal use.  My demo doesn't need redundancy so I left the High Availability checkbox unchecked - normally you want to check this as that's a great built-in feature of Azure. 

SQL Servers

Here you have options for selecting a Host Name Prefix (default = SharePoint), a Pricing Tier for the SQL Servers (default = Standard A5), and the password for the service account.

This Host Name Prefix here defaults to SharePoint, even if you selected a different prefix for the domain controllers in the earlier configuration step. I've kept mine using SharePoint for consistency.

The default pricing tier of A5 includes 4 data disks, 2 cores, 14 GB of memory, and currently costs about $218.74 a month.  (Check here for Azure pricing.) Since mine is a demo, I'll scale it back to a basic plan since I don't need load balancing and auto-scaling. Most companies setting this up for anything other than trivial use like my demo will need beefier SQL Server specs.

The "Use the Administrator password" is asking if you want the service account to use the same password as the User Password that was specified on the first blade "Create a SharePoint Farm." For normal business operations, this would be no but for my demo purposes I've left this option checked.

Lastly, you specify what the name for the SQL Server Service Account will be for running the MSSQLSERVER and SQLSERVERAGENT services. This is in the format of <ServiceAccountName>@<DomainName>.com. The @<DomainName> uses the Forest Root Domain Name that was specified in the Domain Controllers section.

SharePoint Servers

In this blade you set another Host Name Prefix (default = SharePoint), Pricing Tier for the SharePoint Servers (default = A2), and two domain accounts.

As with the previous two sections, I kept the Host Name Prefix as SharePoint for consistency.

The "Use the Administrator password" is asking if you want the two new service account to use the same password as the User Password that was specified on the first blade "Create a SharePoint Farm." For normal business operations, this would be no but for my demo purposes I've left this option checked. If you choose to set individual passwords, you'll also need to set a pass phrase which will be used to join other machines to the farm.

The Setup User Account is the domain account that will be used to execute the SharePoint setup operations. The Server Farm Account is a domain account which will be used to configure and manage the SharePoint server farm, act as the application pool identity for Central Administration, and run the SharePoint timer service.

Optional Configuration:  Virtual Network

Under Virtual Network, the options are a Name (default = the Resource Group name) and Address Space CIDR Block (default =

Optional Configuration:  Storage Account

For the Storage Account, the default is to create a new storage account. Or, you can connect to an existing storage account. The default name for a new storage account is <ResourceGroupName>.core.windows.net.

You can also set a Pricing Tier for the storage account. Default = G1 which is Geo-Redundant with 3 local replicas and 3 geo-distributed replicas.

Optional Configuration:  Diagnostics

Here is where you set if diagnostics will be sent to the storage account.


The Subscription blade is where to specify which Azure subscription to use for these. I only have one to choose from.



This last option relates to where the farm will be created.


The last option near the bottom is to Add to Startboard. Keeping this box checked (which is the default) will create a live tile on the main dashboard when you log in to the new Azure portal.

And with that, all the options should be configured. You might want verify each is correct, then click the Create button. Now the Startboard will display "Creating SharePoint Farm" until setup is complete.

After about 10 minutes, the deployment is complete. Where to go from this point will be discussed in Part 2.

Update 7/17:  The Azure team looked at the correlation ID for my deployment and determined why the error was being shown on the Startboard (in the screen shot just above). They'll be getting it fixed in their next release. From my end, I can connect to the servers and connect to Central Administration so it looks like I can safely proceed with the next steps to get things set up.

Part 2 is continued here:  Creating a SharePoint Server Farm in Azure - Part 2.

Finding More Information

TechNet Blog:  Step-by-Step: Deploy a Highly Available SharePoint Server Farm in the Cloud in Only 8 Clicks

Azure Documentation:  SharePoint Server Farm

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

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:


Updated URL - displays the menu bar and field list:


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

You Might Also Like

Why Bother Renaming Charts and Tables in Excel?

Ways to View Content Published to a Power BI Site

Comparison of Surface Pro 3 to Surface Pro 2

Surface Pro 3

Surface Pro 3

Today I used my new Surface Pro 3 for the entire workday and thought I'd share the differences I've noticed as compared to the Surface Pro 2.

I purchased the first model that offered 8GB of RAM. The Microsoft website is a bit confusing on this point - it makes it look like 4GB or 8GB of RAM is a choice. Well, it is, but what I learned is the first two models (64GB and 128GB of storage) both come with 4GB of RAM. The first model to offer 8GB is the 256GB of storage. So, I have 256GB of storage, 8GB RAM, and the i5 processor.

What is Different about the Surface Pro 3?

Size.  The biggest difference is, of course, screen size. The bigger 12" screen size really makes a big, big difference in usability. The design is more thin and sleek with a bit less weight.

Left:  Surface Pro 2   |   Right: Surface Pro 3

Left:  Surface Pro 2   |   Right: Surface Pro 3

Angled keyboard on the Surface Pro 3

Angled keyboard on the Surface Pro 3

Keyboard.  The pad on the Type Keyboard is smooth now - I like the feel of it much better than the old keyboard.  The keyboard also has a magnetic strip across the top so you have the choice of angling it up a bit (as shown in the picture on the left) or leaving the keyboard flat on the desk. The choice there is a nice touch.It's a bit harder to touch the taskbar icons on the screen if it's angled up, but that's just a bit of an adjustment.

Pen.  The pen is very different. It has 3 buttons on it to launch OneNote, perform right-click operations, and erase.  I haven't used the pen much yet, but I'm really looking forward to breaking it in.

Left: Power supply on Surface Pro 2   |   Right: Power supply on Surface Pro 3

Left: Power supply on Surface Pro 2   |   Right: Power supply on Surface Pro 3

Power Supply.  The power supply got a little smaller which is always great. Its connector is still magnetic, but shaped a bit differently. The new shape makes it just a bit easier to insert into the charging port on the right hand side.

Kickstand.  The kickstand in the back also is now adjustable to nearly any angle you want which should help with using it on a lap or lying on the couch. The kickstand is firmer to adjust but it's not difficult once you get the feel for it.

What Things Got Relocated on the Surface Pro 3?

Start Button.  The Start button moved from the bottom middle to the right middle (if it's sitting landscape like the picture shown at the top).

Power Button.  The power button moved from the top right to the top left.

USB Port.  The USB port moved from the left to the right.

MicroSD Card Reader.  The MicroSD card reader is now tucked away behind the kickstand. At first I had a bit of trouble locating it, but it being out of the way is probably a good thing.

What Stayed the Same in the Surface Pro 3?

Mini Display Port.  The Mini Display Port stayed the same, which is great because I have video adapters for both VGA and HDMI that I can continue using.

USB.  There's still one USB port available on the side (two would have been great, but I have a small hub so that's ok). The power supply still has a USB charger which is handy to charge my cell phone.


In summary, I like the Surface Pro 3 a lot - although I knew I would considering how much I liked the Surface Pro 2. The bigger size is really great. I've gotten to where I really value the portability of a device like this - and I used to be someone who most valued a large monitor (although I do have a second monitor in my home office).

I've been going back and forth between laptop and tablet up until now. Horsepower for running demos (like a full SharePoint BI environment) is the only concern that I need to test out more fully at this point. I'll be firing up my local VMs soon, and I suspect I'll be taking advantage of Azure a bit more going forward for demo purposes. I'm planning to use the Surface Pro 3 as my primary machine most of the time, so we'll see how that goes. 

Excerpt from Surface Pro 3 User Guide

Excerpt from Surface Pro 3 User Guide

Update 7/10:  After installing Hyper-V, the Sleep setting is no longer available. Paul Thurrott discusses the issue here:  Hyper-V and Connected Standby.


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

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.

"Add to Q&A" menu option

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

"Ask with Power BI Q&A" hyperlink

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.

"Show Featured Questions" button

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

"Create Featured Question" button

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

Power BI Security Overview

This blog post is an overview of things you'll want to consider when it comes to security with Power BI.  

This post is as of June 2014 - things can and will change as Power BI grows and matures.

Things to Consider with Security in Power BI

The categories of things to be aware of when you are implementing Power BI include:

  • Authentication of users to access the Power BI Site
  • Permission to read (view) or edit workbooks (XLSX files)
  • Permission to refresh data stored in a workbook
  • Permission to access data via an OData feed
  • Permission to access data via shared Power Queries
  • Roles for data stewards and administrators
  • Service, user, and virtual account to run the Data Management Gateway
  • Secure transmission of data refreshes from Data Management Gateway
  • Ports for Data Management Gateway

Some of these items will be maintained by the administrator of the Power BI system, while some will be maintained by the user (author), or the owner of a document library that stores workbooks. 

Authentication of Users to Access the Power BI Site

Power BI does not currently support anonymous users (although Office 365 itself does). The supported logon types are:

  • A Microsoft Account.  This used to be known as a Microsoft ID or a Windows Live ID. Example:  user@outlook.com.
  • An Organization ID.  Example: user@org.onmicrosoft.com.  Optionally, this ID can be validated against Active Directory if set up in Exchange Online.

Users are set up in the Office 365 Admin Center:

The Users and Groups page in the Office 365 Admin Center

The Users and Groups page in the Office 365 Admin Center

Then, within each individual user's account, they get access to Power BI by being assigned a license:

The Licenses page of a user account in the Office 365 Admin Center

The Licenses page of a user account in the Office 365 Admin Center

Permission to Read (View) or Edit Workbooks (XLSX Files)

For a user to see a Power Pivot workbook, the user needs:

  1. Permission to the document library where the workbook is stored. (By default, a Power BI site has one document library but you can most certainly utilize many more in order to organize content or set up security differently by area. More info is here: Organizing a Power BI Site.)
  2. Permission to the workbook.  Preferably, you will set up files to inherit security from the document library itself, but it is possible to break the inheritance - be cautious if you do that though because it's definitely more maintenance.

Power BI workbooks are secured at the file level - either you can view the workbook or you cannot. There is currently no concept of row-level security in an XLSX workbook that contains an embedded Power Pivot model.  Additional control over row-level security is usually one justification for upgrading to a SQL Server Analysis Services Tabular model.

Note that in order to specify security, you need to view the document library & files via the "old school" way of viewing the document library in SharePoint Online rather than via the Power BI Site with the thumbnail previews. More info about how to access that is here: Ways to View Content Published to a Power BI Site.

The pre-defined SharePoint security groups in Office 365 may work for you, but you may also need to set up additional groups. How to secure & organize content one of the things to look into when you're initially planning your Power BI site.  

Default SharePoint Groups

Default SharePoint Groups

The couple of pre-defined SharePoint groups I want to call out are:

  • Members.  Users in the Members group have permission to read *and* edit the workbook. This means not only can they edit the Power View and/or Excel reports in the workbook, but they can download the workbook and edit the Power Pivot model embedded in the workbook.
  • Visitors.  Users in the Visitors group have permission to only read, or view, the workbook. This means they cannot edit the reports, nor can they download the workbook, nor can they see how the Power Pivot model is defined and its calculations, etc. If a user attempts to download and open a workbook it'll show this message:
Users with Read permissions can interact with reports, but cannot open the file, edit the file, or see the underlying Power Pivot model

Users with Read permissions can interact with reports, but cannot open the file, edit the file, or see the underlying Power Pivot model

You can find more information about permissions in SharePoint Online here:  Understanding permission levels.

Permission to Refresh Data Stored in a Workbook

To be able to successfully set up a scheduled data refresh, a user (or group) has to be explicitly granted permission to do so. This is defined within a data source in the Power BI Admin Center rather than the workbook itself. This is good - if multiple workbooks utilize the same data source (i.e., the same source database for feeding data into Power Pivot), the refresh for each of those workbooks will function with that data source being set up just once.

Users and Groups page when setting up a Data Source in the Power BI Admin Center

Users and Groups page when setting up a Data Source in the Power BI Admin Center

For this to have effect, the "Enable Cloud Access" checkbox also needs to be selected on the Data Source Usage page.

When data refresh runs, it runs under the credentials of the user defined in the "Connection Info" page of the data source in the Power BI Admin Center - this might be the original workbook author but many times it's not. This can have an implication re: the data coming back which may present a security concern if not properly managed. More details are here: Data Source Connectivity for Scheduled Data Refresh in Power BI.

Permission to Access Data Via an OData Feed

Discovering corporate data in Power BI using Power Query is a great feature of Power BI - it makes it easier for users to access data by using search instead of knowing ahead of time what the connection string is for a particular database. Permissions for which users can discover data in this manner is handled with the exact same setting as the one mentioned just above:  in the User and Groups page of the data source in the Power BI Admin Center.

If you need separate groups of users to be able to set up refresh (i.e., the "Enable Cloud Access" option) vs. discover data via an OData Feed, then you'll want to set up two data separate data sources and define the "Data Source Usage" page differently for each:

Data Source Usage page when setting up a Data Source in the Power BI Admin Center

Data Source Usage page when setting up a Data Source in the Power BI Admin Center

Permission to Access Data Via Shared Power Queries

I'm a big fan of sharing Power Queries - just like the OData Feeds that administrators can expose, other Power BI users can share their Power Queries with others. This allows other users to discover them using Search in Power Query, and it promotes reusability and consistency.  

Permissions to find and use shared Power Queries are handled by the user who is sharing the Power Query. It's done in Excel > Power Query ribbon > Manage Queries > Shared > hover on the query and choose Edit Settings:

Properties when sharing a Power Query with other Power BI users

Properties when sharing a Power Query with other Power BI users

Roles for Data Stewards and Administrators

Roles for Data Stewards and Administrators are defined in the Power BI Admin Center:

Role Management page in the Power BI Admin Center

Role Management page in the Power BI Admin Center

Ideally, you have a small group of Administrators - those are the folks allowed to define data sources and gateways and roles and such in the Power BI Admin Center. Even a member of the global Office 365 administrator role needs to be added here.

Also, ideally, you have a small subset of users who have been identified as data stewards. A data steward is allowed to certify a shared Power Query (certified meaning it's been validated and deemed trustworthy by someone who knows the data sufficiently).

Service, user, and virtual account to run the Data Management Gateway

This last section is for the administrator of the on-premises server where the source data is being accessed by Power Pivot and/or Power Query. If you need to set up scheduled data refresh, or expose data feeds via OData, that requires a Data Management Gateway to be installed on the server where the data resides (i.e., before it's imported into Power Pivot). When the Data Source Manager is installed, there are 3 things set up by the install program:

  1. Windows group called Data Management Gateway Users.  These are the user(s) who can run the Data Source Manager application installed on the local server. The user ID of the person who is installing the DMG is added automatically. You might need to add additional people depending on how the server is managed.
  2. Windows service called the Data Management Gateway Host Service.  This is the service that runs the DMG on the server and maintains contact with the Power BI site.
  3. Windows virtual account (managed local account) called NT SERVICE\DIAHostService. This is the context under which the host service runs.
Windows Service which runs the Data Management Gateway

Windows Service which runs the Data Management Gateway

Secure transmission of data refreshes from Data Management Gateway

When doing data refreshes in Power BI, or exposing data via an OData feed, it's typically very important to do so via HTTPS with an SSL certificate to encrypt the transmission so the data is not exposed.  In a production (real) environment you'll want to use an SSL certificate purchased from a certificate authority. For testing purposes, it's ok to use a self-signed certificate. More info about doing that is here: Create a Self-Signed Certificate for Testing.  A bit of additional info about the DMG and certificates is here:  Troubleshooting Power BI for the IT Pro and here: Power BI for Office 365 Admin Center Help.

Ports for Data Management Gateway

The DMG on the on-premises server communicates with cloud services with outgoing ports 9350-9354.  It falls back to 443/80 if the other ports are not open. No incoming ports are required from the internet because messages are received via Service Bus.

For OData feeds, however, it does require an open port of 8050 or 8051. 

More information on this is available from this Channel 9 session called Deep Dive on the Data Management Gateway in Power BI.

As you can see, there's some things to be aware of to make sure your environment is secured the way you intend it to be. Handling of security is one of the reasons I'm an advocate of functional areas partnering with technical staff when implementing Power BI.  

If you can think of something I missed, leave me a comment and I'll be sure to add it.

You Might Also Like...

Differences Between Power BI and SharePoint BI

Getting New Power Pivot Features & Other Office 2013 Updates

Why Bother Renaming Charts and Tables in Excel?

When you add a new chart or table to an Excel workbook, it gets a default name assigned by Excel. This is Chart1, Chart2, PivotTable1, PivotTable2, etc. It's always a good idea to rename tables and charts with a nice self-documenting description. But if you're like me, you may not have always taken the time to tidy up these names unless there's a formula or code actually referencing it. Here's a good reason to bother with names going forward...

Previews in Power BI, SharePoint Online, and OneDrive

In OneDrive and SharePoint Online (including Power BI when you're in the "regular" document library view), when you click the ellipses to show more options, it displays a nice little preview popup. Using the arrows, you can scroll through and see a preview of each chart and table contained in the file before you even open it.

Here's what the preview looks like. In this first example a default name of Chart12 is displayed.

Default name of Chart12

And here's what the preview looks like after the chart has been renamed...nice touch, huh?

Custom name of Sales Units Trend

Power BI Mobile App

Also, the Power BI mobile application displays pivot chart and pivot table names prominently at the top of each screen:

Updating Pivot Table and Chart Names

To update a Chart name, select the chart and go to PivotChart Tools > Analyze > Chart Name.


To update a Table name, go to PivotTable Tools > Analyze > PivotTable Name.

That's it! Quick and easy. Your reward is that the preview mode in SharePoint Online, Power BI, and OneDrive will be a bit cleaner looking.

You Might Also Like...

Comparing Slicers to Standard PivotTable Filters


Differences Between Power BI and SharePoint BI

A Power BI site in SharePoint Online is basically analogous to a Power Pivot Gallery in SharePoint, but there are some key differences that I will cover here in this post. Note this is being written in May 2014 and these differences will change and evolve.

In this blog entry, when I refer to SharePoint BI I mean the on-premises traditional type of environment rather than SharePoint Online. 


Power BI is a hybrid solution which is partially implemented on-premises and partially in the cloud via Office 365. It requires Office ProPlus (via O365) or Office Professional Plus. You'll want to use O365 ProPlus when possible. It's the newer Click-to-Run (streamable) type of installation which delivers new functionality as soon as it's available (such as Synonyms which are currently only available with the O365 ProPlus type of Office). If you're still on Office 2010, it does work just without new 2013 features (and there's a lot of important ones actually).  To take advantage of all Power BI features beyond what is available in Office (such as scheduled data refresh, Q&A, the mobile app, and enterprise search), an Office 365 license and a Power BI license are also required. Also, be aware that with Office 365, computers need to be on the newest operating systems and the newest browsers - this permits Office 365 to provide consistent and predictable product support.

SharePoint BI is usually an on-premises solution (although you could create an Azure VM and run it up there). It requires SharePoint 2013 or 2010. To take advantage of all BI features, the Enterprise versions of SharePoint and SQL Server are required.

Content Viewing

Multiple Document Libraries in the Power BI Site

Multiple Document Libraries in the Power BI Site

Power BI has a very cool feature which displays all document libraries which exist in the Power BI Site.  Although I'd love for this to be a preference whether or not a document library is shown in the Power BI site, I really like this feature because it offers additional flexibility to organize and secure content.

In SharePoint BI, the Power Pivot Gallery is a single document library.

Workbook as a Data Source

Currently, this is probably the biggest, most important, differences between Power BI and SharePoint BI. In SharePoint, we can upload a Power Pivot model to the Power Pivot Gallery and other reports can connect to it using a URL-based connection string. This works for Power View for SharePoint, Reporting Services, PerformancePoint, and Excel. This is a great feature because it separates out the data model from the report(s) if desired - hence the term "workbook as a data source."

However, in Power BI the data model and the reports (created with Excel pivot tables + charts, or Power View for Excel) everything must be contained within a single xlsx file. There's not currently a concept of separating out the data model from the reports, but I sincerely hope that's something the Power BI team can give us. I'm sure there's countless technical challenges to make this happen! The person responsible for the data model isn't always the same person creating the reports, so it would be nice to be able to separate them. More than a handful of reports gets a bit unwieldy in a single Excel file. Doing a "Save As" operation creates duplicate copies of the data which isn't an ideal solution.

Enterprise Search

The Search functionality in Power Query is one of my favorite Power BI features, one that has no equivalent in SharePoint BI at this time. Enterprise Search is the ability to use Power Query to search for corporate data (or public data) which has been exposed by other users sharing Power Queries, or by the administrator exposing an OData feed. 

Max Workbook Size

In Power BI, the maximum workbook size currently is 250MB. Nearly all of the data needs to be in the Power Pivot model because only 10MB is permitted outside of the model. That's because within the model it can be compressed and take advantage of the xVelocity engine. (For this reason, make sure to import your data to Power Pivot and not to the worksheet!)

In SharePoint BI, the maximum workbook size is 2GB. 

Mobile Application

Power BI Mobile App

Power BI Mobile App

Currently only Power BI offers the mobile app which is available for Windows 8.1 devices - iPad support due out by the fall. This is due to Microsoft's cloud-first strategy.

Use of Silverlight

Power BI still uses Silverlight primarily, with HTML5 rendering of Power BI in public preview. You can get to the HTML5 option via an icon at the lower right - this is the best option for iPad mobile access until the native application is available soon.  

SharePoint BI still utilizes Silverlight. As does the Office application.

Q&A Natural Language 

Featured Questions in Q&A

Currently only Power BI offers Q&A. This is due to Microsoft's cloud-first strategy. At first I wasn't terribly enamored by Q&A, but now I realize it's a great way to visualize data on the fly - which means not every single permeation of a report needs to be pre-defined. If used effectively, this has potential to reduce the volume of reports that are extremely similar to each other.

Data Refresh Options

There are more options to control data refresh in SharePoint BI. In SharePoint there are 3 different ways you can approach handling data refresh.  

In Power BI, the options are simpler for the end user but there are some things the administrator needs to know about it.

Data Management Gateway

SharePoint BI has no notion of a Data Management Gateway (DMG), whereas Power BI does since it's a hybrid type of environment partially on-premises and partially cloud-based.

As part of the Power BI DMG and data source options, Power BI also offers the ability to easily expose tables and views as OData feeds for users to consume. This can serve as a 'friendlier' way for users to access data.


With Power BI, you can sign in with an Organization ID (which can be validated against Active Directory), or you can sign in with a Microsoft account.

Typically with SharePoint BI, Kerberos is required and the login is integrated with Active Directory. (By the way, if you've not checked out the whitepaper on Microsoft BI Authentication, it's full of great info applicable to SharePoint BI.)

Management Dashboard

The Power Pivot Gallery in SharePoint has a pre-defined Power Pivot Management Dashboard. Currently, there's not equivalent functionality in Power BI to give the administrator an overall system view of query activity and data refresh activity. But I have my fingers and toes crossed this functionality is coming to Power BI soon.

SSAS Optimization

In Power BI, as soon as a Power-BI enabled workbook (i.e., it contains a data model) is uploaded it is optimized by the system.

In SharePoint BI, there are lengths of time defined to control how long a workbook remains in memory before it's sent to the cache, as well as how long it remains in cache before it's cleared from the cache. 

Power View Export to PowerPoint

Power View for SharePoint is available in SharePoint BI when Reporting Services is configured in SharePoint Mode.  Generally there's not feature differences between Power View for SharePoint and Power View for Excel - but there is one notable difference. Power View for SharePoint can export a copy of the report to PowerPoint, whereas Power View for Excel does not have this feature.

Power View Access to Multidimensional SSAS Models

As of SQL Server 2012 SP1 CU4, Power View for SharePoint supports the ability to access Analysis Services Multidimensional Models as a data source.

Currently, Power View for Excel cannot access Multidimensional models (it can access an Analysis Services Tabular Model though).

How To Choose?

As you can see, the choice for which type of environment is best for your implementation depends on lots of things. The classic "it depends."  A bit of planning is the best course of action to make the best decision for your needs.

If you can think of any other pertinent differences, please leave me a comment - I'd be happy to add it.

You Might Also Like...

Overview of Power BI Features End-to-End

Why Power BI is a Big Deal