Post reply

ETL Process

  • May 17, 2006 at 3:50 am

    #112242

    Hi all

     

    I’m working in DW project. I extract data from operational database, transform and load to DW, this ETL being processed every week. The operational database frequently inserts new data, update existing data and delete (cancel) existing data.  In this situation I am facing difficulty to find what data already extracted to DW, what data being update or Deleted during the week.

     

    Please help me, how can I solve this problem.

     

    Thank you

     

    Shifan

     


    shifan

  • May 17, 2006 at 9:53 am

    #638480

    From the sounds of it you need a relational Cube so that changes are reflected. Problem with that is I do not think you can do that once a week.

    If you want to do a MOLAP then what you can do depending on the record count is truncate the Fact and rebuild it weekly.

    Or if you are using MOLAP and you have a unique ID that can be pulled from the database then maybe this will help

    1) Source Table - create a trigger that puts the ID into another table with a status - 1) Delete, 2)Update

    2) Pull off that secondary table created to minipulate the DW Fact table.

  • May 18, 2006 at 6:25 am

    #638679

    If your source data is small (1 - 20 million records), then you can do full extractions nightly. Otherwise, you will have to find a way to identify data changes. For e.g. if there is an identity key on the source, you can use that to detect new records. If there are modified datetime fields, then you can use those to detect new and changed records. As far as deletesgo, if they are physical deletes, the only way to track those is to have a trigger that writes out the deleted row to some other table. For detecting inserts/updates, you can also create a timestamp (rowversioning) column which is very low impact and does not require special application rewrites assuming that the source is SQL Server

    Rushabh Mehta

    SQL Server MVP
    Solid Quality Learning

  • May 18, 2006 at 8:09 am

    #638734

    I had simillar issue with one of my project. I have used Proactive Cache with MOLAP to resolve this issue.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply