This video outlines basic concepts of Data Warehousing and Data Integration using Microsoft SQL Server, focussing on its Integration Services (SSIS) component.

A Star Schema (00:33) is a commonly used structure for building Data Warehouses, and it benefits from being based on a simple, flexible, and relatively inexpensive to maintain relational design. It was introduced more than two decades ago, and many good books have been written about it, notably a series by Ralph Kimball (see recommended books). Above all, this is the most popular way to build an Enterprise Data Warehouse (EDW) using Microsoft SQL Server (00:53). At its heart, you build fact and dimension tables, which later easily translate to the design of a multidimensional (OLAP) cube, using SQL Server Analysis Services (SSAS). This short video shows an example of a cube (01:13) and it highlights some of its elements, such as calculations, and KPIs, as well as it briefly shows the design of a dimension (01:23), all with the help of Business Intelligence Development Studio (BIDS). The data warehouse tables and the cube shown in this video come from a SSAS database Adventure Works 2008 R2 DW. You can download it and work with all by yourself while practicing with SQL Server—you can find it here on Codeplex, make sure you download the version that matches your version of SQL Server. If you want to play with SQL Server and you do not have it, you can download a trial version from here.

Getting data into a Data Warehouse is usually accomplished by means of ETL: Extract, Transform, and Load operations (01:47), which are part of a larger set of transformations generally referred to as Data Integration. SQL Server Integration Services (SSIS) are an important component of the Microsoft BI platform and they are used for Data Integration and ETL. The second half of this video (02:11) shows a simple SSIS Package that consolidates and aggregates data before loading it to a Data Warehouse (02:59) and, separately, sends it for a customer segmentation analysis using Data Mining components of SQL Server Analysis Services (SSAS). There are not many video examples of running SSIS packages on the web, so make sure to watch this one if you are learning or you are new to Microsoft BI.

Whilst this video shows SQL Server 2008 R2, it fully applies to SQL Server 2012, as the main concepts of Data Warehousing, cube design with SSAS, and data integration with SSIS do not differ between those versions of SQL Server. On the other hand, SQL Server 2012 introduces an entirely new way to build analytical models, known as SSAS Tabular Mode, which is discussed and shown in this article.