Course Duration: 60 hours

Course Schedule: Monday to Friday from 8:00 PM to 10:00 PM Eastern Time.

Here is a brief outline of what courses the experts at SolWin will provide you with during this course:

  • SSRS (SQL Server 2012 Reporting Services)
  • SSIS (SQL Server 2012  Integration Services)
  • DQS (Data Quality Services)
  • SSAS (SQL Server 2012 Multidimensional Analysis Services)
  • SSAS (SQL Server 2012 Tabular Analysis Services) )
  • Dimensional modeling & Data Warehousing
  • MDX Queries

After each class, you will get

  • Comprehensive course material
  • LAB exercises which you need to finish before next class.
  • Recorded video of the class which you can keep it forever and review it later.

You will be immensely trained in the following courses:

1) Dimension Modeling & Data Ware Housing

Module 1: Introduction to Business Intelligence (BI)

In this session student will learn how BI makes difference in competitive word. What is the typical architecture of BI application and which are the various tools available in market to implement BI Solution.

Lessons

  • What is Business Intelligence?
  • Typical architecture of BI application
  • Various tool available in the market to implement BI solution

After completing this module, students will be able to:

  • Describe the basic architecture of BI solution

Module 2: Introduction to Data Warehousing

The students will learn the basic concept of data warehousing and data mart.

Lessons

  • What is the data warehouse and data marts ?
  • OLTP Vs. Data warehouse
  • OLAP
  • Fact & Dimension Table

After completing this module, students will learn:

  • Basic concept of data warehousing
  • Fact and Dimension tables structure
  • What are the degenerated and junk dimensions?
  • Various categories of Fact tables?

Module 3: Dimension Modeling Process

The students will learn the dimension modeling process and slowly changing dimension (SCD) types.

 

Lessons

  • Dimension Modeling Process
  • Slowly Changing Dimensions (SCD) Types
  • After completing this module, students will learn :
  • How to identify dimension and fact tables for the reporting requirements
  • How to implement SCD type

 

Module 4: Documentation

The students will learn the documentation and best practices followed to generate data warehouse schema

Lessons

  • Usage of Kimball spreadsheet
  • ERD representation of Adventure works data warehouse
  • Database project with VS 2010 Ultimate version
  • Sample design document for Adventure Work data warehouse

After completing this module, students will learn :

  • How to use Kimball spreadsheet to generate data warehouse schema?
  • How to use VS 2010 to create ERD schema and test data?

Implementing and Maintaining Microsoft SQL Server 2008 R2 Integration Services (SSIS)

Module 1: Introduction to SQL Server 2012 Integration Services

The students will learn the architecture of Integration Services and the role it plays in extracting, transforming, and loading data. The students will also be introduced to the tools that are used to build and manage Integration Services solutions.

Lessons

  • Architecture of SQL Server Integration Services
  • Usage of Integration Services
  • Import Export Wizard

Lab: Introduction to SQL Server Integration Services

  • Using the Import and Export Wizard
  • Running an Integration Services Package

After completing this module, students will be able to:

  • Describe Integration Services solutions
  • Use Integration Services tools

Module 2: Developing Integration Services Solutions

The students will learn  the development tasks that are involved in creating an Integration Services package.

Lessons

  • Creating an Integration Services Solution
  • Using Variable
  • Using parameter
  • Building and Running a Solution

Lab: Developing Integration Services Solutions

  • Creating an Integration Services Project
  • Implementing a Package
  • Building and Running an Integration Services package

After completing this module, students will be able to:

  • Create a SQL Server Integration Services solution.
  • Use variables.
  • Pass parameter in query
  • Build and run a solution.

Module 3: Implementing Data Flow

The students will learn the data flow sources, transformations, and destinations that can be used to implement a data flow task in an Integration Services control flow. It also explains how to use data flow paths to direct valid and invalid rows through the data flow.

Lessons

  • Data Flow Sources and Destinations
  • Basic Data Flow Transformations
  • Advanced Data Flow Transformations
  • Data Flow Paths

Lab: Implementing Data Flow

  • Data Flow Source and Destination for MS SQL, flat file, Excel and XML files
  • Implementing Transformation task. In the lab demo will be given on Derived Column, SCD task, Look up task, Fuzzy look up and grouping task, Conditional split, Row and percentage sampling, row counts, script task, cache task, merge join task, union all component
  • Using Data Viewer
  • Configuring Error Output

