Tag Archives

5 Articles

Power BI Pro Tip: Troubleshooting Power BI Data Refresh

With Power BI you can create an Excel sheet based on your data and then get it refreshed so your report always has the latest information.

If you run into trouble with this, here is how to troubleshoot the data refresh.

  1. Firstly, make sure you use Power Pivot to connect to your data source and not Power Query as refresh with Power Query is currently not supported.
  2. When making the connection to your data source, just use a regular data connection to the SQL Server source and not through an OData feed.
  3. In Power Pivot, make sure to use the same data provider as your data source has been configured to use in the Power BI Admin Portal (i.e. Native SQL or OLEDB) and enter the server and database name exactly the same as the data source has been configured in your Power BI Admin panel. With “exactly” I mean also casing: so myServer is something else than MyServer. So the server and database name needs to be identical, including casing.
  4. Finish your workbook and upload it.
  5. Make sure the Database Management Gateway is running. Do this by checking the status in the Power BI Admin Portal and check under Data Management Gateways.
  6. Next in the Power BI Admin Portal, check that the data sources have correct settings for server name and database name. Also check that the credentials entered here have permissions to the data sources on the server and have been re-entered after the last time the DMG has been registered. There is no harm in re-entering them just to be sure. Also check that the connection provider here matches the one used in Power Pivot and again the server and database name need to be exactly the same as in your Power Pivot connection settings (see item #3 above).
  7. In the Power BI Admin Portal, check that the user you will be using to schedule the refresh has permissions to do so on every data source you would like to refresh. Do this by opening the settings for the data source in the Power BI Admin Portal and making sure the user is listed under ‘users and groups’.
  8. Plan the refresh on the file. When the schedule hits, your schedule should start and finish successfully.


Power BI Pro Tip: Troubleshooting Data Management Gateway stability

If your Data Management Gateway crashes a lot, just make sure you have .NET framework version 4.5.1 or later installed. 4.5.1. introduced fixes that the Data Management Gateway needs.

If you install it and reboot the server your DMG should be running much more smoothly.

Top 3 of what is new in Power Query for Excel

Just recently the December update of Power Query for Excel has been published. See this blog post on the Power BI for the details: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx.

In this post I want to give you my personal top 3:

1. Automatic detection of table relationships. Like PowerPivot if you import multiple tables from the same source Power Query now detects the relationships and brings them over to the Power Pivot Data Model automatically.

2. Connectivity to new data sources. Now supported: Exchange, Dynamics CRM Online, JSON light and Sybase IQ.

3. Fill Down transformation. Fills all empty cells in a column with the value of the first non-empty cell above them. Very handy for those matrix like Excel sheets!

I will come back to some of these in later posts. Happy Power Querying!

The wait is almost over…

It’s almost Christmas!

Well, uh, that’s not actually why I wrote this post. I noticed a note on the Power BI blog that says: “In a few days we will enable the ability to explore your own data with Q&A. You can add your own models to Q&A and begin to explore them with natural language query.” (See full post here: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx)

Read that again! This means that you can upload your own Power BI workbooks and use Q&A to have a conversation with your data. How cool is that! Minority report anyone?

Power BI Demo Contest

I want to draw your attention to a contest that we run: the Power BI Demo Contest. All you need to do is create a short video of something cool you built using Power BI and share it. You could win great prizes!

More info is on Facebook: https://www.facebook.com/microsoftbi/app_112813808737465

Good luck!

%d bloggers like this: