BusinessObjects Board

WorkFlow runs but does not complete

dataservices sap-erp-bw
#1

Hello all,
So I’ve been around BO for many years but have on recently ventured in to the Data Service space and I have come up with an issue that I do hope someone may help me with as I going bald by the minute tearing what hair I left in my head out.

So whats the issue. And I apologise now for the long note, but this may save you coming back and asking more quesions.

We are using DS 4.1 running on a Windows VM in Dev, System Test, UAT and Production.

I have create a Delta data flow to process CDC actions of Insert, Update and Delete.
The job does the usual stuff of getting all the extra information from support tables within a Query.
From that query, I split to three more queries, one for each of my CDC actions. After the Delete query, I do a Map Operation setting both Normal and Delete to Delete. This then goes of to the Target table and deletes the records.
For the Insert and Update actions, each does a little processing and then I filter these into a Merge operation. I then have another Query to sort the data on the Key. The next step is a Table Comparison which I check the box that the input may contain duplicate keys. From here its off to the Target table. No Key Generation is used as this is a Fact table and only our DIMs have keys.

So hopefully that sounded quite straight forward and it should work. Well it does in every envirionment except Production. At first I thought that maybe this was a data volume issue but its not as one of the jobs that uses this process failed last night having processed 1800 records where the night before it processed 12000 records and completed successfully. (I shouldn’t use the word failed because it actually hasn’t failed, it just hasn’t stopped).

So go back a day and I’ve made some changes to thefirst job that has not been completing.
What I’ve done is on the Insert and Update streams is to remove the Merge and Sort steps, put the Sort in the Insert and Update Queries, given each of these queries there own Table Comparison and also their own Target table.
I ran this yeasterday using Production as my input source and a copy of the Target table was put into my Development schema and I used this as the output.
Job ran in 9 minutes, happy days, put the change into Prodcution, restarted the job that had not completed and it worked perfectly. Took longer to run, but it worked.
Move forward to today. One WF earlier in the process which has worked perfectly since going to Production has now got the same problem. (The one I mentioned earlier with only updating 1800 rows). I thought fantastic, another one, but I have a fix for this. I’ve applied the fix, run the job in Dev and it completed in 4min.
Now here is the problem. I’ve gone and decided to test the WF that I fixed yesterday and it doesn’t work. I believe that it has processed every record it just doesn’t ever get to the completed status and its doing my head in. I feel much like this guy :hb: :hb: :hb: :hb:

Has anybody had this situation before, I am really lost, there are other Data Servies people that work for the company, but as this site has been a Data Stage site for many years, and there is very little quality expertise to ask.

I’m happy to look at any suggestions that you might have.

Thanks for taking the time to read this post.

plessiusa :netherlands: (BOB member since 2004-03-22)

#2

I am missing information :mrgreen:

First of all: a Dutch guy in Australia?

Okay and related to your problem:
What kind of database are you using?

I see some approaches:
Is there something like a lock on the db because there are multiple types of data going to the target table (insert/update). You have tested with a TC and seperate target tables, but maybe there is still an old process?

What SP and fix are you using? There were a lot of TC fixes for example, but I am not sure about the 4.1 stack.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

#3

Actually Dutch guy born in Haarlem grew up in New Zealand and now living in Australia. I get around :lol:

We are using Oracle as the DB.
I’ll be honest, I’m not sure if there are any other fix pack applied to the 4.1 release.
I’ve checked that there are no locks or long operations on the tables.

But with continued persistence and changes I have managed to get the job to run and this was by changing the TC to be Row-by-Row select and removing the Sort from the query.
What I did happen to notice is that now that I have a separate insert and update stream that I actually got a duplicate row in the DB. I already have a plan to fix that in the morning, hopefully the job will still run after that.

Thanks
Arjan

plessiusa :netherlands: (BOB member since 2004-03-22)

#4

I do not trust TC’s. There were some fixes around TC keeping running when there were no changes to process and we had more problems with them.

To check if the TC is causing problems implement this:
http://wiki.scn.sap.com/wiki/display/EIM/Autocorrect+Load+Pushdown+Example

And then specifically with a DT before your target table like this:
http://wiki.scn.sap.com/wiki/display/EIM/Putting+it+all+together

This ensures that you get a full MERGE INTO statement with the maximum amount of performance for Oracle (only delete/insert is faster).

If this works, then your TC was doing difficult.

This approach also solves having seperate update/insert operations on your target table.

It does not solve your duplicate key problem :wink:

Johannes Vink :netherlands: (BOB member since 2012-03-20)

#5

So I have resolved the issue, though I’m not really sure its the best solution, but it is working.

As I mentioned, I had a Sort process after the Merge and the TC had the box Sorted Input checked.
Turns out the Sort and Sorted Input were causing the issue.
As soon as removed the Sort query and changed to a Cached comparison, the job worked and exceeded my expectation in run times.

Apparently though using a Sorted Input is SAP’s preferred method, so I am now going to raise a case with SAP to discuss why this was causing my job to fail.

Thanks again
Arjan

plessiusa :netherlands: (BOB member since 2004-03-22)

#6

So it was the TC… sorting is much faster for bigger data sets, so it is important to get it up and running.

But too be honest, I stopped figuring out why it sometimes works and sometimes not. The allow merge on the target table works also, and even better with big data sets.

Draw back is that it is’nt as visible and not technology independent.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

#7

I would say check your primary key indexes AND make sure DS knows about the primary key. It sounds like DS might be blocking itself. I’ve seen that before…

Leigh Kennedy :australia: (BOB member since 2012-01-17)