Object Based Query option to only get current records

Tina Douglass · 2025-01-15T20:22:41+00:00

There was an error rendering this rich post.

Hello,

Is there an option when doing a query based object in connect to only get the current records for an initial load…similar to if i query in compass?

We are loading a new fact table and there is no reason to bring in 100+M records to get the current record.

Find more posts tagged with

Accepted answers

Diane McElhiney

What you are stating is using the prior provisioned JDBC v1 connector. In Modeler/Connect, you use the Infor Data Lake connector (shown as the blue wave icon below). And, yes, in the QBO you must use date criteria for time-based (incremental) loading ALWAYS. When you specify the table in QBO within the Infor Data Lake connector when specifying the table name, Compass returns only the active current variation. There is syntax to retrieve all variations, include deleted rows, or only active. A query you construct in Data Fabric/Compass can be used within the Birst Infor Data Lake connector QBO.

Enrico Menotti

There are two connectors to Data Lake. The old one was more limited and only allowing the syntax you mentioned. To my understanding, it was using an API directly to Atlas. Old Analytics solutions (up to 2022) were using this approach and had to deal with variations in ADR scripts.

The new connector queries Compass and is much more powerful, allowing for a syntax similar to Microsoft Transact SQL (with some limitations, notably you can't do recursive queries). It retrieves by default the max variation only. You can still limit records by their indexing date on Atlas by using Infor.LastModified() (see Data Fabric docs), and will still include the max variation only (and no deleted records).

To include deleted: FROM Infor.IncludeDeleted('tablename')

To get all variations: FROM Infor.AllVariations('tablename')

(Please double check the syntax with Data Fabric docs.)

Enrico

PS Recent versions of the Analytics solutions use the new connector, and even some predefined views in Compass. I still have to move there, to be honest.

All comments

Diane McElhiney

In using the Infor Data Lake connector, the query you create in Compass can be used in a QBO in this connector. By just specifying the table name (i.e. FROM sasc), the result set is only the current active variation for each row in the result set.

Tina Douglass

Well that is interesting. In all the prebuilt stuff, they query the base table (aka FROM PROJS WHERE lastmodified BETWEEN start date and end date, and then in the publishing logic, they have the check for the most recent variation number. Or are you saying that if there is no lastmodified between, it is only the current records?

Diane McElhiney

Tina Douglass

Diane, one other question: We have high Birst Space Usage, so after i re-extract the table and publish my 'staging' table, i believe i can delete the stage table with a delete script/orchestration step; but do i need to do something to clean up where the data was 'extracted' to?

Diane McElhiney

https://community.developer.infor.com/discussion/comment/855#Comment_855

I would start with spaces that were created as backups or other copied to space which are no longer relevant and out-of-date.

You don't need to purge staging tables after an extract to save space. On the next Orchestration execution of an extract, the staging tables are truncated to allow for the newer extracted result set.

If there is historical data in measure tables which you feel are no long useful for analytics (i.e. data from 7 years ago) then there is an option for removal.

I'd rather have this type of discussion off-line. You can email me directly.

Enrico Menotti

To include deleted: FROM Infor.IncludeDeleted('tablename')

To get all variations: FROM Infor.AllVariations('tablename')

(Please double check the syntax with Data Fabric docs.)

Enrico

PS Recent versions of the Analytics solutions use the new connector, and even some predefined views in Compass. I still have to move there, to be honest.

Diane McElhiney

https://community.developer.infor.com/discussion/comment/854#Comment_854

Refer to Infor Data Fabric User Guide

https://docs.infor.com/inforos/2025.x/en-us/useradminlib_cloud/default.html?helpcontent=datafabrug/cover.html