SQLSVRBC -SQL Server BI Bootcamp (5 Days)
Business Intelligence is HOT! If you are a SQL or Business Intelligence professional, then this boot camp is for you.
We’ve combined these three Microsoft coursesinto a week’s worth of in-depth training to learn SQL 2012/2014 Integration, Reporting and Analysis Services:
55120: Quick Microsoft SQL Server 2012-2014 Integration Services
55124: Quick Microsoft SQL Server 2012-2014 Reporting Services
55125: Quick Microsoft SQL Server 2012-2014 Analysis Services
This fast-paced instructor-led training is designed for those new to SQL 2012-2014 Services and need to quickly get up to speed on the features available in SQL Integration Services; learning how to utilize an Analysis Services multidimensional or tabular solution and quickly learn how to use the Reporting Services development tools to create reports.
This boot camp uses an instructor-led topic explanation, instructor performing detailed demonstrations, then the student performing exercises to duplicate what the instructor demonstrated. Additionally, videos of the demonstrations performed by the course author are included with the course as a take-away. The course completes with additional scenario-based labs to reinforce the course content.
Audience Profile:
This course is intended for IT Professionals; SQL professionals, Microsoft Analysis Services cube and report developers, Microsoft Reporting Services administrators and business intelligence professionals.
Course Overview
Integration Services
SQL Server Integration Services Architecture
In this module we will explore architecture and version changes of SQL Server Integration Services. We’ll then cover the building blocks needed to understand packages and how they are created.
Lessons
Version Changes of SSIS From 2008-2012
The SQL Server Integration Service
Understanding Packages
Managing with Control Flow
Moving Data with Data Flow
Understanding Variables
Understanding Parameters
After completing this module, students will be able to:
Understand packages.
Manage with Control Flow.
Move data with Data Flow.
Understand variables.
Understand parameters.
SQL Server Integration Services Tools
In this module we will learn about and utilize the tools available to assist with package creation.
Lessons
Utilizing the Import/Export Wizard
Working in the Explore Window
Creating SQL Server Integration Services Packages
Utilizing Connection Managers
After completing this module, students will be able to:
Utilize the Import/Export Wizard.
Work in the Explore window.
Create SQL Server Integration Services packages.
Utilize Connection Managers.
SQL Server Integration Services Tasks
SSIS tasks are the foundation of the Control Flow in SSIS. In this module we will walk through tasks and utilize them.
Lessons
Coding the Script Task
Looping and Sequence Tasks
Utilizing Data Preparation Tasks
Data Loading with the Analysis Services Tasks
Using the Workflow Tasks
Managing SQL with the SQL Tasks
Administration of SQL Using the SQL Management Objects Tasks
After completing this module, students will be able to:
Code the Script Task.
Utilize Looping and Sequence Tasks.
Utilize Data Preparation Tasks.
Load data with the Analysis Services Tasks.
Use the Workflow Tasks.
Manage SQL with the SQL Tasks.
Administration of SQL using the SQL Management Objects Tasks.
Data Flow
The Data Flow Task is where the bulk of your data heavy lifting occurs in SSIS. In this module we’ll discuss methods for optimizing sources and explain destination options.
Lessons
Optimizing Sources for Extraction
Using Destinations and Understanding choices
Examining the Transformation and Other Tasks (about 25 of them)
After completing this module, students will be able to:
Optimize sources for extraction.
Use Destinations and understand choices.
Examine the Transformation and other Tasks (about 25 of them).
Variables, Parameters and Expressions
At one point or another, when utilizing SSIS packages, you will encounter the need for dynamic capabilities. This module aims to give you an understanding of how expressions work, and how variables and parameters set up expressions on your SSIS project.
Lessons
Creating Dynamic Packages
Utilizing SQL Server Integration Services Data Typing
Define and Use Variables and Parameters
Using Expressions in Packages
After completing this module, students will be able to:
Create dynamic packages.
Utilize SQL Server Integration Services data typing.
Define and use variables and parameters.
Use expressions in packages.
Containers
Containers are objects in SQL Server Integration Services that provide structure to packages and services to tasks. In this module we will explain how to create and configure containers, and utilize them effectively.
Lessons
Overview of Containers
Utilizing Sequences in a Container
Utilizing Container Loops
After completing this module, students will be able to:
Utilize sequences in a container.
Utilize container loops.
Performing Joins
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. In this module we will explore joins and demonstrate how to use them.
Lessons
Performing Joins with the Lookup Transformation
Performing Joins with the Merge Transformation
Utilizing Cache Modes in Joins
After completing this module, students will be able to:
Perform joins with the Lookup Transformation.
Perform joins with the Merge Transformation.
Tuning SQL Server Integration Services Packages
Before putting your package into production, it is beneficial to take some time to review your package with an eye toward preventing, or at least mitigating, performance problems. In this module we will take a close look at how the SSIS engine works and cover best practices to ensure proper performance.
Lessons
Examining the SSIS Engine
Using the Best Practices
After completing this module, students will be able to:
Examine the SSIS engine.
Use the best practices.
Debugging SQL Server Integration Services Packages
This module describes procedures for debugging SSIS packages and follows with a section on error handling.
Lessons
Designing to Troubleshoot: Using Precedence Constraints
Working with Advanced Logging and Reporting
Handling Errors
After completing this module, students will be able to:
Design to troubleshoot: using Precedence Constraints.
Work with advanced logging and reporting.
Handle errors.
Common SQL Server Integration Services Design Patterns
Design patterns help to solve common problems encountered when developing data integration solutions. In this module we’ll discuss how to choose among certain options, and cover deployment strategies.
Lessons
Choosing the Script Task or Script Component
Working with XML
Using Package Hierarchies: Parent Child
Understanding Deployment Strategies
After completing this module, students will be able to:
Understand how to choose the Script Task or Script Component.
Work with XML.
Use package hierarchies: Parent Child.
Understand deployment strategies.
Reporting Services
Introduction to Microsoft SQL Server Reporting Services
Reporting Services is a report design and enterprise-class information delivery tool. In this module, we will explore the version changes, installation, architecture and tools for Reporting Services.
Lessons
Version Changes of SSRS from 2008-2012
Reporting Services Installation and Architecture
Reporting Services Tools
After completing this module, students will be able to:
Understand version changes of SSRS from 2008-2012.
Understand Reporting Services installation and architecture.
Understand and utilize Reporting Services tools.
Building Basic Reports
In this module, we begin building reports using the basics. We will explore the wizards available and then cover tables, matrices, and lists while demonstrating how to use each of them. Finally, we will take a look at the report items available to help design the report.
Lessons
Wizards
Data Sources
Datasets
Understanding Security in Connections
Tables
Matrix
List
Report Items
Report Parts
After completing this module, students will be able to:
Understand and utilize Wizards.
Create and configure data sources.
Create and configure datasets.
Understand security in connections.
Create and configure Tables.
Create and configure Matrices.
Create and configure Lists.
Understand and utilize Report Items.
Understand and utilize Report Parts.
Highlighting Basic Reports with Visualizations
Visualize and present your data using visualizations. In this module, we cover the visualizations available, while demonstrating each, so you can see for yourself just how captivating data can be.
Lessons
Charts
Gauges and Dashboards
Maps
Sparklines, Data Bars, and Indicators
After completing this module, students will be able to:
Understand and utilize Charts.
Understand and utilize Gauges.
Understand Dashboards.
Understand and utilize Maps.
Understand and utilize Data Bars.
Understand and utilize Sparklines.
Understand Key Performance Indicators.
Developing Advanced Reports
Looking to take reporting to the next level? Then look no further. In this module, we cover what you’ll need to create advanced reports with features such as conditional formatting and interactivity just to name a few.
Lessons
Expressions
Functions
Interactivity
Parameters
Conditional Formatting
Templates
Subreports
After completing this module, students will be able to:
Understand and utilize Expressions.
Understand and utilize Functions.
Understand and utilize Interactivity.
Understand and utilize Parameters.
Understand and utilize Conditional Formatting.
Understand Templates.
Understand and utilize Subreports.
Managing and Maintaining Reporting Services
To share your report, you need to publish or deploy it to a report server or SharePoint site. In this module, we explain deployment options and follow with an explanation on security and administrative tasks.
Lessons
Deploying Reports
Securing Content
Caching
Snapshots
History
Viewing Reports Online
Utilizing Subscriptions
Data Alerts
After completing this module, students will be able to:
Deploy reports.
Secure and manage content.
Understand and utilize Caching.
Understand and utilize Snapshots.
Understand and utilize Report History.
Understand how to complete other administrative tasks.
Analysis Services
Introduction to Microsoft SQL Server Analysis Services
Analysis Services is an online analytical processing (OLAP) database, a type of database that is highly optimized for the kinds of queries and calculations that are common in a business intelligence environment. In this module we aim to get you familiar with the recent changes, explore the architecture options, and explain the tools available.
Lessons
Common Analysis Services Solutions
Version Changes of SSAS from 2008-2014
Analysis Services Installation and Architecture: One Product, Two Models
Choosing the Right Model
Analysis Services Tools
After completing this module, students will be able to:
Understand common Analysis Services solutions.
Understand version changes of SSAS from 2008-2014.
Understand Analysis Services installation and architecture.
Understand how to choose the right model.
Understand the Analysis Services tools available.
The Multidimensional Solution
In the multidimensional model, data is modeled as a series of cubes and dimensions. In this module we will explore how to create and configure a cube. Dimensions will be discussed in a later module.
Lessons
Understanding the Multidimensional Model
Utilizing Data Sources and Data Source Views
Creating a Cube
After completing this module, students will be able to:
Understand the multidimensional model.
Utilize data sources and data source views.
Create a cube.
Dissecting with Dimensions
Dimensions are the categories by which you slice your data to view specific quantities of interest. In this module we will explore dimensions and how they are used in conjunction with attributes, relationships, and hierarchies.
Lessons
Developing Dimensions
Utilizing Attributes
Relating with Relationships
Handling Hierarchies
After completing this module, students will be able to:
Understand and utilize dimensions.
Understand and utilize attributes.
Understand and utilize relationships.
Understand and utilize hierarchies.
Managing Measures
Each cube is made up of one or more measure groups, and each measure group in a cube contains one or more measures. In this module we will explore all there is to know about measures and measure groups, and we’ll also cover calculated members.
Lessons
Measures and Measure Groups
Calculated Members
After completing this module, students will be able to:
Understand and utilize measures and measure groups.
Understand and utilize calculated members.
Configuring Cube Options
Perspectives have the ability to hide certain parts of a complex model, which can help usability, and security roles can be used to deny access to specific rows of data from tables to specific users. Translations allow the metadata of a multidimensional model to appear in different languages for users with different locales on their desktops. In this module we will cover both configurations and the options available.
Lessons
Understanding Perspectives
Utilizing Translations
Browsing Perspectives and Translations
After completing this module, students will be able to:
Understand and utilize perspectives.
Understand and utilize translations.
Browse perspectives and translations.
Deploying
Deploying an Analysis Services project creates the defined objects in an instance of Analysis Services. In this module we will explore deployment options, processing strategies, and security.
Lessons
Examining Deployment Options
Processing Strategies
Exploring Security
After completing this module, students will be able to:
Understand and utilize deployment options.
Understand and utilize processing strategies.
Understand and utilize security.
Deploy a multidimensional model.
The Tabular Solution
Tabular models are in-memory databases in Analysis Services. In this module we will explore the tabular model, create a tabular project, deploy and query the solution using Data Analytic Expressions (DAX).
Lessons
Understanding the Tabular Model
Creating a Tabular Project
Deploying
Browsing the Model
Querying the Solution and Understanding DAX
Maintaining and Optimizing
After completing this module, students will be able to:
Understand the tabular model.
Create a tabular project.
Analyze the data using Microsoft Excel.
Create and configure calculated measures and calculated fields.
Backup and Restore.