Working with Excel Files in Power BI V2
Tuesday, August 11, 2015

In the beginning, Excel was at the center of Power BI. In the “V1″ version of Power BI, the entire product was all about enabling analytics in the cloud, and Excel/Excel services was the delivery vehicle for those analytics. The authoring tools were all Excel add-ins, and the service revolved around updating data models in Excel, and allowing you to work with data models greater than 10 MB. Power BI “V2″ changed that focus completely, completely removing that dependency on Office 365 and Excel. However, all of the Excel goodness that was within the “V1″ product is still there, it just may have moved around a bit, and it works a little differently now.

In the early days of Power BI V1, I wrote up a post about the limitations of the product, specifically the file size limitations. Reviewing this article recently, I was actually struck by how little has changed. The maximum data model size remains 250 MB. In fact, 250 MB is the maximum size of any data model in Power BI, whether or not it originated in Excel. What has changed, rather drastically is the means of enabling this 250 MB limit for an Excel file, which we’ll get to below.

Let’s have a look at some of the major differences between Power BI “V2″ and Power BI “V1″ as it pertains to Excel.

More than just pretty face

In V1, Excel was almost always used as a means of presenting data. It was possible to use Excel as a data source, but the presentation of that data would inevitably be through another Excel file.

In V2, Excel is far more commonly used as a data source. The Power BI Designer, or the web interface can connect to Excel files, and then import the data into data models stored in the service. Native Power BI visualizations are then used for data presentation. These Excel files can be local, in OneDrive, or in OneDrive for Business. If the files are in OneDrive, or OneDrive for Business, the data models can be automatically refreshed when the source workbooks change. However, we can continue to work with Excel as a presentation mechanism.

If the Excel file is stored in OneDrive for Business (as opposed to OneDrive), you can connect the Power BI interface to the workbook in place. Once connected, the workbook can be viewed and interacted with through Excel Services right within the Power BI interface. To do this, from the dashboard, select “Get Data”, choose Files, select the OneDrive for Business option, select the desired workbook, and finally, select the “Connect” button”. You will then be presented with two options.

Importing data from Excel vs connecting to a workbook in place

Note that these two options will ONLY appear if the repository for the workbook is OneDrive for Business. Selecting “Import” will import the data contained in the file into a service based data model. This is the operation that will occur for all other repositories, and it uses Excel as a data source. Selecting “Connect” however connects to the workbook in place, and it adds the workbook as a report to the Power BI user interface.

Excel Workbook in the Power BI section

The workbook appears in the Reports section with a small Excel icon beside it. To view the workbook in Excel Services, click on the ellipsis to the right of the name, and select View. The workbook should load in a new window in full fidelity, and allow interaction.

No artifacts are created in the Datasets or Dashboards sections, the workbook is a self-contained unit. This is important because personal sharing can only be done through Dashboards. Therefore, Excel Services based reports can only be shared through Office 365 Groups.

Office 365 Groups

One of the biggest changes that Power BI “V1″ users will notice (and need to deal with) is the fact that in order to work with Power BI in the same manner, Excel workbooks MUST be stored in a OneDrive for Business repository. With “V1″, a workbook could be stored within any SharePoint Online repository, and enabled for use with Power BI. Therefore, all “V1″ users will need to move these workbooks into OneDrive repositories before the “V1″ service is deprecate (Dec 31, 2015).

Every Office 365 user gets a OneDrive repository by default, and these repositories work just fine for personal use, but most current users that are using SharePoint Online will have workbooks in shared libraries in a collaboration environment. Office 365 Groups also each have their own OneDrive for Business repository, and all group members have access to that repository. Power BI V2 fully supports Groups, so this is the logical place to store all of the “V1″ workbooks for collaboration purposes.

Connecting to a workbook in a Groups OneDrive is identical to the process above, but first, you need to navigate to the Group’s context in the Power BI UI. This is done by clicking on the My Workspace button (and not entirely obvious).

Groups Selector

Simply select the group and you will be working in that Group’s context.

Increased File Size

