How to change table name a view references

Matt Millen shared this question 4 months ago
Answered

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 (7)

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