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

Live Blog - Keynote 2 - 2014 PASS BA Conference with David McCandless

The day 2 keynote at the PASS Business Analytics Conference is with David McCandless.  I'm thrilled to be part of the live-blogging group, so I'll do my best to communicate what's happening for those who couldn't join us in San Jose.

About David McCandless.  David (Bio | Blog) is an information designer, author, and data journalist from London.  He writes for The Guardian sometimes, which has a great Data Store of journalism articles + accompanying data to download.

Live blog of the keynote

Denise McInerney

8:06 - Welcome for David McCandless.

David McCandless

8:08 - Hello!  Has a passion for collecting and organization of data.  Gathering the data and visualizing it in graphical images anyone can understand.  Strange and interesting and imaginative things can happen.

8:10 - Billions of numbers of dollars. These numbers are routinely circulated but are too big to get our heads around. Shows the Billion Dollar-o-Gram. American people give over $3 Billion a year to charities. It's the connections between the data that make it interesting.

8:11 - How to bring this data down to where we can relate to it?  Converted some of the same numbers to a metric we can all relate to.  How much taxpayers pay into each area per day. Goal is so we can more readily relate to it.

8:12 - Playing an animation called Debtris.

8:14 - Love to play with data, have fun with it, see where it leads.  Tries to use it for storytelling & finding underlying patterns.  Showing a timeline of the world's biggest fears over the years. Then shows a pattern for violent video games - there's a regularity to it with twin peaks every year in November & April.  The Columbine shooting has been linked to violent video games. These kinds of patterns won't emerge in a spreadsheet; you have to visualize it to see it.

8:17 - There are stories and patterns lurking in vast data sets to be used and examined.  Showing a new visualization that shows two big peaks per year & mini-peaks each Monday. Asking audience who has seen this visualization before. Turns out it's most common break-up times. Includes spring break, april fools day, summer, and right before holidays. This is according to Facebook status updates.

8:20 - Phrase that "data is the new oil." It's a resource.  David would adapt it to be "Data is the new soil" in that it's a material to get your hands dirty with & nurture & bloom.

photo 3.JPG

8:21 - The term Big Data is confusing because it's both a noun and a verb.  Big Data is many processes - gathering, handling, structuring, examining, discovery, and delivery. Many companies circulate in the top regions.

8:22 - Recently David was investigating if horoscopes all say the same thing?  Did a word frequency analysis. Used Yahoo's horoscope database. If you retrieve from Yahoo's database too frequency they'll ban your IP address for a week so they had to find the right frequency to extract the data.

8:23 - Ended up boiling the horoscope down to several general statements about people being happy.

8:24 - You can learn a lot of the skills you need just by playing around with the data.

8:25 - Recently looked into who has the biggest military budget?  It's the US by far.  Ours is > $700 billion. But...is it true that America has the world's biggest budget?  To be fair we need to ask, who spends as a proportion of their GDP?  Then you get a very different picture where USA drops down to 6th.  The context is what provides meaning.

8:27 - Who has the biggest army?  China with > 2.1 million.  But if we normalize the figure and compare to the population, then China falls to 124th and N. Korea comes to the top. US is 45th.  Need context and meaning to get a clearer, truer picture.

8:28 - Hans Rosling has a phrase: "Let the dataset change your mindset."  Use it to view the world more accurately.

8:30 - Looking into the # of communicating civilizations in the galaxy.  Result is 46. There are 6.9 trillion galaxies in the universe.

8:32 - What is dataviz good for?  Reframing, finding patterns, analysis, normalizing, contextualizing, and pimping.  

8:33 - Looking at his visualized CV to show his work experience visually.

8:34 - David has never been to design school, never trained. Just likes to pick things up & learn by playing with it. Felt like he had an innate sense & become design-literate.

8:35 - Every day we are looking at the Internet. This is training us to look at data in design terms. Now if we visit a shoddy website, how little we trust it? 

8:36 - We can process visualization many times faster than text. In data visualization, it's more memorable & impactful.

8:37 - Showing a visualization of who is suing who in telecoms.  If you remove labels, your eye can focus on answering questions with size & color. Then layer on the story.

8:38 - Metaaphorically, using dataviz is like a new kind of camera.  

8:39 - Looking at a visualization of drug use by country. Good laugh about the Aussies.

8:40 - Showing most popular search terms in a 'painted' visualization. More interesting to look at the information map.

8:41 - Showing a venn diagram to look at what pigs, birds, and people have in common?  Flu.  He calls this the "Infl-Venn-Za."