One of the biggest benefits of the original Power BI “V1″ was the ability to work with workbooks that contained data models larger than 10 MB. The way that this was done was by “enabling” the workbook for Power BI as I outlined in this article. The enablement mechanism no longer exists, but the benefits are still there. The process of connecting a workbook outlined above intrinsically enables the workbook for Power BI and increases the maximum model size from 10 MB to 250 MB. In addition, the workbook can also be opened and interacted with directly from OneDrive. It is also possible to share that workbook with others in your organization, but in order to open those large workbooks in a browser, those users will need a Power BI licence.

Workbook Refresh

The ability to refresh workbooks stored in the cloud from data stored on-premises was, at the time of its introduction, the most important feature of Power BI. Each workbook would be enabled for refresh through an administrative interface, and if the data source matched a registered data source, the service would call an on-premises Data Management Gateway, which would facilitate the refresh of the workbook. This worked, but was somewhat difficult to get set up. Power BI “V2″ has simplified the process tremendously. This does however mean that the process has changed.

Most refreshes are performed on a Dataset, but as mentioned above, an Excel report is self-contained, so the refresh options are available from the report itself. Simply click on the ellipsis to the right of the report and select “Schedule Refresh”.



Report refresh options

The first time this is done, you will need to enter the Data Source credentials. Once entered these will be used for subsequent refreshes. This will also need to be completed before the report can be refreshed on demand. You can also schedule the refresh time here.

If the data source is a supported cloud source, no further steps will be necessary. If it is on-premises, then it is necessary to install the Power BI Personal Gateway. This does not require administrative permissions to run (although it’s best if you do), nor does it require any special permission on the service side. The Personal Gateway is meant to be just that – personal and easy to use.

Hopefully this covers most of the major differences of working with Excel workbooks in V2 of the service. It looks like a big change, but most things are still possible, and some things significantly enhanced.

Where Did Power View Go in Excel 2016?
Wednesday, July 29, 2015

If you’ve been using the Excel 2016 Preview or just Excel 2016 (depending on when you read this), you may have noticed that there is no longer an option to insert a Power View report into o workbook. The reason is that it has been removed from the default ribbon in Excel 2016. It used to be on the Insert tab in the Reports Section, right beside Power Map.

Power View in Excel 2013

However, opening the Insert tab in Excel 2016 reveals it to be missing.

Power View Missing in Excel 2016

Did Microsoft remove Power View from Excel? What’s going on? Power View is still very much a part of Excel; the only change is that now it is no longer a default ribbon option. The good news is that it’s simple enough to add it back in. To do so, we need to edit the ribbon. Click on File-Options, and then select Advanced Options. The ribbon editor will appear. We can add Power View to any tab that we would like, or even create a new one, but here we’re just going to add it back to the Insert menu. To do so, expand the Insert menu. Each command must be added to a group, so we need to click the “New Group” button. Next, because I don’t think anyone will want their group named “New Group”, we want to rename it. In this case, we’ll rename it to “Reports”, the way that it used to be.

Adding a new group to the Insert tab

Next, we need to add Power View into the group. The easiest way to do this is to select “Commands Not in the Ribbon” from the “Choose commands from” dropdown. It’s a long list of items to choose from, and you’ll be tempted to look under “P” for Power View. You will be disappointed. The correct command is actually to be found in the “I”s, and it is “Insert a Power View Report”. Select that option, and click the “Add” button.

Once this is complete, Power View should once again appear in the Insert tab, in the Reports section.

I have no idea why Power View has been removed from the ribbon by default. It may just be temporary given that we’re not yet at release, but it could signal some other change. In any event, if you work with both Power View and Excel 2016, you can continue to do so.

Connecting to Analysis Services in Power BI
Friday, July 24, 2015

At the moment (July 2015), SQL Server Analysis Services (SSAS) data has the widest range of connection options in Power BI. This is a good thing, but the flip side of this is that great power tends to lead to complexity. In this post, I hope to clarify some of the complexity, and clearly spell out the different data connection options for it.

To begin with, SSAS running in Tabular mode is currently the only on-premises data source that supports direct query data. When operating in this fashion, at no point is data persisted or cached in the Power BI Service. Reports exist in the service, but every interaction with the reports goes back to the SSAS on-premises server for execution, and the results are streamed live back through the service to the requesting client. Operating in this mode has several advantages. Since you are maintaining your own SSAS server, there are no total capacity limits on your data models, compared to the 250 Mb limit on most models in the Power BI service. All data is stored on-premises, so it does not count against your total overall storage limits (10 GB for Pro users). Data is served up in real time, so there is no need to wait for a refresh process. Finally, since data is persisted on-premises, there are also no data sensitivity or sovereignty issues. You can use the cloud based Power BI services, and still maintain a policy of having no sensitive data stored in the cloud, or out of country.

There is of course a cost to all of this flexibility. Live connections require a Power BI Pro license (currently $9.99 US/user/month), and anyone consuming these reports must have such a license. You must of course maintain an SSAS server, which has its own added cost, and you must also install and maintain an SSAS Connector somewhere within your environment. Power BI uses Azure Active Directory for authentication, while SSAS uses only Windows authentication. Therefore, your directory needs to be federated with AAD (same as Office 365 directory federation) in order to use it, although this excellent post by Greg Galloway describes a workaround for test scenarios.

Of course, direct query is not the only way to work with SSAS data. SSAS data can also be loaded into a data model, and refreshed on a periodic basis, like most other data sources. In this mode, the data model is loaded into the Power BI service, and is refreshed periodically through the Power BI Personal Gateway. This approach is common to all on-premises based data sources.

On the tooling front, there are currently three different tools that can be used to connect to SSAS data, with only one of them supporting both the direct query and imported modes. Let’s walk through each one.

Excel

Excel can connect to SSAS three different ways. You can use the traditional Data – From Analysis Services on the Data tab, through Power Query and through Power Pivot. The first method connects directly to the SSAS server and is unfortunately not supported at all by Power BI, even with the SSAS Connector installed.

Direct Connection to SSAS in Excel

Power Query will import data from SSAS into a workbook, a data model, or both. Power BI works with the data model, so if Power Query is used the data should be loaded into the model, and not the workbook. There is one special case where data can be loaded into the workbook, and that is if Power BI connects to the workbook and uses Excel Services instead of importing it. When a workbook, is imported, only the data model and Power Views are brought in.

Power Query SSAS Import in Excel

Finally, PowerPivot in Excel can be used directly to import data from SSAS. PowerPivot only loads data into the model, so there is no confusion here.

PowerPivot SSAS Import in Excel

Once the model is created, and visualizations created, you can either import it or connect to it in the Power BI service. Since Excel does not support the direct query mode at all, only import, the data will need to be refreshed periodically. Refresh can be performed whether the Excel file has been imported, or if it is connected through Excel Services. Reports created in Excel can work with SSAS whether it is running in Tabular Mode, or Multidimensional (OLAP) mode.

Power BI Desktop

Power BI Desktop is a client application that allows you to import, or connect to live data, create and edit data models, and produce reports. You can use it to connect to Analysis Services in both live query mode, and in imported mode. Once launched, simply select “Get Data” and then select SQL Server Analysis Services Database and click “Connect”.

The next dialog is important to determine the behaviour of your Power BI Report. Here you enter the name of the SQL Server Analysis Services Server that you need to connect to, and then the way that you can connect. You MUST be able to connect to this from your client – communication does not flow through the SSAS Gateway at this point.

SSAS Connection Options in Power BI Desktop

The first option will connect to the SSAS using Live Query Mode. IN order to use this option from Power BI, you will need to be using the SSAS Gateway somewhere on your premises. The second option will import the data into a model in the same manner that Excel does. This mode will NOT require the SSAS Gateway, but it WILL require the Personal Gateway in order to keep the data in the Power BI model refreshed.

In addition, the Live Query approach will ONLY work with SSAS Servers running in Tabular mode. If your SSAS server is running in Multidimensional mode, the second option (import) is your only choice.

Once the report is created, it can be published to the service using the publish button, or the created .pbix file can be imported via the same mechanism as Excel.

Power BI User Interface

If you have an SSAS Connector registered somewhere within your organization, you can create a connection to it directly from the Power BI user interface. The Power BI interface can only connect to Analysis Services in Live Query mode, imported models must be created using either Excel or Power BI Desktop.

