Why some fisical tables&columns aren't available in some views for Freehand SQL calculated fields?

Joan Poblet ●

Hi again,

A bit more information: after making some tests with several views and tables we have realised that, for each view, it is only possible to use, in the Freehand Sql calculated field, the columns from the mandatory table. Are we right?

Is it the right working or it is a bug? In both cases, how could we bypass this situation to be able to use the Freehand Sql calculated field for all the view columns?

Many thanks for your help.

Best Regards,

Joan.

Reply URL

2

Guest ●

Hi Joan,

Thanks for getting in touch about this, apologies for the delayed response!

You nailed it after poking around a bit more. Freehand SQL queries require that any referenced table be marked as 'Mandatory' in the view in order for the query to successfully run. The error that the freehand builder returned was your Oracle database saying that it couldn't find the table you were asking it to, because the table was not marked as Mandatory in the view. Although it would be nice if Yellowfin were capable of giving a more helpful error message, this is indeed expected behavior, and is due to the way that the view system is structured.

Unfortunately, the only surefire solution that I see to this is to mark all of the tables as Mandatory in the view that you anticipate being used in Freehand SQL queries. It sounds like you already know where this setting is, but I've attached a screenshot just in case :)

dee847b6887e94c43b751c292af69bce

I'm sorry that I don't have a better workaround for you, but please let me know if you have any further questions on this.

Thanks!

-Conner

Reply URL

1

Joan Poblet ●

Hi Conner,

Many thanks for your explanation. If there isn't any other choice, we will have to think about setting the required table as mandatory.

Just in case: what we would like to do is to create a calculated field which would include a substring of the regular field. We thought there wasn't any other choice that using SQL functions. Because of that, we had to use Freehand SQL calculated fields. Do you know if there is any other way to create a substring of a field without using Freehand SQL calculated fields?

Many thanks for your help.

Best Regards,

Joan.

Reply URL

3

Guest ●

Hi Joan,

Apologies for the delayed response here!

There may be another solution to this, however, I would guess that it will result in overall lower performance than marking specific tables as mandatory. Yellowfin has an option at the view level called 'Optimize View'. You can read up a bit on it here, but the short version is that this option cuts unneeded tables out of queries at the report level. So while not using this option will not require you to mark particular tables as mandatory, you may experience some performance degradation as a result of the inclusion of all of the tables in the view.

768bd1100e48815ffc6db4ea25fccd88

In regards to creating a substring, I believe that you're correct. The only way to accomplish this will be to build a Freehand SQL calculated field. (Remember that the table you're using will have to marked as Mandatory.)

Hopefully this gives you a little more to go on. Please let me know if you have any further questions!

Thanks,

-Conner

Reply URL

1

Joan Poblet ●

Hi Conner,

Don't worry, it's ok. :)

Just a last question: what are the benefits/disadvantages of marking a table as Mandatory in comparison to unmarking the option 'Optimized view'?

Many thanks.

Best Regards,

Joan.

URL

2

Guest ●

Hi Joan,

Thanks for understanding! Sure thing, here's a brief rundown:

If you are using a view that does not have the 'Optimize View' option enabled, you won't have to worry about marking any tables as 'Mandatory'. This is because Yellowfin requests all of the database tables from the schema in the view. Therefore, when a calculated field is created in a report, the data is already guaranteed to be there. The big pro of using an 'non-optimized' view is that you never have to worry about marking things that you think a user might want to build a calculated field off of as Mandatory. The drawback however is that it is likely you will experience slower performance if you are trying to create a large/complicated report based off of an intricate schema with lots of tables. This is because Yellowfin is pulling everything in, whether it needs it or not. So, if your schema has only five tables with four columns each, the performance tax may be trivial. But with a schema with 30 tables and 15+ columns each, the results may be much different.

If you are using an optimized view, you will need to mark tables that you will want to use in calculated fields as Mandatory. This is because Yellowfin will exclude tables from report queries which it thinks are going to be unused. The result of this behavior is typically smaller queries that require less data to be passed around, and overall improved report performance. The con here is that, as you have learned, you will need to manually set certain tables to Mandatory in order for Yellowfin to be capable of creating report level calculated fields based on said tables.

Because everyone's environments are so different and diverse, we unfortunately don't have a reliable rule of thumb for when the costs of one option outweigh the other. It is up to each user to evaluate their instance and determine if the manual work of setting tables as mandatory offsets the additional computational cost of using a non-optimized view.

Hopefully I've answered your question, but please let me know if I've missed anything that you were looking for!

Thanks,

-Conner

Hi Joan,

Thanks for understanding! Sure thing, here's a brief rundown:

If you are using a view that does not have the 'Optimize View' option enabled, you won't have to worry about marking any tables as 'Mandatory'. This is because Yellowfin requests all of the database tables from the schema in the view. Therefore, when a calculated field is created in a report, the data is already guaranteed to be there. The big pro of using an 'non-optimized' view is that you never have to worry about marking things that you think a user might want to build a calculated field off of as Mandatory. The drawback however is that it is likely you will experience slower performance if you are trying to create a large/complicated report based off of an intricate schema with lots of tables. This is because Yellowfin is pulling everything in, whether it needs it or not. So, if your schema has only five tables with four columns each, the performance tax may be trivial. But with a schema with 30 tables and 15+ columns each, the results may be much different.

If you are using an optimized view, you will need to mark tables that you will want to use in calculated fields as Mandatory. This is because Yellowfin will exclude tables from report queries which it thinks are going to be unused. The result of this behavior is typically smaller queries that require less data to be passed around, and overall improved report performance. The con here is that, as you have learned, you will need to manually set certain tables to Mandatory in order for Yellowfin to be capable of creating report level calculated fields based on said tables.

Because everyone's environments are so different and diverse, we unfortunately don't have a reliable rule of thumb for when the costs of one option outweigh the other. It is up to each user to evaluate their instance and determine if the manual work of setting tables as mandatory offsets the additional computational cost of using a non-optimized view.

Hopefully I've answered your question, but please let me know if I've missed anything that you were looking for!

Thanks,

-Conner

URL

1

Joan Poblet ●

Hi Conner,

After reading your answer you validated what I thought of. Knowing this, we will analyse which option is better for us in each case.

You can close the ticket.

Many thanks for you good help!

Best Regards,

Joan.

URL

1

Guest ●

Hi Joan,

Excellent, glad that I could help! I'll go ahead and mark this one as completed, but you should be able to reference back to it whenever you like.

And as always, never hesitate to let us know if you have further questions.

Thanks!

-Conner

URL

Why some fisical tables&columns aren't available in some views for Freehand SQL calculated fields?

Replies (4)