Insert new record even when Source has 0 records

[Former Member]

Hi All,

I am loading delta loads to a Write optimized DSO (Delta- both Infopackage and DTP). I need to insert a new record into the DSO based on the logic I have in Start routine and Field routine for some fields.

The record has to be inserted even if the loads pick 0 records from the source. I need to make use of derivations used in object routine for some fields for inserting this new record. Please help out a way to achieve this.

[Former Member · Accepted solution]

Hi,

Thanks for replying. To avoid creating of new object in system please let me know if the below will work,

Write a routine in one of the fields inside a DTP. In this routine create a structure similar to the Result Package of DSO. Populate the fields as per the logic, then append a record into the active table of DSO. This would add a record whether or not the source gets any data. I compromise the BW timestamp by this way but this value I can ok to consider the system time at the time of DTP run.

Please let me know if this will work?

[Former Member · Accepted solution]

Hi,

I am not sure if BI system permits you to do so. Consider below points before going ahead.

As the key in write optimized dso is Request GUID, Data Package, Data Record Number; what values are you going to populate in those fields.

I have never seen any one writing code to insert or delete records in active table of a DSO (except when direct update DSO is used).

If you write code to populate a record, you will not be able to track it based on requests or logs. you will not see coresponding request that loaded this data on the manage screen of DSO and can't be tracked based on requests.

In DTP filters most of the times developers are not given authorizations to modify(add or delete records) in DSO(or cubes or any standard tables) , generally it will be read authorization check the authorizations, see if you have permisions to modify active table in production environment.

I would suggest one more option -

Create a dummy record (with some unique value in a field, lets say a record for 1st Jan 1901) in the PSA from where you are loading data to the DSO.

create delta DTP with filter ( date > 1/1/1901) this is for normal hourly loads.

Every hour with the above delta load, trigger a full load (with filter date = 1/1/1901) that brings this record to the DSO.

Keep in mind that you should not delete this record from PSA when you are cleaning PSA periodically.

Even if you clean it, you have to mailtain it again manually.

Hope it helps.

Regards,

Raghu

[Former Member · Accepted solution]

yes you can try that option that is the only way to execute some coding logic if the no of recrods transferred is zero.

But you have to make sure that there is no data to be transferred for that particular load.

you can make use of this table RSREQDONE. Give your infopackage name and get the reqeust details. sort by date and time to get the latest request and see if the no of records(RSREQDONE-RECORDS) is zero, if yes then you can insert a dummy record into the active table of your write optimized DSO.

--- Thanks..

[Former Member]

Thanks Raghu.

[Former Member]

Hi,

Create a Work Area of type of Source_Package. Loop to populate this work area and append this work area to SourcE_package.

[Former Member]

Hi,

I am loading delta loads hourly from SAP source system into BW DSO. We publish data into downstream systems after processing in BW including details like system time of last data package processed in that load, derived values in object routine, etc. Some times delta loads pick 0 records but still downstream system wants to know these details by getting a single record from the DSO.

So the Requirement is this:

If 0 records are loaded from source then also DSO has to have 1 record with few fields populated based on logics.

If records are loaded from source then single record has to be added to the loaded records in the Write Optimized DSO. This new record should have the time of load of last Data Package.

I debugged and saw that routines in transformation dont get to execute. Where can I code? Please suggest.

[Former Member]

Hi,

Obviously you can't code when the routines are not getting executed(as there is no data from the source).

What i would do is what i was suggesting-

Create a flat file datasource with one record in PSA.

I will always do a full load from flat file datasource after every hourly load into DSO.

In the routine from flat file datasource i will populate the time stamp and load it into the DSO.

Hope it helps.

Regards,

Raghu

[Former Member]

Hi ,

Could you elaborate what you are trying to do? what are you trying to achieve by inserting a new record when there is no data coming from the source?

Create a flat file datasource insert a dummy record into the PSA and go for a full load from the flat file source to the DSO.

[Former Member]

Hi,

I have tried this but I could not see any routine getting executed when 0 record is loaded. It does not seem to even enter the start routine. Any other possibility?

[Former Member]

In the start routine, check if the source package has any lines, and if not then add a line. Alternatively, you can use an end routine and do it there.

Cheers

Henry