Summary

This article explains about How to work DSO Overwrite and Summation functionality.

Author Bio:

Kodanda Pani K.V is a SAP BI/BO consultant currently working with Rukshaya Emerging Technology Pvt Ltd (Bangalore/INDIA). I have 3+ years of experience in various BW/BI implementation / Support Projects.


Topic: DSO Overwrite and Summation function.

Data store object:

  • Overwrite function
  • Summation function
  • In-detail level data
  • All transactional entries are added (sum) or new record added as existing record using same primary keys with using “SUMMATION” option at transformation level.
  • Latest record or updated record Overwrite of existing record using “OVERWRITE” option at transformation level. Means last record was overwrite.
  • Activities Carried Out:-
  • OVERWRITE  functionality:-
  • In this scenario I have created 6 DSO’s different combination key fields.
  • I have chosen  50 entries and load to DSO model 
  • The “Overwrite” function is updated to latest record or new updated record, means last record was overwritten.
  1. S.NO DSO

KEY FIELDS

TRANSFERRED RECORDS

ADDED RECORDS

CR0SS CHECK WITH EXCEL

1

CC, SALES ORG, DIST CH

50

19

19

2

DIST CHANNEL, DIVISION

50

7

7

3

MATR, COMP CODE

50

15

15

4

PRO NAM, MAT CAT

50

7

7

5

TAKEN ALL EIGHT CHAR EXPECT QUANTITY AND AMOUNT

50

50

50

6

CC, SALES ORG, DIST CHA,DIVISION, MAT GR

50

37

37

7

SAL ORG, DIST CHA, DIVI

(HERE QUAN FILED AS “OVERWRITE” AND AMOUNT FIELDS AS “SUMMATION”)

50

19

19

  • SUMMATION functionality:-
  • In this scenario we have created 7 DSO’s different combination of key fields.
  • The “Summation” function is summing the all transactional entries or added the existing value. Means last record added as existing record.
  • At transformation level change the rule type option form Overwrite to Summation option.
  • The 7th DSO is created with Summation and Overwrite option (quantity filed as “overwrite” and amount field as “Summation”). 
  • Observations:-
  • OVERWRITE  function
  • If you want the customer latest transaction then we chose overwrite, where the overwrite functionality come into picture
  • In DSO model three tables 1) activation queue table 2) Active data table 3) change log table.
  • Activation queue: Used to save DSO data records that need to be updated, but that have not yet been activated. After activation, this data is deleted if all requests in the activation queue have been activated.
  • Change log: Contains the change history for the delta update from the DSO into other data targets i.e. DSO or Info Cubes
  • Overwrite is used when you want to overwrite the previous record with the new delta or changed record. when delta happens, data in DSO will be overwritten in active data table. But in change log table previous record will be represented as  minus values and new record as  plus values. Only the changes will be updated to the info provider (info cube). The overwritten values are stored in active table of DSO. Data in the DSO is updated overwritten data.
  • Change log table REDORDMODE function is N- New image and X- before image and space means Afterimage we observed the records.
  • OVERWRITE FUNCTION SCREEN SHOTS:-
  1. 1st DSO of Overwrite function.
  • Active data table for  1st DSO “OVERWRITE”
  • Here amount 813, 00. And quantity is 26,000.

  • Comparison with excel file of 1st DSO Data:-
  • In excel file amount 813, 00. And quantity is 26,000.

  1. 2nd DSO Active data table.
  • Here amount 240, 00. Quantity is 15,000.

  • Comparison with excel file of 2nd  DSO Data:-
  • In excel sheet of 2nd DSO quantity is 15,000 and amount is 2400, 00.

  • SUMMATION function:
  • Summation function used to all transactional entries is added. Means last record as added existing record.
  • In 7 the DSO we have taken as quantity filed is “OVERWRITE” and amount field is”SUMMATION”.
  • What we have done in these DSO quantity field is Overwrite option, the latest record is overwritten and amount filed is a Summation option adding the all transactional entries.
  • SUMMATION FUNCTION SCREEN SHOTS:-
  • EX: 3 rd DSO Summation function:-
  • Active data table of 1st DSO with summation function.
  • Here amount 900, 00. And quantity 24,000.

  • Comparison with excel file of  3rd  DSO Data:-
  • Here quantity is 24,000. and quantity is 900,00

  • EX: 1st  DSO Summation function:-
  • Here 1st DSO summation active data table data.
  • Here quantity is 19,000. And amount is 10, 350, 00.

  • Comparison with excel file of  1st  DSO Data:-
  • Here quantity is 19,000. And amount is 10350,00.

  • THIS  DSO” OVERWRITE” AND “SUMMATION” FUNCTION  TOGETHER:-
  • Here i done one DSO with “SUMMATION” and “OVERWRITE” functions together.
  • Amount is “SUMMATION” and quantity is “OVERWRITE”.
  • Summation value is added as existing record and Overwrite value is overwrite the latest value.
  • Summation value is 15, 000, 00. And Overwrite value is 23,000.

  • Comparison with excel file of   Summation and Overwrite function DSO Data:-
  • Here quantity field is taken Overwrite and amount field is Summation option.
  • In the excel file quantity value is 23,000. And amount value is 15000, 00.

  • Learning:-
  • In the scenario we learned how to impact in business environment with using DSO functionalists “OVERWRITE” and “SUMMATION”.
  • In “SUMMATION” function all the transactional entries are added.
  • Using two options (OVERWRITE and SUMMATION) in one DSO here also happened based on taking a rule details at transformation level aggregation option.

Hopes it will helps…..give me suggestions……


To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. CH Raman

    Hi Pani,

    Good content and use full one. Please try to add screen shot which are visible clearly. we are unable to see clear pic of attached images.

    Thanks

    (0) 
  2. Michael Rau

    Hi Kodandapani,

    Thanks for sharing your absolutely useful document. The “DSO Overwrite and summation” topic is always important in every BW data modeling project.

    The consequences each aggregation mode should be clear for everbody who works with DSOs – So, your document is very helpful.

    Regards,
    Michael

    (0) 
  3. Thomas Kejik

    Hi Kodanda,

    thanks for sharing! For me as a newbi @ BI it would be helpful to know where to change between “overwrite” and “summation”.

    br,

    Thomas

    (0) 
  4. Mohammed Naveed

    Hi Kodanda,

    You did a good job trying to figure out summation and overwrite options of DSO, however, the document could have been made more clearly with a little more effort.

    One more thing that I would like to add.

    Try to experiment with less records or less sampling data that would be very helpful.

    Good work. Keep exploring.

    Regards,

    Mohammed.

    (0) 
  5. dominic emmanuel

    Hi pani,

       That’s a great work, you are giving a spark to the knowledge base, keep up the good work as a newbie I am very much interested to learn new things…. looking forward for your next knowledge base sharing…..

    thanks

    Dominic

    (0) 

Leave a Reply