Entries in Power BI (6)


Initial Pricing for Power BI Has Been Announced

It seems we are a step closer to the Power BI preview becoming ready for release to GA (general availability).  Pricing has been announced here:

I had to look at the following pricing chart for a minute to understand it – usually these kind of charts list the features you have by edition or something to that effect.  However, in this case, the features you have end up being the same (more on that below).  The differences shown below are only related to what prerequisites you already have in order to determine pricing.


Pricing for the E3/E4 plans is shown in the next screen shot.  The importance of E3/E4 is that they come with Office 2013 Professional Plus and SPO Plan 2 – both of which are important prerequisites for Power BI features and functionality.


Let’s say you are an E3 subscriber at $20/month.  Add the $20/month special offer price for Power BI and it’s a total of $40/month per user.  That’s much better pricing than the $52 shown in the far right hand column of the pricing chart.  Hmmm…sounds like a price break to entice more customers to the Enterprise Office 365 plans.

If you do subscribe to the E3 (or E4) plan prior to adding Power BI, you get a *lot* of features which are listed here:  With this pricing model, Microsoft is offering significantly more benefits and value if you purchase Power BI on top of the E3 plan versus standalone Power BI.  Purchasing as an E3 (or E4) add-on is the best deal for those companies who will utilize a good chunk of the extra features.  There's not a price distinction for the producers of information (i.e., the data modelers and report writers who will publish workbooks to Power BI) versus the consumers (i.e., those who may just view content on the web or mobile app) which may be a hurdle in large organizations.  My understanding is that Microsoft has been trying to simplify pricing, so it will be interesting to see if the amount of flexibility for features or users will evolve at some point.  

Keep in mind that if you only have the Office 2013 Professional Plus software, you can still use the Excel components:  Power Pivot, Power Query, Power View, and Power Map.  But…self-service BI becomes far more powerful when you integrate the Excel workbook with Power BI.  What you gain by adding Power BI is the ability to share and collaborate using the BI Sites in Office 365, the ability to schedule data refreshes, and usage of the Mobile BI app.  You can also search for other datasets published to Power BI in order to reuse existing logic (features that minimize duplication of work and reduce the potential for error get a big thumbs-up from me!).  You also get new functionality released “cloud-first” such as the Q&A natural language query ability.

Finding More Information

Office – Power BI Pricing

Chris Webb’s Blog – Power BI Pricing Announced

Office – Power BI for Office 365

ZDNet - Microsoft Pins a Price Tag on its Power BI Business Intelligence Tools



Getting New Power Pivot Features & Other Office 2013 Updates

New Power Pivot Features Released

Have you heard that Power Pivot just got a new Synonyms feature?  Synonyms associate column (attribute) names in a data model with alternate terms so that the Q&A Natural Language Query functionality in Power BI will be able to return results when users search for data using various terminology. Metadata like this will continue to to be more and more important in terms of making the user experience better, but metadata actually isn't what I want to write about today…today I want to focus on how to get new features like the Synonyms in Power Pivot as quickly as possible.

Starting with Excel 2013, Power Pivot is integrated into Excel’s data model. That makes Power Pivot currently dissimilar to Power Query which requires updates to be downloaded and installed. Power Pivot updates will come along with other Office 2013 updates that are released.

Just how do you get Office 2013 updates anyway?

How you get updates depends on how you installed Office 2013. There are two ways to install Office 2013:

  • The Click-to-Run type of installation, or
  • The traditional MSI Windows-based installer

I was inspired to investigate further because, when I checked for the new Synonyms feature in my version of Power Pivot, I didn’t have it yet.  This is because I currently have an MSI-based installation rather than Click-to-Run.

Most installations these days are Click-to-Run by default.  The MSI download media is typically only available to Microsoft customers who have a volume license agreement for Office 2013 or MSDN subscribers.

What is Click-To-Run?

Click-to-Run is a mechanism to install and update Office 2013 software products. It has been around a very long time, but has evolved to be much more sophisticated these days.

Like a traditional software installation, Click-to-Run still installs the Office programs on your local machine and uses local machine resources. However, the installation process is done via streaming which is broken up into small chunks to take it easy on bandwidth.  Even if you use Office 365 or Office on Demand sometimes, if you download the software package it does install and run from your machine.

By default, if you installed Office 2013 using the Click-to-Run method, the machine will receive automatic updates for Office 2013 (such as the update to Power Pivot mentioned at the very beginning of this blog entry). It handles installing the updates in the background during a time when the user isn't actively using components which need to be updated.  Although, depending on the kind of update, it’s possible the user will see a notification or a request for the app to be closed so it can proceed.

