Please note that this post is regarding Power BI for Office 365 (the old experience) which will be deprecated by March 31, 2016. Although some some content might still be relevant.
This is thought to be a technical introduction to Power BI limitations & considerations which hopefully will allow you to avoid most common implementation pitfalls. Please take into consideration that Power BI is in a fast-paced development and that features and functionality might change in near future.
- 1 Contents
- 2 Required Services
- 3 Required Software
- 4 Power BI Data Management Gateway
- 5 Supported data sources & scheduled data refresh
- 6 Power BI max workbook size
- 7 Power BI data source limitations and issues
- 7.1 Not able to change data source type in Power BI report
- 7.2 Not supported to use SQL statements as source in PowerQuery
- 7.3 Unsupported data types
- 7.4 Fields in Power View reports are not updated when changed in PowerPivot
- 7.5 Not able to update fields in PowerPivot when using PowerQuery
- 7.6 Scheduled data refresh max time span
- 7.7 Not able to cancel a scheduled data refresh
- 7.8 Not able to disable a report once enabled in Power BI
- 8 Platform support
- 9 Future & opportunities – Power BI preview
The current version of Power BI (2015-03-08) is available in two subscription levels. Either as an add-on to an existing E3/E4 Office 365 subscription or as a stand alone subscription which includes Office 365 as well.
Read more about Power BI licensing options and pricing here:
You may wonder what the difference is between Power BI and Office 365/SharePoint online (Office 365 includes a SharePoint Online subscriptions plus additional features). At first it might not look as that much difference except the more visual and interactive Power BI site however are there are a few important differences:
|Feature||Office 365/SharePoint Online||Power BI|
|Access on-premise data||No||Yes|
|Max Excel Services workbook size
(If file is larger user may still download the file and use it in the desktop version of Excel)
(250 for Excel file content and 250mb for PowerPivot Data Model)
|Scheduled Data Refresh||No||Yes|
|Power BI Q&A
(Ask questions with “natural language” and get answers on the fly. Works but is still somewhat limited in many business cases and also requires well named and structured data)
|Access through windows store app (works on windows 8 based tablets and PC’s)||No||Yes|
More on differences between Power BI and Office 365/SharePoint online:
- Office 2010 Professional
- Office 2013 Professional
- Office 365 Pro Plus
- Includes additional features such as adding synonyms to your PowerPivot data model for discovery in Power BI Q&A
You will also need to ensure that you have the following add-ins installed and enabled in Excel to be able to use all features of Power BI:
- PowerPivot (required by PowerView and used as data & analytics engine withing Excel)
- PowerView (required to build highly interactive and visual reports/dashboards)
- PowerQuery (required for import of certain data sources, see next section)
- PowerMap (only for desktop use in Excel at the moment)
To view current enabled add-ins in Excel click file -> options -> add-ins -> choose manage: com add-ins -> Go…
For Office 2010 you will need to download and install all plugins above from Microsoft web site.
As default only PowerPivot, PowerView are installed with Excel 2013 but are NOT enabled. If you have Office 2013 or Office 365 Pro Plus PowerMap might also be installed but also needs to be enabled.
To install PowerQuery for Office 2013 go to the following link:
Power BI Data Management Gateway
To source on-premise data to the cloud a Data Management Gateway (DMG) is required. This is an intermediate software that runs on a server inside your corporate network that sources data from internal resources to the Power BI cloud.
The DMG will copy your on premise data to the cloud. The data is always stored within your Excel documents. When using PowerPivot models there is a “hidden” tabular engine in the cloud that will use the data from the uploaded and enabled Excel files.
You can read more about how to install the data management gateway and configure data sources here:
Important things to consider when installing the data management gateway
- Installation account and service account that runs the data management gateway service requires full administrative access to the server
- Windows firewall must be enabled for installation and uninstallation. Yes this is stupid but this is the way it is. (DMG version 1.2 and 1.4)
- Power BI Data Management Gateway TCP ports:
Outgoing TCP ports: 9350-9354 (Falls back to port 443/80 on failiure)
OData feeds require incoming ports 8050 or 8051 to be open by default
Although the installation and configuration might be straight forward many enterprise organisations may have security rules/process that affects the requirements above.
Supported data sources & scheduled data refresh
In Power BI you have the ability to schedule data sources for automatic refresh on a daily or weekly basis. At the time of writing scheduled data refresh is supported only for the following data sources:
|Data Source||Location||Auto-Refresh||Data Management Gateway Required|
|SQL Azure Database||Azure||Yes||No|
|Azure VM running SQL Server||Azure||Yes||Yes/No|
|SQL Server (2005 and later)||On-Premises||Yes||Yes|
|Oracle (10g and later)||On-Premises||Yes||Yes|
 Azure VM may be configured with access to SQL by using a public IP and adding an exception in firewall/network configuration. However when configured as a virtual network with a private IP address data management gateway is required. For a production environment use data management gateway with a secure connection.
 Note that no gateway is required for OData feed. However if you connect to an OData feed through PowerQuery a data management gateway is required. Hence if you like to access an OData feed on the internet or on your Office 365 site do NOT use PowerQuery.
 All data sources in PowerQuery must be hosted on the same data management gateway.
 Read here for more details on supported Power Query data sources: https://support.office.com/en-au/article/Supported-Data-Sources-and-Data-Types-cb69a30a-2225-451f-a9d0-59d24419782e
