OBIEE12: Problem to create an (dense)analysis with a logical SQL

Question

Hi,

I need to create an analysis and I want to make a dense extraction to display all rows of a dimension. I could do that within the repository design but this decrease considerably the performance.

Ive written a logicial SQL that does the trick and this SQL works perfectly within the Issue SQL in administration panel!

But If I try to create a new analysis with I get this error:

Error generating view. Error getting cursor in GenerateHead

Error Details

Error Codes: OAMP2OPY:KNYNDVB7

Invalid SQL Identifier: (SELECT 0 s_0,......

I cant understand what Im doing wrong... how can I handle this behavior ?

here is how the SQL looks like:

SELECT  s_0,s_1, ..

FROM

(

SELECT

s_0, s_1, ...

FROM "Case Event"

WHERE

...conditions...

) A --sql I've copy-past from another analysis

RIGHT OUTER JOIN

(

SELECT

s_0, s_1

FROM "Case Event"

WHERE

...conditions...

) D --dimension I want dense

ON ( A.s_0 = D.s_0 AND A.s_1 = D.s_1  )

ORDER BY ...

FETCH FIRST 200001 ROWS ONLY

MichaelRm · Answer

Hello,

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" !

Thanks!

Probs · Answer

Hi,

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)

SELECT

s_0,

s_1

…

FROM

(

/* your custom sql */

) "CustomSQL"

MichaelRm · Answer

Hi,

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