In order to browse an on-premises SSAS server, first Select “Get Data” in the Power BI user interface, then select “Get” in the Databases section. Then select the “SQL Server Analysis Services” option, and click “Connect”. When you do, you’ll be presented with a screen containing all SSAS servers that have been registered with your organization.

SSAS Servers Registered with Power BI

Click on the one that you wish to connect to, and you’ll be presented with a list of data models to connect to.

SSAS Model Selection

Once selected, the model will appear in the list of Datasets in Power BI.

Clicking on it will allow you to browse the model, and to start building reports. Since the Dataset uses Live Query, there is no need to schedule any sort of a refresh, your reports will always be as fresh as the data on SSAS.

 

Summary

We can summarize the various SSAS connection options for the various Power BI design tools in the following table.

Design Tool Live Query Imported
Excel No Power Query Load to ModelPower Pivot
Power BI Desktop Yes (Tabular mode) Yes (all modes)
Power BI UI Yes (Tabular mode) No

 

I expect some of this information to change over time, but at the initial launch of Power BI V2, this is where Analysis Services fits in.

Sharing Power BI Content with Office 365 Groups
Thursday, July 23, 2015

The Power BI sharing story got a lot clearer this week with the changes in the service that go along with General Availability. These changes included the integration with Office 365 groups, which will in my opinion, be the preferred way to share Power BI content with others.

If you’re unfamiliar with Office 365 Groups, what you need to know is that Groups is not a product per se, but really an integration mechanism that binds together multiple elements of Office 365, and as of now, Power BI. When a group is created, a number of things happen – a distribution list is created in Exchange, a Site Collection is created in SharePoint containing that Group’s OneDrive, and an Azure Active Directory group is created for membership in AAD. Now, a Power BI workspace is created for that group as well.

How Power BI works with groups

If you’ve been working with the Power BI preview already, you are familiar with the personal workspace. This is the workspace that you see when you first log into the Power BI service, and until now, the only workspace that was available. Within the personal workspace, you can create datasets, reports, and dashboards. Dashboards can be shared to the personal workspace of other people within the organization, but now you can also switch to the workspace of an Office 365 Group. To do so, click on the Workspace selector in Power BI. Initially, it will be labelled “My Workspace”.

You’ll then be able to select from any of your Office 365 groups. All groups that you are a member of should appear here automatically, you don’t need to register them. Once selected, you’ll be working within the context of that group. If it’s empty, you’ll be prompted to add data, and if not, you’ll be taken to a default dashboard. Everything that you do at this point will be done within the context of that group, and will not affect your personal workspace. In addition, everything that you do here will be visible to all members of the group that use Power BI. There is no need to “share” anything.

Sharing to the personal dashboard vs sharing via groups

Groups represent a fundamental change to sharing in Power BI. The Personal Workspace is just that, personal. It is possible to share dashboards from here with colleagues, but the assumption is that you are the only person that may make changes. A Groups workspace turns that on its head, and assumes that everything is shared by default.

When you share a dashboard from the Personal Workspace, recipients can view the dashboard, and interact with the underlying reports. There is (currently) no mechanism to allow those recipients to make changes to those reports and dashboards. However, when working in the Groups workspace, any member of the group can make changes. Any changes made are also immediately visible to all other members of the group.

Personal OneDrive vs Group One Drive

In its original incarnation, Power BI worked with Excel files stored in SharePoint Online document libraries, including OneDrive libraries. With this version, Power BI will refresh and render Excel workbooks with full fidelity as well, but now they MUST be stored in a OneDrive library. Each user receives a single OneDrive library through Office 365, and they may also have a OneDrive personal library. In addition, each group also has a OneDrive library, and these can be used as well. The way to use them is to connect to the workbook from within the Group’s workspace.

In order to connect to an Excel Workbook from the Personal Workspace, you click on “Get Data”, click the “Get” button in the Files section, and select from Local File, OneDrive – Business, or OneDrive Personal.

Selecting from Local File or OneDrive personal will import the contents of a workbook into a Power BI dataset. That dataset will be refreshable directly from OneDrive, or through the Personal Gateway if Local File was chosen. However, selecting OneDrive – Business will allow you to select your file, then give a further two options.

