MS fabric PBI report Scheduled Refresh Disabled- D365 BC

[Grapefarmer]

Hi All;

I have published a PBI report to fabric which draws data from two sources:

1) Microsoft Busincess Central (Microsoft login manages access to the API)

2) A Static CSV file on our network drive (It's a calendar reference file which doesnt really ever need to be updated)

When I try and refresh the semantic model, i get an error that a data source is missing credentials.

I suspect, but do not know that the problem is to do with the CSV file reference.

PBI indicates that i need to update credentials, but I cannot access this in fabric- it's greyed out.

I also can't find where to interrogate the credential settings for each individual table referenced in the PBI semantic model.

I'm looking into using a personal or server on-premise gateway which might work, but that requires various corporate approvals, and work with our IT provider which may take weeks.

Use a personal gateway in Power BI - Power BI | Microsoft Learn

Ideally, I want to be able to switch off refresh for the local static CSV files, and allow the rest of the Business central data to refresh independently.

Where in PBI / Fabric can I access the credential settings for each table?

See screenshot for the error encountered and symptoms.  

[Joe_Barry · Accepted solution]

Hi @Grapefarmer 

This will happen with a local file service and you will need to setup a Gateway for this. Is the network drive connected to SharePoint? What you can do if it is.

• You can open the file
• go to file and you will see a list of recently opened files.
• Right click on the name of the CSV you want to import into Power BI and click on copy path.
• In Power BI Desktop go to Add Source
• Then Web
• Paste the URL in the field and ok.

The CSV will now load and you shouldn't have any issues with credentials after you publish, as your Microsoft credentials will be enough.

Hope this helps

Joe

[v-cgao-msft · Accepted solution]

Hi @Grapefarmer ,

Consider uploading the csv to a cloud drive such as a Sharepoint folder, OneDrive etc. This will bypass the on-premises data gateway.

SharePoint and OneDrive for Business files import - Power Query | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Grapefarmer]

One thing that hasnt been answered here yet is how to *DISABLE* the refresh selectively on some PBI tables in a semantic model on the fabric service. Many static model reference files just don't need to be updated.

Also, if the file on onedrive being referenced by the model is deleted, or moved, for example, having the model spit the dummy and not refriesh anything just because it can't refresh one table out of 10 coudl be avoided. 

[v-cgao-msft]

Hi @Grapefarmer ,

1. Excluding queries from refresh:

Managing query refresh -Excluding queries from refresh

2. Use dataflow to import the csv and then connect to dataflow. as long as you don't refresh/delete the dataflow, it won't affect the data refresh.

3. New a enter data table.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-cgao-msft · Accepted solution]

Hi @Grapefarmer ,

Consider uploading the csv to a cloud drive such as a Sharepoint folder, OneDrive etc. This will bypass the on-premises data gateway.

SharePoint and OneDrive for Business files import - Power Query | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Grapefarmer · Accepted solution]

Hi Gao;

This looks like a great solution, but there were a few steps to take to get it to eventually work!

Follow me on the journey!

Initially, when copying the onedrive sharing URL I get the following error:

This outcome was when following the advice on the link you provided.  (see screenshot below)

However, when copying the link directly from the excel desktop app, the onedrive hosted file loads successfully, so the source and format of the Onedrive file web link is obviously really important. 

The next step, after publishing the test report to the PBI / Fabric service was to Edit the credentials for the onedrive connection, and use OAUTH2 / Organisational privacy settings

I got some pointers off this useful blog - Dataonwheels. 

Power BI: Refreshable Excel Files in PBI Service – Data on Wheels – Kristyna Ferris & Steve Hughes

The change in credentials resulted in a successful refresh.

Note that the onedrive refresh notification doesn’t seem to change, and seems to remain static at the original publishing time & date, but this seems to be nothing to worry about. 

[Joe_Barry · Accepted solution]

Hi @Grapefarmer 

This will happen with a local file service and you will need to setup a Gateway for this. Is the network drive connected to SharePoint? What you can do if it is.

• You can open the file
• go to file and you will see a list of recently opened files.
• Right click on the name of the CSV you want to import into Power BI and click on copy path.
• In Power BI Desktop go to Add Source
• Then Web
• Paste the URL in the field and ok.

The CSV will now load and you shouldn't have any issues with credentials after you publish, as your Microsoft credentials will be enough.

Hope this helps

Joe