OLTP and OLAP Architecture RRS feed

  • Question

  • Hi i was reading the article on http://msdn.microsoft.com/en-us/library/aa140038(office.10).aspx regarding creating an OLAP from your OLTP Database. And quiet frankly the article was awsome however i had a couple of questions. Now i know that both the OLTP and OLAP databases are separated from each othera and OLAP databases use cubes to produce reports. The article states that

    "Data cubes provide the final piece of the puzzle. A cube aggregates the facts in each level of each dimension in a given OLAP schema."  "That is the big advantage of a cube. You can ask any pertinent question and get an answer, usually at warp speed"

    Now my question is since the OLAP and OLTP databases are isolated then ... whenever a normal transaction occurs in the OLTP database does it also go ahead and update the OLAP database (wouldnt that consume some time) and wrt to the article the customers at the grocery store might run away....  Let me rephrase the question say the OLAP data is continuosuly being upgraded then when does the OLAP database gets synchronised with the OLTP database ??  


    A candle loses nothing by lighting another candle.
    Wednesday, November 3, 2010 11:29 AM

All replies

  • The usual approach is an overnight job.

    With many businesses, data which is correct up to yesterday is good enough.  It's used for reporting and analysis rather than real time comparison.  Complications like OOps I ordered 1111111111 instead of 11 are fixed.  In fact it's not unusual to have say your order data captured by a 3rd party package which stores data on a database server which you can only read from when it's not busy,. Maybe that's Oracle and other than that you use SQL server.

    Some businesses, however, don't want a day's lag.

    One intermediate approach is to run occasional batch jobs that pick up the latest hour or half hour's worth of transactions and copy those.  Maybe corrections are rare and insignificant.  If not you can add an entry to a exceptions table saying this otherwise "old" transaction has changed and needs updating in the cube.

    Some businesses log ship to a second server and run frequent updates from that.

    Very few places copy the data across as it's captured. 

    There are mixes of these.  Say you're a global business with several branches.  One sub business sells spoons, another aircraft.  Many spoons for pennies, few aircraft for millions.

    Wednesday, November 3, 2010 2:25 PM