To make things even more confusing there are three places in Excel to import data:
As long as you stick to the supported data sources you should not have any problem to use any of the three different methods. Power BI will use the connection string that you find under data tab -> connections.
Before going ahead. Carefully plan which data sources you intend to use and DO READ the information in the following links.
Supported data sources for scheduled data refresh:
Power Query pre-requisites:
Power BI max workbook size
Tip: To avoid building unnecessary large workbooks load data directly into Power Pivot without saving it into an actual Excel sheet first. In Power query right click connection choose load to… then chose only load data to data model:
Power BI data source limitations and issues
Not able to change data source type in Power BI report
Keep in mind that once you have chosen a data source there is no support to change to another data source type without rebuilding the connection which also will force you to rebuild the report and data models. As for example if you have created a SQL connection using the data tab in Excel to a Microsoft SQL server instance you are not able easily switch this to a PowerQuery Teradata connection.
Not supported to use SQL statements as source in PowerQuery
When building your query use the application interface (navigation) to select a table or view. Writing custom SQL statements is currently not supported.
Error message received when trying to create a PowerQuery connection that uses a SQL statement as source definition:
“Unsupported data source types detected”
Unsupported data types
Also be careful by using data types that are not supported for example hour-to-minute and other time related data types in Teradata. Read more here:
Error message received when trying to refresh a report with unsupported data source types:
“The following system error occurred: Type mismatch. Errors in the back-end database access module. OLE DB was unable to convert a value to the data type requested for column ‘XX’. The current operation was cancelled because another operation in the transaction failed.”
Fields in Power View reports are not updated when changed in PowerPivot
When you modify a field in PowerPivot e.g. change a field name this change does not go through to your Power View report. Instead it gets removed from the reports. This means that for any name changes you do within PowerPivot you have to re-add those fields to your report.
Not able to update fields in PowerPivot when using PowerQuery
If you make changes to a source table or view those fields get detected and added to your PowerQuery connection when refreshed from within PowerQuery. However this does NOT propagate properly to PowerPivot. Hence in order to get your added or updated field into PowerQuery you will need to remove your PivorPivot table and re-add it. This implies that all your changes for that table in PowerPivot has to be re-developed. This also forces you to re-add all fields affected Power View reports (as describe in previous section). This is massively annoying however Microsoft states that they are aware and currently working on a fix for this.
Note: If you try to edit the data source in Power Pivot you will get the following error message
“The connection you’re trying to edit was created with Power Query. To change this connection, use Power Query”.
Scheduled data refresh max time span
Not able to cancel a scheduled data refresh
When you have scheduled a data refresh you are not able to turn it off by default. Or you have a switch to turn it off but there is a bug preventing the save button to show so you are not able to save your changes. Resolution: Use a document object model inspector or other browser developer tool to enable the disabled save button and you are able to save your changes.
Not able to disable a report once enabled in Power BI
Currently there does not seem to be any function to disable a report once it has been enabled. The only option is to delete the report and upload it again:
Usually you would visualize Power BI reports with Power View which currently is supported for by the following platforms and browsers:
Windows 8 with:
• Internet Explorer 10+ desktop
• Internet Explorer 10+ modern UI
Windows 8.1 with:
• Internet Explorer 11 desktop
• Internet Explorer 11 modern UI
Surface Pro with:
• Internet Explorer 10+ desktop
Windows 7 with:
• Internet Explorer 10+
Note: There is an HTML5 version of Power View available. This version could theoretically work on any device with a HTML5 compatible browser. However it currently has at least the following limitations and bugs:
- Using iPad (any version) and Safari the browser crashes when using zoom (as by zooming with fingers on the touch screen) withing browser window. Using alternative browser such as Google Chrome makes the experience better.
- Bubble chart diagram is not able to play using the play axis
- Text-based filter miss search boxes which makes it difficult for users to filter on larger data-sets. Either the user has to scroll a lot to find desired item to use as filter however this is also limited since the filter list will not show all items if to large data-set.
- Text and design of Power View reports may differ from silverlight version hence you might need to adjust design to make it work desirable in HTML5 mode.
Future & opportunities – Power BI preview
Microsoft recently released the preview version of Power BI which will add a number of interesting features which among others are:
- SSAS Connector
You will no longer be limited to copy local data to the cloud but be able to query an on-premise SSAS server directly through the SSAS Connector which works similar as the DMG but live.
- App for IOS and Android
- Power BI designer
New application to create reports and dashboards without Excel
- New chart components (speed meters etc…)
- Interactive dashboards linking to underlying reports
You might expect that all previous features of Power BI will be implemented in the new version however that does currently not seem to be the case. For example I have noticed the following with the Power BI preview:
- The right filter pane of Power View reports is not available in the new iPad app.
- Teradata data sources through Power Query is not supported for scheduled data refresh
- There are probably other limitations as well. I have tried to ask Microsoft about if filter pane and Teradata data sources will be supported in the next general availability release of Power BI but has not received any response yet.
Checkout more on: