SQL Server 2012 End-to-End Business Intelligence
Workshop
5 Days (BI-BIE2E12-201-EN)
Description
Business intelligence solutions provide the infrastructure that enables users at all levels of a business to make better decisions based on more accurate and up-to-date
information. This workshop focuses on teaching IT professionals the best practices and skills required to successfully design, build, deploy, and operate a business intelligence solution using SQL Server 2012 Integration Services, Analysis Services and Reporting Services.
Target Audience
This course is designed for IT professionals who are interested in learning how to implement Business Intelligence solutions on the Microsoft SQL Server platform. Basic knowledge of BI concepts is assumed and some experience with SQL Server is
required.
Prerequisites
Before attending this course, it is recommended that students have the following skills:
• Basic knowledge of Business Intelligence
• Knowledge of relational database systems
• Experience with SQL Server database
• Basic knowledge of windows security
Course Objectives
Upon completion of this course, the student will be able to:
• Understand dimensional design and key Business Intelligence concepts
• Design a simple data mart
• Use SSIS to build a simple ETL process to load the data mart from various
• source systems
• Develop and customize a UDM for implementing basic usability features
• Use SSRS to develop and customize reports
• Understand other ways to consume data from the UDM
Course Summary Outline
Day 1
Module 01: Introduction to Business Intelligence
This module introduces BI and components of a BI solution and then discusses the various products that are available from Microsoft for implementing a BI solution and how those technologies fit into the BI component stack. We will then introduce in more detail the technologies that are part of the SQL Server BI Platform and also highlight some of the major changes in each of those technologies in 2012. We will also look at the various tools that are available for developing and managing the technologies.
• BI Practice
• Components of a BI solutions
• MS BI stack
• Intro to the MS SQL BI Platform
• Highlight some major changes between SQL Server 2005, 2008 and 2012.
• Client Tools for development and management – also highlight differences from
• 2005
Module 02: Preparing Data for Analytics
This module provides an introduction to dimensional modeling and lays the foundation for more detailed topics covered over the next few modules. We will cover Dimensional Modeling concepts including Fact and Dimension tables and how to implement a
dimensional model within your organization. We will also address the needs for implementing dimensional models and talk about Data Warehousing and Data Marts.
We will also briefly discuss processes for loading data into these dimensional tables.
• The Need
• Dimensional Modeling
• Dimensional Modeling Concepts
Module 03: Introduction to SSIS
This module introduces the various high-level features of SQL Server Integration Services and also introduces the development environment for SSIS
• The Need
• SSIS Components
• Changes in SQL Server 2012
• Package Components
• Data Sources and Connections LAB: Creating a simple package
Module 04: Implementing workflows using SQL Server Integration Services
This module provides an introduction to the components of SQL Server Integration Services and the package. It also highlights changes in 2012 for in the control flow environment and explains how to use the components and features of SSIS to build process workflows.
• The Need
• Package Components
Tasks, Containers and Precedence Constraints
• Variables
• SSIS Expressions
LAB: Implementing workflows in Microsoft SSIS
Day 2
Module 05: Processing data using SQL Server Integration Services
This module highlights the various components of a data flow environment and then explains how to use these components and features to implement ETL processes. This module does not go into great details around the internals of SSIS Data Flow.
• The Need
• Data Flow Components: Source and destination adapters, transformations, data
• flow paths
• Highlight: ADO.Net support
• Building data flows
• Highlight: Lookup Transform LAB: Processing Data using SSIS
Module 06: Loading Dimension Tables using SQL Server Integration Services
This module covers the specific components of the data flow that are used to implement ETL processes to load dimension tables.
• The Need
• Slowly Changing Dimension Concept
• Highlight: Slowly Changing Dimension Transform
• Other Dimension Load Types
• Highlight: MERGE statement support LAB: Creating Dimension Load Packages
Module 07: Loading Fact Tables using SQL Server Integration Services
This module covers the specific components of the data flow that are used to implement ETL processes to load fact tables.
• The Need
• Loading Fact Tables using SSIS Transformations
• Analysis Services Integration LAB: Creating Fact Load Packages
Module 08: Managing SQL Server Integration Services Packages
This module covers management and deployment aspects of SSIS packages.
• Logging
• Configuration
• Deployment
LAB: Deploying and Scheduling SSIS Packages
Day 3
Module 09: Introduction to SQL Server Analysis Services
This module introduces key concepts and features in SQL Server Analysis Services and OLAP technologies. At the end of this module, attendees will learn how to build a basic cube and understand the power that it provides for analysis.
• The Need
• Introduction to OLAP Fundamentals
• The UDM and Benefits
• Analysis Services Fundamentals
• Basic Components of the UDM: Data Sources, Data Source Views, Cubes,
• Dimensions
• Components of a SQL Server Analysis Services Database
• Building a basic cube
• Deployment and processing LAB: Building a cube
Module 10: Customizing the UDM
Through this module, attendees will understand the various customization options that enhance usability and performance of the UDM and allow them to extend the
usefulness of the data analysis via a “single-version of the truth”.
• The Need
• Introduction to the SSAS designer
• New design features in SSAS 2012
• Dimension customization: Dimension properties, attributes and hierarchies
• (Customization scenario/how-to approach)
• Cube customization: Cubes, measure groups, measures (Customization
• scenario/how-to approach)
• Advanced SSAS database components: Dimension relationships, Calculations,
• KPI’s,
LAB: Customizing the UDM
Day 4
Module 11: Advanced Features of SSAS UDM
This module covers some of the advanced administrative and security features for managing SSAS databases.
• Data Processing
• Measure Group Partitions
• Proactive Caching
• Aggregation and Usage Based Optimization (UBO)
• Role-based Security
Module 12: Introduction to MDX
This module provides an introduction to the Multi-Dimensional Expression (MDX) language for writing custom calculations and queries and building custom members.
• Overview of MDX
• Learning MDX
MDX Constructs
Member Functions
MDX Query Syntax
Module 13 [Optional]: Introduction to Data Mining
This module introduces Data Mining and provides an understanding of the business uses of data mining and also provides an overview of the data mining process.
• The Need
• Data Mining Process
• Data Mining Algorithms
• Changes in SQL Server 2012
• Data Mining add-ins in Excel 2007
• LAB [Optional]: Building a Data Mining Model
Module 14: Introduction to SQL Server Reporting Services
This module will introduce the Reporting Services, the architecture and components of SSRS. Additionally, we will look at the components of a Report and understand the features and functionality including new features in SSRS 2012.
• The Need
• SSRS 2012
• SSRS Architecture and Components
• What’s new in SSRS 2012 Architecture
• Report Components
• What’s new in SSRS 2012 Report Components
Day 5
Module 15: Defining Reports using SQL Server Reporting Services
This module will familiarize people with the design tools for designing reports and explain features available to customize report look and functionality.
• The Need
• SSRS 2012 Designer
• What’s New in SSRS 2012 for Report design
• Report Queries and Parameters
• Report layout and customization
• Report Interactivity
• Charts and Graphs
• Extending Report Functionality LAB: Building a Simple Report
Module 16: Customizing SSRS Reports
This module looks at advanced customization options for enhanced report interactivity and functionality.
• Expressions
• Report Interactivity
• Report Formatting
• Advanced Functionality LAB: Customizing Reports
Module 17: Adhoc Reporting with Report Builder
This topic introduces the self-services capabilities in Reporting Services using Report Models and Report Builder clients.
• Ad-hoc reporting
• Report Builder Client
• Building Report Models LAB: Building a Report Model
LAB: Creating a Report using Report Builder 2.0
Module 18 [Optional]: Advanced SSRS Features
This module covers some of the management and security aspects of SSRS
• Report Access
• Linked Reports
• Subscription and Delivery
• Data Caching
• Report History
• Monitoring
• Security Considerations
• Upgrade Considerations
Module 19: Tying it all together
This is a wrap-up module that does a very quick overview of the topics we covered in the class.
