Selective Deletion in Infocube

[Former Member]

Hi,

Is selective deletion will be performance issue?

Our scenario is client want to see all data from sales (without billing), billing and delivery in one infocube...

Now, we are loading all the data in CUBE1.

CUBE1 contains:

- all billing data

- all sales w/o billing data

- all delivery w/o billing data

Now, since the sales w/o billing data is already uploaded in CUBE1. What if the sales w/o billing data has now billing data. This will cause duplicate in CUBE1. Now we are planning to have selective deletion to delete the sales data w/o billing IF it already has billing data uploaded in CUBE1. Is this possible?

If VBAP-SALESNO. (without billing) = VBRP-SALESNO. (with billing)

     DELETE VBAP-SALESNO. (wihtout billing) line item.

This is just our initial plan in mind, and of course, this will be done by an abaper. But we just wanted to check if this is possible enough to avoid duplciation.

This will be the same for delivery.

Thanks

[Former Member]

Hi All,

We have tried all possible solutions to avoid this data model but we cannot have it all in one line.

Our scenarios are below:

We want to combine sales, delivery and billing data.

Now we want billing qty if the sales order has a billing data.

If the sales order does not have billing data, we will need to get the delivery value for qty.

If the sales order has no delivery data and billing, we need the sales qty..

also they have special process for a certain customer.

If the specific customer has sales order but no billing value, it will look sales qty because it does not go to delivery process.

That is why we got this data model:

1. We load all billing data in the cube..

2. We load delivery data that do not have billing data.

3. We load sales data that do not have billing data.

Our problem is, when we load the next billing data, it will contain the billing of the previously sales data w/ no billing. This will duplicate the data. since we have loaded previously the sales that has no billing data, but now it has been billed.

That is the reason we need to have deletion in infocube that has already been stored.

Thanks.

[former_member214274]

Hi Anne,

It should work.

if you are deleting based on fact table of cube, it will have performance issues.

(Highly normalized structure)

Better do the same in write optimized DSO layer and then load to cube.

[former_member186399]

Hi Anne,

I would recommend you to change the modeling for this report. The cube may not support your particular case ,so make use of a DSO to consolidate your data first. Then you may move the data to the cube for reporting.

You can also use the rule group to reverse some particular data from your DSO.

Regards

Gajesh

[former_member186053]

Hi Anne,

You better explain how the sales,billing, and delivery data is loaded into cube. Are you using separate DSOs to load those data and finally to cube from all these three DSOs.

Thanks,

Vengal.

[Loed]

Hi Anne,

Are you going to use the logic above to incoming data only? How about the data already stored in CUBE?

I didn't try yet to do a lookup in the tables of CUBE..

Regards,

Loed

[former_member185132]

I don't believe this is possible using selective deletion. In sel.del you can provide some criteria for deletion. For instance, you could give the criteria as VBAP_SALESNO = <<some list of values>>. But you cannot give a criteria such as VBAP_SALESNO = SOME_OTHER_FIELD of the cube.

Can you explain your data model and requirement please.

[Former Member]

Hi Suhas Karnik,

We have tried all possible solutions to avoid this data model but we cannot have it all in one line.

Our scenarios are below:

We want to combine sales, delivery and billing data.

Now we want billing qty if the sales order has a billing data.

If the sales order does not have billing data, we will need to get the delivery value for qty.

If the sales order has no delivery data and billing, we need the sales qty..

also they have special process for a certain customer.

If the specific customer has sales order but no billing value, it will look sales qty because it does not go to delivery process.

That is why we got this data model:

1. We load all billing data in the cube..

2. We load delivery data that do not have billing data.

3. We load sales data that do not have billing data.

Our problem is, when we load the next billing data, it will contain the billing of the previously sales data w/ no billing. This will duplicate the data. since we have loaded previously the sales that has no billing data, but now it has been billed.

That is the reason we need to have deletion in infocube that has already been stored.

Thanks.

[former_member185132]

Hi,

That could be the requirement, but BW does not have the capability to sel. delete a cube when one field = another field. It can only delete based on a field = some_value or list_of_values.

So in your case, design it by creating a consolidation layer as suggested by Gajesh.

First, create a consolidation DSO. Add two flag infoobjects. One for "has billing data"  and another for "has delivery". I think the key should be only Sales Ord. Num.

Next, load from Sales -> consolidation DSO. Do not populate the two flags. In other words, leave those fields unmapped - do not assign even a constant to them in the transformation.

Load Billing -> cons. DSO. Here, populate the "has billing data" flag. Leave the other one unmapped.

Load Delivery -> cons DSO. Here, populate the "has delivery" flag. Leave the other one unmapped.

Now, the delta load to your cube should be just direct mappings, no logic.The rest of the logic should be in the report. In the Bex query, drag the Billing Qty kf and restrict it for the "has billing" flag = X.

Also drag the Delivery Qty kf and restrict it for "has billing" = blank

Also drag the Sales Qty and restrict it for "has billing" = blank and "has delivery" = blank

For the specific customer requirement, I did not fully understand it, but the above approach should handle the rest of the scenario.

[Former Member]

Suhas Karnik wrote:


Also drag the Sales Qty and restrict it for "has billing" = blank and "has delivery" = blank

Now, are issue here is what if the Sales Qty that has no billing from previous load, will now have a billing data. We should not count the sales qty uploaded before that has no billing data. We should just be considering the latest which is the sales has already billing.

[former_member185132]

That will not be a problem. Once the SO gets billed, the flag in the cons. DSO will get a "has billing" flag = X.

Once this record goes to the cube in the next load, in the Cube too that SO will have the billing flag. And then the sales qty is restricted on this flag in the report. So it should work correctly.

I would advise you to try it in dev with all your scenarios. As somebody on SCN without access to your system can only help to some extent and cannot solve the whole problem (which is indeed a little complex). You may need to tweak as per your scenarios and req, but this approach would work for you.

[Former Member]

Hi Anne,

Selective deletion will have no performance issue.

- all sales w/o billing data

It depends on the type of extractor you use (like AIM, NewD, etc ) . In most standard extractor will handle this issue and don't have to do a selective deletion.

Thanks,

Shakthi Raj Natarajan