Solved: DataSet refresh with Import vs Direct Query

drinko · ‎02-25-2021

I have a couple of .pbix reports where I used Import to bring in SQL Server data and a few CSV files (dimension tables). For some reason I can't schedule Dataset refreshes in the PBI Workspace. I get the following error:

"Data source error Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh."

I don't understand if I provide credentials will that allow me to schedule Dataset refreshes without error? Where do I do this? I don't really understand the construct of the Dataset refresh, especially when I have SQL Server DB and CSV sources. It seems unnecessarily complicated.

drinko · ‎02-27-2021

Through much late night research I have discovered a few things that are not documented very well. In my case, I have a SQL Server datasource and some CSV files that are FTP'ed or just live on our DB server. I use Import instead of Direct Query on the DB. I wanted to schedule dataset refreshes each morning, but did not understand the Gateway requirements and setup. I installed the Standard Gateway on the DB server and logged in.

The next step is to go into the Workspace and click on Dataset (stoplight icon) > Settings > Gateway Connection. It was fairly intuitive to setup the SQL Server using the DB credentails. The challenge was the .csv files and understanding the relationship between the dataset settings and the datasource settings. They need to match exactly. Probably has something to do with passing credentials. I was mapping the R-drive (from my laptop) and then the E-drive (on the server itself) and finally figured out to use the IP address for server name. Example: \\10.100.1.1\AppDB\xxx.csv. It will still not connect successfully until you verify that your datasource settings for those .csv files are also mapped using the IP address example. The last piece was then figuring out the proper construct for defining each datasource in the Gateway (its not very intuitive). Its a combination of clicking on the gear icon to manage the gateway datasources (1 for each .csv file and DB). Use the correct mapping and your network credentials for the server (it should connect to the .csv file). Then you need to go back into the Gateway Connections and select the "Maps To" and select the gateway datasource that you just setup. You should then get a greenlight that it connects. You can verify the datasource credentials and schedule the refreshes for when you need them. Hope that helps.

View solution in original post

drinko · ‎02-27-2021

Through much late night research I have discovered a few things that are not documented very well. In my case, I have a SQL Server datasource and some CSV files that are FTP'ed or just live on our DB server. I use Import instead of Direct Query on the DB. I wanted to schedule dataset refreshes each morning, but did not understand the Gateway requirements and setup. I installed the Standard Gateway on the DB server and logged in.

The next step is to go into the Workspace and click on Dataset (stoplight icon) > Settings > Gateway Connection. It was fairly intuitive to setup the SQL Server using the DB credentails. The challenge was the .csv files and understanding the relationship between the dataset settings and the datasource settings. They need to match exactly. Probably has something to do with passing credentials. I was mapping the R-drive (from my laptop) and then the E-drive (on the server itself) and finally figured out to use the IP address for server name. Example: \\10.100.1.1\AppDB\xxx.csv. It will still not connect successfully until you verify that your datasource settings for those .csv files are also mapped using the IP address example. The last piece was then figuring out the proper construct for defining each datasource in the Gateway (its not very intuitive). Its a combination of clicking on the gear icon to manage the gateway datasources (1 for each .csv file and DB). Use the correct mapping and your network credentials for the server (it should connect to the .csv file). Then you need to go back into the Gateway Connections and select the "Maps To" and select the gateway datasource that you just setup. You should then get a greenlight that it connects. You can verify the datasource credentials and schedule the refreshes for when you need them. Hope that helps.

drinko · ‎02-26-2021

We are using Service (cloud) and not on prem. I have been all over Dataset settings and have no "edit credentials" anywhere. I can refresh the reports in Desktop with no issue so credentials should be in there somewhere. There is no indication of a particular data source that is not reachable. When I click on Dataset Settings, the Datasource Credentials is grayed out. Do I have to have a Gateway installed on our DB server to let this work?