Power BI report server points to Old database when automatic refresh kicks in

[Mithraveer]

Hi,

We have a on-premise Power BI report server and have hosted a number of power bi dashboards in here. All the PBI dashboards are sourced in a oracle database. Recently we moved to a new Oracle database. So I opened the dashboards in PBI desktop and updated the oracle database and it can point to the new oracle database successfully. I saved the updated PBI file and uploaded it into the PBI report server. But as soon as the scheduled refresh kicks in the PBI report server, the dashboard goes back to the old oracle database. The 

Is there a way to clear DB cache in the On premise PBI report server. Please advice.

Thanks in advance

Mithra

[v-jtian-msft]

Hi,@Mithraveer .I am glad to help you.
Please try to clear the data source and credentials information in Power BI Dektop and reopen the pbix file that is causing the problem and remove the pbix file from your report server.
Restart Report Server Configuration Manager and re-upload the report from Desktop to your report server.
Take care to modify the Connection string in the report DataSource to make sure it points to the new Oracle database, not the old one.
Sometimes clearing the cache may solve the refresh issue, please try clearing your browser cache.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mithraveer]

Hi Carson, 

Thanks a million for your response back to my query. I tried the exact sequence of steps as mentioned in your solution. 

1. Open the file in PBI Desktop. Cleared cache from the Options --> Data Load menu. Cleared Permissions from the data source settings. Saved the file an closed it.

2. Reopened the file, hit refresh and gave the credentials and the report refreshed correctly pointing to the New database. This report refreshes correctly everytime I refresh it in the PBI Desktop. Saved the report

3. Cleared all the existing files from the PBI RS

4. Logged into the PBI RS and Opened Report Server Configuration Manager. Stopped and Started services.

5. Now refresh the PBI RS website and uploaded the latest file as per step 2. When I upload it, it shows the latest data. Then I confirm the DB details and save the successful connection. Then I setup the automatic refresh. 

But still as soon as the first automatic refresh/refresh now option kicks in, the report goes back to the old Database again. 

I also took a backup and deleted the Encryption key in the Report server Configuration Manager in the same sequence as above. But still the report is pointing to the old database.

One thing to note here is the service name of the old and new oracle database are the same and only the host name has changed. So not sure if this is causing the issue.

Kindly let me know what else I could try in this instance.

Kind Regards,

Mithra

[v-jtian-msft]

Hi,@Mithraveer .Thank you for your reply.
Have you tried to modify the connection string of the report, you mentioned that the oracle database name has not changed, but the hostname has changed, please try to modify the hostname in the connection string

If you can't modify the connection string properly, you can also do it via API.
Change data source connection strings with PowerShell - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Mithraveer]

Hi @v-jtian-msft,

The connection string is automatically populated from my tnsnames.ora file. When I changed the alias in my connection string, it is working fine in my PBI Desktop, but not in the PBI Report server since the alias name comes into my connection string. 

Also the connection string is automatically populated and not allowing me to add the server name in the Report server. Since both my report server and desktop are May 2020 version, I am not sure If I can use the Power shell method, you highlighted above. 

Could you please let me know if there is a possibility to edit my tnsnames in such a way that it picks up the host name as well. 

Thanks a million for your responses so far. Any more suggestions will be much helpful.

Kind Regards,

Mithra

[v-jtian-msft]

Hi,@Mithraveer .Thank you for your reply.
Given that your situation is unique and your repot server version is May 2020. So I have found the following relevant documentation which I hope will help you.
URL:
Change data source connection strings with PowerShell - Power BI Report Server pre-October 2020 - Po...
 The reportserver version is very important, while changing your report server version is generally not recommended.
For your question: does the report server support editing your tnsnames so that it can also pick up hostnames.
The report server itself has a lot of limitations, so it is best to operate according to the official documentation itself to avoid data loss and other accidents. As for the report server's ability to recognize tnsnames, I suggest you contact your team's IT department and ask them to let you know if they are skilled in the use of power shells to avoid accidents.
You can try to modify the connection string of some databases to see if it works.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Mithraveer]

Hi @v-jtian-msft ,

Thanks a milion for your response,

I checked the link you provided. It has a note stating that this only works for DirectQuery. But whenever I add the Oracle database in my PBI Desktop it only enables the Import options in the Data Connectivity mode and not the DirectQuery as shown below. So will this cause any issues ? Kindly advice.

Kind regards,

Mithra

[v-jtian-msft]

Hi,@Mithraveer .Thank you for your reply.
Thank you for your reply.
According to the documentation, the way I provided is currently only applicable to direct query, while for import connection mode, Microsoft is under development (in the process of launching).
For the data source connection mode, I do not recommend you to change it directly, because the change of data connection mode will lead to a lot of functionality limitations, in short, this document does not seem to be suitable for you at the moment, and I recommend you not to try to change the data source connection mode either. This is likely to cause unexpected situations.
I will continue to follow up with you on this current issue and will keep you informed of any suitable developments.

Thank you for your understanding.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Mithraveer]

Hi @v-jtian-msft ,

Based on one of your above response, I tried couple of items and one solution worked but with a little change in the format. I gave my server name in below shown format and the refresh worked both in my PBI Desktop and PBI report server. By this way the scheduled refresh works fine.

Host_name:Port_number/Service_name

One small issue with this is, we have two host names as a failover service and quiet often we do site switches between the two host name. So whenever we do a site switch, I need to manually update the server name refresh the report and upload the updated file into the report server.

Is there a way to include two hostnames to the above format, so that it picks up the anyone one server which is available, I tried a couple of combinations but still did not get it right. Any suggestions will be very helpful.

Also thanks a million for all your suggestions.

Kind Regards,

Mithra

[JPM]

Host_name:Port_number/Service_name is oracle ezconnect , depending on your oracle version you may be able to use ezconnect plus Oracle Database Easy Connect Plus

You may also have more than one tnsnames file on your report server if you have installed both 32 and 64 bit drivers so worth checking out enviromental variables on the server to make sure you are pointing at the correct one 

[Mithraveer]

Hi @JPM,

Thanks a million for your response. The oracle easy connect plus script accomodating two hostnames is working fine in my Power BI Desktop(may 2020). When I upload the same file into the Power BI report server (may 2020), I am getting an error -  ORA-12154: TNS:could not resolve the connect identifier specified. I used the below format and tried few other combinations.

Protocol://host1,host2:port_no/service_name

But still no luck. The Report server is only accepting the one format Host_name:Port_number/Service_name. 

Any suggestions will be really helpful.

Regards

Mithra