Interestingly, Click-to-Run is considered to be virtualized which means that all the Office 2013 program files are isolated.  This isolation allows programs to co-exist (such as Office 2010 and Office 2013 side by side).  The virtualization also allows it to continue to receive updates independently of Windows updates.  The streaming and virtualization behavior of Click-to-Run is based on Microsoft Application Virtualization (App-V) technologies.

The biggest benefit of Click-to-Run is that the updates are pushed automatically which means you don't need to install updates, service packs, or patches.  Users will probably love this – system administrators, well, maybe not all of them will love it. Click-to-Run also touts the benefit of being able to start using the software before it's finished installing.  It can do this because it sets up the most commonly used features first and continues to finish in the background.  These commonly used features are known as the “First Run Experience.” 

How to Check What Office Version You Have and if It's Click-To-Run or Not

To check the version that is installed:

  1. Launch Excel ( or any other Office app) and create a new workbook
  2. Choose File > Account

This is what it looks like if you do *not* have Click-to-Run:


If you do have Click-to-Run, you will also see this option on the Account page:


If automatic updates are enabled in the Update Options, then you should be getting the latest Office 2013 updates as they are released.

What if You Don't Have the Updates You Want?

If you’re in a corporate environment, chances are you won’t have much control over this as IT policies will dictate how Office is installed and updated.  System administrators are likely to handle installations using the Office Deployment Tool (ODT). 

If you do have the Click-to-Run version of Office 2013, make sure that updates are turned on (File > Account > Office Updates section).

If you have the MSI version of Office 2013, you have two choices:  One is to wait until the updates come through via the Windows update process (if it doesn’t conflict with your IT corporate policies, you can allow Windows updates to install automatically under Control Panel > Windows Update > Change Settings).  I don't currently know what the time difference is between Click-to-Run feature releases and the Windows Update releases - I'd love to hear more details in the comments if you know.  The other choice is to uninstall Office 2013 and reinstall it using a Click-to-Run version.  From what I’ve read, I believe you are able to keep your same product key.

Before you choose to reinstall Office 2013 with the Click-to-Run method, beware of the following limitations:

  • Not all add-ins and apps will behave the same way
  • Some functionality isn't available (such as SharePoint BCS or Edit in Datasheet functionality)

The full list of known issues can be found here:  On the positive side, search functionality in Outlook is more full-featured with the Click-to-Run.

There are multiple choices for where to download the Click-to-Run software from depending on how it was purchased.  You probably want to start with “My Office” found here: or this link:

Another way to download the Click-to-Run version of Office 2013 would be to log onto your Office 365 account.  Click the gear symbol at the top right, then Office 365 settings, then Software.  If you have the type of O365 account that provides the Office suite, then you’ll see the software listed there.  Additional FAQs can be found here:



Ways to View Content Published to a Power BI Site

There are multiple ways to view content published to a Power BI site.  Four are mentioned below.  These screen shots were taken during the Public Preview of Power BI (during which I cleverly called my site “PowerBISite”).

Power BI Site

The Power BI Site that we see in the first screen shot is an app that was installed from the SharePoint Store when the site was initially provisioned and set up.  Apps are considered “mini applications” which add and extend functionality.  Even though this view is using the app, I haven’t seen where it’s typically referred to that way (i.e., because the mobile app is the one called Power BI App).  This view seems to be typically referred to as the Power BI Site.

The Power BI Site is what I’d expect most users and report consumers will prefer to use because it offers the thumbnail previews.  Think of this as the front door.

My URL for the Power BI Site in the USA is as follows: &SPHostUrl=


Power BI Site Home Page

The home page for the SharePoint Online site looks like the next screen shot before it is customized.  In the Public Preview, this is where navigation takes me if I click the “Return to SharePoint” hyperlink in the top right-hand corner of the Power BI Site (i.e., the screen shot shown above).  This home page is where you can access the Page properties via the ribbon.

The URL for this page is:


Power BI Site Document Library

There’s also the document library itself.  You can think of this as the traditional “All Documents” view of the first screen shot above – just without the thumbnail previews.  Same content, just a different entry point.  This is where you can get to the File and Library properties via the ribbon, so this view will be useful occasionally.

Think of this as the back door.  The first screen shot above is an endpoint which allows access to this document library.

The URL for the document library is:


Power BI App (aka Power BI Mobile App)

