Fusion Applications OTBI/Essbase : General Ledger - Balances Real Time fails to Query The Essbase Cube, instead it queries the transactional database FSCM_OLTP (Doc ID 1965181.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Fusion Application Toolkit - Version 11.1.8.0.0 and later
Oracle Fusion General Ledger Cloud Service - Version 11.1.8.0.0 and later
Information in this document applies to any platform.

Symptoms

Fusion Financials 11.1.8 OTBI Essbase - analysis queries the transactional db instead of Essbase
OnPremise

Unexpected Behavior
-----------------------------------
Subject Area : General Ledger - Balances Real Time should query Essbase GL cube


The FA financial Essbase cube has been created and populated according based on the OTBI documentation and Essbase can be accessed via SmartView.
However while trying to create some analysis in OTBI (SA "General Ledger - Balances Real Time"), Essbase is not queried even if present in the RPD.

DB SQL seen in in the nqquery.log

Sending query to database named FSCM_OLTP (id: <>), connection pool named Connection Pool, logical request hash fd3c65e2, physical request hash aedd55f5: [[
WITH
SAWITH0 AS (select T288013.C64190816 as c1,
     T288013.C130160118 as c2,
     T288013.C197594221 as c3,
     T288013.C105329137 as c4
from

     (SELECT V518783208.FISCAL_PERIOD_NAME AS C64190816,        
V518783208.FISCAL_QUARTER_NUMBER AS C130160118,        
V518783208.FISCAL_YEAR_NUMBER AS C197594221,        
V518783208.FISCAL_PERIOD_NUMBER AS C105329137,        
V518783208.REPORT_DATE AS PKA_ReportDate0,        
V518783208.FISCAL_PERIOD_SET_NAME AS PKA_FiscalPeriodSetName0,        
V518783208.FISCAL_PERIOD_TYPE AS PKA_FiscalPeriodType0 FROM
GL_FISCAL_DAY_V V518783208 WHERE ( ( (V518783208.FISCAL_PERIOD_NAME =
'10-15' ) ) )) T288013),
SAWITH1 AS (select D1.c1 as c1,
     D1.c2 * 100 + D1.c3 * 10000 +

Steps
-------------
1. Set UP Essbase for OTBI per the OTBI Admin Guide
2. Execute the following OTBI analysis:

"General Ledger - Balances Real Time"."Time"."Fiscal Period" s_1,
  SORTKEY("General Ledger - Balances Real Time"."Time"."Fiscal Period") s_2,
  "General Ledger - Balances Real Time"."- Balance"."Period Net Activity" s_3
FROM "General Ledger - Balances Real Time"
WHERE ("Time"."Fiscal Period" = '10-15')
ORDER BY 1, 3 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

later finding: Payables to Ledger Reconciliation Report (seeded) was also tested . The query does not return any data and does not query the Essbase cube
either.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms