I am trying to create a Calc View to provide data for a report which for every slice of data should show some "this month" and "last month" data. My approach so far is to have a (graphical) Calc View which does Union of "this month" data (referred further as M) with basically the same data set (let's call it M1) where I only replace calendar month with another "calmonth1" column (I'm on SPS02 which doesn't have addmonth function yet, so had to make a Join with a month lookup table here).
That approach works mostly fine -- I get the data for this and last month, and performance is reasonable. Except when I specifically filter the testing SQL query by Calendar Month: then the runtime jumps from 0.5 sec to 9 seconds!
I have been able to narrow the issue down to the Union statement, where my two data sets have different fields (M.calmonth and M1.calmonth1) united in the resulting field U.calmonth. So, I guess, when I apply the filter to the field U.calmonth (effectively), the system cannot derive the corresponding values of M1.calmonth1 to cascade the filter down to dataset M, and tries to extract everything into M1, then does the Union with M, then selects what's right.
The same thing actually happens with a Projection, which indicates the issue is not in Uniting results from two data sets but exactly with deriving "reversed selections".
I went through HANA manuals, and the only related hint there was to have a JOIN ALL on key fields instead of a Union. I'm keeping it as a last resort, as I want to avoid scripting due to high maintenance effort.
I would be grateful to hear about alternative designs. Thanks!