Hakoonamatata

 Profile Answers by Hakoonamatata 

• Jul 9th, 2007

 

As the term suggests, a real-time datawarehouse is a system which reflects all changes to its sources in real time. As simple as it sounds, this is still an area of active research in the field. There are a few vendors that claim to have a solution for the same, but I would still not out my money on them.

In traditional DWH, the operational system(s) are kept separate from the DWH for a good reason. The Operational systems are designed to accept inputs or changes to data regularly, hence have a good chance of being regularly queried. On the other hand a DWH is supposed to do just the opposite - it is used to query data for reports only. No changes to data, through user actions is expected (or designed). The only inputs could come from the ETL feed at stipulated times. The ETL wowuld source its data from the Operational systems just explained above.

To create a real-time DWH we would have to merge both systems (several ways are being explored), a concept that is against the reason of creating a DWH. Bigger chalanges occur in terms of updating aggregated data in facts at real time, still maintaining the surrogate keys. Besides, we would need lightening fast hardware to try this upon.

Near Real time DWH is a trade-off between the conventional design and the dream of all clients today. The frequency of ETL updates in higher in this case for e.g once in 2 hours. We can also analyse and use selective refreshes at shorter time intervals, while complete refreshes may still be kept further apart. Selective refreshes would look at only those tables that get updated regularly (e.g. railway reservation records), while complete refreshes may look at tables that et updated at longer time intervals ( List of reservation operators for security may change once a day).