Analytics Software

Hi all

I upgraded from 10g to 11g and now my Ago function seems to not be working but i have no idea why.


The data is basically coming from a table like so:

AcctNbr EffDate Balance

12345 01/01/2010 $200
12345 01/02/2010 $205
12345 01/01/2011 $206
12345 01/02/2011 $207

So you can see that I have a balance amount for each day of the year.

So What I am trying to do is get the Year Ago amount.

I have this table joined to a basic time dimension table. Year > Quarter > Month > Week > Day.

In 10g I created a logical column using this and it worked fine:

AGO("LDW"."FA_ACCTBAL"."NOTEBAL", "LDW"."DimTime"."Year", 1)

In 11g I get no results. The log file says it my YAGO Measure is being converted to a null. (converted to NULL because its grain is below query's grain.)

SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6 FROM (
SELECT
0 s_0,
"LDW"."FA_ACCTBAL"."EFFDATE" s_1,
CASE WHEN "LDW"."FA_ACCTBAL"."MAJOR" ='CK' THEN 'Chequing'
WHEN "FA_ACCTBAL"."MAJOR" ='TD' THEN 'Term Deposit'
WHEN "FA_ACCTBAL"."MAJOR" ='SAV' THEN 'Savings'
WHEN "FA_ACCTBAL"."MAJOR" ='MTG' THEN 'Mortgage'
WHEN "FA_ACCTBAL"."MAJOR" ='CNS' THEN 'Consumer'
WHEN "FA_ACCTBAL"."MAJOR" ='CML' THEN 'Commercial' END s_2,
"LDW"."FA_ACCTBAL"."% Chg YAGO Notebal" s_3,
"LDW"."FA_ACCTBAL"."Chg YAGO Notebal" s_4,
"LDW"."FA_ACCTBAL"."NOTEBAL" s_5,
"LDW"."FA_ACCTBAL"."YAGO Notebal" s_6
FROM "LDW"
WHERE
((FA_ACCTBAL.MAJOR IN ('CML', 'CNS', 'MTG')) AND (((FA_ACCTBAL.EFFDATE IN (current_date-1)) OR (FA_ACCTBAL.EFFDATE IN (current_date-366)))))
) djm ORDER BY 1, 3 ASC NULLS LAST


]]
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-23] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- General Query Info: [[


]]
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-52] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- Expression 'Ago(NOTEBAL:[DAggr(FA_ACCTBAL.NOTEBAL)], [Level Year], 1)' converted to NULL because its grain is below query's grain.
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-52] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- Expression 'Ago(NOTEBAL:[DAggr(FA_ACCTBAL.NOTEBAL)], [Level Year], 1)' converted to NULL because its grain is below query's grain.
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-52] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- Expression 'Ago(NOTEBAL:[DAggr(FA_ACCTBAL.NOTEBAL)], [Level Year], 1)' converted to NULL because its grain is below query's grain.
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-52] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- Expression 'Ago(NOTEBAL:[DAggr(FA_ACCTBAL.NOTEBAL)], [Level Year], 1)' converted to NULL because its grain is below query's grain.
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-52] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- Expression 'Ago(NOTEBAL:[DAggr(FA_ACCTBAL.NOTEBAL)], [Level Year], 1)' converted to NULL because its grain is below query's grain.
[2011-03-23T21:51:55.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-52] [] [ecid: 0000Iv^gxcn99hWjLxMMOA1DX^y9000GDt] [tid: 1b6c] [requestid: 5df40012] [sessionid: 5df40000] [username: Administrator] -------------------- Expression 'Ago(NOTEBAL:[DAggr(FA_ACCTBAL.NOTEBAL)], [Level Year], 1)' converted to NULL because its grain is below query's grain.


Any idea what has happened here?