Dynamic table joins should account for calculated fields
Yellowfin tries to be pretty smart when it comes to generating SQL and that's a good thing. When you build a report off of a view, Yellowfin looks at what fields the report is using and only joins to the tables those fields belong to and excludes any "unused" tables in the view. This helps speed up the db query.
Unfortunately, this process does not account for calculated fields. When I create reports using calculated fields, Yellowfin does not check to see what fields those calculations are based on and include the necessary tables that house those fields. Consequentially, the report will not run unless I add non-calculated dimensions or metrics from each of the tables my calculation relies on, even if I have no desire to include any of those dimensions or metrics in my report. Sometimes I then have to artificially group those metrics/dimensions into a single group and hide the column on my report to get the data to aggregate correctly.
Furthermore, when you group a field into only one group, the SQL that Yellowfin generates is totally wonky:
CASE
WHEN 1 = 0 THEN CAST( "Schedules"."LDate" AS VARCHAR )
WHEN "Schedules"."LDate" IS NOT NULL THEN N'Group of all dates'
ELSE CAST( "Schedules"."LDate" AS VARCHAR )
END,1=0 is always false, so no need to include that, then you're only including non null dates in the group and if the date is null you're going to cast it as a varchar for some reason? (which still results in null). Maybe someone who knows SQL better than me can explain how this code is better than simply: N'Group of all dates',Long story short, Yellowfin should save the associations between calculated fields, the fields those calculations rely on, and the tables that house those fields so that when we include calculated fields on our reports, the necessary tables will be joined in the SQL so that our reports will run without extra tinkering.
I like this idea 
Hi Stephen,
I think the Mandatory switch in the view builder might be able to help you out here. If you turn it on then the table will always be included in the view. It will definitely help you include a table in a view (that you only need for a calculated field) without having to add unnecessary fields to the report.
I guess the only drawbacks would be if the report creator doesn't have the permission to edit the view, or if you don't want that table to be mandatory for hundreds of reports made off of the view but rather, just for one report.
Please let me know what you think about this idea.
regards,
David
Hi Stephen,
I think the Mandatory switch in the view builder might be able to help you out here. If you turn it on then the table will always be included in the view. It will definitely help you include a table in a view (that you only need for a calculated field) without having to add unnecessary fields to the report.
I guess the only drawbacks would be if the report creator doesn't have the permission to edit the view, or if you don't want that table to be mandatory for hundreds of reports made off of the view but rather, just for one report.
Please let me know what you think about this idea.
regards,
David
Hi David,
Thanks for the response. I did not know about the "Mandatory" switch. That is a helpful work around if I expect my calculated field to be used in most or all of the reports I generate from that view. However, it is still a work around and it does have the drawbacks you list.
The feature to have tables in a view included dynamically based on what each report calls for is a good feature and I'm glad Yellowfin includes it. I think extending that feature to also work for calculated fields is a logical next step and I think your team should take a look at it. Every time a user thinks, "I wonder if Yellowfin will let me do this" and the answer turns out to be yes, your product becomes better and better in their eyes.
Hi David,
Thanks for the response. I did not know about the "Mandatory" switch. That is a helpful work around if I expect my calculated field to be used in most or all of the reports I generate from that view. However, it is still a work around and it does have the drawbacks you list.
The feature to have tables in a view included dynamically based on what each report calls for is a good feature and I'm glad Yellowfin includes it. I think extending that feature to also work for calculated fields is a logical next step and I think your team should take a look at it. Every time a user thinks, "I wonder if Yellowfin will let me do this" and the answer turns out to be yes, your product becomes better and better in their eyes.
Hi Stephen,
yes I understand and agree with what you're saying, the "Mandatory" switch would only be helpful basically if you could predict the future! And so I have raised a new enhancement request (YFN-10879) for the Optimise View functionality to include calculated fields when it works out which tables are to be included in the view.
I think I remember a few years back a developer telling me that such a modification would be very difficult with the way the current architecture is, but then again, I might be wrong about that. Either way, the requested functionality would certainly be very useful indeed if it could be implemented, and that's why I've gone ahead and raised the request.
Thanks again for your input!
regards,
David
Hi Stephen,
yes I understand and agree with what you're saying, the "Mandatory" switch would only be helpful basically if you could predict the future! And so I have raised a new enhancement request (YFN-10879) for the Optimise View functionality to include calculated fields when it works out which tables are to be included in the view.
I think I remember a few years back a developer telling me that such a modification would be very difficult with the way the current architecture is, but then again, I might be wrong about that. Either way, the requested functionality would certainly be very useful indeed if it could be implemented, and that's why I've gone ahead and raised the request.
Thanks again for your input!
regards,
David
Replies have been locked on this page!