Choose Between Views and Tables on View Creation

Renato Marcello dos Reis shared this idea 4 months ago
Idea Logged

Hi guys,

When we create a View, the creation page starts the following procedure on the connected database.

exec sp_tables NULL,NULL,NULL,N'"''TABLE'',''VIEW''"'
With this command, the Yellowfin returns all tables and views within the database. I was wondering if this behavior can be changed, by returning only tables or only views depending on some configuration.


Thanks in advance.

Renato M.

Comments (12)

photo
0

Hi Elizabeth,

Thanks for reaching out to support with your issue.

It sounds as though you have a very large view, that includes 123,400 tables, is that correct?

You may benefit from View Caching in this circumstance. See here -

https://wiki.yellowfinbi.com/display/user80/View+Caching

You may also have success here by disabling the Data Preview setting, see here-

https://community.yellowfinbi.com/topic/prepare-stage-of-view-builder-is-slow


If you continue to experience issues, would you be able to provide a compressed copy of your Yellowfin logs folder for further analysis?

Another ptentially helpful tool here would be the Yellowfin Performance Snapshot Analysis tool, perhaps you could run this and provide results?

https://community.yellowfinbi.com/knowledge-base/article/yellowfin-performance-analysis-snap-shot-tool

Also, could you confirm the build details of the Yellowfin instance you are experiencing this with? This information can be found at <url:port>/info.jsp

The reason I ask is that there may have been a recent fix for this -

We do have a setting that might resolve these issues. Earlier this year we added an option to cache meta-data calls. This caches the results of getTables() and getColumns() indefinitely, so that after the initial database meta-call, all subsequent calls will fetch the results from memory. This makes editing views a lot faster as the meta-data is fetched on nearly every page refresh. These changes will be in the 7.4.10 Yellowfin release which is due this week.

Thanks,

Eric

photo
0

Hi Eric

I wasn’t in the office yesterday, hence the delay in responding to your email.

Release information -:

d7dc8da5183f00624b8d2f0e70febe14

The 123,400 tables are brought back by every view, for example, this view only has 4 tables -:

237b788f774b78f18f551650b606095c

Attached are the logs as requested.

Regards

Liz

From: Support Queue <support@Yellowfin.bi>

Sent: Tuesday, August 6, 2019 3:06 AM

To: Elizabeth Lekic <Elizabeth.Lekic@datacom.com.au>

Subject: Re: Editing of Views is very slow [#14655]

Files: logs.zip
photo
0

Hi Eric

Further to the below email, I have tried switching off the Data Profiling option and the view still brings back the 123,400 tables -:

9d8332288bd3418b192f1041045ccaa8

831fb70bd6322420816bfd6e898a3dfd

Regards

Liz

From: Elizabeth Lekic

Sent: Wednesday, August 7, 2019 8:45 AM

To: 'Support Queue' <support@Yellowfin.bi>

Subject: RE: Editing of Views is very slow [#14655]

Hi Eric

I wasn’t in the office yesterday, hence the delay in responding to your email.

Release information -:

1d0d860412691b979a96cbc45f905c54

The 123,400 tables are brought back by every view, for example, this view only has 4 tables -:

e580d2ee33899ace0aaed53c249225d3

Attached are the logs as requested.

Regards

Liz

From: Support Queue <support@Yellowfin.bi>

Sent: Tuesday, August 6, 2019 3:06 AM

To: Elizabeth Lekic <Elizabeth.Lekic@datacom.com.au>

Subject: Re: Editing of Views is very slow [#14655]

photo
0

Hi Elizabeth,

Thanks for trying that out for me. There's a couple ways we can look at this -

A) Limiting the number of tables. there's not a Yellowfin function to provide this, but it could be tuned on the backend. You could create a separate schema in your RDBMS and use that to base your YF views off of, for example.

B) Make the return faster. There's a number of ways to make Yellowfin "faster" usually by allocating more memory to the application. Here's some resources if you would like to explore this further -

https://community.yellowfinbi.com/knowledge-base/article/where-can-i-find-how-much-memory-is-allocated

https://community.yellowfinbi.com/knowledge-base/article/yellowfin-performance-tuning-or-monitoring

https://community.yellowfinbi.com/knowledge-base/article/yellowfin-internal-cache-options

C) There's also the possibility to inquire to developers wheter this "limit" could be an added feature in future versions of the software.


Is there a preferred way you'd like to go about resolving this?

Thanks,

Eric

photo
0

Hi Eric

Thanks for the update.

I would like it raised with the Developers, I personally cannot see why anyone would want to troll through 123,400 tables while editing views etc.

Are we the only company using Yellowfin with SAP?

Regards

Liz

From: Support Queue <support@Yellowfin.bi>

Sent: Thursday, August 8, 2019 9:12 AM

To: Elizabeth Lekic <Elizabeth.Lekic@datacom.com.au>

Subject: Re: Editing of Views is very slow [#14655]

photo
0

Hi Liz,

I could create an enhancement request for developers for possible inclusion in a future version. In this case could you provide details on the manner you would like this limiting to be applied?

Thanks,

Eric

photo
0

Hi Eric

We would like the following enhancement -:

When a view is created and/or modified, no tables are to be returned for the database list view.

Only return tables when a user uses the search option during the create/modify view phase.

The SAP Schema contains over 100,000 tables, having these listed in the database view each time we create/modify a view, results in long delays between each click.

Regards

Liz

From: Support Queue <support@Yellowfin.bi>

Sent: Friday, August 9, 2019 3:18 AM

To: Elizabeth Lekic <Elizabeth.Lekic@datacom.com.au>

Subject: Re: Editing of Views is very slow [#14655]

photo
0

Hi Liz,

Thanks for the details here. I have gone ahead and made a request to developers to look into this possible software enhancement. I have attached this ticket to the task. Updates to the task will be provided here as they are available. I will in turn mark this ticket as Idea Logged; feel welcome to reply here with further related inquiries.

Thanks,

Eric

photo
1

Hi Renato,


Thanks for reaching out to support with your issue.


It's funny, I just had another client asking a similar question that I created an enhancement request to developers about:

https://community.yellowfinbi.com/agent/object/14655

I think this current Idea might be able to be "merged" with the additional details you've provided. Would you be able to take a look and see if this is the functionality you're looking for? Or if you have specific requirements on your end that would warrant amending this, or creating a separate request?


Thanks,


Eric

photo
1

Hi Renato,

Sorry, I sent the wrong link! Here you go -

https://community.yellowfinbi.com/topic/editing-of-views-is-very-slow

Thanks,

Eric

photo
1

Hi Eric,


It's a very similar request indeed. Though my database doesn't have 124 thousand tables.


The solution he's suggesting would be fine I think, however, I would do it on a very different way.


My request is based on some databases who has a few tables, but with views listed, the result can be hundreds or thousands tables only taking system tables in mind. A simply user can be onerwhelmed by the large list of tables mentioned by Yellowfin and may be confused on a View Creation.

On the other side, we can have a view creation based only on views created to assist the users over a complex creation, by only choosing exactly what they need.

A simply change on the query used by Yellowfin would be fine. By mentioned this, if there's some trigger to set a return of Views, Tables or Both would be ok.

I hope this helps.

Thanks a lot for your very quickly response.

photo
1

Hi Renato,

Thanks for the additional info here, I've gone ahead and amended the developer task to include your request details, and added your organization as an affected client. Updates to the task will be provided here as they are available. I'll mark this as Idea Logged in the meantime, feel welcome to reply here with further related inquiries.

Thanks,

Eric