MCSE Business Intelligence

Microsoft 70-461: Querying SQL Server 2012

Module 1

• 1.1 Introduction to SQL Server 2012
• 1.2 SQL Instances
• 1.3 Editions and Licensing
• 1.4 SQL Server Objects
• 1.5 System Databases
• 1.6 SQL Server Data Tools
• 1.7 Object Explorer
• 1.8 Object Scripting
• 1.9 Template Explorer
• 1.10 Query Tabs
• 1.11 Projects and Solutions
• 1.12 Customizing SSDT
• 1.13 Debugging T-SQL
• 1.14 SQLCMD
• 1.15 Exam Scenarios
• 1.16 Module Review

Module 2

• 2.1 T-SQL Overview
• 2.2 T-SQL Elements
• 2.3 Batch Separators
• 2.4 Line Terminator
• 2.5 Variables
• 2.6 Control Flow
• 2.7 Expressions
• 2.8 Functions
• 2.9 Predicates and Operators
• 2.10 SELECT
• 2.11 Column Aliasing
• 2.12 SELECT Execution
• 2.13 Exam Scenarios
• 2.14 Module Review

Module 3

• 3.1 SELECT Statements
• 3.2 Basic SELECT Statement
• 3.3 Specifying Columns
• 3.4 Derived Columns
• 3.5 Aliasing Columns
• 3.6 Calculated Columns
• 3.7 Ordering Results
• 3.8 Returning Distinct Results
• 3.9 Exam Scenarios
• 3.10 Module Review

Module 4

• 4.1 Filtering
• 4.2 WHERE Syntax
• 4.3 BETWEEN
• 4.4 WHERE Syntax IN
• 4.5 Filtering NULL Values
• 4.6 LIKE
• 4.7 Data Type Comparison
• 4.8 Filtering Data with WHERE
• 4.9 TOP
• 4.10 OFFSET-FETCH
• 4.11 Exam Scenarios
• 4.12 Module Review

Module 5

• 5.1 Join and Set Operators
• 5.2 Using Joins
• 5.3 Categories of Joins
• 5.4 INNER JOIN
• 5.5 OUTER JOIN
• 5.6 JOIN Demo
• 5.7 Set Operators
• 5.8 Set Operators Demo
• 5.9 Exam Scenarios
• 5.10 Module Review

Module 6

• 6.1 Subqueries and Table Expressions
• 6.2 Subqueries
• 6.3 Self Contained Subquery
• 6.4 Correlated Subquery
• 6.5 Subquery Demo
• 6.6 EXISTS
• 6.7 Common Table Expressions
• 6.8 Temporary Tables
• 6.9 Table Variables
• 6.10 Table Demo
• 6.11 Exam Scenarios
• 6.12 Module Review

Module 7

• 7.1 System Functions
• 7.2 Scalar Functions
• 7.3 ROWSET
• 7.4 System Function Demo
• 7.5 Aggregate Functions
• 7.6 DISTINCT
• 7.7 Aggregation Demo
• 7.8 GROUP BY
• 7.9 HAVING
• 7.10 Conversion Functions
• 7.11 Exam Scenarios
• 7.12 Module Review

Module 8

• 8.1 T-SQL Programming
• 8.2 Logical Functions
• 8.3 IIF, CHOOSE and CASE
• 8.4 Logical Function Demo
• 8.5 Using Views
• 8.6 Stored Procedures
• 8.7 Functions
• 8.8 Triggers
• 8.9 Views Demo
• 8.10 TRY CATCH
• 8.11 Exam Scenarios
• 8.12 Module Review

Module 9

• 9.1 Transactions
• 9.2 Introduction to Transactions
• 9.3 INSERT and UPDATE
• 9.4 DELETE and TRUNCATE
• 9.5 Transactions Demo
• 9.6 Managing Transactions
• 9.7 XACT_ABORT
• 9.8 Managing Transactions Demo
• 9.10 Exam Scenarios
• 9.11 Module Review

Module 10

• 10.1 Query Performance
• 10.2 Indexing
• 10.3 Index Strategies
• 10.4 Indexes Demo
• 10.5 Execution Plans
• 10.6 Statistics IO
• 10.7 Execution Plans Demo
• 10.8 Exam Scenarios
• 10.9 Module Review
• 10.10 Program Review

Module 1

• 1.1 Introduction to SQL Server 2012
• 1.2 SQL Instances
• 1.3 Editions and Licensing
• 1.4 SQL Server Objects
• 1.5 System Databases
• 1.6 SQL Server Data Tools
• 1.7 Object Explorer
• 1.8 Object Scripting
• 1.9 Template Explorer
• 1.10 Query Tabs
• 1.11 Projects and Solutions
• 1.12 Customizing SSDT
• 1.13 Debugging T-SQL
• 1.14 SQLCMD
• 1.15 Exam Scenarios
• 1.16 Module Review

Module 2

