Column not displaying any data

Charles M · October 2018

Hi All,

The development team was trying to create a dashboard, based on soemthing that shipped with OBIA. For one of the columns, we are not getting any data. I've starting to dig into the RPD and see what I can find out about this. So far, I can see some details from presentation layer on through the physical table. It does appear that the physical column contains data.

Trying to find some assistance to see where things are breaking down. I've taken some screenshots to help out with troubleshooting.

Here's an exceprt of the dashboard:

'Spend Year Ago' should have some data, we hoped.

OBIEE 11.1.1.9.3 (Bi Apps 11.1.1.10.1)

Regards,

Charles

Gianni Ceresa · October 2018

Hi,

Did you look at the physical query generated to see what could make the data not being retrieved?

Did you already saw that column working? What kind of formula does this column have? Is that using AGO? Is that a physical column in the DB? How is it mapped in the RPD?

Well ... more questions than answers

Charles M · October 2018

I can also see that pulling this column directly out of the subject area for an Analysis does not yield any results (whereas, some of the other Spend columns will return data on their own).

Charles M · October 2018

Well ... more questions than answers

Yes ... that's ok, we are only at the beginning . I will find these answers out for you (was checking to make sure there was data coming over from the ETL first; it looks like it, so we can focus on the warehouse side of things now).

Thank you, I will get back on these ...

Charles M · October 2018

Hi Gianni,

Looks like we are getting a cache hit on this. Here is the logical query:

SELECT
0 s_0,
"Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date" s_1,
"Procurement and Spend - Invoice Lines"."Supplier"."Supplier Name" s_2,
MONTH("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date") s_3,
MONTHNAME("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date") s_4,
YEAR("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date") s_5,
"Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."Spend Year Ago" s_6,
"Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."Spend" s_7,
REPORT_SUM("Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."Spend Year Ago" BY ) s_8,
REPORT_SUM("Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."Spend Year Ago" BY MONTH("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date"),MONTHNAME("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date"),YEAR("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date")) s_9,
REPORT_SUM("Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."Spend" BY ) s_10,
REPORT_SUM("Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."Spend" BY MONTH("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date"),MONTHNAME("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date"),YEAR("Procurement and Spend - Invoice Lines"."Invoice Details"."Invoice Receipt Date")) s_11
FROM "Procurement and Spend - Invoice Lines"
WHERE
((YEAR("Invoice Details"."Invoice Receipt Date") = 2018))
ORDER BY 1, 6 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY

-------------------- Cache Hit on query:

Did you already saw that column working?

I don't believe this has ever worked, as intended.

What kind of formula does this column have?

Is that using AGO?

Not seeing it from the front end

Is that a physical column in the DB?

Yes, I believe so. I could see data in it, based on what I traced it to in the RPD (according to the data lineage I have too).

How is it mapped in the RPD?

Starts here, in the BMM:

"Core"."Fact - Spend and AP Invoice Distribution"

Regards,

Charles

Charles M · October 2018

... probably should've included this too:

sum(case

when Dim_W_STATUS_D_AP_Invoice_Approval_Status.W_STATUS_CODE = 'APPROVED'

and Dim_W_XACT_TYPE_D_AP_Spend_Classification.INTEGRATION_ID <> '0' then

indexcol( case VALUEOF(NQ_SESSION.PREFERRED_CURRENCY) when 'Document Currency'

then 0 when 'Local Currency' then 1 when 'Global Currency 1' then 2 when

'Global Currency 2' then 3 when 'Global Currency 3' then 4 else 5 end ,

Fact_Agg_W_AP_INV_DIST_MONTH_A.INVOICE_DOC_AMT,

Fact_Agg_W_AP_INV_DIST_MONTH_A.INVOICE_LOC_AMT,

Fact_Agg_W_AP_INV_DIST_MONTH_A.GLOBAL1_INVOICE_AMT,

Fact_Agg_W_AP_INV_DIST_MONTH_A.GLOBAL2_INVOICE_AMT, Fact_Agg_W_AP_INV_DIST_MONTH_A.GLOBAL3_INVOICE_AMT,

cast(NULL as DOUBLE PRECISION ) ) else 0 end )

Charles M · October 2018

Hi Gianni,

I was able to disable the cache, and get the physical query

WITH

SAWITH0 AS (select sum(case when T2553670.W_STATUS_CODE = 'APPROVED' and T2553644.INTEGRATION_ID <> '0' then T2535762.INV_DIST_AMT else 0 end ) as c1,

case when T2565464.ROW_WID = 0 then NULL else T2565464.DAY_DT end as c2,

T2534878.NAME as c3,

TO_NUMBER(TO_CHAR(case when T2565464.ROW_WID = 0 then NULL else T2565464.DAY_DT end , 'MM'), '99') as c4,

RTRIM(TO_CHAR(case when T2565464.ROW_WID = 0 then NULL else T2565464.DAY_DT end , 'Mon')) as c5,

TO_NUMBER(TO_CHAR(case when T2565464.ROW_WID = 0 then NULL else T2565464.DAY_DT end , 'yyyy'), '9999') as c6

from

W_DAY_D T2565464 /* Dim_W_DAY_D_Invoice_Receipt */ ,

W_PARTY_D T2534878 /* Dim_W_PARTY_D_Supplier */ ,

W_AP_INV_DIST_F T2535762 /* Fact_W_AP_INV_DIST_F */ ,

W_XACT_TYPE_D T2553644 /* Dim_W_XACT_TYPE_D_AP_Spend_Classification */ ,

W_STATUS_D T2553670 /* Dim_W_STATUS_D_AP_Invoice_Approval_Status */

where ( T2534878.ROW_WID = T2535762.SUPPLIER_WID and T2534878.SUPPLIER_FLG = 'Y' and T2535762.INVOICE_RECEIVED_DT_WID = T2565464.ROW_WID and T2535762.DELETE_FLG = 'N' and T2535762.APPROVAL_STATUS_WID = T2553670.ROW_WID and T2535762.SPEND_TYPE_WID = T2553644.ROW_WID and TO_NUMBER(TO_CHAR(case when T2565464.ROW_WID = 0 then NULL else T2565464.DAY_DT end , 'yyyy'), '9999') = 2018 )

group by T2534878.NAME, case when T2565464.ROW_WID = 0 then NULL else T2565464.DAY_DT end )

select 0 as c1,

D1.c2 as c2,

D1.c3 as c3,

D1.c4 as c4,

D1.c5 as c5,

D1.c6 as c6,

cast(NULL as DOUBLE PRECISION ) as c7,

D1.c1 as c8,

0 as c9,

0 as c10,

0 as c11,

0 as c12

from

SAWITH0 D1

order by c6, c4, c5, c3, c2

Avinash Pentyala · October 2018

The data is available in DWH for last year?? you can create a report with Spend and Year columns and see it brings the years data.

Charles M · October 2018

Hi Avinash,

We should have the data there. Initial Extract Date for the ETL started in the end of 2016.

Regards,

Charles

Gianni Ceresa · October 2018

As Avinash said, you need to check if you have data for the previous year for the "Spend" column as the formula of "Spend Year Ago" uses the AGO function.

Being OBIA the RPD is a nice "mess", so that column is a nice CASE WHEN coming from a bunch of tables with inner joins between them and there are 2 possible LTS used.

So to find out where the issue is you don't have many other options than taking the SQL query and going back table by table to look if with the set conditions you are supposed to have some data or not.

Gianni Ceresa · October 2018

Charles M wrote:We should have the data there.

It's OBIA, what is expected is often not there in reality

Oracle Analytics Cloud and Server

Categories

Column not displaying any data

Answers