Support for Oracle analytical functions in Teiid ?

[masilamani]

Hello,

Is Teiid supports Oracle Analytical functions which should be push-down function to oracle data source?  I looked at the source function feature which seems to be only for scalar functions but does not support analytical functions as shown below. 

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )

Example:  ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY hiredate NULLS LAST) RNO

                 COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT

Thanks, Mani

[shawkins]

Teiid 7.5 added support for built-in analytical and windowed aggregates in general.  7.6 corrected a few issues, and 7.7 added a general concept of a user defined aggregate function.  So if you are starting with Teiid 7.7+, then you can use the built-in aggregates in a windowed manner and also the analytical function row_number, rank, dense_rank - see https://docs.jboss.org/author/display/TEIID/Expressions.  The Teiid 7.7 user defined aggregate support is through extension metadata on scalar functions - see https://docs.jboss.org/author/display/TEIID/DDL+Metadata Create Function options.  The extension metadata "aggregate" and "analytic" should allow you to create a custom analytic function.  It can even be supported in the engine - see https://docs.jboss.org/author/display/TEIID/Support+for+User-Defined+Functions+%28Non-Pushdown%29

If using Designer though, there is a caveat that the extension metadata file shipped even with the latest version is not up-to-date, so you aren't given a chance to manipulate these properties - https://issues.jboss.org/browse/TEIIDDES-1456 

[masilamani]

Hi Steve,

Thanks and it's very useful to know that this feature is included in Teiid.  We are using Teiid Designer 7.4.5 which is part of RedHat EDS 5.2 so i wonder whether Teiid 7.7 will be included in 5.3 release.   will take a look at it when we have Teiid 7.7 available in EDS.

Thanks, Mani

[rareddy]

Yes, Teiid 7.7 will be in the EDS 5.3.

[masilamani]

Thanks Ramesh.  Looks like EDS 5.3 / SOA-P 5.3 is already released so will look into oracle analytical functions.

[rareddy]

If you are working with EDS or Teiid 7.7, please note that some of the above links supplied above point to 8.x Teiid not 7.7 Teiid. So, those DDL based features are not available, however user aggregates are supported.

Ramesh..

[masilamani]

Ramesh,

Thanks for the info.

Thanks, Mani

[yapnel]

Hi

Can you advise if the analytical functions will be pushed down to source?

Nelson

[shawkins]

Yes, windowed functions are eligible for push-down.  I should correct above that user defined aggregate support starts in Teiid 8.0, not 7.7.  But you are able to define custom analytical functions using the same extensions as well.

Steve