Warehouse design and updating
This slows down testing and debugging even more because the external data source might have millions of rows of data.Moreover, you are putting a high burden on the external data source, which is a production database.
This staging table approach not only solves many of the issues I had before, it also provides a simple and robust way to design and build ETL processes in SSIS.
But, when I first starting building BI solutions, I was hardly able to find any architectural guidance for best practices for building BI solutions based Microsoft’s tools.
Microsoft has a wonderful set of tools to create powerful BI solutions, nevertheless the learning curve is pretty steep.
Learning SSIS involves a steep learning curve and if you have a software development background like I do, you might first be inclined to build your ETL program from scratch using a general purpose programming language such as C#.
However, once you master SSIS you’ll be able to write very efficient ETL processes much more quickly.