Modify Cube Data

[Former Member]

Hi Experts,

Is it possible to modify specific data in a cube?

For example:

I want to modify the Div data from 6 to9 and div data from 5 to 6 for all the rows where PRD = 100.

i.e.

UPDATE table_name
SET Div = 9
WHERE PRD = 100 AND Div = 6

UPDATE table_name
SET Div = 6
WHERE PRD = 100 AND Div = 5

Current data:

RET	PRD	KEY	Div	Wnr	 Amnt
123	100	200	2	1	 $30,677.00 
124	100	200	3	2	 $2,038.00 
129	100	200	4	3	 $60.00 
131	100	200	5	25	 $100.00 
132	100	200	5	33	 $132.00 
133	100	200	6	1	 $-   

After modification of Div data from 6 to 9 and 5 to 6:

RET	PRD	KEY	Div	Wnr	 Amnt
123	100	200	2	1	 $30,677.00 
124	100	200	3	2	 $2,038.00 
125	100	200	3	2	 $2,038.00 
126	100	200	3	2	 $2,038.00 
127	100	200	4	2	 $40.00 
128	100	200	4	6	 $120.00 
129	100	200	4	3	 $60.00 
131	100	200	6	25	 $100.00 
132	100	200	6	33	 $132.00 
133	100	200	9	1	 $-   

Please let me know if you need any further clarification.

Thanks a lot

[former_member184494]

The data in the cube is stored in the fact and dimension tables - you cannot just update the tables directly because of the tight link between SID and DIM ID.

The easiest way is to possibly load a negative value for the records you want to remove to make it zero and load the correct value in.

Even if your cube is not fed by a flat file - the effort involved in updating the table , adjusting the indices , validating and reconciling the numbers is more than the effort to build a flat file interface for the same.

[Former Member]

Hi Bhuiyan,

it is quite possible with a self transformation on the same cube.

In your transformation, you should have 1:1 transformation for all the objects with Keyfigures multiplied by -1. This will make all the existing data 0. In the same transformation, you have to load same set of records with corrected division.

This will bring the data back with correct division.

You can follow this up with compression with zero elimination to reduce the number of records created by this data load.

Hope this helps?

Thanks,

Krishnan.

[Former Member]

You can push existing data in cube to temp area (e.g. another cube) and then

1. Load back to cube by applying code.

2. Add that cube to multiprovider if you are using MP for reporting

Aletrnatively, If business perits, relaod cube data

Edited by: Nikhil Saxena on Jul 6, 2011 10:34 AM