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:

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?

jprakash

Hi,

Can you give the full query generated from BI Server?

Thanks, JP

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

]]

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

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

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

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

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.

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.