THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Scheduled Data Refresh in Power BI #powerbi #powerpivot

Now that Power BI is finally available (I still use terms as “released” for software and “in production” for a service…) I made some test on the new Scheduled Refresh option that you have for your models (other info here.

First of all, the Refresh feature within the Excel workbook no longer works, at least when you are using Data Management Gateway – you need to schedule a refresh for that, and I think this is a good idea. The refresh time could be very slow and waiting 30 seconds or more without being able to interact with the workbook wasn’t good from the user experience point of view. Well done.

Now, when you schedule the refresh, you can set a frequency that can be daily or weekly. No intra-day updates, unfortunately. I think you might workaround this by creating several copies of the same workbook and then defining a different daily schedule for each copy, at different hours. I think that a more frequent update could be a good idea for certain businesses. Maybe with some limitation, but if the workbook is small I don’t see the real reason for such a limitation (considering that it could incentivize bad behaviors like the workaround I just described).

You can also request a manual refresh – the strange thing is that you can do that only after you defined a scheduled refresh (maybe I don’t need that, so I might create an unnecessary schedule just for a single manual refresh).

SNAGHTML2f2d425

I observed that when you request a manual refresh, in reality you just put a request in an internal queue somewhere on the cloud. After you request a refresh, you cannot request a second one, because one refresh is running.

image

At this point, you see the status Running in the History tab:

image

In reality, chances are that you are just waiting but nothing is running. In fact, I have seen my workbook refreshed after 10 minutes, but history log says that execution time was only 28 seconds. For this reason I assume that there is a queue that handles these requests on the cloud and manual refresh can take so long to finish. However, the second execution required less than 2 minutes, so I am not sure it was because I refreshed a workbook on my tenant for the first time (maybe some deployment was involved) or I was just unlucky and my queue was particularly slow at the previous request.

image

I look forward to see more data sources supported by scheduled data refresh, and in particular Power Query would be an important step for making Power BI adoption even larger.

Published Tuesday, February 11, 2014 8:18 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Krishna said:

On the Power BI MVA session, Data Management Gateway supports SQL and Oracle sources at the moment.  Is this the same thing?

February 11, 2014 2:35 PM
 

Marco Russo (SQLBI) said:

Yes it is.

February 11, 2014 4:16 PM
 

Ásgeir Gunnarsson said:

Hi Marco.

Is there any way to connect to a on premise SSAS with the Data Management Gateway in the released version?

I think I might have read something about a workaround using the connection string from the Excel file or something like that. Have you heard about it or is it just not possible?

February 12, 2014 2:48 AM
 

Marco Russo (SQLBI) said:

It is not possible. A workaround might be using a linked server with SQL Server, because SQL Server is supported. I haven't tested it.

February 12, 2014 4:06 AM
 

Ásgeir Gunnarsson said:

Thanks for your answer Marco.

February 21, 2014 2:13 PM
 

Raghav said:

Hi Macro / Asgeir,

I have tried auto data refresh for on premise SSAS cube by doing workaround using linked server with SQL server and it is successful. If you want more details like how to retrieve cube data, you can look into my recent post here.. http://raghavmaddali.blogspot.com/2014/04/power-bi-schedule-data-refresh-for.html.

April 5, 2014 4:40 AM
 

Marco Russo (SQLBI) said:

Great post! I also tweeted it.

Thanks!

April 5, 2014 4:56 AM
 

Raghav said:

Yes Macro. I tried it by seeing your tweet. Thanks for your great Idea Macro.

Thanks Again

-R

April 5, 2014 5:08 AM
 

martinse said:

Is it possible to refresh power query queries from SharePoint 2013 yet?  Seems like a very basic feature that absolutely must be there for this product to be viable in an enterprise.

July 21, 2014 5:07 AM
 

Marco Russo (SQLBI) said:

At the moment Power Query can be refreshed only on SharePoint Online with Power BI. I think that for SharePoint we have to wait the next major release of Office.

July 21, 2014 5:08 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement