Description
This one day seminar will cover best practices for designing and managing Integration Services and Analysis Services. Based on lessons learned and best practices created, we will walk through how BI architects and developers can take advantage of these best practices to create better and more robust BI solutions.
Target Audience
This course is intended for:
Prerequisites
Experience with SQL Server 2005 or SQL Server 2008 in the following areas:
- T-SQL
- Data Warehousing
- OLAP
- SQL Server Agent
Experience with Visual Studio .NET in the following areas:
Course Summary Outline
Module 1: Business Intelligence Frameworks
As more and more organizations and teams within organizations have started adopting SQL Server Business Intelligence tools, there is increasing need for standardization early in the process to avoid common management problems that arise from lack of standardization and good management techniques. In this session, we will discuss how to perform business analysis. Then we will focus on the Extract-Transform-Load (ETL) processes using SQL Server Integration Services (SSIS). We will help you identify areas of standardization and pitfalls that arise from lack of standards, reviews some common standardization approaches and best practices and finally walk you through a standardized template example that can be customized, deployed and used as a starting point for an SSIS project. Finally, we will show a lightweight solution that helps you implementing parts of the complete ETL framework, like logging and auditing, after a SSIS package is already in production.
Module 2: Advanced SSIS Topics
In the second module of the seminar, we are going to show solutions for some of the toughest problems in ETL processes for maintenance of a data warehouse. We will explain the Slowly Changing Dimensions problem and introduce standard solutions. However, the solution for the SCD problem that comes with SSIS out of the box is very slow. We will show additional, better and more scalable solutions. We will show how one should work with state fact tables, also known as the problem of semi-additive measures. We will introduce the most optimized lookups. Finally, we will talk about leveraging SSIS multi-threading to achieve effective parallel execution of a package.
Module 3: Optimizing SQL Server Analysis Services Cubes
This session discusses aggregations and optimization techniques for SQL Server Analysis Services (SSAS). We will cover techniques for creating useful aggregations and also monitoring and analyzing queries to get a better sense of how aggregations are used. We will also look at some other cube optimization techniques. Besides efficient queries, efficient processing is important as well. We will discuss how to squeeze processing time into an available time window. We will also introduce some notes from the field, from real-life projects. Finally, we are going to discuss why additional solutions for data warehousing besides SSAS are needed. We will show the advantages of Parallel Data Warehouse appliance and the advantages of column-oriented storage introduced in PowerPivot.
Module 4: Securing SQL Server Analysis Services Data
This session will cover in-depth security implementation from built-in security all the way through custom security options to secure Analysis Server.
Module 5: Efficient Data Mining
SSAS has actually two parts: OLAP and Data Mining engine. Data mining is the most advanced part of a business intelligence solution. In order to get efficient data mining models, we need to prepare the data appropriately. In this module, we are going to spend a good portion of time for data preparation. Then we are going to discuss how to measure efficiency of our models. We will discuss how to use data mining models in production as well.