Oracle Analytics, Data Flow, is there a row limit?

 When working with Oracle Analytics we can use "Data Flow". This is end user oriented ELT tool. 

Usually the results are limited to 2 million rows, yet there is a case when the limitation doesn't kick in. This happens when all the sources and the target are in the same Database. In that case the entire process is done in the DB and is not subject to the limitations.

This is documented in Oracle Support: What Are The Data Row Limits and Join Limits For Data Flows in Oracle Analytics? (Doc ID 2789840.1) where this behavior is described as "function ship". 

For example, I will use my NY.Yellow table with over 50 million data rows.

When trying to load the DB table-based dataset into Dataset Storage using dataflow, I see the 2 million rows limit in the data flow log:

[nQSError: 60008] The query exceeded the maximum query governing rows 2000000 from the database ... 

When both source and target are in the same DB (and the target is Database Connection), I load successfully all the 50 million rows into a new dataset without issues. 

Oracle Analytics Server 2025, AKA 8.2 is available

Oracle has released this year OAS version, OAS 2025, sometimes named internally, OAS 8.2.

This is the official blog post about the release, it includes some new features too: https://blogs.oracle.com/analytics/post/announcing-the-general-availability-of-oracle-analytics-server-2025

The documentation is here.

The "what's new in OAS 2025" section is here.

While the download page is here, I recommend using the edelivery site here, if you don't want the complexity of searching the correct version of Weblogic.

Gianni Ceresa uploaded the docker version here: https://github.com/gianniceresa/docker-images/tree/master/OracleAnalyticsServer  

Since the OAS 2025 should be equivalent to OAC Jan 2025, you can also look at the list of OAC new features videos of March 2024 - January 2025 here. 

Seems we are ready...

 

Oracle Analytics Default date format in parameter

 A quick reminder to my self:

When creating a parameter and want to use it in filter / calculation, there must be a default. For date default this is the default format: 

DATE '2014-04-12'

For example: 

Val_Date = @parameter("P_DATE")(DATE '2014-04-12')

Last occurrence of sub-string in a string - The locate function secret part in Oracle Analytics

 I need to find the last occurrence of a specific character in a string (and using only the part that follows that character). For example in a string 'abc-de-fghi-jklm' I want to locate the last '-' and get the sub-string of 'jklm'. In my cases there is unknown number of '-' characters.

The basic function to locate a sub-string in a string is "Locate". 

While it seems Locate has only 2 parameters (the string we are searching and the full string), actually they are 3 parameters. The third one is optional: start position.

LOCATE(CharacterExpression, CharacterExpression [, StartPosition] ) 

The Start Position can accept negative values as well, meaning search from the end of the string.

So in my case Locate('-', 'abc-de-fghi-jklm', -1) is the solution.

To get the sub-string 'jklm' I will use 

SUBSTRING( 'abc-de-fghi-jklm'

                   FROM  Locate('-', 'abc-de-fghi-jklm', -1)+1)

The SUBSTRING function parameters are:

SUBSTRING(CharacterExpression FROM StartPosition [FOR length])