Last but not least, there’s the Power BI App (aka the Power BI Mobile App) which can be downloaded from the Windows Store.  If you hear someone refer to the “Power BI App” most likely they are talking about the Mobile App and not the browser-based Power BI Site (i.e., the app that was initially installed from the SharePoint Store).


Just a sidenote:  A “Featured” report in the browser-based Power BI Site (first screen shot above) isn’t the same thing as a “Favorite” report in the mobile app (fourth screen shot above).


Power Query Data–Should it be Loaded to the Worksheet?

Overview:  This post discusses situations when you may *not* want to load Power Query data to the worksheet, but instead to the data model only.  One reason relates to file size limits in Power BI for Office 365; the other reason is to simplifying & de-duplicate the fields displayed in the Pivot Table Field List.

Power Query Settings

When you bring data into Excel via the Power Query add-in, you have some options on the Query Settings pane. 

Enable download:  Needs to be set to On in order to retrieve or download the data from the source.

Load to worksheet:  Specifies whether the data downloaded will be stored in an Excel table within the worksheet.  Default = Yes.

Load to data model:  Specifies whether the data downloaded will be stored in the data model (i.e., Power Pivot).  Default = No.


There’s a couple of reasons why you might want to flip-flop the above settings.  Specifically:

Load to worksheet:  Change to No.

Load to data model:  Set to Yes.

Following are a couple of reasons why you may want to consider loading to the data model only.

File Size Limits for Browser Display in Power BI for Office 365

The default file size limit in Office 365 for browser display is 10MB.  That limit for browser display has been increased to 250MB if all 3 of the following are true:

  • The workbook is enabled for Power BI (which requires SharePoint Online Enterprise E3 or E4 plan), and
  • The workbook contains a data model (i.e., an embedded PowerPivot model), and
  • Data in the workbook outside of the data model doesn’t exceed 10MB.  <--includes Power Query data residing in an Excel table

This means you want to be very careful when working with Power Query to not necessarily import its data to the workbook, particularly if the volume of data is high.  The two-step process to import only to the data model would be:

  1. Move the slider “Load to worksheet” to Off.  <--Important to click this one 1st before it becomes greyed out when you click #2.
  2. Click the “Load to data model” option.


The reason this distinction makes a big difference in the file size is because the data model uses xVelocity compression.  This is a much higher compression level than standard xlsx data.

Duplicates in the Pivot Table Field List

Loading the data to only the data model and not the accompanying workbook has another benefit.  You won’t see the set of data twice in the pivot table field list.


It’s much cleaner having one clean set of fields to choose from.  You can still use the “Filter & Shape” functionality of Power Query without loading the data to the worksheet.

Also, be aware that there’s some situations where Power Pivot will implicitly convert data to the data model, such as:

  • When Power Map is used within an Excel workbook.  Power Map has a dependency on the data model for its functionality, so if you have not explicitly created a Power Pivot data model, it will do so on the fly when you insert a Power Map worksheet.
  • When you import two or more tables at once.
  • When you insert a Power View worksheet using a Table range of cells.

Finding More Information

Office – File Size Limits for SharePoint Online



Navigating the Power BI Site in Office 365

Overview:  This post includes links for getting around Office 365, specifically as it pertains to the use of Office 365 for the Power BI Preview (i.e., not all O365 menus will be explored here).  Intended audience is the system administrator.  Purpose of this blog post is not to discuss functionality, just links and locations within the O365 portal.

Please note that this was published on 9/2/2013 – the look and feel of the menus and page structure will undoubtedly change as O365 progresses.  All screen shots below presume you have administrator privileges to the site, so you may see less things based on your O365 plan and your permissions.

Office 365 Contextual Menus and Links

Power BI is delivered using SharePointOnline within Office 365.  You can think of it as SharePoint in the web using HTML5.  A lot of SharePoint concepts will be very familiar.  However, navigating around is a bit different.

Depending on where you are at in the site, all menus are not visible.  The menus are contextual (i.e., they change based on current context - where you are located in the portal).  Also, breadcrumbs are only minimally used.  So until you have figured out the site and bookmarked some key pages, you might feel like you are hunting around for where you were or where you need to get.  The remainder of this blog entry outlines some of the key places I’ve located while working with the Power BI Preview in O365.

All links below need “YourDomainName” replaced with your valid name for them to work properly.

Admin Centers and O365 Site Settings Links

The Power BI preview uses the E3 (Enterprise 3) plan, which has nearly all features.


Since I have the E3 business plan, I find the following Admin & Settings links to be relevant for my purposes (i.e., testing out the Power BI Preview):

