Due to storage and IO issues and due to more demanding analysis and reporting, the need of an Entreprise Dwh was critical.
Already 107 Dwh incremental flows has been implemented out of 50 client databases and the system is scalable to accept multi-threading and detailed logging mecanism, coupled with real-time supervision.
The DWH delivers the data for a large OLAP cube with monthly data partitions aligned with monthly olap partitions. Processing is incremental every hour.
It is based on large 2-node Sql server clusters, data compression and partitioning technics and SSIS with cozyroc components. Any ETL task performance is monitored and we can see in real time the extraction successes, failures and bottlenecks through the reporting portal. The system is optimized to use as best the OLTP engine and the IS engine performance.
Purges on client production databases are asynchronous and completely decoupled from the DWH server in order to perform better and without connectivity issues.