Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
63
Views
4
Comments
MichaelRm
MichaelRm Rank 1 - Community Starter
edited Aug 13, 2024 5:40PM in Oracle Analytics Cloud and Server

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

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Have you tried to modify the metadata in your repository in order to create the outer joins that you require?

  • MichaelRm
    MichaelRm Rank 1 - Community Starter

    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

  • Probs
    Probs Rank 2 - Community Beginner

    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
    MichaelRm Rank 1 - Community Starter

    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!