Have you tried to modify the metadata in your repository in order to create the outer joins that you require?
Yes, I have tried using this way : https://gerardnico.com/dat/obiee/obis/densification_repository
It works for this analysis but it downgrades considerably performance... a lot... I have a lot of other simple analysis that simply failed after looping while because there is not enough resources in tablespace TEMP... I guess that some thing is badly linked and that the "preserve" dimension involve too much cross join and so too much combination and rows... But Im new with that, so... maybe I was wrong somewhere!
Thats why I tried with logical SQL... my query works fine but not when I want to create an analysis! What is the best way to achieve this or correct this behavior?
Thanks a lot
You should really consider going via the RPD because of the obvious error you’ve received. There are a host of errors and issues you will face if you still plan to use the logical SQL specifically around maintaining and enhancing your report.
Try creating simple SQLs to achieve the densification and then model them into the RPD (using fact based vertical partitioning). OR if possible, realize it in the database itself using views, MVs, or Analytic Views.
However if you still want to go with this, try the following syntax:
(wrap your SQL in a sub-query)
/* your custom sql */
Thanks for your tips. I tried and unfortunately I got the same behavior.
So you may have right, I need to look at doing this in the backend or within the repository again! Im going to learn more about what is "fact based vertical partitioning" !