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

ODV not retrieving all records from OBIEE analysis

Received Response
191
Views
17
Comments
2»

Answers

  • Engrid Braholli
    Engrid Braholli Rank 3 - Community Apprentice

    Hi Michael,

    On OBIEE side the ResultRowLimit has the following value:

    <ODBC>

    <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

    <ResultRowLimit>10000000</ResultRowLimit>

    </ODBC>

    thanks

  • Michael Verzijl
    Michael Verzijl Rank 6 - Analytics Lead

    Are you able to share the SQL generated within DVD?

  • Engrid Braholli
    Engrid Braholli Rank 3 - Community Apprentice

    HI,

    I see the below query being generated in the DV logs for BI:

    [2018-04-05T12:14:25.618+02:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 2b20] [messageid: USER-16] [requestid: fffe0005] [sessionid: fffe0000] [username: weblogic] -------------------- Execution plan: [[

    RqPopulate <<1085>> [for database 0:0,0] append  file

        RqList <<1110>> [for database 3023:43812:'weblogic'.'mybi_uat',87]

            T1000001.COLUMN1 as c1 [for database 3023:43812:'weblogic'.'uat',87],

            T1000001.COLUMN2 as c2 [for database 3023:43812:'weblogic'.'uat',87],

            T1000001.COLUMN3 as c3 [for database 3023:43812:'weblogic'.'uat',87],

            T1000001.COLUMN4 as c4 [for database 3023:43812:'weblogic'.'uat',87],

            T1000001.COLUMN4 as c5 [for database 3023:43812:'weblogic'.'uat',87]

        Child Nodes (RqJoinSpec): <<1126>> [for database 3023:43812:'weblogic'.'uat',87]

            RqJoinNode <<1125>> []

                [/shared/ODV Reporting/<analysis>] as T1000001

    ]]

    [2018-04-05T12:14:25.619+02:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 2b20] [messageid: USER-18] [requestid: fffe0005] [sessionid: fffe0000] [username: weblogic] -------------------- Sending query to database named 'weblogic'.'uat' (id: <<1110>>), connection pool named 'weblogic'.'uat', logical request hash a15a920a, physical request hash 9ee09528: [[

    <?xml version="1.0" encoding="UTF-8" ?>

    <Query  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" queryid="17460-23157" locale="en">

      <Select>

        <Projection>

          <Column  columnAlias="c1">

            <Expr  tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN1>]]></Name></Expr>

          </Column>

      

          <Column  columnAlias="c2">

            <Expr  tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN2>]]></Name></Expr>

          </Column>

      

          <Column  columnAlias="c3">

            <Expr  tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN3>]]></Name></Expr>

          </Column>

      

          <Column  columnAlias="c4">

            <Expr  tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN3>]]></Name></Expr>

          </Column>

      

          <Column  columnAlias="c5">

            <Expr  tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN4>]]></Name></Expr>

          </Column>

        </Projection>

        <From>

          <TableRef  joinType="Inner"><LeftTable idQuoteChar="" tableNo="1000001"><Catalog><![CDATA[]]></Catalog><Schema><![CDATA[]]></Schema><Name><![CDATA[/shared/ODV Reporting/<analysis_name>]]></Name></LeftTable>

        </TableRef>

      </From>

    </Select>

    </Query>

    *I have just masked the column names shown above.

  • Engrid Braholli
    Engrid Braholli Rank 3 - Community Apprentice

    Below is the query that is generate on BI server side:

    [2018-04-05T12:00:28.146+02:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: e80f8094b3c2d8a3:-18fc1428:1626207411d:-8000-000000000003601a,0:1:17:3] [tid: 11f8] [messageid: USER-0] [requestid: 4a3b0006] [sessionid: 4a3b0000] [username: biadmin] ############################################## [[

    -------------------- SQL Request, logical request hash:

    3af145af

    SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/ODV Reporting/ANALYSIS';SELECT

       0 s_0,

       "LD"."F07"."COLUMN1" s_1,

       "LD"."F07"."COLUMN2" s_2,

       "LD"."F07"."COLUMN3" s_3,

       "LD"."F07"."COLUMN4" s_4,

       "LD"."F07"."COLUMN5" s_5

    FROM "LD"

    WHERE

    ("F07"."COLUMN1" IN (400, 401, 402))

    ORDER BY 1, 2 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 3 ASC NULLS LAST, 6 ASC NULLS LAST

    FETCH FIRST 100000001 ROWS ONLY

    ]]

    [2018-04-05T12:00:28.177+02:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: e80f8094b3c2d8a3:-18fc1428:1626207411d:-8000-000000000003601a,0:1:17:3] [tid: 11f8] [messageid: USER-23] [requestid: 4a3b0006] [sessionid: 4a3b0000] [username: biadmin] -------------------- General Query Info: [[

    Repository: Star, Subject Area: LD, Presentation: LD

    ]]

    [2018-04-05T12:00:28.177+02:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: e80f8094b3c2d8a3:-18fc1428:1626207411d:-8000-000000000003601a,0:1:17:5] [tid: 11f8] [messageid: USER-18] [requestid: 4a3b0006] [sessionid: 4a3b0000] [username: biadmin] -------------------- Sending query to database named DW (id: <<966312>>), connection pool named Conn_DW, logical request hash 3af145af, physical request hash c907cfb9: [[

    select 0 as c1,

         D1.c5 as c2,

         D1.c4 as c3,

         D1.c3 as c4,

         D1.c2 as c5,

         D1.c1 as c6

    from

         (select T113269.COLUMN1 as c1,

                   T113269.COLUMN2 as c2,

                   T113269.COLUMN3 as c3,

                   T113269.COLUMN4 as c4,

                   T113269.COLUMN5 as c5

              from

                   LD.TABLE1 T113269 /* B02*/

              where  ( T113269.COLUMN2 in (400, 401, 402) )

         ) D1

    ]]

  • Engrid Braholli
    Engrid Braholli Rank 3 - Community Apprentice

    Yes, even when I was testing in the beginning I was getting less rows (like 65,000 rows). I increased some of the other OBIEE instanceconfig parameters to higher values and now its getting around 1M rows, but still not all of them.

    As mentioned in one of the above posts I found also an instanceconfig.xml file that ODV has on its folders, (C:\Users\<username>\AppData\Local\Temp\DVDesktop\logs\OBIS), which has similar parameters to the OBIEE config file. I updated the row numbers also on this file but this one looks like is getting replaced everytime ODV is restared.

    thanks

  • Michael Verzijl
    Michael Verzijl Rank 6 - Analytics Lead

    I have tested on my local system and seem to only get 55.000 rows returned. Need to investigate why this is happening.

  • Engrid Braholli
    Engrid Braholli Rank 3 - Community Apprentice

    Hi all,

    Does anyone have any update on this issue? Has anyone else been facing the same thing with ODV?

    thank you.