 | |
How often should data be extracted from OLTP to OLAP?
comp.databases.olap comp.databases.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
How often should data be extracted from OLTP to OLAP? -
07-31-2003
, 04:28 PM
#2
| |||
| |||
|
|
I was wondering how often should data be extracted from OLTPs to OLAP repositories? Should this process be automated or manual? |
How often you do it depends on how often data changes and what degree of
latency is acceptable in your business. Most people do it daily or weekly,
but some financial data only changes monthly or even less often. A very
small amount of data may need to be updated more than once a day, but the
business value of doing this rarely exceeds the technical complications --
so don't be carried away if some users insist that they must have up to the
second data without being able to explain why. Few analytical (as opposed to
operational) decisions are affected by the transactions that occurred in the
last five minutes.
Be warned, however, that several vendors are keen to sell expensive
"real-time" solutions, whether or not they are really needed.
Nigel Pendse
OLAP Solutions
http://www.olapreport.com
#3
| |||
| |||
|
|
It seems to me that users want to have data more and more frequent. However there are a few things to consider: - Data size. Very huge databases (TB's of data) cannot be updated on a very frequent basis, as such operations may take many hours. in these cases the databases needs a maybe half of a weekend for processing - and subsequently weekly or monthly basis is the best current technology can offer. - Transaction and or maintenence activity on the OLTP database. When does the production database have little or no activity, and when can we access the system in order to not disturb the daily activiteies. - Data delivery. In cases where you cannot read OLTP systems directly via SQL/ODBC/ etc - for example where data needs to be collected from several countries around on flat files - routines needs to be set up accordingly. - Can the DW/OLAP database be updated incrementally? If yes - it is often possible to update quite frequently even from quite large OLTP databases. - Reporting cycluses Several examples I been involved into - particularly with financial models/reporting require usually monthly data during the most part of the month - however during the reporting cyclus the database needs to be uppdated several times even at working-hours. In these cases the database usually updates automaticaly every night, but during the reporting-cyclus the database update can be manually run by power-users - or the database have an automatic refresh of data 2-4 times at daytime every day in the first week each month. - General rule: the database refresh itself every night with new data from the OLTP where there usually is little transaction activity. - Realtime-OLAP. Require quite simple data models to work OK, as these databases usually needs to be a ROLAP model in order to work properly. Realtime OLAP is quite diffecult to design - as this is both an analysis- and a tranaction-system at the dsame time. "Nigel Pendse" <nigelp.nospam (AT) compuserve (DOT) com> wrote "Helen Sharman" <nopsam (AT) hotmail (DOT) com> wrote in message news:lSfWa.29335$LD6.241686 (AT) news0 (DOT) telusplanet.net... I was wondering how often should data be extracted from OLTPs to OLAP repositories? Should this process be automated or manual? It should certainly be automated. How often you do it depends on how often data changes and what degree of latency is acceptable in your business. Most people do it daily or weekly, but some financial data only changes monthly or even less often. A very small amount of data may need to be updated more than once a day, but the business value of doing this rarely exceeds the technical complications -- so don't be carried away if some users insist that they must have up to the second data without being able to explain why. Few analytical (as opposed to operational) decisions are affected by the transactions that occurred in the last five minutes. Be warned, however, that several vendors are keen to sell expensive "real-time" solutions, whether or not they are really needed. Nigel Pendse OLAP Solutions http://www.olapreport.com |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.