Microsoft SQL Server Integration Services (SSIS) and WhereScape RED are very different products, but both can be used to build a data warehouse and can provide high performance. This paper describes the strengths of each product and the best path to take.
SSIS provides useful data transformation tasks out of the box. However, support staff needs technical knowledge of data warehousing best practices and SSIS behavior to troubleshoot any technical issues. SSIS does not come with any frameworks or data warehousing best practices built-in.
We have found that experienced data warehouse managers will always choose supportability, the ability to troubleshoot and enhance over a few percent of processing time. They recognize that user experience is what makes data warehouse successful, and the factors that make the user experience better are:
● Fast response to change requests
● Reliable operation
● Confidence in the data
Whilst SQL Server Integration Services excels at loading data from different sources. Once the data is loaded, WhereScape RED can build all the downstream SQL Server objects such as facts, dimensions, and Microsoft Analysis Services cubes in the database 10 times faster than with traditional, hand-coding methods.
SSIS’s lack of integration between database design and process design slows both development and maintenance and increases the risk of error. Iterations take between 2 and 10 times longer using SSIS over WhereScape RED.