Database Software

Hi guys

I designed very simple test cube with one dimension (both MOLAP driven).

The dimension PRODUCT consists of three levels:
- Group
- Category
- Product_detail

PRODUCT_SRC table to load PRODUCT dimension:

PR_GROUP_NAME PR_GROUP_ID PR_CATEGORY_NAME PR_CATEGORY_ID PR_DETAIL_NAME PRODUCT_DETAIL_ID

dairy 1000 yoghurts 1000000 yoghurt_1 1000000000
dairy 1000 yoghurts 1000000 yoghurt_2 1000000001
dairy 1000 yoghurts 1000000 yoghurt_3 1000000002
candy 1001 cookies 1000001 cookies_1 1000000003
candy 1001 cookies 1000001 cookies_2 1000000004
candy 1001 cookies 1000001 cookies_3 1000000005
beverages 1002 juices 1000002 juice_1 1000000006
beverages 1002 mineral water 1000003 mineral_water_1 1000000007
beverage 1002 energy drink 1000004 energy_drink_1 1000000008


The cube SALES has one measure:
- Value_of_sales (sum aggr)

SALES_SRC table to load SALES cube:

VALUE PROD_ID ID

1236 1000000002 2
115 1000000006 3
1697 1000000005 4
12 1000000004 5
168 1000000008 6
1984 1000000005 7
9684 1000000004 8
84 1000000002 9
8 1000000007 10
498 1000000006 11
4894 1000000008 12
4984 1000000004 13
448 1000000003 14
4489 1000000004 15
13 1000000001 16
879 1000000004 17
896 1000000006 18
4646 1000000007 20

I created the dimension PRODUCT and a mapping which loaded the data into the dimension. It worked perfectly. The hierarchy was created as I expected.

Then I created cube SALES and a mapping which should load the data into the cube. It is very very simple mapping - there were just only two items on the canvas:

- SALES_SRC table
and
- SALES cube

and two lines:

- from SALES_SRC.VALUE to SALES.VALUE_OF_SALES
- from SALES_SRC.PROD_ID to SALES.PRODUCT_NAME

Then I deployed everything and ran mapping, which loaded cube. But in my opinion the cube was not populated in a proper way, because it was no aggregation performed at the lowest level of product hierarchy - there was only a value of the first occurence of certain product. I mean:

In SALES.SRC we have for instance:

VALUE PROD_ID ID

1236 1000000002 2
84 1000000002 9

For me the the value in the cube should be 1236 + 84 = 1320, but the value in the cube at PRODUCT_DETAIL_LEVEL for yoghurt_3 is only 1236 - first occurence of this product in SALES.SRC.


Why hasn't been the data aggregated at lowest level of PRODUCT dimension hierarchy - is it the way OWB does such things?

Should I manually aggregate the data before loading to cube (just to use Aggregator to aggregate the data at lowest level)? If yes - what about incremental loading of data to cube (the old value value is simply replaced by new one and not summed in the cube)

In data warehouse solutions of other vendors the cube in such situation is loaded as I expected here.

I really don't know what to do. I do really appreciate any help from you.

Thank you in advance

Peter