Virtual table SQL query

Sofia Rabin shared this question 4 months ago
Answered

Hi,

We need to find all the reports which based on specific tables from DWH.

I saw similar question in YF community and even used the query from the answer. (https://community.yellowfinbi.com/topic/find-all-reports-based-on-a-certain-table-in-dwh)


But, seems like we are missing the usage of the tables if they are part of virtual tables.

is there any way to see the query of virtual tables in db?


Thanks,

Sofia

Comments (7)

photo
1

Hi Sofia,

Thanks for reaching out to support with your question. After looking into this briefly it looks like it might be tricky to get what you're looking for in the DB, "seeing the query of virtual tables" is easy to see in the report sql however -

5ba76223ca543543600c5cf6d2ed6305


Just want to confirm that's what you'd be looking to find here - in this case that production.product is being used by "vt report." Hoping I have it right?

I can check to see if the Audit Content reports have anything that includes this, as that's normally how these report tools are built. If there's nothing that identifies what you're looking for currently, I can make a request to look into the possibility of including it in the future. Otherwise, custom queries like this are beyond the scope of support technically, but there's the possibility of having a consultant look into assisting with this, is that something you'd like to explore?

Thanks,

Eric

photo
1

Hi,

Thank you.

What we are trying to do is to find if there is any use of tables which we want to remove from our DB.

if the table is part of virtual table we can't identify it unless we start looking at views SQL.

Is there maybe any way to find the view SQL in the DB?


Thanks,

Sofia

photo
1

Hi Sofia,

So far I've not found a way to get the info you're looking for, or any Audit Content report that includes this information, but I'll ask a couple of additional resources. If we can't find something, perhaps this is something you'd like to see included in a future version of the software?

Thanks,

Eric

photo
1

I'm also in need of something similar to this. We need to find every report using a particular field within a table. I also saw the other YF link to a query that would give this information, but it appears that some of the tables it references are not available to us. I could go through every report's views sql code, but that could take forever and I'm sure we will have to do this again in the future.

Thanks,John

photo
1

Hi John,

Every field used in reports can be found between the ReportField and ReportFieldTemplate tables. ReportFieldTemplate stores fields in the View that are subsequently used by reports of course, and ReportField entries are only generated once a change is made to the field at the report level. You can see by the FieldTemplateId in ReportField table that this value still links to the FieldTemplateId in the ReportFieldTemplate table, so you can see the relationship between that field and the field in the View.

Considering this information, based on the way these values are stored, it's not so straight-forward determining every field being used at the report-level as most field references in reports are actually direct references to fields in the View and the ReportFieldTemplate table stores ViewId's, not ReportId's. You may be able to link the ViewId's to ReportId's in the ReportHeader table and come up with some kind of approximate query, but I'm thinking it may not even be possible to accomplish this.

Regards,

Mike

photo
photo
1

Hi Sofia,

Stepping in since Eric was out yesterday and today. You should be able to return Virtual Tables based on the query provided in the link... the issue is that when you're defining the WHERE conditions, most Virtual Tables don't have a ViewName assigned to it. You can confirm this by doing

SELECT * FROM ReportView WHERE ViewTypeCode = 'VIRTUAL';

/fb6ac4e45701c28a6b112c1afc545571

If you change the .... 'and rv.viewname = 'New View'; section to 'and rv.viewname is null', it should return the appropriate results (Virtual Table values).

/39482477af45a74ce253a419a6f9436b

Regards,

Mike

photo
1

Hi Sofia,

I just wanted to check in and see if there was anything else you needed here.

Regards,

Mike

photo
1

Hi Sofia,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike