This dataset contains on-premises data sources. But it doesn't!

[IanR]

Hi,

I'm trying to set up a scheduled refresh for the first time. With the exception of one table all my data comes from an oData connection to CRM Online. The one table that doesn't is a dates table which is static and in the Power BI Desktop version of the model it does not get refreshed. When I try to schedule a refesh I get the message below:

'This dataset contains on-premises data sources. To schedule data refresh, try Power BI Pro for 60.00 days, or contact your Office 365 tenant administrator to purchase Power BI Pro. '

Does this message mean what it says? Is this really an on-premises data source problem (maybe I don't understand the term on-premises) or do I have to upgreade to the pro version to schedule a refresh? We are using the free version or Power BI that comes with our Office 365/CRM licenses.

If I need to upgrade do all the people who will be accessing the data need to be on the pro version as well, or just me?

Thanks

[v-yuezhe-msft]

Hi @IanR,

Could you please post the screenshot of data sources in Power BI Desktop for us to check if you contain on-premises data source in your dataset? You can go to File -> Options and settings ->Data source settings to check data sources.

Adding to other post, when you contain on-premises data source in your dataset and set schedule refresh for your dataset, it depends on how you share the reports to other users that if other users require Power BI pro license. If you use “Publish to web” feature in Power BI Service to generate a link for the users to view reports, they don’t require pro license. However, if you share reports using other methods(for example, share dashboard), other users will require pro license.

Thanks,
Lydia Zhang

[IanR]

Hi Lydia,

In ‘File | Options and Settings | Data Source Settings’ there is a reference to the Excel file on my local system that was the original source for my dates table. However, in the properties for the Dates table in Query editor the box for ‘Include in report refresh’ is unchecked. I’m not importing data for this table when I refresh but the original ‘on-premises’ source is mentioned in Data Source Settings. Is that the problem?

If so, how do I fix it? I’ve tried to delete the entry for this file in Data Source settings but it won’t let me. There’s an option to clear permissions but that didn’t delete the file and probably won’t make a difference for an Excel file anyway.

Thanks

Ian

[v-yuezhe-msft · Accepted solution]

Hi @IanR,

Yes, that is the problem.

Please delete the Date table by right-clicking it in Report view of Power BI Desktop and selecting "Delete".


And instead, you can directly create a date table using DAX as described in this blog, this way, after you publish Power BI Desktop file to Power BI Service, gateway is not required when you refresh your dataset.



Thanks,
Lydia Zhang

[IanR]

Wow. is that the only option? I have lots of measures associated with the Dates table and probably some non-standard columns that would not be reproduced by the code in the blog (very interesting blog by the way). And lots more measures that use the measures associated with the dates table.

I guess I could work out how to create my non-standard columns but will the measures associated with the Dates table disappear when I delete the original table? Do I have to copy them to text and then associate them with the new table. Sounds like a lot of work with a large chance that I will miss something. Is there no other way?

[GilbertQ]

Hi @IanR

Whilst it might be a bit of effort, I would suggest this is the best way. As well as moving forward to potentially always create your date table only in Power BI.

For the custom columns that you need I am sure that you can re-create them in the Query Editor. Once you have got your date table in the format with the columns you require, you can always copy all the code from the Advanced Editor. So that when you create your next Power BI Desktop File you can copy and paste the code.

Or an alternative is to save the Power BI Desktop file as a Template (PBIT).

[Greg_Deckler]

Where did your date table come from originally? Any chance you could just copy and paste it into an Enter Data query?

[IanR]

Hi,

There are eight tables from CRM Online plus the dates table. The eitht tables take their data from some standard CRM tables (LeadSet, ContactSet, OpportunitySet, TeamSet, SystemUserSe), a Click Dimensions table (cdi_SentMailSet), a custom table in CRM (WatchesOwned) and a table that I create in the query editor from the SystemUserSet and TeamSet tables (UsersAndTeams). There's a lot of data there and some of it is too confidential to distribute. Apologies.

Is there anything in particular I should be looking out for? Is it usually possible to set up a scheduled refresh without a pro license?

Thanks

Ian 

[GilbertQ]

Hi @IanR

@Greg_Deckler is correct in that it appears that your other data tables are causing your current issue.

Currently using the CRM Online with data outside of the CRM Online can only be used with the scheduled refresh which will require a Pro license, and as far as I am aware there is no other way to refresh without uploading the updated Power BI Desktop File manually.