You can translate the question and the replies:

UNION of Derived Views coming from a single query to the source data

I have a data source where due to it's hierarchical nature that I need to flatten it & output as a list (with hierarchy level no. being just another column) my approach is as follows. Say we have 3 levels of hierarchy, I would set the first view, view_l1 being the first list, add a hierarchy level no. column, & of course, from the source, the last column is an [Array]... My second view, view_l2 will be a flattened view_l1 where the output is everything of that level & I removed everything from the first level, add a hierarchy level no. column, & the last column again is an [Array]... My third view, view_l3 will be a flattened view_l2 ... and so on, no last array column of course. Then I UNION the 3 of them together to get a long list. Looking at the "Tree view", it seems each branch has it's own source, although it is from the same source. Looking at the execution trace, it does seem that each view does it's own query to get the source of data to reach it's own level. Is it possible to have the UNION where all the views are executed from only 1 query to the source? Or is there a better way than UNION? Many thanks in advance.
16-06-2015 03:12:47 -0400

2 Answers

I'm afraid that in this case an UNION of three flattened views would be the only way to go, since there's no other way to recover data from distinct hierarchy levels and make it look like a single level in only one query. However, there's a way to improve the process you described: if 'view_l1' is your first level flattened view, and you build 'view_l2' as a flatten on top of 'view_l1', then executing 'view_l2' will cause 2 flattening operations (and a 'view_l3' built this way will need 3 flatten operations). To avoid this, you can simply build 'view_l2' as a single flatten of the base view, by selecting the 'level 2 array' when creating the view, and then deleting the fields you don't need. This way, each level will only need a single flatten operation, regardless of the depth they're at. Finally, if your Web Service call is too costly, keep in mind that you can always cache either the base view or the whole UNION view. If you cache the base view, your UNION view will still execute 3 queries and 3 flattens, but subsequent queries over the UNION will go to the cache instead of the Web Service (and then execute the flattening), which is tipically much faster.
Denodo Team
19-06-2015 16:48:36 -0400
Yes, I suppose.. I shall be testing out the caching capabilities soon. Many thanks!
 Edited on: 28-07-2017 05:35:22 -0400
You must sign in to add an answer. If you do not have an account, you can register here