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

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

Received Response
1
Views
2
Comments
Pollocks01
Pollocks01 Rank 1 - Community Starter
edited Aug 13, 2024 6:09PM in Oracle Analytics Cloud and Server

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?

Answers

  • Pollocks01
    Pollocks01 Rank 1 - Community Starter

    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
    Pollocks01 Rank 1 - Community Starter

    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.