Wednesday, June 19, 2013

Loading Data to ASO Cube - How "Override" Option Works.


Data Load Properties in Rule file like Overwrite and Add to existing will not have any impact on data load for ASO cubes. where as  subtract from existing will work as expected.We have to mention the load type while importing data from the “Load Buffer” in MAXL statement ( "Load data" window if you are loading from EAS). Below are some examples to explain how these properties will work.


1) Override (Default): By Default, ASO will use “override” property. Data will over write only for the intersections we load.
          
Syntax:
import database 'AppName'.'DBname' data from data_file '/Folder/Newfile.txt' using server rules_file 'Test' to load_buffer    with buffer_id 1  on error write to 'Newfile.txt';

import database 'AppName'.'DBname' data from load_buffer with buffer_id 1;
                                                                (or)
import database 'AppName'.'DBname' data from load_buffer with buffer_id 1 override values;


 For example,
 Existing Data: Jan-> Sales = 10
                                               Feb-> Sales =20

                                  Loading: Feb-> Sales =30
                                               Mar-> Sales =40

                                After Load:Jan-> Sales =10
                                                Feb-> Sales =30
Mar-> Sales =40.

2)Override all data:   It works in a different way. Unlike override, this will replace whole data exist in the cube with the data file we load . Data cells will be cleared in the cube if you are not loading to those cells.
Syntax:
 import database 'AppName'.'DBname' data from load_buffer with buffer_id 1 override all data;


For example, 
                          Existing data:Jan -> Sales = 25
                                              Feb -> Sales = 569

                                 Loading:Jan->Sales = 34

                              After Load:Jan -> Sales = 34
                                              Feb -> Sales = #Mi


3) Override incremental data  Unlike above all the override options, override incremental data deals with slices of data. When you specify this option, data will be loaded to incremental slice. You will get sum of both primary slice and incremental slice when you pull the data.

Syntax:
import database 'AppName'.'DBname' data from load_buffer with buffer_id 1 override incremental data;

For examples, if we have data like below when are loading the first time
            
                        Existing data:Jan -> Sales = 25
                                            Feb -> Sales = 569
                                                 
                               Loading:Jan -> Sales = 34
                                           Mar -> Sales = 100

                           After Load:Jan -> Sales = 59 (25 Primary Slice + 34 Incremental Slice)
                                           Feb-> Sales = 569(Primary Slice)
                                           Mar - >Sales = 100 (Incr Slice)


When we are loading the same file with different data for the next time it looks like below.

                    Existing data:Jan -> Sales = 59  (25 Primary Slice + 34 Incremental Slice)
                                        Feb-> Sales = 569.
                                        Mar -> Sales = 100 (Incr Slice)
                           
                           Loading: Jan-> Sales = 5

                        After Load:Jan-> Sales = 30 (25 Primary Slice + 5 Incr Slice)
                                        Feb-> Sales = 569
                                        Mar -> Sales = #Mi

By looking at the above two examples we can conclude that, every time it will override all the data in incremental slice but it will not change primary slice. Data for Mar ->Sales set to #mi since the data was not loaded during second time.


The difference between “add values” and “override incremental data”  is, “add values” will always add the data to the primary slice where as the “override incremental data” will override the data within the incremental slice.

Author : Deepa.

8 comments:

  1. excellent post
    thank you

    ReplyDelete
  2. Excellent post. Thanks a lot.

    ReplyDelete
  3. A great post, as clear as crystal.Thanks a bunch.

    ReplyDelete
  4. The code in example 1 above throws errors. Are you sure it's grmatically correct?

    ReplyDelete
  5. Nevermind. My bad. Had a tick mark I couldn't see without my glasses on :)

    ReplyDelete