{"id":2882,"date":"2016-05-20T00:45:21","date_gmt":"2016-05-20T00:45:21","guid":{"rendered":"http:\/\/bicortex.com\/?p=2882"},"modified":"2016-05-27T02:31:15","modified_gmt":"2016-05-27T02:31:15","slug":"designing-data-acquisition-framework-in-sql-server-and-ssis-how-to-source-and-integrate-external-data-for-a-decision-support-system-or-data-warehouse-part-1","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/designing-data-acquisition-framework-in-sql-server-and-ssis-how-to-source-and-integrate-external-data-for-a-decision-support-system-or-data-warehouse-part-1\/","title":{"rendered":"Designing data acquisition framework in SQL Server and SSIS \u2013 how to source and integrate external data for a decision support system or data warehouse (Part 1)"},"content":{"rendered":"

Note: Part 2 to this series can be found HERE<\/a>, Part 3 HERE<\/a>, Part 4 HERE <\/a>and all the code and additional files for this post can be downloaded from my OneDrive folder HERE<\/a><\/p>\n

Introduction<\/strong><\/h3>\n

There is a lot of literature and Internet resources on the subject of data warehouse and decision support systems architecture, including considerations for Kimbal vs Inmon approach, data storage and management systems vendor options, RDBMS vs NoSQL arguments etc., however, in a typical small-to-medium enterprise environment, the first step to creating a data warehouse is designing a data acquisition job to move the source data into a staging area. Providing the most prevalent approach to data warehouse design is employed i.e. no near-real time or streaming architecture is required, the staging server\/database, storing a\u00a0copy of a transactional system data for further processing and transformations\u00a0is created and populated in the first instance.<\/p>\n

Most of the time, sourcing transactional data and placing its copy on the staging database simply involves a full or delta copy from the operational system(s) data into the staging database. There is typically no schema denormalisation involved at this stage but data cleansing routines can be employed to make data cleaner and conforming to business definitions e.g. missing values substitution, data types conversion, de-duplication etc. Sometimes, certain degree of ‘pruning’ may be employed to separate redundant and information-poor data from data which can be turned into insight, thus competitive advantage. Also, since the advent of cloud providers\/services, with their huge on-demand and cost-competitive processing and storage capabilities, ELT (extract, load and transform), rather than ETL (extract, transform and load) approach may be more applicable for some scenarios. These may include dealing with large volumes of data e.g. generated by a variety of dispersed systems such as IoT devices or operating on a database engine designed for fast, high-concurrency data processing e.g. massively parallel processing (MPP) engine. Therefore, depending on how much you would like to massage the data before it finds its way into the landing\/staging area, data acquisition can either become as simple as a like-for-like, source-to-target copying or as complex as an intricate collection of transformations, mostly to deal with data quality and deluge issues.<\/p>\n

To demonstrate a sample workflow for a data acquisition job of moderate complexity and data volume I will describe a sample SQL Server Integration Services (SSIS) package which anyone versed enough in T-SQL and SSIS can replicate and modify according to the project and business needs. This package has been ‘taken out’ of one of my previous client\u2019s environment and can serve as a template for sourcing transactional data into a staging database for further processing and massaging. To make this example more akin to a typical business scenario and more flexible for future reuse I have deliberately assumed the following:<\/p>\n