• 2.1 T-SQL Overview
• 2.2 T-SQL Elements
• 2.3 Batch Separators
• 2.4 Line Terminator
• 2.5 Variables
• 2.6 Control Flow
• 2.7 Expressions
• 2.8 Functions
• 2.9 Predicates and Operators
• 2.10 SELECT
• 2.11 Column Aliasing
• 2.12 SELECT Execution
• 2.13 Exam Scenarios
• 2.14 Module Review

Module 3

• 3.1 SELECT Statements
• 3.2 Basic SELECT Statement
• 3.3 Specifying Columns
• 3.4 Derived Columns
• 3.5 Aliasing Columns
• 3.6 Calculated Columns
• 3.7 Ordering Results
• 3.8 Returning Distinct Results
• 3.9 Exam Scenarios
• 3.10 Module Review

Module 4

• 4.1 Filtering
• 4.2 WHERE Syntax
• 4.3 BETWEEN
• 4.4 WHERE Syntax IN
• 4.5 Filtering NULL Values
• 4.6 LIKE
• 4.7 Data Type Comparison
• 4.8 Filtering Data with WHERE
• 4.9 TOP
• 4.10 OFFSET-FETCH
• 4.11 Exam Scenarios
• 4.12 Module Review

Module 5

• 5.1 Join and Set Operators
• 5.2 Using Joins
• 5.3 Categories of Joins
• 5.4 INNER JOIN
• 5.5 OUTER JOIN
• 5.6 JOIN Demo
• 5.7 Set Operators
• 5.8 Set Operators Demo
• 5.9 Exam Scenarios
• 5.10 Module Review

Module 6

• 6.1 Subqueries and Table Expressions
• 6.2 Subqueries
• 6.3 Self Contained Subquery
• 6.4 Correlated Subquery
• 6.5 Subquery Demo
• 6.6 EXISTS
• 6.7 Common Table Expressions
• 6.8 Temporary Tables
• 6.9 Table Variables
• 6.10 Table Demo
• 6.11 Exam Scenarios
• 6.12 Module Review

Module 1: Introduction to Data Warehouses

• 1.1 Overview of Data Warehouses
• 1.2 Examine Data Warehousing Concepts
• 1.3 Introduction to Data Analysis and Reporting Concepts

Module 2: Creating Dimensions and Changing Granularity of Dimensions

• 2.1 Create Dimensions
• 2.2 Change Granularity

Module 3: Creating Fact Tables and Column store Indexes

• 3.1 Create Fact Tables
• 3.2 Create Column store Indexes

Module 4: Implementing Data Warehouses in SQL Server 2012

• 4.1 Overview of Data Warehouse Components
• 4.2 Install Integration Services

Module 5: Working with Integration Services

• 5.1 Create an SSIS Project
• 5.2 Add Packages to an SSIS Project

Module 6: Managing Control Flow

• 6.1 Deploy a Control Flow
• 6.2 Execute Packages from Different Control Flow

Module 7: Working with Dynamic Variables

• 7.1 Create Expressions with Variables
• 7.2 Create Package Templates

Module 8: Implementing Data Flow

• 8.1 Add Data Flow Components to a Data Flow Task
• 8.2 Perform Transformation Tasks
• 8.3 Track Changed Data

Module 9: Managing Data Flow

• 9.1 Add Script Tasks and Script Components to an SSIS Package
• 9.2 Profile OLTP Data
• 9.3 Cleanse Data Using DQS

Module 10: Managing SSIS Package Execution

• 10.1 Execute SSIS Packages on the Server
• 10.2 Configure the Package Execution Strategy

Module 11: Debugging and Troubleshooting Packages

• 11.1 Debug Package Execution Components
• 11.2 Troubleshoot Package Failures

Module 12: Deploying Packages

• 12.1 Deploy SSIS Packages
• 12.2 Examine MSI Packages

Module 13: Securing Packages and Databases

• 13.1 Secure Packages
• 13.2 Secure Databases

Module 14: Working with MDS and Windows Azure

• 14.1 Configure MDS
• 14.2 Work with MDS Databases
• 14.3 Work with Windows Azure

Module 1: Introduction to Business Intelligence and Data Modeling

• 1.1 Introduction to Business Intelligence and Data Modeling Part 1
• 1.2 Introduction to Business Intelligence and Data Modeling Part 2
• 1.3 The Microsoft Business Intelligence Platform Part 1
• 1.4 The Microsoft Business Intelligence Platform Part 2
• 1.5 The Microsoft Business Intelligence Platform Part 3
• 1.6 The Microsoft Business Intelligence Platform Part 4

Module 2: Implementing Reports The SQL Server Reporting Services

• 2.1 Implementing Reports the SQL Server Reporting Services Part 1
• 2.2 Implementing Reports the SQL Server Reporting Services Part 2
• 2.3 Implementing Reports the SQL Server Reporting Services Part 3
• 2.4 Implementing Reports the SQL Server Reporting Services Part 4
• 2.5 Implementing Reports the SQL Server Reporting Services Part 5
• 2.6 Implementing Reports the SQL Server Reporting Services Part 6
• 2.7 Implementing Reports the SQL Server Reporting Services Part 7
• 2.8 Implementing Reports the SQL Server Reporting Services Part 8

