7 Ответы Последний ответ: 08.01.2016 9:11, автор: 2973433

    Unable to see data in one column on analysis

    2973433

      Hello,

       

      I can't see a data in one column although I can see data in database,

      here is my scenrio:

       

      I'm creating analysis like this:

       

      As you can see I'm able to see data only in 2 first columns but not in last one.

      So I'm checking the query in logs and it looks like this:

      picutre.jpg

      from 
        DIM_ACCRUALPARTNER T2632,
        DIM_REDEMPTIONPARTNER T3052,
        FCT_TRANSACTIONSRELATIONS T4257 left outer join FCT_TRN_ACCRUALS T4318 On T4257.TDR_SRCTRANRESGID = T4318.TRA_TRANSACTIONGID,
        FCT_TRN_REDEMPTIONS T8838
      where ( T2632.APA_GID = T4318.TRA_ACCRUALPARTNERGID and T3052.RPA_GID = T8838.TRR_REDEMPTIONPARTNERGID and T4257.TDR_DESTTRANRESGID = T8838.TRR_TRANSACTIONGID ) ),
      

       

      Alright so I'm executing very similar query in my database:

      select apa_businessname, rpa_businessname, trr_m_quantityofpoints from 
           DIM_ACCRUALPARTNER ,
           DIM_REDEMPTIONPARTNER ,
           FCT_TRANSACTIONSRELATIONS  left outer join FCT_TRN_ACCRUALS  On TDR_SRCTRANRESGID = TRA_TRANSACTIONGID,
           FCT_TRN_REDEMPTIONS 
      where  ( APA_GID = TRA_ACCRUALPARTNERGID and RPA_GID = TRR_REDEMPTIONPARTNERGID and TDR_DESTTRANRESGID = TRR_TRANSACTIONGID ) 
      

       

       

      And I can see data in all three columns.

      So could you help me solve this issue, why I don't see the same data in OBI?

        • 1. Re: Unable to see data in one column on analysis
          jprakash

          Hi,

           

          Can you give the full query generated from BI Server?

           

           

          Thanks, JP

          • 2. Re: Unable to see data in one column on analysis
            2973433

            Yes of course:

            [2016-01-07T09:21:19.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a7,0:1:1:3] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] ############################################## [[

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

            86c84f6

            SET VARIABLE QUERY_SRC_CD='Report';SELECT

              0 s_0,

              "Finance"."Accrual partner"."Business name" s_1,

              "Finance"."Redemption partner"."Business name" s_2,

              "Finance"."Redemptions"."Quantity of redeemed points" s_3

            FROM "Finance"

            ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST

            FETCH FIRST 65001 ROWS ONLY

             

             

            ]]

            [2016-01-07T09:21:19.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a7,0:1:1:3] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- General Query Info: [[

            Repository: Star, Subject Area: LIVELO, Presentation: Finance

             

            ]]

            [2016-01-07T09:21:19.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a7,0:1:1:5] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Sending query to database named clmdb7 (id: <<22738>>), connection pool named Connection Pool, logical request hash 86c84f6, physical request hash b729a035: [[

            WITH

            SAWITH0 AS (select distinct T2632.APA_BUSINESSNAME as c1,

              T3052.RPA_BUSINESSNAME as c2,

              T3052.RPA_GID as c3,

              T2632.APA_GID as c4

            from

              DIM_ACCRUALPARTNER T2632,

              DIM_REDEMPTIONPARTNER T3052,

              FCT_TRANSACTIONSRELATIONS T4257 left outer join FCT_TRN_ACCRUALS T4318 On T4257.TDR_SRCTRANRESGID = T4318.TRA_TRANSACTIONGID,

              FCT_TRN_REDEMPTIONS T8838

            where ( T2632.APA_GID = T4318.TRA_ACCRUALPARTNERGID and T3052.RPA_GID = T8838.TRR_REDEMPTIONPARTNERGID and T4257.TDR_DESTTRANRESGID = T8838.TRR_TRANSACTIONGID ) ),

            SAWITH1 AS (select distinct 0 as c1,

              D1.c1 as c2,

              D1.c2 as c3,

              cast(NULL as DOUBLE PRECISION ) as c4,

              D1.c3 as c5,

              D1.c4 as c6

            from

              SAWITH0 D1)

            select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,

              D1.c2 as c2,

              D1.c3 as c3,

              D1.c4 as c4

            from

              SAWITH1 D1

            order by c1, c2, c3 ) D1 where rownum <= 65001

             

            ]]

            [2016-01-07T09:21:20.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a9,0:1:1] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Query Status: Successful Completion [[

             

            ]]

            [2016-01-07T09:21:20.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a9,0:1:1] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Rows 9, bytes 36360 retrieved from database query id: <<22738>> [[

             

            ]]

            [2016-01-07T09:21:20.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a9,0:1:1] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Physical query response time 0 (seconds), id <<22738>> [[

             

            ]]

            [2016-01-07T09:21:20.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a9,0:1:1] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[

             

            ]]

            [2016-01-07T09:21:20.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a9,0:1:1] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Rows returned to Client 9 [[

             

            ]]

            [2016-01-07T09:21:20.000-02:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 3ebfd888e3312216:-672f59a9:151a16fd3ec:-8000-00000000001865a9,0:1:1] [tid: 1ab7b700] [requestid: 2c980008] [sessionid: 2c980000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 1, Response time 0, Compilation time 0 (seconds) [[

             

            ]]

            • 3. Re: Unable to see data in one column on analysis
              jprakash

              Hi,

               

              Your case is "Fact not joining all dimensions"

              Refer the below link kpi partners: When Fact Tables Do Not Join to All Dimension Tables

               

              Thanks, JP

              • 4. Re: Unable to see data in one column on analysis
                2973433

                I've seen this link before, however I'm not sure how to apply it in my case.

                My physical layer looks like this (all columns have connection):

                picture2.jpg

                The third column is from fct_trn_redemptions fact which joins to first partner directly and indirectly to another one so I don't really understand why are you saying that my fact is not join all dimensions.

                In all three facts I tried to set content level but I didn't help.

                by the way how do you know the reason? Which line indicates it?

                • 5. Re: Unable to see data in one column on analysis
                  Simon.D

                  Why do you have three fact tables joined together like that?

                  • 6. Re: Unable to see data in one column on analysis
                    2973433

                    well it's kinda complicated...Our databse is built in way where this the only one relation that work for both partners.

                    Anyway it works in databse so I guess it should work in OBI as well.

                    • 7. Re: Unable to see data in one column on analysis
                      2973433

                      So please tell me if I understood correctly,

                      In my case I should set content level in fct_trn_redemptions table (this is the table where third column comes from) as PartnerR Details?

                      Because if so, it didn't work.

                       

                      Anyway I would be greatful for any ideas how to solve my problem, I am really stuck with this.