8:42 - Looking at a dataviz - if Twitter community were 100 people. Then turns to a dataviz about TV watching.

8:45 - Showing at a dataviz of the most commonly used PIN numbers. Reveals habits people have.

8:46 - Showing graphic of causes of death which was done for an art gallery. 

8:47 - Goal is to 'see the invisible' in the data.

8:48 - Looking at a dataviz of supplements. Relationship between efficacy and popularity? This one graphic took two months to analyze and create. All this data is in Google Docs and open.

8:49 - You can condense a huge amount of data into a small space. Then enjoy it as if it's a painting.

8:50 - Looking at biggest data breaches.

8:51 - The active exploration & joy of interactivity.  Interactive apps for storyfinding is the new frontier. It reveals the invisible.

8:52 - Qualitative can be visualized too. Showing an infographic of left & right government to compare & contrast the worlds. The same word, like equality, can have very different meanings.

8:56 - David puts his failed graphics on the web for people to learn from.  An overly complex graphic just moves the problem. The graphic needs to unveiled. 

8:57 - He tends to create circular diagrams, but their usability. It's beautiful, but what is going is on?  

8:58 - An infographic that fills every possible space can be too overwhelming.

8:59 - A "charticle" is one that is part chart/part article. What can be removed? What works?

9:00 - What does work? Information and Design. A good visualization combines many of these things.

9:01 - Allowing yourself to play with data, get your hands dirty, helps you see what comes out. In that spirit, David wants to play a little game:  the dataviz quiz.  He has removed the labels & is asking the audience to say what the data is representing. Some good laughs!

9:09 - Q&A with the audience.  All of his datasets are available on Google Docs. He and his team spend 80% of their time on the data, and only 20% on the visualization.



Live Blog - Keynote 1 - 2014 PASS BA Conference with Kamal Hathi and Amir Netz

The day 1 keynote at the PASS Business Analytics Conference is with Kamal Hathi and Amir Netz.  I'm thrilled to be part of the live-blogging group, so I'll do my best to communicate what's happening for those who couldn't join us in San Jose.

About Kamal Hathi.  Kamal (Twitter | LinkedIn) is responsible for managing the overall strategy, design, development and delivery of SQL Server business intelligence technologies. He's been with Microsoft since 1996, first as as Program Manager for MSN, then a Product Unit Manager for SSAS and SSIS, and most recently as General Manager for SQL Server BI technologies. One thing Kamal believe strongly in is data stewardship. To hear more about this and other thoughts on where things are headed in the BI space, check out Mark Vaillancourt's interview with Kamal at last year's PASS Summit: http://markvsql.com/2013/11/pass-summit-interview-with-kamal-hathi/.

About Amir Netz.  Amir (Twitter | LinkedIn) is a Microsoft Technical Fellow and chief achitect for Microsoft's BI technologies. Amir came to Microsoft in 1997 from Panorama Software, first as a Partner Architect, then Distinguished Engineer, prior to Technical Fellow. One thing Amir advocates for is democratization of BI, by empowering users through self-service. A Technical Fellow is Microsoft's highest technical leadership rank, afforded to only about 20 people.  

Will we hear some exciting announcements from Kamal and Amir? I hope so! Either way, their keynote should be informative and entertaining.

Live blog of the keynote

Tom LaRock (PASS President)

Source:  PASS BAC Flickr acct at  https://www.flickr.com/photos/passevents/sets/72157644424706732

Source:  PASS BAC Flickr acct at  https://www.flickr.com/photos/passevents/sets/72157644424706732

8:03 - PASS President Tom LaRock takes the stage.  Gets a round of applause when he asks, "We get paid to work with data every day. Who is excited about that?"

8:05 - Who are we? Variety of titles. In this room we have over 300 industry-leading companies. Since 1999 PASS has supported data professionals. PASS runs through the involvement of hundreds of passionate volunteers - they need your help.   PASS now has over 100,000 members.

8:06 - Think about what you are passionate about with data & figure out a way to get involved with PASS. Lots of opportunities, whatever you are interested in, there's a way for you to be involved.

8:07 - Next 2 days there are over 70+ sessions + Community Zone. Running group tomorrow too, sponsored by SQL Sentry. Morning breakfast sessions. Unconference tonight. A lot is going on! Reminder that all sessions are being recorded & available on USB.

8:08 - Thank you to partners. Give sponsors a hug!

8:09 - Next conference April 20-22, 2015 in Santa Clara, California.

John Whittaker (Dell)

