You can translate the question and the replies:

Unions and Dimensional Reporting

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. Consider: 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?
user
10-07-2015 16:43:14 -0400

2 Answers

Your scenario is a perfect candidate for a nested Join. Nested Joins work great when one of the results sets is much smaller than the other one. It will "pass" the IDs retrieved from the dimension table (in your example, the Key_B for the line with name "Wheels") to the facts larger table. This filter will now be pushed down to the different branches of the UNION view, so you should get the performance that you need. Also note that the nested loop is not one-by-one. It happens in blocks. For example, if there are multiple rows for the line named "wheels", Denodo will build a WHERE clause with multiple values (using IN or OR operators). An important consideration for nested Joins is that the small table must be on the left side, as that is the table that gets executed first.
Denodo Team
10-07-2015 19:44:32 -0400
Thank you! That has helped a lot!
user
13-07-2015 14:17:00 -0400
You must sign in to add an answer. If you do not have an account, you can register here