Deleting records in OBIEE when they're deleted in Siebel

3025352

Hi all,

My company uses OBIEE to perform analytics from Siebel. It's come to my attention that when a record is deleted out of Siebel, whenever the ETL runs, it doesn't recognize to delete the record from the data warehouse/OBIEE and I'm still performing business intelligence from data that should not be in OBIEE. How would I go about removing the records that are deleted from Siebel in OBIEE?

Thank you!

Thomas Dodds

You need to determine whether or not the deleted records (from the source) are material to analysis or not ... they might not be for day-to-day operational/tactical reporting, but what about analytical/strategic reporting?  A deletion of  a dimensional record from the source HAS an impact on the warehouse star schema as you may/should have fact records associated via warehouse keys.

Your best bet is to record ALL data in the warehouse then process the deletions (at the source) as SOFT DELETES in the warehouse (ie:  delete_flg = 'Y'); then based on analytical/information requirement you filter for the delete_flg.  Physical deletions out of a warehouse 'after the fact' (no pun intended) WILL cause you trouble and thus are much more expensive in terms of support and maintenance costs (time and labor).

Lombo

Hello,

Are you using OBI Applications ?  or have you developed your own ETL…

If you are using OBI Applications I understand it will depend upon the version… For 7.9.6.4 you can check the OBI Applications documentation doc E35271-01,  4.19.4.1.5 About Delete Triggers

Antonio Lomboglia

***Moderator action (Timo): removed private contact data.***

De:

Enviada em: segunda-feira, 29 de fevereiro de 2016 12:19

Para: Antonio Lomboglia

Assunto: https://community.oracle.com/Business Intelligence Suite Enterprise Edition - Deleting records in OBIEE when they're deleted in Siebel https://community.oracle.com/m52it8-cgfx-85vgn

https://community.oracle.com/ORACLE<https://community.oracle.com>

Deleting records in OBIEE when they're deleted in Siebel

created by 3025352<https://community.oracle.com/people/3025352> in Business Intelligence Suite Enterprise Edition - View the full discussion<https://community.oracle.com/message/13710983#13710983>

3025352

I'm not sure what your asking. Can you clarify?

cesar.advincula.o

Hello,

It depends on what kind of ETL process you are using. If your use Oracle BI Apps ETL Process or you use Customized ETL Process:

1. Oracle BI Apps,

   Check what @Lombo says

2.Customized ETL, here are an example of your OLTP table,

Architecture references:  OLTP tables (Replicate tables) -> STAGE -> WAREHOUSE

delete

      from RA_CUSTOMER_TRX_ALL inv

      where inv.customer_trx_id  in

                                    (select a.customer_trx_id

                                     from RA_CUSTOMER_TRX_ALL a

                                         LEFT JOIN AR.RA_CUSTOMER_TRX_ALL@PROD12@SOURCE_LOCATION o

                                         ON o.customer_trx_id = a.customer_trx_id

                                     where o.customer_trx_id is null);

       commit;

Kind Regards,

Thomas Dodds

I highly advise avoiding an actual delete from the warehouse ... use soft-delete.

cesar.advincula.o

Hello, I know that soft delete if the more suitable to this requirements and I do agree with you, however, what happend if soft deleted have not implemented?

For that reason, i gave two solutions, I know delete in warehouse is not a best practice, but sometimes if there is not a solution, we have to do it, just we have to be sure which tables we have to delete.

Kind Regards,

Thomas Dodds

I think the OP is asking how to process source deletes in the warehouse ... so they are looking to implement a mechanism.

[Deleted User]

+1 it looks like a very basic question so both concepts might be new to him.

My 10 cents: soft deletes. Definitely soft deletes!

If the warehouse exists since a moment and no soft-deletes were captured its best to prepare and proper soft-delete mechanism and do a one-off comparison between source and DWH and implicitly mark all delta rows in the DWH as deleted with a specific timestamp so they can be identfied as "deleted between brith of DWH and beginning of soft-delete tracking".

3025352

thank you for your reply. I've decided to run a FULL load ETL weekly so OBI will be updated with the deleted records from Siebel.

[Deleted User]

???

You're killing your historization and killing everything that's been deleted? In other words: You're basically killing the whole point of a DWH?