Source:  PASS BAC Flickr acct at  https://www.flickr.com/photos/passevents/sets/72157644424706732

Source:  PASS BAC Flickr acct at  https://www.flickr.com/photos/passevents/sets/72157644424706732

8:10 - Welcome to John Whittaker, Sr. Director Information Management, from Dell Software. Speaking about Big Data, Predictive, & The Middle Market.  A key component Dell builds solutions for is the middle market. Is Big Data real & happening in the middle market?  How should the middle market approach Big Data?  Not just for large enterprises. 96% of organizations surveyed are in-flight with an existing project or plan to start one in 2014. 

8:13 - #1 reason for success of a Big Data project:  Strong cooperation or collaboration between business and IT. Need LOB there to guide the outcome. Rogue outcomes around Big Data can happen when data governance isn't present.

8:14 - Overwhelmingly there were two primary responses for doing Big Data:  (1) real-time processing of data & analytics, and (2) predictive analytics.  

8:15 - Biggest challenges for Big Data projects:  (1) Complexity of new data types & structures, and (2) Sheer volume of data slows processing. Followed by (3) Budget limitations.  But really...it's all about data complexity.

8:17 - Other Key Takeaways:  (1) Skills gaps (skills & tools).  Tooling is unsophisticated. (2) Decisions should be split evenly between IT and LoB business teams.  (3) Is it making a difference?  50% of orgs with a big data initiative iflight are satisfied with quality & speed of their decision making.  

8:20 - Dell has over 30 years of information management software experience. Dell's goal is to offer end-end solutions for data management. 

Kamal Hathi & Amir Netz (Microsoft)

8:23 - Amir & Kamal introduce themselves. Been an amazing year since they last spoke to this audience. A year of innovation:  Not just lots of new technologies but also a new CEO. Never had a CEO before announcing a new version of SQL Server.

8:24 - Kamal is jazzed because Satya Nadella has made Indian accents cool again.  Lots of laughter.

8:25 - 2 million+ downloads of Power Pivot since 2010. 100,000+ downloads of Power Query since Feb. 2014. 55,000 Power Map preview downloads. 100 million+ HDInsight compute hours. 12,500+ Power BI tenants activated.

8:26 - Kamal is going to use Power BI to look at Power BI.  He's using Q&A to ask how many Power BI tenants. Explains what a tenant...Microsoft is 1 tenant with 100k users. Company has one tenant, many users per tenant.  Asking Power BI tenants by country. Seeing map of global users - biggest in US. It's in Bermuda to Iceland and everywhere. 

8:28 - Kamal looking how Power BI tenants are growing.  Line zooming up over last 2 months. Spike when Q&A went public in September. Another spike in February when the GA announcement went out. Nice growth happening so far.  

8:29 - Number of questions answered by Q&A last month:  1,091,547 - that's over a million!  They look at this so they can understand the features and who is using them and how they can be improved.  

8:30 - Looking at telemetry - the most used feature of Q&A is choosing the "select suggestion" drop-down. They know to continue investing in this.

8:31 - Share URL by E-mail is new & is just taking off. Looking at other Data Discovery features, like changing fields displayed or filters to see the visualization change.  Usage for some of these types of features have doubled in the last month because Microsoft has made these features more prominent and discoverable - because it's there by default more people are using it.

8:32 - Amir says looking at the stats is interesting, but wants to look at it in action instead... They asked for people to show how Power BI is used. Announcing the winner of the Power BI demo contest:  Michael Carper.  Brings Michael onto the stage.  Amir is animatedly recapping what Michael's demo is about - can you affect the results of an NBA game by tweeting?  If you tweet in the 3rd quarter you have the best chance of affecting the game!  More tweets, more wins!

8:35 - Amir is presenting (another!) award to Michael for his Power BI Demo Contest entry.

8:36 - Amir explains how a cloud service like Power BI is a game-changer for Microsoft. They learn from the data & can improve the service every single months.  Kamal showing usage by browser usage. HTML5 running across various browsers.  Kamal is running a Chromebook & explaining how the web-based display works the same across devices.

8:38 - Amir says they hear the feedback: it should always work on every platform.  "BI on any device." 

8:38 - Announcement from Amir:  iOS app for Power BI will be available in the App Store by this summer.  The native app experience will be the best experience.

8:39 - Microsoft knows we have many in-premises investments...First step towards this is the scheduled data refresh in Power BI.  Kamal bringing up a Power BI site to see the thumbnails. These thumbnails are different....they are actually Reporting Services and the data was on-premises.

