Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE, KPI'S
I had issues with kpi comments in OBIEE but i have been able to resolved it.
Now when i login to the application as weblogic i can access kpi's with their values but when i login with another user and i open any kpi, it can not access the values. I have checked the connection pool in rpd and i can view data. Below was the message when trying to create analysis.
Any help or directions to resolve it will be appreciated.
 | No Results |
| The specified criteria didn't result in any data. |
Answers
-
Did you check the SQL generated when logged in with a normal user?
Any security enabled?
0 -
Sounds like row level security - have you checked your rpd to see if any filters are being applied to data for the subject area by groups / users?
0 -
As Michael says, examine the physical sql generated in the two cases, it will give you the best starter to resolve this.
0 -
Yes i checked. find it below:
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] ############################################## [[
-------------------- SQL Request, logical request hash:
2374b000
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/Network Projects Directorate/2017/MKPIs/% of Projects within Allowable Cost Variance';SELECT
0 s_0,
"ECG Scorecard"."D2 Times"."Cal Quarter Number" s_1,
"ECG Scorecard"."D2 Times"."Cal Year" s_2,
(((FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))-(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')))))*100)/(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')))) s_3,
((FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))-(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))) s_4,
CASE WHEN (FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))>(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')))*0.99) THEN 'OK' ELSE CASE WHEN (FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))>(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')))*0.5) THEN 'Warning' ELSE CASE WHEN (FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))<=(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')))*0.5) THEN 'Critical' ELSE ' ' END END END s_5,
FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))) s_6,
FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D06OB02K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))) s_7
FROM "ECG Scorecard"
ORDER BY 1, 3 ASC NULLS LAST, 2 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- General Query Info: [[
Repository: Star, Subject Area: ECG Scorecard, Presentation: ECG Scorecard
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Sending query to database named ORCL (id: <<9504>>), connection pool named Connection Pool, logical request hash 2374b000, physical request hash 288c0fff: [[
WITH
SAWITH0 AS (select avg(T6929.ACTUALS) as c1,
avg(T6929.TARGETS) as c2,
T152.CAL_QUARTER_NUMBER as c3,
T152.CAL_YEAR as c4
from
TIMES T152 /* Dim_Times */ ,
W_MEASUREMENTS_F T6929 /* F1_MEASUREMENTS */ ,
W_USERS_D T8402 /* Data Filter */
where ( T152.DATE_KEY = T6929.TIME_ID and T6929.M_ID = '17D06OB02K01' and T6929.OBJ_ID = T8402.OBJ_ID and T6929.FLAG = 'Y' and T8402.USER_ID = upper('cobeng') )
group by T152.CAL_QUARTER_NUMBER, T152.CAL_YEAR)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8 from ( select distinct 0 as c1,
D1.c3 as c2,
D1.c4 as c3,
(D1.c1 - D1.c2) * 100 / nullif( D1.c2, 0) as c4,
D1.c1 - D1.c2 as c5,
case when D1.c1 > D1.c2 * 0.99 then 'OK' else case when D1.c1 > D1.c2 * 0.5 then 'Warning' else case when D1.c1 <= D1.c2 * 0.5 then 'Critical' else ' ' end end end as c6,
D1.c1 as c7,
D1.c2 as c8
from
SAWITH0 D1
order by c3, c2 ) D1 where rownum <= 65001
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Query Status: Successful Completion [[
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Rows 0, bytes 0 retrieved from database query id: <<9504>> [[
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Physical query response time 0 (seconds), id <<9504>> [[
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Rows returned to Client 0 [[
]]
[2018-04-20T03:50:42.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-000000000000030b] [tid: 15a8] [requestid: 71fd0008] [sessionid: 71fd0000] [username: cobeng] -------------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds) [[
]]
0 -
Do you also have the weblogic SQL generated?
What is the user filter doing in above SQL?
what is the filter on T6929.M_ID = '17D06OB02K01' doing?
0 -
Hi,
this is your give away; -
W_USERS_D T8402 /* Data Filter */
where ( T152.DATE_KEY = T6929.TIME_ID and T6929.M_ID = '17D06OB02K01' and T6929.OBJ_ID = T8402.OBJ_ID and T6929.FLAG = 'Y' and T8402.USER_ID = upper('cobeng') )
0 -
This is for weblogic SQL generated
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] ############################################## [[
-------------------- SQL Request, logical request hash:
693a066a
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/CSD Directorate/2017/MKPI''s/% of Staff trained per Request';SELECT
0 s_0,
"ECG Scorecard"."D2 Times"."Cal Month Number" s_1,
"ECG Scorecard"."D2 Times"."Cal Quarter Number" s_2,
"ECG Scorecard"."D2 Times"."Cal Year" s_3,
(((FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))-(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')))*100)/(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')) s_4,
((FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))-(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))) s_5,
CASE WHEN (FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))>(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')*0.99) THEN 'OK' ELSE CASE WHEN (FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))>(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')*0.5) THEN 'Warning' ELSE CASE WHEN (FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))))<=(FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y')*0.5) THEN 'Critical' ELSE ' ' END END END s_6,
FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG ACTUALS" USING (("ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01') AND ("ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y'))) s_7,
FILTER("ECG Scorecard"."F1_MEASUREMENTS"."AVG TARGETS" USING "ECG Scorecard"."F1_MEASUREMENTS"."M_ID" ='17D03OB07K01' AND "ECG Scorecard"."F1_MEASUREMENTS"."FLAG" ='Y') s_8
FROM "ECG Scorecard"
ORDER BY 1, 4 ASC NULLS LAST, 3 ASC NULLS LAST, 2 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- General Query Info: [[
Repository: Star, Subject Area: ECG Scorecard, Presentation: ECG Scorecard
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Sending query to database named ORCL (id: <<11739>>), connection pool named Connection Pool, logical request hash 693a066a, physical request hash 6de04732: [[
WITH
SAWITH0 AS (select avg(T6929.ACTUALS) as c1,
avg(T6929.TARGETS) as c2,
T152.CAL_MONTH_NUMBER as c3,
T152.CAL_QUARTER_NUMBER as c4,
T152.CAL_YEAR as c5
from
TIMES T152 /* Dim_Times */ ,
W_MEASUREMENTS_F T6929 /* F1_MEASUREMENTS */ ,
W_USERS_D T8402 /* Data Filter */
where ( T152.DATE_KEY = T6929.TIME_ID and T6929.M_ID = '17D03OB07K01' and T6929.OBJ_ID = T8402.OBJ_ID and T6929.FLAG = 'Y' and T8402.USER_ID = upper('weblogic') )
group by T152.CAL_MONTH_NUMBER, T152.CAL_QUARTER_NUMBER, T152.CAL_YEAR)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9 from ( select distinct 0 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c5 as c4,
(D1.c1 - D1.c2) * 100 / nullif( D1.c2, 0) as c5,
D1.c1 - D1.c2 as c6,
case when D1.c1 > D1.c2 * 0.99 then 'OK' else case when D1.c1 > D1.c2 * 0.5 then 'Warning' else case when D1.c1 <= D1.c2 * 0.5 then 'Critical' else ' ' end end end as c7,
D1.c1 as c8,
D1.c2 as c9
from
SAWITH0 D1
order by c4, c3, c2 ) D1 where rownum <= 65001
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Query Status: Successful Completion [[
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Rows 4, bytes 384 retrieved from database query id: <<11739>> [[
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Physical query response time 0 (seconds), id <<11739>> [[
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Rows returned to Client 4 [[
]]
[2018-04-20T04:13:33.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 86e2aae11abc6a85:6b84a00a:162e28111e4:-8000-0000000000000449] [tid: 15c8] [requestid: 71ff0009] [sessionid: 71ff0000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds) [[
]]
0 -
Do you have any filtering in the analysis pointing towards the logged-in user?
Otherwise check you RPD for security enabled.
0 -
Sorry, meant to add, and caught the post button prematurely; -
Hi,
this is your give away; -
W_USERS_D T8402 /* Data Filter */
where ( T152.DATE_KEY = T6929.TIME_ID and T6929.M_ID = '17D06OB02K01' and T6929.OBJ_ID = T8402.OBJ_ID and T6929.FLAG = 'Y' and T8402.USER_ID = upper('cobeng') )
This definitely means that you have row level security in your rpd, have a look into =>
https://www.rittmanmead.com/blog/2007/05/obiee-and-row-level-security/ for further on this, and then trace through your rpd for the parallel set up that must be there...
0 -
No, i dont have any filter in the analysis. I also checked the RPD security using the manager >> identity and everything is fine.
0