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:
|
 |
| 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!
Alw
Always interesting to read a demonstration of new functionalities.
Nicely done.
P.S. : why can't I edit my own comment (%$£ù^=£ùm$m$ !!)
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 ?
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
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
Hi!
Scenario of loading data by additive delta, need for aggregation of data, planning option, inventory option with non-cumulative key figures....
BR, Maksim
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
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?
Thanks in advance,
Manos
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