After completing this module, students will be able to:

  • Implement data flow sources and destinations.
  • Implement data flow transformations.
  • Implement advanced data flow transformations.
  • Implement data flow paths.

Module 4: Data Flow Task – Transformation task category and best practices

The students will learn how SSIS package perform ETL operation in buffer memory, what are the best practices and which component to choose while performing ETL operation.

Lessons

  • Transformation task categories – Blocking transformation, partially blocking transformation and non blocking transformation
  • Alternative to Blocking transformation
  • Appropriate usage of buffer memory at run time

Lab: Implementing Data Flow

  • Replacement of SCD component
  • Replacement of Merge join

After completing this module, students will be able to:

  • Categories transformation task into blocking , non blocking and partial blocking transformation
  • Create logic that can be used to replace blocking and non blocking transformation
  • Manage buffer memory to get maximum output out of SSIS run time engine

Module 5: Implementing Control Flow

The students will be introduced to the tasks and precedence constraints that can be used to implement control flow in an Integration Services package.

Lessons

  • Package level properties
  • Control Flow Precedent Constraints
  • Control Flow Containers

Lab: Implementing Control Flow

  • Creating a Simple Control Flow
  • Configuring Precedence Constraints
  • Using Containers

After completing this module, students will be able to:

  • Configure control flow tasks.
  • Configure control flow precedence constraints.
  • Configure control flow containers.

Module 6: Implementing Control Flow Components

The students will be introduced to the tasks and precedence constraints that can be used to implement control flow in an Integration Services package.

Lessons

  • Control Flow Tasks that include Execute SQL Task, Web Services Task, Send mail task, For each loop container, Sequence container, For loop container, FTP task, Script Task, Execute Package task, Maintenance plans task, Analysis services processing task and Analysis services Execute DDL task.

Lab: Implementing Control Flow

  • Lab implementation of control flow tasks that include Execute SQL Task, Web Services Task, Send mail task, For each loop container, Sequence container, For loop container, FTP task, Script Task, Execute Package task, Maintenance plans task, Analysis services processing task and Analysis services Execute DDL task.

After completing this module, students will be able to:

  • Configure control flow tasks

Module 7: Implementation of ETL Packages

The students will be introduced to the design of dimension and fact packages that is used to populate data in data warehouse.

Lessons

  • Design of Dimension package
  • Design of Fact package

Lab: Implementing Control Flow

  • Creation of Dimension Package
  • Creation of fact package

After completing this module, students will be able to:

  • Create fact and dimension packages that are responsible to push data into data warehouse

 

Module 8: Implementing Logging

The students will learn  how to use logging in an Integration Services package, and explained how to configure and use logging providers to generate information about a package’s execution.

Lessons

  • Overview of Integration Services Logging
  • Enabling and Configuring Logging

Lab: Implementing Logging

  • Configuring Logging
  • Implementing Custom Logging

After completing this module, students will be able to:

  • Describe Integration Services logging.
  • Implement Integration Services logging.

Module 9: Debugging and Error Handling

  • The students will be introduced to how to debug Integration Services packages by using the debugging tools in Business Intelligence Development Studio. It then explains how to implement error-handling logic in an Integration Services package.

Lessons

  • Debugging a Package
  • Implementing Error Handling

Lab: Debugging and Error Handling

  • Debugging a Package
  • Implementing Error Handling
  • Controlling Failure Behavior

After completing this module, students will be able to:

  • Debug an SSIS package.
  • Implement transactions.

 

Module 10: Data Quality Services (DQS)

The students will be introduced to how to use DQS for data cleansing, its configurations and how to deploy DQS packages to production servers.

Lessons

  • Knowledge Management & Data Cleansing
  • Data Matching in Data Quality Services

Lab: Creation and usage of Knowledge base

  • Creating a knowledge base
  • Preparing a Package to clean data
  • Deploying a Package

After completing this module, students will be able to:

  • DQS configuration
  • Implement DQS.
  • Deploy packages.

Module 11: Configuring and Deploying Packages

The students will be introduced to how to create Package Configurations and how to deploy Integration Services packages to production servers.

Lessons

  • Package Configurations
  • Deploying Packages

Lab: Configuring and Deploying Packages

  • Creating a Package Configuration
  • Preparing a Package for Deployment
  • Deploying a Package

After completing this module, students will be able to:

  • Implement package configurations.
  • Deploy packages.

Implementing and Maintaining Microsoft SQL Server 2012 Analysis Services (SSAS)

 

Module 1: Introduction to Microsoft SQL Server Analysis Services

This module introduces common analysis scenarios and describes how Analysis Services provides a powerful platform for OLAP solutions and data mining solutions. The module then describes the main considerations for installing Analysis Services.

Lessons

  • Overview of OLAP Concepts.
  • Overview of SQL Server Analysis Services

After completing this module, students should be able to:

  • Describe data analysis solutions.
  • Describe the key features of SQL Server Analysis Services.

Module 2: Creating Multidimensional Analysis Services Solutions

  • This module introduces the development tools you can use to create a multidimensional Analysis Services solution and describes how to create data sources, data source views and cubes.

Lessons

  • Developing Analysis Services Solutions
  • Creating Data Sources and Data Source Views
  • Creating a Cube

Lab: Creating Multidimensional Analysis Solutions

  • Creating a Data Source
  • Creating and Modifying a Data Source View
  • Creating and Modifying a Cube

After completing this module, students will be able to:

  • Develop Analysis Services solutions.
  • Create a data source and a data source view.
  • Create a cube.

Module 3: Working with Cubes and Dimensions

  • This module describes how to edit dimensions and to configure dimensions, attributes, and hierarchies.

Lessons

  • Configuring Dimensions
  • Defining Attribute Hierarchies
  • Sorting and Grouping Attributes

Lab: Working with Cubes and Dimensions

  • Configuring Dimensions
  • Defining Relationships and Hierarchies
  • Sorting and Grouping Dimension Attributes

After completing this module, students will be able to:

  • Configure dimensions.
  • Define hierarchies.
  • Sort and group attributes.

Module 4: Working with Measures and Measure Groups

This module explains how to edit and configure measures and measure groups.

Lessons

  • Working With Measures
  • Working with Measure Groups

Lab: Working with Measures and Measure Groups

  • Configuring Measures
  • Defining Dimension Usage and Relationships
  • Configuring Measure Group Storage

After completing this module, students will be able to:

  • Work with measures.
  • Work with measure groups.

 

Module 6: Customizing Cube Functionality

This module explains how to customize a cube by implementing key performance indicators (KPIs), actions, perspectives, and translations.

Lessons

  • Implementing Key Performance Indicators
  • Implementing Actions
  • Implementing Perspectives
  • Overview about Translations

Lab: Customizing Cube Functionality

  • Calculated Measures and Named Sets
  • Implementing a KPI
  • Implementing an Action
  • Implementing a Perspective

After completing this module, students will be able to:

  • Implement Key Performance Indicators (KPIs).
  • Implement actions.
  • Implement perspectives.
  • Understand translations.

Module 7: Deploying and Securing an Analysis Services Database

This module describes how to deploy an Analysis Services database to a production server, and how to implement security in an Analysis Services multidimensional solution.

Lessons

  • Deploying an Analysis Services Database
  • Securing an Analysis Services Database

Lab: Deploying and Securing an Analysis Services Database

  • Deploying an Analysis Services Database
  • Securing an Analysis Services Database

After completing this module, students will be able to:

  • Deploy an Analysis Services database.
  • Secure an Analysis Services database.

Module 8: Overview of the different Clients used for Reporting from Analysis Services

This module would describe the different clients which could be used for analyzing OLAP data. This would include discussions around Excel, SSRS, PPS 2010 etc.

 

Module 9 : Analysis Services Tabular Model

This module explains how to develop an OLAP Solution using the Tabular Model.

Lessons

  • Dimensions and Hierarchies
  • Calculated Columns and Measures
  • Security with Roles
  • KPI
  • Partitions
  • Perspectives

Lab: Developing a Tabular Model OLAP Solution

  • Creating an Analysis Services Tabular Project
  • Creating Hierarchies
  • Creating Calculated Columns
  • Creating Measures
  • Defining Roles
  • Creating KPI
  • Creating Partitions
  • Creating Perspectives

After completing this module, students will be able to:

  • Create Hierarchies
  • Create Calculated Columns and Measures
  • Implement Security using Roles
  • Create KPI
  • Create Partitions
  • Create Perspectives

Implementing and Maintaining Microsoft SQL Server 2012 Reporting Services (SSRS)

 

