Is it possible to change schema in the view?

Ivan Dolinin shared this question 14 months ago
Answered

Hello, we have a view View1 and reports based on this View1 (MS SQL Server, YF 7.3). Is there a way to change the schema from "dbo" to something else that tables in the view are based on after it is created and used?

Comments (4)

photo
1

Hi Ivan,

if you've moved the MS SQL Server tables to a different schema after the Yellowfin reports and views have been created then you could change the schema in the Yellowfin Data Source to the new one and that should update the existing definitions. If that doesn't work then you could turn off the "Include Schema in SQL" option in the Yellowfin Data Source. Or finally, you could change manually the definition in the Yellowfin database by going to the ReportView table and finding your view record (the view name is actually stored in the ViewDescription column), and then underneath it you will see some entries with the ViewTypeCode value being "CHILDELEMENT" which means they represent the individual tables within your view. And that is where you will see the "dbo" in front of the table name, so that is where you could update the schema names manually.

And then because it is a backend modification you would have to refresh the application local cache by either restarting Yellowfin or using the info_cache_ehanched JSP available from the following KB article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-avoid-having-to-restart-yellowfin-after-backend-modifications-to-the-yellowfin-database

Also, if you do choose to go with that last option of backend modifications, then please remember to backup your Yellowfin database beforehand!

Please let me know how you get on with this.

regards,

David

photo
1

Hi Ivan,

just wondering how you got on with this?

regards,

David

photo
1

Hi David, i found that modifying exported XML and reimporting it back with different schema worked the best for me. Thanks for you help!

photo
1

Hi Ivan,

that's a very good alternative solution, well done for coming up with it, and I'm glad it all worked out!

Thanks for letting me know.

regards,

David