1 2 Previous Next 20 Replies Latest reply on May 31, 2018 11:35 AM by 1296495

    ODV not retrieving all records from OBIEE analysis

    1296495

      Dear all,

       

      I am trying to achieve an integration of ODV Desktop with Oracle BI EE. We have latest version of ODV and OBIEE version 11.1.1.9.0.

       

      I am able to create a dataset from a pre-build BI Analysis and I am able to retrieve records in ODV. The issue is that for some reason not all records of the analysis are retrieved into ODV.

      When I access the analysis from OBIEE I am able to downloan all the records of the report into a CSV file. But ODV is not retrieving all the records and I dont know why.

       

      I have increase all the parameters inside the instanceconfig file of OBIEE into big numbers.

       

      Can you please suggest for any reasons why is this think happening when connecting ODV with OBIEE?

       

      I tested the same query from ODS with direct database access and in that case it is retrieving all the records.

       

      Will appreciate your support.

       

      regards.

        • 1. Re: ODV not retrieving all records from OBIEE analysis
          Gianni Ceresa

          Hi,

          If you look in the log where you find the query sent to the source (OBIEE in your case), do you see a "LIMIT" clause inside the query?

          • 2. Re: ODV not retrieving all records from OBIEE analysis
            1296495

            Hi,

             

            I guess the log file to be checked is ...\OBIS\obis1-query.log.

             

            I have checked the log file and the query that is being generated but there is no "LIMIT" clause on the query.

             

            thank you.

            • 3. Re: ODV not retrieving all records from OBIEE analysis
              1296495

              Hi all,

               

              Can you please provide any feedback on the above, because I am still facing the same situation.

               

              thank you.

              • 4. Re: ODV not retrieving all records from OBIEE analysis
                Joel Acha

                and when you run the query that is in the obis1-query.log file directly on the database (TOAD,SQL Developer), do you get the correct results?

                • 5. Re: ODV not retrieving all records from OBIEE analysis
                  1296495

                  Hi,

                   

                   

                  Yes I am able to execute the query direclty on the database (with TOAD) and it is showing all the records.

                  Also the report that I have build in BI is displaying all the records and I am able to download all the records in CSV file from OBIEE web interface.

                  • 6. Re: ODV not retrieving all records from OBIEE analysis
                    Mark_Daynes

                    Hi,

                    Great pointers from the guys above - What I was wondering is about volume - is this a huge dataset? - can you tell us how many records are returned in BIEE and how many in DV.

                    Also, have you tried taking the data straight from the subject area - effectively creating a variant of the analysis in DV.

                    Mark

                    • 7. Re: ODV not retrieving all records from OBIEE analysis
                      1296495

                      Hi,

                       

                      In response to Mark_Daynes questions.

                       

                      I tried various ways to build the report and retrieve the data in DV ie. by creating a version of the report directly from subject area in DV, by creating the dataset based on an analysis already build in obiee. the nr of records retrieved is always the same.

                      In terms of number of records, the simple report I am currently trying to work with has:

                      -- in obiee it has 1,186,000 (all records are exported in CSV file from obiee)

                      -- while in DV it looks like it downloads only 1,100,001

                       

                      I tried to download the file in csv from DV by building a project but now I am getting the following error:

                      Exceeded configured maximum number of allowed input records. ResultRowLimit(table) OR CubeMaxRecords(Pivot/Chart) needs to be reset in the config file. Please check with your administrator.

                       

                      I found some config files on the path "C:\Users\<username>\AppData\Local\Temp\DVDesktop\config\fmwconfig" and incresed the limits there but it looks like they are getting replaced everytime I open DV desktop, so the situation is the same when downloading the file in csv. Is there any other path where I can file DV Desktop config file?

                       

                      .

                      • 8. Re: ODV not retrieving all records from OBIEE analysis
                        1296495

                        Dear all,

                         

                        Has anyone else faced the issue described above with DV desktop?

                         

                        thanks.

                        • 9. Re: ODV not retrieving all records from OBIEE analysis
                          Michael Verzijl

                          What is the setting of ResultRowLimit on OBIEE side? Please check instanceconfig.xml and report back

                          • 10. Re: ODV not retrieving all records from OBIEE analysis
                            1296495

                            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

                            • 11. Re: ODV not retrieving all records from OBIEE analysis
                              Michael Verzijl

                              Are you able to share the SQL generated within DVD?

                              • 12. Re: ODV not retrieving all records from OBIEE analysis
                                1296495

                                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.

                                • 13. Re: ODV not retrieving all records from OBIEE analysis
                                  Michael Verzijl

                                  Do you also have the corresponding query executed from the BI server?

                                  • 14. Re: ODV not retrieving all records from OBIEE analysis
                                    1296495

                                    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

                                     

                                     

                                    ]]

                                    1 2 Previous Next