OBIEE 12.2.1.2.0 Visual Analyzer - why is Keep Selected, Drill etc only sometimes available in conte

Pollocks01

I have a standard (not calculated) measures: Consumption (kWh). I have 2 standard (not calculated) attributes CUST_DISPLAY_NAME and SRVC_DISPLAY_NAME. The VA Analysis is on a Subject Area (rpd).

I have a calculated measure too: "Top 25 kWh" which is as follows:

TOPN(Consumption (kWh), 25)

I put Consumption (kWh) and Top 25 kWh onto X-Axis and SRVC_DISPLAY_NAME onto Y-Axis of a Horizontal Stacked chart. I get what I want which is to display the Top 25 services by Consumption kWh. The context menu contains all operations e.g. Drill, Keep Selected, Remove Selected, Sort, Zoom.

I take SRVC_DISPLAY_NAME off and add CUST_DISPLAY_NAME instead. The chart still displays what I want which is to display top 25 customers by Consumption (kWh). The context menu, however, only contains Sort, Zoom.

I see nothing in the JavaScript Console or in EM's Diagnostics Log Messages so I don't think that something's failing per se....

Both CUST_DISPLAY_NAME and SRVC_DISPLAY_NAME look the same in the BI Admin Tool (repository) - same data type, no special logic - just two different columns on two different tables.

What's going on here?

Pollocks01

Possibly related to error:

"[nQSError: 59031] A query cannot contain multiple instances of TopN or BottomN."

..which looks to be because - in some cases - VA tries to calculate multiple charts using a single SQL. Related to the error above, I see:

[2016-12-09T12:55:43-05:00] [OBIPS] [ERROR:31] [] [saw.httpserver.processrequest] [ecid: 8fc20c1f-2ade-4471-b1d0-a9419eaa38ce-00006160,0:1] [tid: 1987221248] [SI-Name: ] [IDD-Name: ] [IDD-GUID: ] [userId: prodney] Odbc driver returned an error (SQLExecDirectW).

State: HY000.  Code: 10058.  [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

(HY000)

State: HY000.  Code: 43113.  [nQSError: 43113] Message returned from OBIS.

(HY000)

State: HY000.  Code: 59031.  [nQSError: 59031] A query cannot contain multiple instances of TopN or BottomN. (HY000)

SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1; SELECT

   0 s_0,

   "Obfuscated Data Business Model"."02 - Customer"."CUST_DISPLAY_NAME" s_1,

   "Obfuscated Data Business Model"."02 - Service"."SRVC_DISPLAY_NAME" s_2,

   "Obfuscated Data Business Model"."01 - Core Measures"."Consumption (kWh)" s_3,

   TOPN("Obfuscated Data Business Model"."02 - Customer"."CUST_DISPLAY_NAME",25) s_4,

   TOPN("Obfuscated Data Business Model"."02 - Service"."SRVC_DISPLAY_NAME",25) s_5

FROM "Obfuscated Data Business Model"

WHERE

("Obfuscated Data Business Model"."Time"."Year" = '2016')

FETCH FIRST 5000001 ROWS ONLY

[[

File:odbcstatementimpl.cpp

Line:228

Location:

    saw.httpserver.processrequest

    saw.rpc.server.responder

    saw.rpc.server

    saw.rpc.server.handleConnection

    saw.rpc.server.dispatch

    saw.threadpool.socketrpcserver

    saw.threads

RSP: o:go~r:report

HttpCommand: GoJSONDataLayouts

SID: rmkm2ipeghgtkqsafn45tbefbm

AuthProps: AuthSchema=Impersonate|IMPERSONATE=prodney|NQ_SESSION.SERVICEINSTANCEKEY=ssi|PWD=******|UID=t0alechwsrv2o36pzqmg1bfiuxkdynj7|User=prodney|GUID=prodney

SessionID: .c.u0hlnoc

RptPath: 

RemoteIP: 

SIKey: ssi

]]

My VA canvas has 2 charts here: one for "Top 25 Services, Consumption (kWh) by SRVC_DISPLAY_NAME" and the other for "Top 25 Customer, Consumption (kWh) by CUST_DISPLAY_NAME". OBIEE is trying to be smart by consolidating the queries into one.

Working on a reproducible test case on the HR schema......

Pollocks01

Not able to replicate this using Live/Direct SQL against tables in the HR schemas making me think that perhaps this behaviour is Subject Area related...however it's certainly sporadic per the screenshots. Sometimes context menu is missing items on one chart and sometimes on the other.