8:41 - Announcement from Amir:  SSRS will be a native component of Power BI.  All the functionality of SSRS will be available in Power BI by this summer.  

8:42 - SSRS connectivity will be real-time connectivity to the on-prem data source. No refresh required.

8:43 - Kamal explaining they want to promise companies will be able to choose cloud or on-premises, whichever works for you.

8:44 - Amir reflecting years ago that BI was just about the "guy in IT" or "the consultant" and we trusted the professional will do a great job & deliver value to the end users. Next was the age of self-service BI - the analyst or power user should be able to able to create or consume. Still will be some who create and some who consume.  Now we are entering another new age:  the age of Data Culture.  You don't have to be an analyst - anyone who has a question or is curious should be able to answer their own question. Goal is to make the tools so simple that people can do this.

8:46 - Amir loves music and movies-he's done both. This time going to do a demo on Hawaii, which he loves.  Hawaii regularly asks people via surveys how they spend their time & how they spend their money. Amir realized Hawaii was collecting all these forms so that he could have a great Power BI demo.  =)

8:47 - Amir is using Q&A to look at average spending per day.  Is about $182 per person. Then a breakdown by island. Lana'i is quite a bit higher than the rest - just over $300, followed by O'ahu at $200.  

8:49 - Amir looking at # of visits plus where people are coming from. The big spenders seem to be from Japan. Now looking at visits by date and seasonality. Canadians don't seem to spend much & come in the winter.

8:51 - Amir has observed lots of public displays of affection from the Japanese. So he, of course, checked the data!  Turns out lots of Japanese come to Hawaii for their honeymoon. He says Japanese honeymooners are higher than any other segment of people. Has a new investment suggestion for us here.  

8:53 - Emphasizing that using natural language and ease-of-use experience to every area of BI, even areas that are boring and dry.  New button at the top of the Power BI site page to create a new KPI.  Brings up a KPI Editor - this KPI editor uses Q&A to get a visualization and choose "add selected tiles."  Just built a dashboard page all using natural language.  These are displaying across the top of the Power BI site page.

8:55 - Now Amir wants to look at how predictive analytics can be so easy that every user can do it?  

8:56 - Announcement from Amir:  Forecasting in Power View.  Time-series analysis.  [Big round of applause.]  Data scientists & user-interface designers have been working on this for a year. Goal is for someone without that background to be able to do predictive analytics.

8:58 - Forecasting is available today.  Available on every line chart.  Drag the line forward and the future points will appear. There's also a pane on the right to make some modifications to it.

8:59 - Looking at a one-time event in the line chart that is skewing the future line chart results. Demonstrating changing this point that's an outlier so that it will then adjust the future results.

9:00 - How to know confidence a year ago?  Can check validity of results by looking at if it were done, say a year ago, what would it have said?  

9:01 - Forecasting needs a time dimension, a measure, and a line chart for it to work.

9:02 - Pulling a volunteer from the audience, Julie, to drive the demo without knowing ahead of time what she will be doing. 

9:03 - Showing a standard Power View report on the large perceptive pixel device. What about users who have questions that haven't been answered by the report that is published?

9:04 - Announcement from Amir:  Data Exploration Mode is going to be available to every user. This is a new button at the top right.  Get a field list now. Full power of working with the report to change it on the fly within Excel Online.  

9:05 - Announcement from Amir:  Tree Map visualization.  Shrieks from Jen Underwood when Data Exploration Mode just returned a Tree Map.

9:07 - Demonstrating the ability in Data Exploration Mode to drag data points from one chart to another to affect the visualization being displayed. Not just cross-filtering anymore.

9:08 - Demonstrating creating a new chart by dragging a data point to the canvas. Selection of chart type appears on the side of the new data visualization to select chart type.

9:10 - Dragging one column name from the field list over to the canvas - # of clicks required is reduced.  Demonstrating dragging the "Unknown" data point into a garbage can, to improve the bar chart display.

9:12 - Announcement from Amir:  New "Bump" gesture. Drag two data visualizations into each other, to bump them, and they'll combine into one chart.  Then do a "Shake" (wiggle the chart) to separate the visualization back into separate charts.

9:15 - Wrap up. The Journey to Data Culture Begins Today.

Data Source Connectivity for Scheduled Data Refresh in Power BI

As discussed in my earlier blog entry about when a gateway is required, we know that if you need to schedule a daily or weekly refresh for a Power BI workbook that's up in a Power BI site, and it points to an on-premises database, there needs to be a gateway and data source defined in the Power BI Admin Center.  This connectivity is usually defined by the administrator of your Power BI Site in Office 365.  

