Combining unions of multiple calc views all joined by same Table Function output - Is there a way?

[MattHarding]

Short question form:

Anyone have any suggestions on how to parameterise a calculation view so another calculation view can use it, passing the output of a table function as the values to be used for the Parameter (similar to how you do it with table data sources).

Long question form:

I have a table function that returns for the current user, their employee id and their direct reports employee id. This is obviously complex logic as it needs to recurse the org structure to find everyone. Doesn't take long, but you would hope this would be executed only once in any calculation view that leverages it.

Now for a Smart Business KPI tile, I'm attempting to understand how to do the following in the most performant way possible.

In short, I'd like the output to look like this:

Personnel Number, Full Name, Event Description, Date

e.g. This could look like this

123, Terry Jones, Upcoming Birthday, 23/9/2017

123, Terry Jones, Upcoming Anniversary Date, 2/1/2018

123, Terry Jones, Probation Period End, 2/8/2017

234, Astrid Levi, Birthday, 12/2/2018

etc...Note - Birthday, Anniversary Date and Probation period all come from different data sources; and upcoming birthday and upcoming anniversary dates fields plus the full names field are calculated fields.

Now the first way you might think to do this is to create 3 calculation views (1 for birthdays, 1 for anniversaries, 1 for probation period) that do this for all personnel; and then union it in a different calculation view and finally join with the table function. Unfortunately, from experience, the calculated fields get processed against all table entries prior to filtering on the join; hence will be incredibly slow.

To improve this significantly, I could slightly tweak the above and use the table function as a join in each calculation view, prior to doing the calculated fields; which will make it much, much faster, but is HANA smart enough to not run the Table Function multiple times? I think it is, but will need to profile it to know for sure. (This is what I plan to do)

Another option is to do the join without calculated fields, and then do the calculated fields for next birthday, and next anniversary date once you have the final result set. e.g. Let HANA do it's optimisation on the joins which it can do because you are doing the calculated fields at the end. This is not great as it means less reuse for the "next birthday" calculation view (for example).

What I would really prefer is to be able to create a parameter (or variable) in each calculation view which I can pass all the personnel to filter them up front. But from what I can tell, parameters based on data sources can only look at tables and not use Table Functions or output from Calculation Views?

This would logically be the quickest way it could run if this was feasible; and provide the most reuse.

Any suggestions?