Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
So I am having trouble getting data to refresh with a SQL Server Connection in the new version of Power BI Report Server.
I created a brand new report in the October Report Server Desktop version with a connection to a simple view in SQL Server.
In the Desktop tool the data will refresh fine.
I then save the report to the report server and it renders fine.
If I change the data in SQL and hit refresh on the server nothing happens.
I went to Manage and then data sources and entered my credentials (same as what is used on the Desktop version).
Hit refresh and still no data change.
I then created a schedule to refresh the data hourly and the data did not change.
In the Schedule Refresh page i hit refresh now and the status just hangs on 'refreshing'.
Looking at the log file i keep seeing this error
2017-11-08 12:00:42.3443|ERROR|19|Failed to get connection string for model | RequestID = 44ccb0b2-29ee-8b56-c3f6-12c3e1e167ec ClientSessionID = 9d75fe2b-ee23-96a5-400c-5259dd79a716 Microsoft.PowerBI.ReportServer.AsServer.AsConnectionException: Failed to get connection string for model
In the data model I can't edit the connection string.
Am i missing a step?
Kind regards
Dean.
Solved! Go to Solution.
So I found the problem.
There were 2 data sources in the model pointing to the same database and for some reason I could not enter credentials for the second data source as the second one disn't show up.
The reason there were 2 was that the database name was all lower case in one source and camel case in the other.
I used the advanced editor in desktop to rename them to be unique and then there was one source and data refreshes fine.
In the Portal can you go to the management page for that report and click the Data Sources tab. From there can you try to 'Test connection' for each of the data sources that you have in the report and let me know if that works? Thanks!
Thanks for the reply!!
I've done that and the connection was successful.
I bounced the server in hope but no luck.
The scheduler always says refreshing
And the data source looks as per below.
Also tried using OleDB connections instead of SQL server connections and still same response.
btw. I am using direct query with the SQL connections.
The refresh works when using Import connection.
Looks like the schedule is running and the output contains no errors.
Here is an example of the output from the RSPowerBI2017XXX file:
2017-11-14 13:18:08.5061|TRACE|5|AnalysisService XEvent: {
"Name": "PBIRS.AS.ExecutionStatistics",
"Use": "Info",
"Id": "539f329c-47e9-4528-a393-945fb34ebc9d",
"Time": "2017-11-14T03:18:07.5559345Z",
"Properties": {
"Summary": "Execution stats: time(Req-Start-UTC)=03:18:07.553, time(Req-Execute-UTC)=03:18:07.553, time(Req-Dispatch-UTC)=03:18:07.553, val(Req-ParsingThreadpoolType)=1, sum(Req-ParsingTasks)=1, sum(Req-ParsingQueueWaitingThreads)=4, sum(Req-ParsingQueueBusyThreads)=0, sum(Req-ParsingTime-ms)=0, sum(Req-ParsingWait-ms)=0, sum(Req-TCPRawBytes)=719, time(Req-TCPRequestStart-UTC)=03:18:07.553, sum(Req-HeapAllocated-kB)=3, sum(Req-HeapReallocated-kB)=0, sum(Req-HeapReallocs)=6, sum(Req-HeapReleased-kB)=1, last(Req-DiscoverType)=30, sum(Req-Duration-ms)=0, time(Req-ParseReqStart-UTC)=03:18:07.553, time(Req-ParseReqLoadEnd-UTC)=03:18:07.553, time(Req-ParseReqUTCLEnd-UTC)=03:18:07.553, time(Req-ParseReqEnd-UTC)=03:18:07.553, last(Req-Priority)=2, time(Req-Done-UTC)=03:18:07.553,",
"RequestID": "539f329c-47e9-4528-a393-945fb34ebc9d",
"ActivityID": "859d36b4-50a1-4dc5-8ce0-b968479aa2f0",
"ActivityType": "TPWM",
"ClientActivityID": "539f329c-47e9-4528-a393-945fb34ebc9d"
}
}
So I found the problem.
There were 2 data sources in the model pointing to the same database and for some reason I could not enter credentials for the second data source as the second one disn't show up.
The reason there were 2 was that the database name was all lower case in one source and camel case in the other.
I used the advanced editor in desktop to rename them to be unique and then there was one source and data refreshes fine.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
6 | |
4 | |
3 | |
2 |
User | Count |
---|---|
13 | |
10 | |
5 | |
5 | |
3 |