Think about this though...the Power BI Admin Center defines the gateway (where the server is at) as well as the data source (database) that's being accessed....does that mean the connection string and credentials used in the Power BI Admin Center "trump" what's been set up in the workbook?  You betcha it does! I don't know how the Power BI team refers to it**, but the way I think about it is there is a "matching process" for the workbook to locate which data source in the Power BI Admin Center to use.

**Update:  Since writing this blog entry I learned that the Power BI team calls this a Discovery Process

The Matching Process

The thing I'm trying to articulate with this blog entry is this...how does Excel Workbook D know to use Data Source 2 for its data refresh operations in the Power BI Site?

Based on what I've tested so far, the following needs to match between what's defined in the workbook and what's defined in the Power BI Admin Center:

1. Server name matches between workbook and a data source in the Power BI Admin Ctr
2. Database name matches between workbook and a data source in the Power BI Admin Ctr
3. Same provider is used in the workbook and a data source in the Power BI Admin Ctr
4. User who is defining the data refresh has permission to the data source in Power BI

This has a couple of very specific implications, particularly #4 if security is involved for the data coming out of the source data.  But we'll get to that.  First, let's back up a moment and talk connections, shall we?  

Connections and Credentials in Power Pivot

In the Power Pivot workbook, a data connection to the source system being used is defined. The person creating the workbook usually uses their own credentials in this situation. They manually refresh the data while they're developing and validating the Power Pivot model (i.e., while working in Excel before it's finished & uploaded to the Power BI Site).

Most users will assume that what's been defined in the workbook will continue to be used once the refresh is scheduled in Power BI - but that's not necessarily true unless the administrator intentionally uses the same connection string from the workbook. The problem with that theory though is that the administrator won't be able to use the user's individual credentials. So the administrator would usually set up a specific service or application account to run the refresh - this is fine if all the data is open. Problem being that this account used in the Power BI Admin Center may or may not have the same permissions to data in the source system as the individual user.

If the source database is in Azure, then this isn't a concern. The refresh process will continue to use the credentials stored in the workbook. However, if the source database is on-premises, thus requiring a Gateway and Data Source in the Power BI Admin Center, then this "matching process" comes into play. Let's explore each of the 4 criteria...

1. Server Name Matches Between Workbook and Power BI

This one is pretty straightforward - same server is referenced inside the workbook connection and the data source in the Power BI Admin Center.

2. Database Name Matches Between Workbook and Power BI

This second one is also pretty obvious - same database (aka Initial Catalog) is referenced inside the workbook connection and the data source in the Power BI Admin Center.

3. Same Provider is Used in the Workbook and in Power BI

How the connection in the workbook is handled by default depends on the individual computer. A Power Pivot connection will default to using the Native Client provider if (a) SQL Server is installed on the computer, (b) SQL Server tools are installed on the computer, or (c) the user has downloaded sqlncli.msi from the SQL Server Feature Pack. When using the native client provider, 11.0 = SQL Server 2012, and 10.0 = SQL Server 2008 and R2. (There is no native client provider for SQL Server 2014.)  Here's what it looks like in Excel of the native provider is used:

If SQL Server tools or the provider has *not* been installed on the user's computer, then the Power Pivot data connection will default to OLE DB (although it can be changed by the user).

A data source being created in the Power BI Admin Center defaults to OLE DB (although it can also be changed) which is a different default than inside of Power Pivot. If most users of the Power BI system don't have SQL Server installed (which would be the norm), and you expect them to choose the simplest/default connection types in Power Pivot, then using OLE DB here in the Admin Center may be the least troublesome.

However, if a user has uploaded a workbook with a native client connection (or .NET), but the data source in the PBI Admin Center is set up using OLE DB, the refresh operation will fail.

****Thanks to Gerhard Brueckl who alerted me to this issue with the mismatch of providers.****

So, depending on your environment, this is a great opportunity to give users some documentation or FAQ for what type of connections you want them to use in Power Pivot.


4. User Who is Defining the Data Refresh Has Permission to the Data Source in Power BI

This last item is related to the "users and groups" who have been defined in the PBI Admin Center as having access to the data source. Permissions to each data source can be critical, depending on security of the data coming out of the data source - there's more to the story here, but I'll save that for another post.

If a user tries to set up a data refresh and the "matching process" finds a data source that meets criteria 1-3 but not criteria 4, you'll get an error with a message like this: "We are unable to refresh the on-premises data connection. Reason: You are not authorized to access the data source. Please contact your Power BI Administrator to get access to the data source."

Credentials Used for Power BI Data Refresh

One last thing I want to make sure is emphasized:  the credentials set up in the Power BI Admin center data source are what's used for scheduled data refresh & on-demand data refresh in the Power BI Site. I did a test where the workbook that was uploaded had user credentials; however, the "matching" data source in the Power BI Admin Center used a service account for its credentials. Ran a Profiler on the local database to verify that the credentials defined in the Power BI Admin Center (the service account) are what was being used:

So, in summary, keep in mind that what's in the Power BI Admin Center will "trump" what's in the workbook for data refresh from on-premise data sources (whereas Azure will continue to use the connection defined in the workbook). Watch out for this change in credentials if you have limited security to data going in in your environment. You want to avoid the situation where User Emily uploads a workbook with just Division A data, for example, and then the scheduled refresh runs with an account that has broad permissions & then suddenly User Emily sees everything next time she opens the workbook. This can be managed via permissions, it just requires knowledge of what is going on in the environment. 

Finding More Information

Power BI Support Blog - Connectivity 101  <--Good tip here on testing connectivity

Office - Schedule data refresh for workbooks in Power BI for Office 365

You Might Also Like...

When is a Data Management Gateway Needed in Power BI?

Power Query Data - Should It Be Loaded to the Worksheet?

Whatcha Bringin' To the PASS BA Conference?

I'm seriously super excited for the PASS BA Conference coming up the first week of May.  Last year I wasn't able to go, and I was a very sad girl. So instead of living vicariously through tweets & blog posts, this time I'll be there. Here's my quick list of what to bring with ya....

Enthusiasm & energy. If you're traveling far, the time change is harder on some of us weaklings. I know I'm not flying halfway across the world like some people, but since I'm an early-to-bed person anyway, that means I'll want to go to bed at dinnertime while in California. So for me, this means consciously focusing on eating well & sleeping just enough to keep the energy level up throughout.

Guts. Don't be shy to walk up to someone you recognize and say hi. It doesn't come naturally for most of us, but the effort is worth it. I know SQL people from all around the world now because I've met them at conferences like this - that is just truly incredible when I think about. 

Learning objective(s). I like to give a bit of thought as to which sessions I'll attend ahead of time - it helps me think through what I want to learn more about.

Tablet. Electronic or paper, if you're the type who likes to take notes. I do like to jot down notes. I've read some notes of mine that I just don't remember taking - and learned something all over again!  Since we can't actually retain everything we hear over the course of 2+ days, it just makes sense for me. I like OneNote for this purpose - usually I'll type but sometimes I'll also draw with a styles. Don't forget the plug-in for it too, if you're going electronic.

Phone charger. Just in case.

Small extension cord. Seats on the perimeter of the meeting rooms go first because of the outlets.

Business cards. In case you meet someone you would like to keep in touch with, you may want to have some cards with you. I always seem to fumble around for them, so am going to try to have them handy in a pocket.

Sweater or jacket. Yeah, yeah, I'm always cold. 

Ibuprofin. If sitting in those uncomfortable chairs gets you like it gets me, having a little stash of ibuprofin (or whatever) sure is helpful.

Tissue. If you suffer from allergies, those little travel pouches of Kleenex are handy.

Small(ish) bag of stuff. For me, having a very small bag of my stuff is important. I refuse to lug around a heavy laptop bag anymore! For me, this means no extra purse, just one small bag.

Well, now there will be no mystery as to what I'll have with me at the conference. See you there!

P.S. The Charlotte BI Group has a discount code if you haven't registered yet - it's BA2M3R.

Organizing a Power BI Site

There's several important differences between a site set up in Power BI for Office 365 (SharePoint Online) vs. a Power Pivot Gallery in SharePoint on-premises. One key difference I want to focus on now is how you can organize the Power BI site.

