Can't mix data sets - static and data gateway? All static or all gateway works but not mixed?

[Boyan]

HI everyone,

I have a report with all static datasets. By static I mean bunch of spreadsheets. They get embedded into PBI upon publishing and all is well. Then I added a SQL source. All works on Destkop - I have real time connection to the SQL server. However after publishing it appears to me that PBI wants ALL data to be sourced from the DB gateway including the spreadsheets. Why is that? Any way around it? Those spreadsheets never change and I was hoping to continue to access them as embeded datasets just as I can do if my entire report is spreadsheets based.

Can I not mix static PBI embeded datasets with dynamic SQL data? I have ton of all-SQL reports using the SQL gateway and ton using all-embded spreadsheets but never before had I mixed them in one report and now that I have - I am facing this issue.

Any help will be appreciated

Thank you

Boyan

[lbendlin]

"the service can't see my C:\ drive yet it works - that's because the spreadsheets are embedded on the fly as data set in PBI cloud service"

That is incorrect. Spreadsheets are not embedded on the fly.   Your setup only works if you keep updating the PBIX file on the dektop and then push the update to the workspace. The moment you try to schedule  refresh on the workspace it will fail.

[Boyan · Accepted solution]

Thank you @lbendlin and @lbendlin - ok so I think I got to the bottom of it. Just as @lbendlin accurately corrected me - data sets fail when attempted to refresh. It's a one time deal as part of the push to the workspace. Thanks guys

[Anonymous]

Hi @Boyan,

I'm glad to hear this helps.🙂

If you faced some issues again, you can feel free to post your questions here.
Regards,

Xiaoxin Sheng

[Anonymous]

HI @Boyan,

Your data sources are referenced from local device files, it needs to be configured on your gateway even if they do not change anymore. Or your report not refresh due to not all data sources configured correctly.

Configure scheduled refresh 

Data refresh in Power BI 

For this scenario, I agree with 'bendlin' 's suggestion, you can consider to move them to a network driver and get data from them. After these steps, these data source not require a gateway to refresh.

Use OneDrive for Business links in Power BI Desktop 

Regards,

Xiaoxin Sheng

[Boyan]

Hi everyone,

I see few people have responded to me and I really appreciate your input.

I'm afraid I've failed to communicate effectively and that's on me :). My core issue here is inconsistency between how one would approach a Power BI report design. If the report is created in Desktop and then few spreadsheets are loaded as data sources (say from my local c:\temp) then upon publishing the report to PBI service NO gateway is required and of course - the service can't see my C:\ drive yet it works - that's because the spreadsheets are embedded on the fly as data set in PBI cloud service.

Learning from that experience I figured ok I would keep the spreadsheets as data sources but add just one SQL source. Well as soon as I do that then ALL data sources, including those that worked before now must be sourced via a gateway? (and yes of course I can get all of them gateway-sourced via SharePoint, OneDrive or what not, this isn't the issue)

Why is that? 

Thank you

~B

[Anonymous]

HI @Boyan,

In fact, this not means each type of data source requires a gateway.

Power BI data sources 
E.g. you are mixed multiple data source and one of them require to use the gateway to handle refresh. 

For this scenario, you need to use configure the gateway of this data set and enable the option to use the gateway handle cloudy Datasource if this type of data source existed in your dataset.

Merge or append on-premises and cloud data sources 

BTW, if your sheet no have too many records, you can also consider using the 'enter data' feature to import data to power bi. Power bi will recognize them as static data and not required to handle with a gateway.
Regards,

Xiaoxin Sheng

[lbendlin]

No, that's not how this works, unfortunately.  They do not get embedded, they get referenced (in the data source definition). The Power BI service then has no idea how to get to them, and it desperately needs the gateway to help out.

Do not place Excel files where the service cannot see them. Either load them into the workspace as workbooks, or - easier - put them onto a Onedrive. Then the service can see them and no gateway is required.

This is entirely independent of the SQL source.

[lbendlin]

You will want to explain that a bit more. When you say static do you mean you use the Power Query "Enter Data"  dialog?  Or are these files on your local PC (please say no!)?

[Boyan]

Hi, sorry, by static I mean an Excel sheet on my local PC. If all of them are XLS on my local PC then they get embeded to the  report when publishing it to PBI. However as soon as I have SQL queries then it no longer wants to embed the Excel sheets and instead expects the gateway to find them - which of course can not be done