Object Based Query option to only get current records
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.
Best Answers
-
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.
0 -
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.
0
Answers
-
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.
0 -
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?
0 -
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?
0 -
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.
0 -
Refer to Infor Data Fabric User Guide
https://docs.infor.com/inforos/2025.x/en-us/useradminlib_cloud/default.html?helpcontent=datafabrug/cover.html
0