DW-- Long hours for minimum incremental data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: DW-- Long hours for minimum incremental data

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    DW-- Long hours for minimum incremental data

    Hi

    One of our client is having an DW environment, where
    300mb of weekly incremental data is applied every week to the
    Data warehousing environment.

    They do the below steps,
    Download the data which is in the form of Dump.
    ETL,(17 procedures are running) which is running for 35 hours.
    ( The experts of the project who were supporting this told
    the time taken is correct, but we doubt.)
    Cube building --RUnning for 12 hours.

    Every week they are doing the above procedure.

    Database -- Oracle 8i.

    Since We are new to data warehousing environment and we
    were asked to look into this, we need advices from experts
    about the job running for long hours. We think they are doing
    the entire rebuilding instead of applying the incremental data
    into the required format.

    I am sure that I will get immediate reply from Slimdave / Tamil..if they available.

    Thanks

  2. #2
    Join Date
    May 2005
    Posts
    17
    you should provide us with more info of the unput data, the programs
    being run, and their codes what jah think

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace the statements, see what is being done all you have said so far is that something is being done and it takes a long time

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Hi engiri,
    I think you are more concern to the whole process of warehousing
    the new data to the DW database and not a specific procedure
    that hampers the performance of loading the data.
    ---------------

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I have designed a DW setup before that cut the usual whole process of loading new data to the DW server, regenerate the cube to up to 400%.

    Here is what I did, create a dummy database that its only purpose is to load new data anytime as long as the new data(either dump, data from other db server, etc.) is available. After the new data are loaded, when DW DB server is available for use, retrieve all new data from the dummy server using dblink and then update the date range of the cubes(discover in my case at that time) for regenerating/updating the cube. And then truncate again all data from dummy server.
    So, users still enjoy the use of the DW server, while the dummy database uploads the new data. And I do it every night.
    ---------------

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I don't like to sound picky, but it seems like what you want is for someone else to do your consultancy engagement for you.

    So, maybe they are indeed doing a full rebuild instead of an incremental one. I guess the first step would be to actually find out.

    It takes tham 35 hours + 12 hours for cube rebuilds to load 300Mb of data? That's less than a single CD! What's their ETL tool, Quick BASIC?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Apr 2003
    Posts
    353
    Thanks slimdave.

    Started my analysis.(Oppurtunity for my to get into DW environment.)

    There is no ETL tool used.
    18 custom developed procedures are used during ETL operations.

    Let me trace the procedures, and anlyse where the problem is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width