Module 3: Supporting Self Service Reporting

• 3.1 Supporting Self Service Reporting Part 1
• 3.2 Supporting Self Service Reporting Part 2

Module 4: Managing Report Execution and Delivery

• 4.1 Managing Report Execution and Delivery
• 4.2 Managing Report Execution
• 4.3 Subscriptions and Data Alerts

Module 5: Creating Multidimensional Databases

• 5.1 Creating Multidimensional Databases
• 5.2 Creating Data Sources and Data Source Views
• 5.3 Creating and Browsing a Cube

Module 6: Working with Cubes and Dimensions

• 6.1 Working with Cubes and Dimensions
• 6.2 Sorting and Grouping Attributes

Module 7: Working with Measures and Measure Groups

• 7.1 Working with Measures and Measure Groups

Module 8: Introduction to MDX

• 8.1 Introduction to MDX
• 8.2 Adding Calculations to A Cube

Module 9: Customizing Cube Functionality

• 9.1 Customizing Cube Functionality Part 1
• 9.2 Customizing Cube Functionality Part 2
• 9.3 Customizing Cube Functionality Part 3

Module 10: Implementing A Tabular Data Model

• 10.1 Implementing A Tabular Data Model Part 1
• 10.2 Implementing A Tabular Data Model Part 2

Module 11: Introduction to DAX

• 11.1 Introduction to DAX
• 11.2 Demonstration Making a Tabular Model Part 1
• 11.3 Demonstration Making a Tabular Model Part 2
• 11.4 Using DAX

Module 12: Implementing an Analysis Services Tabular Data Model

• 12.1 Implementing an Analysis Services Tabular Data Model Part 1
• 12.2 Implementing an Analysis Services Tabular Data Model Part 2
• 12.3 Deploying A Tabular Data Model

Module 13: SQL Analysis Services

• 13.1 SQL Analysis Services Part 1
• 13.2 SQL Analysis Services Part 2

Module 14: Creating Data Visualizations with Power View

• 14.1 Creating Data Visualizations with Power View

Module 15: Supporting Self Service Reporting

• 15.1 Supporting Self Service Reporting

Module 16: Performing Predictive Analysis with Data Mining

• 16.1 Performing Predictive Analysis with Data Mining
• 16.2 Using the Data Mining Wizard

Module 17: Predictive Analytics

• 17.1 Predictive Analytics Part 1
• 17.2 Predictive Analytics Part 2

Module 1: Intro to Business Intelligence

• 1.1 The Business Intelligence Solution
• 1.2 Overview of Business Intelligence
• 1.3 BI and Power Pivot Demo
• 1.4 ETL Intro
• 1.5 Designing Data Warehouses
• 1.6 Design for Speed
• 1.7 BI Semantic Model

Module 2: Database Essentials

• 2.1 Basic Database Objects Demo
• 2.2 Stored Procedures and Triggers Demo
• 2.3 Database Clusters Demo
• 2.4 SQL Server Management
• 2.5 Table Triggers
• 2.6 DB Configuring Options
• 2.7 DB Backup
• 2.8 DB Files and Restore

Module 3: Business Intelligence Development

• 3.1 Using SQL Server Data Tools Demo
• 3.2 Building an IS Integration Services Package Demo
• 3.3 Loading Data from Remote Sources Demo
• 3.4 Processing Data from Web Service Demo

Module 4: Business Intelligence Results

• 4.1 Report Building Intro Setup Demo
• 4.2 Using Report Builder Demo
• 4.3 Reporting from Multiple Tables Demo
• 4.4 Report Function and Matrix Demo
• 4.5 Interactive Reports Demo
• 4.6 Sorting Report Data Demo

Module 5: Advanced Reporting and Development

• 5.1 Drill Through Reports
• 5.2 Data Visualization
• 5.3 Creating Store Procedure from Scratch
• 5.4 Memory Optimize Table

Module 6: SQL Server Integration Services (SSIS)

• 6.1 Building Our First Integration Project
• 6.2 Data Transformation
• 6.3 Introduction to Big Data
• 6.4 Transformation Using Store Procedure
• 6.5 Transformation Using SSIS
• 6.6 Building Fact Table Using Store Procedure
• 6.7 Building Fact Table Using SSIS

Module 7: SQL Server 2014 New Features

• 7.1 Backup Enhancements
• 7.2 Index Enhancements
• 7.3 Online DB Enhancements
• 7.4 Back Up to The Cloud
• 7.5 Partition Data

Module 8: Course Review and Exam Prep

• 8.1 BI Review
• 8.2 SharePoint Overview
• 8.3 SharePoint Demo
• 8.4 Exam Preparation
• 8.5 Before You Take the Exam