For the sake of this question, this example is significantly reduced in scope, so in any response please understand the tables are actually much larger and more dimensions are involved.
Table 1 (Key_A, Key_B, Value_A, Value_B, Value_C)
Table 2 (Key_A, Key_B, Value_A, Value_F, Value_G)
Table 3 (Key_A, Key_B, Value_F, Value_M, Value_P)
We would like to use Denodo to bring these together so that we can provide:
View_X (Key_A, Key_B, Values...)
And then join View_X to dimensions on those keys. And use those dimensions to drive reporting.
At first glance, anyone would suggest "Union". And we tried that. We encountered this problem, and would help solving it: when we use a Union, the engine (both Denodo and database) behave by performing the Union first and then any keyed filters. In this case, those tables are MUCH larger than the example and Union performs horribly. However, when separated for the same query, each one performs very quick.
For sake of argument, let's say we want KEY_B above to be our Product Line dimension. And someone wants to report on Product_Line = 'Wheels'. When run in each table (single example: Table1.Key_B = Line_Dimension.Key AND Line_Dimension.Line_Name = 'Wheels') all 3 queries have their results in a matter of 10 seconds or so.
Obviously, we want the user to run just 1 query instead of 3 and manually adding results. However, when we wrap a UNION into that concept (example: View_X.Key_B = Line_Dimension.Key AND Line_Dimension.Line_Name = 'Wheels'), then the net result is a 30 minute wait. Because the database (and Denodo if we stop Union Delegation) does the Union work BEFORE using the common key to all 3 queries.
Does anyone have any advice on how to tune Denodo to change that?