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.
excellent post
ReplyDeletethank you
Good Excellent
ReplyDeleteExcellent post. Thanks a lot.
ReplyDeleteA great post, as clear as crystal.Thanks a bunch.
ReplyDeleteGood, nice post
ReplyDeleteThe code in example 1 above throws errors. Are you sure it's grmatically correct?
ReplyDeleteNevermind. My bad. Had a tick mark I couldn't see without my glasses on :)
ReplyDeletethanks guys
ReplyDelete