SQL Server BI Bootcamp

Code: SQLSVRBC
Course duration: 5 days
Price:

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.

Course Reviews

No reviews found for this course.

Be the first to write a review