ETL Architecture for Solving Data Warehouse Challenges

Data Warehouses are heavily in use nowadays in most of businesses around the world. Data Warehousing brings higher performance, faster and more insightful data analysis out of operational databases. By the way there are some challenges in design and implementing Data Warehouses which needs robust and reliable ETL implementation. In this session you will learn an ETL Architecture implemented by SSIS and MDS to solve couple of most challengeable Data Warehousing scenarios which are Slowly Changing Dimension and Inferred Dimension Member. There will be many demos through this session which helps you to understand design and implementation of the architecture. Using Master Data Services as a MDM solution and SSIS as an ETL tool, require more architectural consideration, especially for Inferred Dimension Members. MDS will keep a single version of truth of the record, and Dimensions in the Data Warehouse will use MDS entity records as source. With Appearing new records that are not exists in MDS, an inferred record should be added into MDS, and the update structure for Data Warehouse should apply only update for inferred members instead of applying Slowly Changing Dimension. In this chapter you will learn an architecture as a best practice to implement Inferred Dimension Members with MDS and SSIS.