Skip to main content

Hi there. I‘m having trouble with a three tables in my project. What they have in common is that they are tables that I created specifically (that is not with a mapping to ODX) and populate with a Custom Table Insert.

The problem seems very similar to this one: Data Clensing results in time out | Community (timextender.com)

For example, I deployed and executed one of my tables yesterday evening (not a large one, about 20.000 rows). And these are the results:

 

I’m not sure what the problem is but the tables are able to finish execution if the Custom Table Insert is set to valid, however then all my lookup fields (and fixed value fields) are null. If it’s set to raw, then the execution of the data cleansing rules seems to go on forever. 

In relation to the answers to the other question from @Bernarddb, then yes Operator for the lookup fields is normally always none for me (but I also don’t get exactly the same error): 

Some of the settings might be wrong perhaps: 

- Tables are not guarded on deploy/execute. 
- Physical valid table is enabled.
- Batch data cleansing is enabled. 
- All schemas are set to default except for Valid schema which is set to stg_d365.

This is on Version: 6745.1

 

@darrirafn I think i might have a clue what's going wrong.

So first of all you lookup operators shoudln't be none. This way TX doesn't index the fields that are being used for the relation. Second are you using Super natural keys? and so if you are using super natural keys are you using them as read from store? When you are using SNK's you shoulnd't use read from store, but join them to the table as a lookup.

But for the lookup problem set them all to maximum (this will work fine) Then redeploy the table your lookup is comming from (and also execute) and after that also execute your main table again.

Hi @darrirafn 

With lookup operator options, it is important that all the lookups in the table use the same one.

TOP is in my experience slow, so I would always use MAX, none is just a straight up equal join.

You can see how it is done in the Data Cleansing Procedure, if you change the operator the join statement will change in there.

One of my first tasks in this company was to figure out why a execution took way longer and I finally found that just one of the lookups used MAX instead of top and that is what caused the increase in time, but once all were set to MAX and not top, it actually was even faster than it was before the change.

The increase could also be related to the table insert, if you add it to valid instead of raw.

Thank you very much for your help @Thomas Lind and @Bernarddb! 

I changed my lookup fields to have Operator = Maximum and my tables were swiftly deployed and executed! 

Reply


Terms and ConditionsCookie settings