Power BI Admin Center
SharePoint Admin Center
Site Settings
Site Contents
Office 365 Admin Center
Office 365 Settings

(Note that I omitted the Exchange and Lync Admin pages because they aren’t relevant for what I’m doing with the Power BI Preview.  They are of course very important for an Enterprise implementation of Office 365.)

Power BI Admin Center


SharePoint Admin Center


Site Settings


Site Contents


O365 Admin Center


Office 365 Settings


Accessing Sites, Document Libraries, and Documents

The previous section covered accessing various pages for setting up the system.  Now let’s focus on where we will store our content.

Use the “Sites” menu option located in the top toolbar of some higher level pages.


All Sites

The launch page displays sites you have permission to see. 


Power BI Site Home Page

I created a new site called “PowerBISite” because for me it’s just for learning and doing a bit of playing around.  In reality you’ll want to think of it like any other site and give it a good name that suits its purpose.


Power BI Documents

Documents within a Power BI-enabled site are displayed with a preview of the report which looks like this:


Within the site, the contextual menus have additional choices.


Tracking Usage Analytics and Queries

Within the Power BI-enabled site, there is a menu option called “Manage Data.”


Manage Data


Additional Resources for Getting Started with the Power BI Preview

Office 365 – Getting Started with Office 365

Office 365 – Community (Blog, Forums, Wiki, Troubleshooting) 

TechNet – Power BI Forums

Office – Power BI Sites on Power BI for Office 365

Office – Power BI Admin Center Help



Why “Power BI” is a Big Deal

A preview of Power BI will be available later this summer.  Here’s a recap of what I’ve learned so far + why I think this is a very big deal for the world of Microsoft Business Intelligence.

Excel is Primary Tool for Self-Service BI

There are four components to Power BI, and all are delivered via Excel.  Microsoft is capitalizing on the fact that a huge percentage of business users are most comfortable in an Excel environment.  Historically a large portion of Microsoft BI was developed with SQL Server and SharePoint tools – i.e., software predominantly available to IT and BI professionals.

The newest Excel capabilities lower the entry point of BI to anyone who has Excel and an interest in working with data.  SQL Server and SharePoint will continue to be my professional-level tools of choice for creating and delivering Corporate BI solutions.  Meanwhile Excel has emerged as the primary mechanism for creation and delivery of Self-Service BI solutions.  This continues to build upon the Personal BI > Team BI > Corporate BI framework promoted by Microsoft using a combination of Microsoft Office that’s installed on the desktop, Office 365 in the cloud, SQL Server and SharePoint.

Four Components to Power BI

Two of the Power BI components are for data preparation:  Power Pivot and Power Query.  The other two are for data visualization:  Power View and Power Map. 

  • Power Pivot.  Imports and integrates data from various sources for the purpose of creating an in-memory data model.  This allows a functional user to combine various data sources which improves its value – classic examples here are the addition of weather data or demographics data to corporate sales data.  The Power Pivot data model also supports calculations, derived fields, aggregates, hierarchies and key performance indicators.  The ability to create a data model with a relatively low learning curve is a very big deal for data analysts, particularly for one-time analysis type of situations.  It can also be helpful to IT and BI folks for prototyping.
  • Power Query.  Formerly Codename Data Explorer, this tool will be able to search for data (just like a web search experience) online or within your corporate data sources.  The selected data set is immediately imported into an Excel table – this search feature alone is a game-changer.  Some of the native data connectivity it offers are truly distinctive and make data access far easier than it’s been in the past.  It also allows you to do things like merge data, rename columns, replace values and other data modifications in a step-by-step process.  A very big deal here is that the step-by-step data cleansing and shaping process is savable and therefore repeatable.  You can also view the state of the data before and after each step.  Optionally, you can bring the data into your Power Pivot model to continue working with the data even further.
  • Power View.  Power View is an interactive data visualization tool.  It does certain things like highlighting and cross-filtering really well with very little learning curve.  Working with data in Power View bears similarities to working with Excel Pivot Tables as well as to PowerPoint.  In addition to charts, graphs, and table data, Power View also supports maps which pan and zoom via integration with Bing Maps.  The naturally interactive behavior of Power View is what makes it a very big deal.
  • Power Map.  Formerly Codename GeoFlow, Power Map is a 3D mapping tool.  What makes it unique is the ability to save scenes and play them over time in a tour.

Cloud-Based Self-Service BI