Module 1: Introduction to Microsoft SQL Server Reporting Services

The students will be introduced to the architecture of Reporting Services and the role it plays in an organization’s reporting life cycle, the key features offered by Reporting Services, and the components that make up the Reporting Services architecture.

Lessons

  • Overview of SQL Server Reporting Services
  • Reporting Services Tools

Lab: Introduction to Microsoft SQL Server Reporting Services

  • Exploring Report Designer
  • Exploring Report Manager

After completing this module, students will be able to:

  • Describe the features of SQL Server Reporting Services.
  • Describe the Reporting Services tools.

 

Module 2: Authoring Basic Reports

The students will learn the fundamentals of report authoring, including configuring data sources and data sets, creating tabular reports, summarizing data, and applying basic formatting.

Lessons

  • Creating a Basic Table Report
  • Formatting Report Pages
  • Calculating Values

Lab: Authoring Basic Reports

  • Creating a Basic Table Report
  • Formatting Report Pages
  • Adding Calculated Values

After completing this module, students will be able to:

  • Create a basic table report.
  • Format report pages.
  • Calculate values for a report.

 

Module 3: Enhancing Basic Reports

The students will learn about navigational controls and some additional types of data regions, and how to use them to enhance a basic report.

Lessons

  • Interactive Navigation
  • Displaying Data

Lab: Enhancing Basic Reports

  • Using Dynamic Visibility
  • Using Document Maps
  • Initiating Actions
  • Using a List Data Region
  • Creating a Tablix Report
  • Adding Chart Subreport to Parent Report

After completing this module, students will be able to:

  • Create reports with interactive navigation.
  • Display data in various formats.

Module 4: Publishing and Executing Reports

The students will learn the various options you can use to publish reports to the report server and execute them.

Lessons

  • Publishing Reports
  • Executing Reports
  • Creating Cached Instances
  • Creating Snapshots and Report History

Lab: Publishing and Executing Reports

  • Publishing Reports
  • Executing Reports
  • Configuring and Viewing a Cached Report
  • Configuring and Viewing a Snapshot Report

After completing this module, students will be able to:

  • Publish reports.
  • Execute reports.
  • Create cached instances.
  • Create snapshots and report history.

Module 5: Using Subscriptions to Distribute Reports

The students will learn how to implement subscriptions so that you can distribute reports either automatically by e-mail or by publishing reports to a shared folder.

Lessons

  • Introduction to Report Subscriptions
  • Creating Report Subscriptions
  • Managing Report Subscriptions

Lab: Using Subscriptions to Distribute Reports

  • Creating a Standard Subscription

After completing this module, students will be able to:

  • Describe report subscriptions.
  • Create report subscriptions.
  • Manage report subscriptions.

Module 6: Administering Reporting Services

The students will learn how to administer the Reporting Services server, how to monitor and optimize the performance of the report server, how to maintain the Reporting Services databases, and how to keep the system secure.

Lessons

  • Reporting Server Administration
  • Performance and Reliability Monitoring
  • Administering Report Server Databases
  • Security Administration
  • Upgrading to Reporting Services 2012

Lab: Administering Reporting Services

  • Using Reporting Services Configuration Manager
  • Securing a Reporting Services Site
  • Securing Items

After completing this module, students will be able to:

  • Administer the reporting server.
  • Monitor performance and reliability.
  • Administer the Report Server databases.
  • Administer security.
  • Upgrade to Reporting Services 2012.

MDX Queries

  • Basics of MDX
    • Introduction to Axis and similarity to Geometry.
    • How MDX is different from SQL statements and how we should approach the same.
    • Rows, Columns and Pages
    • Non empty keyword and how it helps performance
    • Attribute relations in SSAS and how it affects MDX syntax and why it is important to get it right.
  • Working with Sets
  • Working with Calculations
  • Working with Hierarchical Calculations
  • Working with Time bases calculations like QTD, YTD. Also would cover what is required at the OLAP layer to help these calculations.
  • Deploying calculations.

Testimonials

 “Its been a really great experience having taken up the SQL server course at SolWin. The best part is knowing that you are going to learn something new everyday and it is exciting.” Harikumar KV

“SQL was initially something I feared of learning and I wasn’t sure of anything much in this stream. The traning sessions held at SolWin enabled me to handle the learning process pretty easily.” Tejas Bhatt