Skip to Content
Author's profile photo Maksim Alyapyshev

Using Advanced DSO with Snapshot Support Option

Hi!

In the post I will demonstrate use of Advanced DSO (aDSO) with Snapshot Support Option enabled. There is a description of Snapshot Option from documentation.

If your DataSource only allows full update, you can use the Snapshot Support flag to make sure that deleted requests are still updated. Upon activation, the system recognizes records that are in the active table but not in the load request. These are written to the change log as reverse images.

Make sure that all records are contained during every load, as data could otherwise be lost.

For demo I am using BW/4 SP04 system. This option was not appeared in BW/4, it was available is NW BW 7.5 already.

Demo Scenario

I am going to demonstrate functionality of this options.

For this purpose I created my own table similar to HR Personal Actions table (PA0001).

I created also:

  • Generic DataSource based the table
  • Advanced DSO with the same structure based on fields
  • Transformation (1:1) and DTP (with FULL) for data provisioning

Demo Functionality

1.Create demo records in HR Actions table, execute DTP, activate data and check aDSO tables.

Source Table and Inbound Table*

*had the same data before activation

Active Table (after activation)

Change Log (after activation)

2. Change records in HR Action table, execute DTP, activate data and check aDSO tables.

Change data in Source Table:

  • For employee 1 – change non-key fields
  • For employee 2 – delete record completely
  • For employee 3 – add new record
Active Table (after activation)
Change Log (after activation)

Snapshot Option functionality is working fine:

  • Active Table contain last snapshot data
  • Change Log Table contain both type of records:
    • reverse for deleted records (R)
    • before image(X) and after image (‘ ‘) for changed records.

Performance Test

1.HR Actions table was refilled with new dataset, that had about 1 Million number of records.

Initially we had 10 employees with 1 action each at 01.01.2010. Then I created and appended from this initial 10 records by increasing calendar day in future 1 Million records.

2. Data was loaded and activated.

3. Data in HR Action table changed, each 31.12.YYYY action was deleted.

4. Changed Data was loaded and activated.

5. All requests was activated successfully.

6. Loading process and activation process each performed in 3 sec. Seems for me normal result at test system.

7. Change log had 2700 records for second request with record mode ‘R’.

Summary

In the post I demonstrated functional and performance part of aDSO Snapshot options.

I have to say one word about use-cases:

  • If you have only full option to load data, you could use this option and don’t case anymore about deleted records in source system.
  • I think it is better then full deletion and reloading of BW Models, because it could make a delta records for up-flow BW Models.

 

Thank you for attention!

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Frederic Cincet
      Frederic Cincet

      Alw

      Author's profile photo Frederic Cincet
      Frederic Cincet

      Always interesting to read a demonstration of new functionalities.

      Nicely done.

      P.S. : why can't I edit my own comment  (%$£ù^=£ùm$m$ !!)

      Author's profile photo Mihir L Kiri
      Mihir L Kiri

      Nice Article Maksim Alyapyshev. One question here : What if I have a pseudo delta routine in DTP (i.e. DTP which is full with routine). This generally used when we know that data previous to certain age does not change. Previously we used to to delete overlapping requests with same selection. Does the delete flag considers the selection of the full DTP or we have cannot use pseudo delta with such a setup ?

      Author's profile photo Maksim Alyapyshev
      Maksim Alyapyshev
      Blog Post Author

      Hi, Pseudo delta is not good idea, cause of records which will be filtered in DTP considered like deleted records. This solution for a huge amount of data seems not to be suitable.

      But...may be there are some workarounds like Semantic Groups ( for generation of similar aDSO with the same structure) and split loading into several flows with pseudo delta by time characteristics. If data in some flow could not be changed, just stop loading it.

      Or may be use use aDSO_1 with Snapshot Option with pseudo delta loading and aDSO_2 (without Snapshot Option) which accumulate no-modified records from aDSO_1 (before they will be deleted by loading aDSO_1. Need to setup initial pseudo delta loading of aDSO_1 and loading of aDSO_2 with a way that in work according to business rules of no-modiable time periods.

      BR, Maksim

      Author's profile photo D D
      D D
      Dear Maksim,

       

      Thanks for your blog.

      Using ADSO I still have one open point, in which business scenario, would I must use a ADSO type Cube rather than an ADSO type DSO ?

      Do we still need cube in our dataflow, as on SAP on HANA creating reports on DSO will no more have performance issue ?

      Maybe you owuld know ?

       

      Regards

      DD

      Author's profile photo Maksim Alyapyshev
      Maksim Alyapyshev
      Blog Post Author

      Hi!

      Scenario of loading data by additive delta, need for aggregation of data, planning option, inventory option with non-cumulative key figures....

      BR, Maksim

      Author's profile photo Morten Lindenfalk
      Morten Lindenfalk

      Hi Maksim,

      Thanks for sharing your knowledge.

      In my scenario I need to full load from source where data is only available in a certain time period, e.g. -36 months. I would like to keep the history in BW and with Snapshot Support enabled in ADSO, old months deleted in source will be removed in active table in BW.

      Any suggestions to solve this?

      Best regards,
      Morten

      Author's profile photo Emmanouil Kouvaritakis
      Emmanouil Kouvaritakis

      Dear Maksim,

       

      thanks for sharing but I have one question.

       

      The solution seems to work only if the ADSO gets data only from one Datasource (single source system). What if we have connected 10 different clients  under the same ADSO?

      • scenario: compounding data : Key on system id
      • scenario: 1 ERP instance with multiple clients or multiple systems.
      • Datasources are the same across clients.

       

      Thanks in advance,

      Manos

       

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Hi,

      The only solution seems to be integrating them into a standard combined ADSO before processing them to the snapshot-enabled one. Or making sure to process source system by source system to the next layer excluding deletion of other source systems (dynamic DTP filter).

      I would advise doing the reverse, though; having one snapshot enabled ADSO per source system in the acquisition layer and harmonizing into propagation layer after.

      KR,
      Sebastian