I received a hint on OBIEE time series functions from my friend Peter S. Apparently the time series functions AGO and TO_DATE don’t work when you’re using it on tables with fragmented LTS.
A quick test yielded this:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22042] AGO function may not be used on ‘Amount’ because its source is horizontally partitioned. (HY000)
Checking metalink, I found document 739584.1 – AGO function with combined multiple sources. Enhancement request #7438154 has been raised and is targeted for 11g.
Cheers all!
Yes that's right, I hit this issue a while ago when demoing all the key features of the admin tool in one session – you can't use fragmentation and time-series functions in the same logical table (or measure). As you say, this is due to be fixed in 11g.
cheers, Mark