Unable to see data in one column on analysis — Oracle Analytics

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

Unable to see data in one column on analysis

Received Response
21
Views
7
Comments
2973433
2973433 Rank 3 - Community Apprentice
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

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?

Answers

  • jprakash
    jprakash Rank 2 - Community Beginner

    Hi,

    Can you give the full query generated from BI Server?

    Thanks, JP

  • 2973433
    2973433 Rank 3 - Community Apprentice

    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) [[

]]
  • jprakash
    jprakash Rank 2 - Community Beginner

    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

  • 2973433
    2973433 Rank 3 - Community Apprentice

    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?

  • Simon.D
    Simon.D Rank 4 - Community Specialist

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

  • 2973433
    2973433 Rank 3 - Community Apprentice

    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.

  • 2973433
    2973433 Rank 3 - Community Apprentice

    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.