Monthly Archives: March 2015

Power BI limitations & considerations

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.

Contents

Required Services

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:
https://www.powerbi.com/dashboards/pricing

Difference between Office 365 and SharePoint online?

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)
10mb 250mb
(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)
No Yes
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:

https://technet.microsoft.com/en-us/library/dn198235.aspx

https://support.office.com/en-sg/article/File-size-limits-for-workbooks-in-SharePoint-Online-9e5bc6f8-018f-415a-b890-5452687b325e?ui=en-US&rs=en-SG&ad=SG

Required Software

  • 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…

Power BI Enable Plugins Excel

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:

http://www.microsoft.com/en-us/download/details.aspx?id=39379

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.

PowerBI Data Management Gateway Architectue

You can read more about how to install the data management gateway and configure data sources here:

https://support.office.com/en-nz/article/Power-BI-for-Office-365-Admin-Center-Help-5e391ecb-500c-47a3-bd0f-a6173b541044?ui=en-US&rs=en-NZ&ad=NZ

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[1]
SQL Server (2005 and later) On-Premises Yes Yes
Oracle (10g and later) On-Premises Yes Yes
OData feed[2] Web/On-Premises/Azure Yes No
Power Query[3][4] On-Premises/Azure Yes Yes

[1] 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.

[2] 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.

[3] All data sources in PowerQuery must be hosted on the same data management gateway.

[4] 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:

  • Data Tab
    power bi import data sources data tab
  • Within PowerPivot
    power bi import data sources powerpivot
  • PowerQuery tab
    power bi import data sources power query tab

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:

https://support.office.com/en-au/article/Supported-Data-Sources-and-Data-Types-cb69a30a-2225-451f-a9d0-59d24419782e

Power Query pre-requisites:

https://support.office.com/en-us/article/Data-source-prerequisites-6062cf52-c764-45d0-a1c6-fbf8fc05b05a?CorrelationId=c378d239-021b-4a58-8711-d4058d315f69&ui=en-US&rs=en-US&ad=US

Power BI max workbook size

Beware of that Power BI currently does not allow files larger than 250mb. Actually you are allowed to have 250mb of workbook content and 250mb of PowerPivot data in your workbooks.
Power BI 250mb workbook size limit

 

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:
powerquery load data to 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.

power query navigation

Use Power Query navigation when creating a connection

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 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:

https://support.office.com/en-au/article/Supported-Data-Sources-and-Data-Types-cb69a30a-2225-451f-a9d0-59d24419782e

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.”

power bi data refresh type mismatch

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”. 

to change this connection use power query

Scheduled data refresh max time span

In the current version of Power BI you are only able to schedule data refresh 3 months into the future. Hence you will manually need to update reports 3 months after schedule which is rather inconvenient. Note that the little forward button is grayed out in date selector of the image below which is a screenshot from data refresh page in Power BI. There is also no way to trick the date selector by using JavaScript or inject any other value into the date text-box. (It will fail to save the schedule)

Power BI maximum month scheduled data refresh

 

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:

power bi not possible disable report

Platform support

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:

https://www.powerbi.com/dashboards