Power BI will be delivered in Office 365 (which actually uses SharePoint Online).  Although the preview that starts later this summer will use Office 365, all 4 Power BI tools are available to use as Excel add-ins now (some functionality is not fully supported within on-premises SharePoint).  Considering Microsoft has a cloud-first strategy, it’s not a big surprise that the initial preview will be with Office 365.  It’s unclear currently if all Power BI functionality will be available to on-premises SharePoint sites following the initial cloud-based rollout.  I hope so.

This focus on the cloud is a big deal, especially for small and medium-sized businesses.  It can also be a good thing for business units that need self-service capabilities but don’t have IT support or resources to fire up the required infrastructure.  Even large corporate environments can find some benefits to augmenting their on-premises BI environment with the cloud – especially if they want to upgrade to newer versions in the cloud faster than they can upgrade on-premise versions (i.e., a company-wide upgrade to Office 2013 can be a big task).  Creating the SharePoint and SQL Server server infrastructure to support Excel Services, PowerPivot for SharePoint, Reporting Services, and Analysis Services is not an easy task.  Not having to invest so much time, effort, and cost into setting up this infrastructure will be welcomed by many organizations.  However, there are certainly some obstacles which prevent some companies from using Excel in Office 365 even if they wanted to.  It will be interesting to see to what extent companies embrace the cloud, or utilize a hybrid approach to on-premise and cloud BI.

Mobile Support

Quite obviously mobile support is a big deal … Microsoft has announced native mobile applications for Windows 8, Windows RT, and the iPad.  HTML5 will be used for browser-based mobile delivery on other types of devices.  The way I understand it, to see a report on a mobile device it’ll need to be published to a Power BI workspace in O365, and in the properties of the report you’d set it as a “Mobile Favorite.”  Translation:  companies with Excel and Power View in SharePoint now won’t get to take advantage of this mobile functionality right away.


     Source for Image:  Power BI Demo by Amir Netz available here:

My understanding is Silverlight won’t be immediately removed from the stand-alone Power View application in or the SharePoint PowerPivot Gallery, but I’m guessing that’ll come in time.

Data Search and Refresh

Office 365 will utilize collaborative BI workspaces optimized for BI functionality.  Users will be able to publish Excel workbooks to workspaces and securely share them with coworkers.  Reusable data connections can be published, and data refreshes can be handled on-demand or on a schedule.  Data refreshes can access corporate data on-premises as well as data online (with proper security credentials of course).  Being able to search and access your enterprise data is a very big deal (this sounds like something the IT folks will need to facilitate for self-service users to take advantage of).

Queries created from Power Query will be able to be searched and shared among users.  This sharing of queries is a big deal – anything that promotes reusability & consistency gets a big thumbs-up from me.


     Source for Both Images:  Power BI Demo by Amir Netz available here:

Tracking of Data Usage

A new feature I’m particularly excited about is the tracking of data usage.  Stats re: how often data sources & reports are accessed will be readily accessible to those who build models and reports.  This is currently a feature of Central Administration in SharePoint, or by querying the Power Pivot Management Data tabular model if you don’t want to grant Central Admin access to a non-SharePoint administrator.  I look forward to seeing how easily this information can be reviewed for one report, one data source, or for many reports and data sources.

Easy access to these stats is a big deal because if you see usage going way up, then there’s an awareness that this particular report or data source has become critical.  This might mean a data refresh failure is of greater urgency, or data quality is of even greater concern.  Let’s say it’s a Power Pivot model that’s become critical and it continues to grow larger and more complex – that might be a perfect scenario to upgrade the self-service Power Pivot model to a corporate Tabular model in Analysis Services.

Data Stewardship

Lastly, a big component of Power BI is the concept of data stewardship.  There’s not a lot of public information about this available yet.  This will include management of security, data refresh permissions, tracking data usage, as well as a few other things that we’ll learn more about when the preview is available.  I’ve typically thought of data stewardship with the mindset of data quality and/or master data management, so I’m extremely interested in seeing the direction Microsoft will take with this.  Regardless of how you define it, data stewardship is the kind of thing that can really make a self-service BI initiative more successful and/or less problematic – so I think this concept of data stewardship will end up being a very big deal.

Finding More Information

Register for the “Microsoft Power BI for Office 365” preview – Office Site

Chris Webb’s Blog – Some Thoughts About Power BI

Jen Underwood’s Blog – Microsoft Office 365 Cloud Power BI

Jason Thomas’ Blog – Power BI for Office 365

Jamie Thomson’s Blog – Thoughts on Power BI for Office 365

John White’s Blog – Power BI-What Is It?