With your Power BI site, you can set up various document libraries within the site and they will all show up on the Power BI app page (i.e., the "app" view in the browser rather than the regular doc library view - see this if you don't know what I mean by that statement). Different document libraries can be used to organize content based on subject area, functional area, or whatever makes sense. The document libraries can also have separate permissions (which might allow you to set security at the document library level which is far less tedious than managing it at the individual file level). When it comes to content arrangement & permissions, the same planning you would have put into structuring SharePoint document libraries still applies. Here's an example of how a Power BI site looks with various document libraries within the same site:

With a Power Pivot Gallery in SharePoint (on-premises), it's associated to one single document library. So, the ability for the Power BI site to display content across various document libraries in the site opens up lots of possibilities.  For details on how to create a document library, see my short video on MSBIAcademy.

You can also create subsites if that would be helpful from an organizational or permissions perspective. Individual subsites do get their own unique Power BI app page, but they share the same Power BI Admin Center.

One last thought for you - notice in the screen shot above how there is a document library for "FAQ and Help." It's got a > symbol at the beginning of the name to ensure it's sorted at the top. Something like this is a great way to deliver documentation to your users. (Hint, hint.)

You Might Also Like...

Creating Document Libraries in Power BI (7 minute video)

Ways to View Content Published to a Power BI Site

Navigating the Power BI Site in Office 365

When is a Data Management Gateway Needed in Power BI?

Recently I've been chatting with some fellow BI professionals about the Data Management Gateway (DMG) in Power BI and when exactly it's required to be used. 

Before we answer this question, let's go through a quick bit of background...

What is the Data Management Gateway?

There are 3 components involved, all of which are set up by the Power BI administrator:

  1. Data Management Gateway (DMG). This is a client agent installed on the on-premises server. The DMG handles encryption, compression, and transmission of data using a service bus. Communicates with O365 using a key provided by the Power BI Gateway.
  2. Power BI Gateway. This is set up in the Power BI Admin Center (in O365). Handles communication with an on-premises server.
  3. Power BI Data Source. This is set up in the Power BI Admin Center (in O365). Connects to a database via the Gateway.

When the administrator creates a Data Source in the Power BI Admin Center, you have 2 choices: To allow cloud access and/or to create an OData feed. If cloud access is allowed, that will permit users of the Power BI app to schedule data refreshes that point to that database. If you create an OData feed, that will expose tables and views to organizational search using Power Query.

When is the Data Management Gateway Required?

SQL Server Database On-Premises:  Yes
Oracle Database On-Premises:  Yes
Windows Azure SQL Database (WASD):  No*
SQL Server Database in an Azure VM:  No*
Public OData Sources:  No

Each of the * items are cloud services associated with Azure. For those types of data sources, Power BI permits the scheduled data refresh to occur using stored credentials in the workbook, or using the secure store service, without requiring the 3 components listed above to be set up by the administrator.  As of now (March 2014), it's currently just the on-premises data sources which require the 3 components listed above.

The advantage of not having to set up the DMG is that it's less steps to set up. If you're using a cloud service as a way to quickly and cheaply set up a data source for temporary use or a one-time analysis, the simplicity is nice.

One disadvantage of not having to set up the DMG is that the Power BI system administrator isn't seeing the data refresh activity occur in the Power BI Admin Center.  As of this release of Power BI, I'm not seeing where data refreshes from cloud sources are being logged. In a managed self-service BI environment, this visibility to what's really happening in the system is very important.

Another disadvantage is that it's a different user experience. Let's say User Emily upload a new workbook which has a data source from SQL Server on-premises. Her scheduled data refresh will fail unless the Power BI administrator has set up the 3 components listed above. However, if User Emily uploads a new workbook which has a data source from an Azure SQL Database, her scheduled refresh will be successful without these extra steps. Since users aren't always aware of where data sources are, this can lead to an inconsistent experience where they're not exactly sure when it "just works" versus when to contact the Power BI administrator for additional help.

Having said all that, I'm sure we all expect that much of how things work will evolve as Power BI matures and grows. It is, after all, in its first release. Am just passing along how I've observed that it's working currently so administrators and users are aware of the behavior.

Finding More Information

Power BI for Office 365 Admin Center Help

Scheduled Refresh FAQ

You Might Also Like...

Overview of Power BI Features End-to-End

Creating an OData Feed in Power BI to Access Corporate Data Sources (7 min. video)

Top 2 Features of Power Query

Power Query is one of the 4 add-ins for Excel which integrate with Power BI for Office 365. I'd like to share what I see are the 2 biggest features of Power Query.

Repeatable Steps


We've heard lots of times that cleaning up data to get it in the correct format can take many hours, and sometimes this work has to be done routinely such as every month. Here's the perfect situation where Power Query can help.

Each step you perform to change the data in some aspect is shown in the "Applied Steps" pane. If you click on each step, you can actually see the state of the data before and after each step was applied.

What makes this ultra cool? The fact that these steps are repeatable the next time you need to go through the same exercise. This is similar to the idea of an Excel macro, but much easier to use.

To me, the time savings and efficiency of repeating steps to cleanse and restructure data is *the* biggest benefit of Power Query.

Shared Queries

One of the inherent downsides to asking business users to publish their own datasets and reports is that the quality and integrity of the data can be questionable depending upon knowledge of the person preparing the data and how thoroughly it's been verified. There's also an increased chance of duplicating work that's already been done by someone else which isn't efficient.  Here's where sharing Power Queries comes in.


If you have a Power BI license, and you are signed in to Power BI from within Excel, you can share a Power Query (once it's complete & validated of course).

When you share a Power Query, the name and description become all searchable.  You can also specify who to share the query with - this would usually include other people, but you could just specify yourself if that makes the most sense.  And, you can even specify a URL with more information about the query (which is a truly excellent feature that I fear will be underutilized).

After it's been shared, you and other users can discover this query when using the Online Search functionality in Power Query. You would search for "Organization" data to discover a shared Power Query.


When you are logged into the Power BI for O365 app (via a web browser), if you click on "My Power BI" at the top right you will be able to view how often your shared queries show up in search + how often your queries are selected for use and by whom. 

What's really great about sharing Power Queries is the underlying logic is done just once and repeated potentially over and over - and you have visibility into who else is using your shared queries - now that takes things to the next level. 

You Might Also Like...

Power Query Data - Should It Be Loaded to Worksheet?

Overview of Power BI Features End-to-End

Overview of Power BI Features End-to-End

The following diagram is an end-to-end overview of the major features of Power BI for Office 365.


The Excel Add-Ins + Office 365

Excel is the environment for data access, preparation, modeling, and visualization of the data.  In addition to consuming data sources directly, there are Enterprise Data Search features to search for shared queries which have already been published to Office 365 - this makes the above diagram become very cyclical in nature as Excel is used to publish as well as consume.

Although you can use just the 4 Excel add-ins and get a lot of capabilities, there are some major pieces of functionality which become "unlocked" by integrating with Power BI in Office 365. SharePoint Online within O365 is the place for collaboration & sharing of workbooks, for scheduling data refreshes, as well as using Q&A to search for data across workbooks much like using a search engine. The ability to share and certify Power Queries also becomes available when the Excel user is signed into an O365 account with Power BI.  The Mobile App, available from the Windows Store, also becomes a possibility when used with O365. 

Within O365, there's primarily two main areas:  the Power BI App where business users will view and interact with workbooks, as well as the Power BI Admin Center which is used by the system administrator to manage security and data connectivity to corporate data sources.  To access corporate data, a business user needs to work with a system administrator to request a gateway and data source to be established; optionally, the administrator can set up an OData feed to expose corporate data for purposes of Enterprise Data Search.

The above diagram represents my understanding of the overall system - if you have any feedback or suggestions on it, I'd love to hear them in the comments.

What's the Additional Functionality Power BI Provides Over an E3 O365 Subscription?

It seems there's a bit of confusion on just what additional functionality Power BI provides over an E3 subscription.  Essentially, you cannot create a Power BI Site without purchasing the Power BI subscription.  When you have provisioned a Power BI Site in Office 365, the additional things you can do include:

  • Use the Power BI Site (which is really just a specialized document library) to share workbooks with coworkers
  • Perform Q&A searches on the workbooks
  • Schedule automatic data refresh
  • Utilize shared Power Queries (part of Enterprise Data Search)
  • Utilize certified Power Queries (part of Data Stewardship)
  • Open up corporate data sources via an OData feed (part of Enterprise Data Search) 
  • Use the Mobile BI App

The chart on this Power BI Pricing page is another way to see how the Power BI subscription is an "add-on" to the E3 subscription.  In the diagram above, the Office 365 box in the middle which contains the Power BI Site and Power BI Admin Center are opened up via the Power BI subscription.

You Might Also Like...

Types of Projects to Consider for Microsoft Power BI

Why Power BI is a Big Deal

Power BI Error - Provider Microsoft.Mashup.OleDb.1 is not registered

In Power BI for Office 365, you can schedule the data in the Excel data model (Power Pivot) to be refreshed daily or weekly. This is one of the additional pieces of functionality available when the Excel workbook has been uploaded to your Power BI App in Office 365.

However, as of this writing (February 2014), scheduled data refresh for data loaded to Power Pivot via Power Query is not yet supported. It will be supported - it just didn't make it in time for the initial release.

If you see an error such as this...

The following system error occurred: Class not registered. The provider 'Microsoft Mashup.OleDb.1' is not registered.

...that tells you that Power Query is present in the Excel file and so data refresh cannot yet be scheduled.


More information about which data sources can be refreshed automatically can be found here:

Scheduled data refresh for workbooks in Power BI for Office 365