At this point, it may come as no surprise that a data source formulated for use in Integration Services isn't any different than a data source formulated for use with Analysis Services. Not only that, but data source views play the same role across both Integration Services and Analysis Services. A great benefit of having a common data source is to share it across multiple SSIS packages. Perhaps you recall from earlier in this book that data source views are similar to views in SQL Server. If you want to work with a limited set of tables, that is, a view in the relational world, you can emulate that using a data source view when creating SSIS packages. If you want to use the same table more than once for certain operations, you can create named queries, all of which utilize a shared data source connection in turn. You will learn more about these capabilities when creating a SSIS package.
In this chapter you learn specifically about Analysis Services-related tasks but you should know that there are 29 control flow items (most of which are tasks), plus another 11 maintenance plan tasks. Examples of commonly used tasks are the Send Mail Task, which is used to notify the administrator of job status; the Bulk Insert Task, which is used to insert data from flat files into tables at high speed; and the Data Flow Task, which contains a number of transforms internally — a container that helps to complete tasks like merging data and then sorting it, and so on. One capability of interest regarding the tasks is the product allows you to have multiple tasks in the designer that can be executed in parallel — more on that later this chapter.
The process of populating data warehouses and data marts requires well-groomed data. Getting to wellgroomed data requires the use of capabilities found in Data Flow Transformations. These Transforms can be used, for example, to sample, count, or merge data. Because there are 28 Transforms under Data Flow Transformations, there is no lack of tools you can use to make your data conform to whatever design you wish it to take.
In Chapter 1 you saw an example that included various representations of Microsoft such as MSFT, MS, Microsoft, and MSoft. These different representations from various source systems might cause some difficulties; there could be a strong business need to have a standard format and merge the relevant data. SSIS fuzzy look-up transformations help you in achieving the transformation by means of comparing the various Microsoft strings and deriving a standardized output MSFT. You can use the lookup transforms and do conditional split transforms, or write your own code to perform this operation using the script transform. This example demonstrates a typical data cleansing operation with SSIS transforms and multiple ways of achieving the end goal.