How to change table name a view references

Matt Millen shared this idea 14 months ago
Idea Logged

Hi,

I created a view in Yellowfin in my public redshift schema off a temporary table (let's call it public.tmp_view_1) so that I could get internal feedback on the reports created using the view. Now the format of the reports is finalized I'm going to create an automated job to write the data to production (call it prod.view_1).

What I'm concerned about is breaking all my reports if I just delete public.tmp_view_1 and drag in prod.view_1 in the Entity Relationships tab of the View Builder. I also have a lot of calculated fields that I think may get deleted.

Is there any way to switch the tables the view refers to over without breaking everything? In hindsight I should've just used a virtual table with select * from public.tmp_view_1 and the change would've been simple as the two tables are identical.

Thanks,

Matt

Comments (11)

photo
1

Hi Matt,

While it is possible to accomplish what you are seeking through back-end queries against the configuration database, I am hesitant to recommend this unless it is absolutely necessary.


Generally I would recommend testing these new tables in a mirrored schema of your current database. This will assure that the table names are all the same (something Yellowfin requires), and allow you to simply report the new reports/views on top of a datasource that points to the production database.


Is this something that you plan to do frequently?

Thanks,

Eric

photo
1

Normally I have used virtual tables for similar things, but that's generally because I do some manipulation of the data in the query. Because this would have been a simple select * from I pointed directly at the temp table. I certainly won't be making the same mistake again. I don't see a way of not breaking all my reports and losing the calculated fields at the view level right now.

Another issue I've seen is that if I have two tables in different schemas with the same name, i.e. qa.table_1 and prod.table_1, in the View Builder I can only see one of them (and not the one I want). Referring directly to tables seems like something to avoid completely

photo
1

Hi Matt,

It's recommended to Build Yellowfin Views from static datasources, so as not to modify the underlying data the metadata layer is dependent upon. If special circumstances require that you orchestrate your workflow in this way, I'd suggest working with a Consultant in determining an appropriate work flow as it pertains to the Yellowfin platform.

In regards to the second issue, can you confirm your datasource has the "Include Schema in SQL" option enabled?

Thanks,

Eric

photo
1

"Include Schema in SQL" option is checked, it seems like there is some deduplication at the table name level rather than at the combined schema and table level. I'll find a workaround for this by renaming tables in the db.

As we do not have a standard data model there is often a requirement to create reporting tables that will act as a view in yellowfin. In future I will write the data to a permanent schema to avoid the problems faced here. Having said that, functionality to switch out table names and update field mappings would have been very useful in development, switching between QA and prod is fairly commonplace

photo
1

Hi Matt,

If you are concerned your deduplication issues are defective behavior, we could take a look at that.

I understand the hassle of working around and modifying protocol. As I sad earlier, if this is something you would like to pursue, I can reach out to your Account Manager and put you in touch with our CSMs for a more in-depth consultation.

Thanks,

Eric

photo
1

Hi Matt,

Just wanted to check in, were you still seeking support for this issue?

Thanks,

Eric

photo
1

Hi Matt,

I'm going to go ahead and mark this ticket as Answered. Feel welcome to reach out with further inquiries.

Thanks,

Eric

photo
1

I have the same issue ... databases are dynamic sources in my view so why not give the possibility to change the schema and table name in a specific view.

In my case the schema and the tablenames are changed as the data stored in that table has changed (so the schema is changed and the table name also to reflect those changes). I also would like to change the schema and or the tablename without manually editing all kind of repo database tables.

I do understand that it should only be possible when you edit a view instead of cloning it. The same for changing the datasource. But not giving a possibility to change views and tablenames seems unnecessary restrictive to me.

photo
1

Hi JeRoen,

Thanks for reaching out to support.

In conversations with a colleague, he has had success renaming tables referenced by a view in the back-end, for example -


UPDATE public.reportview

SET viewname='"public"."CNV_campaign_data_extract"'

WHERE viewid = 89949

(please practice due diligence and have working backups when querying the configuration DB, as it is technically out-of-scope of support and can lead to irrevocable damage to the system)

So it should not be too difficult to implement this functionality in the frontend, I'm thinking a "rename" option when a table is selected in the View Builder -

5e20b1a4ebde33483d7c216d186ae522


Would this be the type of functionality you'd like to see in this case? Yellowfin does have the option to rename a table in a view in the case a table is not found, but perhaps in your case the original table still exists in the system.


82c62d2367f719e3e08902d4e67691c9


There's also an existing task to have the ability to be able to rename tables via web service, if that's more along the lines of what you're looking for. Let me know if there's a particular avenue you'd like to take here.

Thanks,

Eric

photo
1

Hello Eric,

Thank you for your extensive answer.

The added rename button (schema and tablename) would be perfect. It seams that all needed functionality is there already, only not available when the original table still exists. Or something like the code mode in YellowFin 9 so that the source XML for a certain table or view is also editable. This would make it also possible. The GUI way is offcourse the nicer way. But having code mode in view building would be nice too (I think about changing calculated fields without total rebuilding)... but that is a much broader issue.

I am not using webservices myself (only the GUI) but when the functionality off one is build then I think it should be relatively small effort to add the GUI or vice-versa.

Add my name to this functionality please. Thank you.

Kind Regards,

JeRoen

photo
1

Hi JeRoen,

Thanks for the reply. I've gone ahead and created a Developer task to look into the functionality you're seeking

Ability to Rename Existing Table in View Builder

Updates to the task will be provided here as they are available. I will in turn mark this ticket as Idea Logged for now; feel welcome to "like" the idea for increased visibility, or reply here if you have additional related inquiries.

Thanks,

Eric