Hello Birst fans,
I am adding the MPDSUM to our Source Space and just want to import the non deleting rows, but doesn´t work.
Does anyone faced this?
@pedro-fernandes
If you are using the Infor Data Lake Shared Connection supplied by an Analytics Solution, you cannot use a complex WHERE statement. Filtering 'deleted' must be handled within a Scripted Source referencing the raw data in the Source Space.
The reason for no complex WHERE conditions is the Infor Data Lake connection is using JDBC v1 driver. However, we will be supporting the Compass driver in our 2022.10 Birst release (please note, the availability is subject to priority shifts and QA testing; as of this posting the Compass support is on schedule),
Does the Compass driver allow for complex WHERE conditions?
@lcallahan
Yes the Compass driver will allow for complex WHERE. Please note the even when the capability is released in a Birst version, the Analytic Solutions teams will need time to adopt within their solutions Modeler Connect. Yet, with the availability of this Compass connector in Modeler Connect, users can use it immediately.
NICE!
My intentions with that is to reduce the import time. I need to live with that until the that upgrade.
Thank you for your support
I want to clarify the date criteria in the Query Based Object (QBO) SELECT statement you had initially posted. Is that value hard-coded? If so, it is beneficial to create a variable and use the variable in the QBO for a more optimal incremental load. How the variable is populated with the appropriate date after each load consists of a Scripted Source which SELECTs the MAX(lastmodified) date from the staged extraction and stores it into a hierarchy along with any other extract dates you wish to track. The Cloudsuite Analytic Solutions for Birst already have the scripting in place.
Pedro,
While I understand that you are trying to reduce your extract time, I would not recommend not bringing in "deleted" records. What happens is then is an item that has been deleted after it has been brought into Birst will not be deleted from Birst, and you will have no way of knowing, in Birst, which records should be deleted. Instead, you should bring in all records, add the "deleted" column to each Birst hierarchy and as a measure in each fact table, and write "delete" scripts to actually delete any record from the dimensions and fact tables where deleted = true.
The M3 publish the MPDSUM (item structure) everyday. I need a script to load the entire table into a Atribute table. So I created a variable that calculate the "yesterday" datetime and add it into Modeler->Exctract script. In Modeler->Prepare I filter just the active rows (deleted='false') and load into an Atribute table.
Import -> 1.5M lines (2min)
Transform and Write -> 0.78M Lines (2min)
Not Bad
I record this table as an Atribute table, with no measure columns. My intention is to rewrite all active records into (deleted=false). I'm still working on that.
