Network Sizing Considerations - Database to BI Database Server
Communication between the BI database server and the ERP database is required during the nightly BI daily load.
The BI DWD Scheduler communicates requests to the ERP database server using an ODBC connection, and in return the ERP database server returns all required data to the BI database server. Outside of BI daily load times there should be no communication between the two servers. If the daily load fails, the administrator or business may require the load to be rerun at a different time.
• Bandwidth requirements, why this is important, and how to address this by sizing properly.
• Effects of latency/packet loss (WAN), why this is important, and how to address this by sizing appropriately.
Importance of Latency and Bandwidth
Bandwidth considerations are important for this aspect of BI for the following reasons:
• As a large volume of data may be transferred between the ERP system and the BI server—hundreds of MBs, multiple GBs, depending on database size, transaction volumes, and so on— bandwidth must be suitably sized so as not to act as a bottleneck to ETL processing.
• The BI daily load must complete within a limited business-imposed daily time-window. The transfer rate of large data transfers can be limited by both bandwidth and latency. For this reason, sizing and allocating appropriate bandwidth is vitally important.
Bandwidth Requirements
Figures quoted here the peak bandwidth requirements, but note that this is not a sustained rate. The sustained bandwidth transfer rate is much lower than the peak rate.
• LAN Guideline. No more than 10 MB/s (80 Mb/s).
Note: From current internal testing as of November 2011, we see LAN transfer rates of no more than 8 MB/s (3 MB/s normally) with a setting of 10,000 for fetcharraysize.
• WAN Guidelines. Under 5 MB/s. We see degradation to:
• 0.4 MB/s at 200-ms latency (peak value)
• 0.4 MB/s at 400-ms latency (peak value)
Impact of Latency on Bandwidth Requirements
Example: If you take a customer with 1 GB of data to transfer, you can estimate the minimum amount of time for that data to transfer from the ERP system to the BI database server:
LAN: 1000 MB/(3 MB/s) = 333 seconds = 5 mins 33 seconds
WAN, 200 ms: 1000 MB /(0.4 MB/s) = 2,500 seconds = 41 mins 36 seconds
In reality, the peak rates are sustained only briefly and average transfer rates are much lower—some average transfer rates may be ten times lower. These figures are only provided here as an indication of how bandwidth and latency can influence data transfer times, and how these effects can impact daily load times.