Data Clensing results in time out

@aftabp It can have different reasons, 

1.if are using a serverless azure database. It sometimes just drops the script and doesn't throw an error

2.Another one might be that your odx execution process are still running and the table is waiting for the transfer.

3.If you are using lookups on the table make sure you are not using the aggegration method none, since your table will not be indexed and if you have multiple lookups it might take a long time without the indexing. (preffered setup is maximum)

and you did write over the tables names, but in the mapping the orginal name is still visible ;)

@brett.parkin 

1. Yes, the server is AZURE Serverless DB.
2. This following job was not an execution job. I have manually triggered Execute on table in DSA. Since ODX table is Guarded (on execution and deployment), i am wondering what process is running on table in ODX? 
3. Yes, i have look-ups. How do i find out if there are aggrigations. I have looked at Advanced properties for the table and there are not aggrigates 

4. I have kept the tables names

Hi,

if you expand a Conditional lookup and expand the lookup fields, you can right-click those to see what aggregation Operator is assigned. Note that I would consider adding required indexes over using the Maximum operator depending on the circumstances.

You might want to investigate this "Warning: Null value is eliminated by an aggregate or other SET operation.” in your stacktrace. That is usually a sign that something is not going well in the SQL that is being run.

Jumping in on what rory is saying i did have a messages like this before if you did put an quotes on a native transformation for instace 
 

this looks like this in the option menu:
 

But it should be:

When searching for a string tx put's the quotes around the value automaticly 

Don't know if this is what's going wrong but it might be worth a look

@aftabp Did you already solve this problem? if you did would like to know the solution

Hi @aftabp please let us know if the issue is resolved, and if so please help us by marking a best answer above. If you have any follow up questions please let us know

@aftabp What was the solution in the end? Did you try all 3 the options provided?

Dear @Bernarddb 
I still have issues with data cleanising 

So i noticed that somehow Table_A in DSA was empty. Since Table_A in ODX is static i have set it to Guard. No issus there, right ?

There are 9,6 million rows in Table_A.

Total time is 5 min and 20 sec, but the job has been going on for almost 2 hours. Its still in DSA and MDW is yet to be run

@aftabp what type of transformations are you doing? Did you change all the lookup values to anything but none? If so you also need to deploy the related tables to get the indexing working. If you have other transformations do you use alot of nested case statements? What you can also do I manually index the lookup link fields in the table it's going to and the table it's coming from. 

But these are just things I can think of without seeing what you are exactly doing with your transformations. Or maybe by accident you did disable the truncate raw table before Transfer in the table settings?

If it isn't any of this we will need more information since there are alot of things that can be happening.

@Bernarddb 

• Lookup values have always been MAXIMUM
• There are no nested case-statements.
• Truncate raw table before Transfer in the table settings is not disabled

@aftabp  Are you running on Azure? If so can you check if the db is high on log io or that your cpu is maxing out? Log would be the purple line. If this is capping out you are probably running not enough cores or you should switch to hyperscale. If you don't see alot of activity you are probably running on serverles and you should consider using provisioned. If it's non of this we will need more information before we can help out. 

@Bernarddb 

I have just rerun the job and here are the stats from AZURE

Here is how it looks like in TX

Looks like the database is just going to sleep. If it doesn't spike back up. Is the db serverles?

DB is not sleeping. You can see the spikes after 2 PM

Yes, its serverless 

When servelles is spinning up more CPU's it sometimes drops the sql statement it was running. I don't know why. But put it on provisioned for a day and then rerun the table. It might be the fix