Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Unanswered: OLTP and OLAP database

    Can someone explain in general what they mean? their concepts, and their designs? I know they have something to do with report designs rite?


    Just doing some homework for an interview which might ask the q.

    I'll reading from various links, but just want to know what u guys, in your words, would explain this ?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: OLTP and OLAP database

    In couple words:

    OLTP - database for registering data (transactions - new data, any updates). Usually OLTP databases keep data what could be changed (for instance - something was sold and can be returned during one month, after month sale is final).
    OLTP databases are normalized up to third normal form (for increasing speed of transactions).

    WAREHOUSE database keeps historical data about something (let say about final sales). Usually WAREHOUSE databases are very big.
    WAREHOUSE databases are denormalized (for increasing speed of reports).

    There is no OLAP database (at least it is not regular database). OLAP is a technology for getting any kind of reports for analysis historical data.
    OLAP can use OLTP or WAREHOUSE databases. It is possible to use OLAP from Microsoft or somebody else - but anybody can create special "OLAP" for his database (of course - question is about efficiency).

    I guess anybody will understand that you know something about databases after this answer.

    May be somebody else could add something or change my answer.

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    thanks for yer reply. I've look through the web and gettin to know more about this things.

    Although I have previously been in db designs, didn't really know there were such terms for such things
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    OLTP = On Line Transaction Processing
    OLAP = In Line Analysis Processing used to be DSS

    OLTP is set up as earlier stated and designed for fast transaction throughput.

    OLAP which does not exactly = Wharehouse is usualy denormalized to simplify queries and tables are usually heavily indexed.

    You can see whre the two designs can clash.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2003
    Posts
    27
    Sorry another question on this

    When it comes to OLTP and OLAP, usually data in summarized form will have to be transfered to the OLAP databases. In terms of performance, how will the transfer affect the current OLTP which may be running missing critical system and cannot afford to have their performance be degraded??
    What are the possible solutions to this?

    thx

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The most succeful systems I have seen had the OLTP data replicated to the OLAP environment and then summerized in the OLAP environment.
    This is in now way the only solution, just least impact on either system.

    Usually you can get by with doing most of the summerizing and denomalizing on the OLTP side and finish up on the OLAP side.

    Your companies resources and the nature of your OLTP and OLAP environment will dictate whee yo udo the work.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Aug 2003
    Posts
    27
    thanks
    already done that ... just wanted to find out if there's any other way round it =)

Posting Permissions

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