“Import” is the same process as OneDrive – personal, or local file – the date is imported from the workbook into the dataset. However “Connect” establishes a report connection between the Power BI service and the OneDrive file, allowing it to be rendered in the Power BI site through Excel Services.

Once this is done, the workbook will appear in the Reports section in Power BI with a small Excel icon beside it. Unlike other sources, no dataset or dashboard are created because the report is a self-contained entity.

The experience is quite similar within a Groups workspace, with one important difference – neither OneDrive-Personal nor OneDrive – Business are options.

Instead, we are presented with the Group’s OneDrive which makes sense given that we’re in the Group workspace. The group OneDrive is backed by Office 365 which means that it functions the same way as OneDrive – Business. Excel workbooks can either be imported or connected to.

Can we use Power BI with Team Sites like before?

As mentioned above, the original Power BI service rendered workbooks from any SharePoint Online document library. The new service works with OneDrive libraries only. This means that any workbooks that are currently stored in SharePoint Online and use Power BI features will need to be moved into Group based OneDrive, or personal OneDrive in order to be able to continue to take advantage of Power BI features. In other words, Groups are REALLY important to Power BI. The original Power BI for Office 365 service will continue to be available, but will shut down on December 31, 2015.

Sharing Externally

The V1 service allowed for the external sharing of workbooks through the external sharing facilities of SharePoint. However, due to licensing restrictions, the experience wasn’t optimal. If the data model was too large, the external user would not be able to open the workbook in a browser, and would instead be required to download it in its entirety in order to open it. This was because the external user would most likely not have a Power BI license. The V2 service allows users to share dashboards from their Personal Workspaces, and to collaborate fully in Group Workspaces, but there is currently no way to share Power BI content externally, or anonymously. This has been identified as a priority, but is not available yet.

I have no specific information about how this might be done, so I am free to speculate. I suspect that the Groups mechanism will be leveraged to accomplish external content sharing. At the moment, Office 365 groups do not allow for external members, but if they did, ths would solve the external sharing problem. I’m betting that this will be the approach.

Microsoft is betting a great deal on Office 365 groups, and Power BI is one of the first services to demonstrate this deep integration. If you’re already or will be invested in Power BI, I would strongly suggest that you get familiar with them.

The New Power BI Personal Gateway – Do I Need It?
Tuesday, July 14, 2015

Last week, Microsoft released the Power BI Personal Gateway. The Personal Gateway lets you keep dashboards created in the new Power BI Dashboard service updated with data from your on-premises data sources. This is important – nobody wants to manually refresh data all of the time. However, the service already updates many data sources updated automatically – when is this tool necessary? Also, there is already a refresh tool available for Power BI called the Data Management Gateway – what’s the difference between these two tools, and when would I use one versus the other? This post is an attempt to answer these and a few other questions.

To set the stage, we need to distinguish between the original Power BI service (V1) and the Power BI service released on July 24 2015 (V2 or Power BI Dashboards). The V1 service runs (or ran, depending on when you read this) as an add-on to Office 365. Among other things, this service allows Excel files with embedded Power Pivot data models to be used from Office 365. The Data Management Gateway can be connected to the service to keep those workbooks refreshed with data on a periodic basis. The new V2 version of the service removes the dependency on Office 365 and Excel. It allows users to connect directly to their data, and to use Power Query, Power View, and (essentially) PowerPivot to transform it, visualize it, and create dashboards from it. In this new model. Office 365 is simply a repository for Excel files, which become a source for both data and reports, depending on how they are connected.

In addition to refresh capabilities, the new Power BI (V2) service supports direct querying of on premises and cloud data sources. This is significantly different than data refresh. In a live connection scenario, dashboard interactions are sent back to the data sources in real time where they are executed, and the visualizations are updated through the service in real time accordingly. In a refresh scenario, a data model that exists in the Power BI service is updated from a source on a periodic basis. This refresh has been the job of the Data Management Gateway, and is also the job of the new Personal Gateway.

Architecturally, the two services can be viewed as follows:

With this in mind, let’s answer a few anticipated questions

Will I need the Power BI Personal Gateway to do live query of on premises data?

No. The Personal Gateway, like the DMG, performs a data refresh of a model that is stored within the Power BI service. Live queries are executed against on-premises data models, so in this scenario, the Personal Gateway plays no part.

If I have the DMG, do I need the Personal Gateway?

The answer to this is that it depends. Although related, the two products do different things. The DMG is responsible for keeping the data models contained within Excel workbooks and stored in SharePoint online up to date. The refresh process in this case is the equivalent of opening the Excel workbook, selecting the Refresh All Connections button, saving it back and allowing he service to update the model stored in the service. The Personal Gateway has no workbook to update, it only updates the service based model. Therefore, if you do need to keep workbooks refreshed in Office 365, you will need to use the DMG. However, if instead you upload your workbook to the new “V2″ service, you will need to use the new Personal Gateway.

Can I install the Personal Gateway and the DMG on the same machine?

No. The Personal Gateway is really an evolution of the original DMG and uses the same underlying code base. The two are incompatible and cannot be installed on the same machine. An attempt to do so will result in the following error:

If I have the SSAS Connector, do I still need the Personal Gateway to refresh data?

Yes, The SSAS Connector is a service that is installed on-premises to allow the Power BI service to perform live queries on SSAS servers. In order to keep data in a Power BI model up to date from an on-premises data source, the Personal Gateway is necessary. However, it is not currently possible to install both the Personal Gateway and the SSAS Connector on the same machine. In fact, if you attempt to do so, you will receive precisely the same error as above. The SSAS Connector is another variant of the original DMG.

Do I need to use Power BI Designer to create a refreshable model in Power BI “V2″?

No. While Power BI designer is one tool for doing this, it is not the only one. Models refreshable from on-premises data can also be created by using the Power BI user interface and connecting to Excel workbooks.

Will any data model created in Excel or Power BI Designer work with the Personal Gateway?

(note – this answer has been updated from it’s original to correct some inaccuracies. Thanks to Derek Rickard for pointing this out)

No. In order for a model to be refreshable by the Personal Gateway, it must have been created from a refreshable data source. This is a similar to the DMG which could also refresh some direct on premises data sources, but the difference is that Power Query was required to refresh anything but SQL Server or Oracle data sources. In Excel, a model can be created using PowerPivot, Power Query, or by the selection of appropriate options when importing data.

The following data sources are currently supported.

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List
  • File (CSV, XML, Text, Excel, Access)
  • SQL Server Analysis Services Tabular models
  • Folder
  • Custom SQL/native SQL

Do I need the Personal Gateway to refresh data sources from the cloud?

No. As with Power BI “V1″, cloud based data sources can be refreshed directly from the service, with no need for a gateway. However, if your model contains data sources from both on premises and the cloud, a gateway will obviously be required. Also, as mentioned above, Power Query must have been used to acquire the data. Supported cloud data sources are:

  • Azure SQL Database
  • Azure Blob Storage
  • Azure Table Storage
  • Azure HDInsight
  • Azure Marketplace
  • Dynamics CRM Online
  • Facebook
  • Google Analytics
  • Salesforce Objects/Reports
  • OData feeds
  • Web (HTML & Web APIs)

Do I need to be an Administrator to run the Personal Gateway?

No. This is a major departure from the DMG. The DMG installed as a service, which requires administrator level permissions to do. In addition, Configuration of data sources at the service level required special permissions. The DMG was designed to be run by administrators. The new personal BI “V2″ is designed to meet the needs of both individual users, and enterprises, and correspondingly, the Personal Gateway can be run by anyone. I suppose that the word “personal” in the name should be a bit of a hint.

At install time, the system is interrogated to determine the current user’s permissions. If the permissions are sufficient, the Personal Gateway installs itself as a service, allowing full unattended operation. If permissions are insufficient, the gateway installs itself as an application. When installed in this manner, the application must be running in order for any refreshes to occur. Obviously, the user must also be logged in.

 

I’ll add more Q&A to this post as needed over the coming weeks. The coming release of the new Power BI service promises to be exciting. For more details, check out the Personal Gateway release announcement.