This three-day instructor-led course provides existing SQL Server Business Intelligence (BI) professionals with the knowledge and skills to use new and enhanced BI capabilities in SQL Server 2014.
Lesson 1: Using the CDC Control Task for Incremental Data Loads
- Overview of Data Warehouse Load Cycles
- Options for Extracting Modified Data
- Change Data Capture
- The CDC Control Task and Data Flow Components
- Demonstration: Using the CDC Control Task
- Loading a Data Warehouse from CDC Output Tables
- Demonstration: Using CDC Output Tables
Lesson 2: Deploying and Operating SSIS Packages
- SSIS Deployment Models
- Package Deployment Model
- Project Deployment Model
- Deployment Model Comparison
- Creating an SSIS Catalog
- Environments and Variables
- Deploying an SSIS Project
- Viewing Project Execution Information
- Demonstration: Deploying an SSIS Project
Lab: Using SQL Server Integration Services
Lesson 1: Introduction to Data Quality
- What Is Data Quality, and Why Do You Need It?
- Data Quality Services Overview
- What Is a Knowledge Base?
- What Is a Domain?
- What Is a Reference Data Service?
- Creating a Knowledge Base
- Demonstration: Creating a Knowledge Base
Lesson 2: Using Data Quality Services to Cleanse Data
- Creating a Data Cleansing Project
- Viewing Cleansed Data
- Demonstration: Cleansing Data
- Using the Data Cleansing Data Flow Transformation
Lab A: Cleansing Data
Lesson 3: Using Data Quality Services to Match Data
- Creating a Matching Policy
- Creating a Data Matching Project
- Viewing Data Matching Results
- Demonstration: Matching Data
Lab B: Deduplicating Data
Lesson 1: Introduction to Master Data Services
- The Need for Master Data Management
- What Is Master Data Services?
- Master Data Services and Data Quality Services
- Components of Master Data Services
Lesson 2: Implementing a Master Data Services Model
- What Is a Master Data Services Model?
- Creating a Model
- Creating Entities and Attributes
- Adding and Editing Members
- Demonstration: Creating a Master Data Services Model
- Editing a Model in Microsoft Excel
- Demonstration: Editing a Model in Excel
Lesson 3: Managing Master Data
- Hierarchies and Collections
- Creating Derived Hierarchies
- Creating Explicit Hierarchies
- Creating Collections
- Finding Duplicate Members
- Validating Members with Business Rules
- Demonstration: Creating and Applying Business Rules
Lesson 4: Creating a Master Data Hub
- Master Data Hub Architecture
- Master Data Services Staging Tables
- Staging and Importing Data
- Demonstration: Importing Master Data
- Consuming Master Data with Subscription Views
- Demonstration: Using Subscription Views
Lab: Implementing Master Data Services
Lesson 1: Reporting Services Overview
- Reporting Scenarios
- New and Enhanced Features
- Self-Service Reporting
Lesson 2: Reporting Services and Microsoft SharePoint Server
- Enhancements to SharePoint Integration
- Installing Reporting Services in SharePoint Mode
- Configuring Reporting Services in SharePoint Mode
- Scaling Out Reporting Services in a SharePoint Farm
Lesson 3: Introduction to Data Alerts
- What Are Data Alerts?
- Creating Data Alerts
- Managing Data Alerts
- Demonstration: Creating and Managing a Data Alert
Lab: Using SQL Server 2014 Reporting Services
Lesson 1: Introduction to Analysis Services Tabular Data Model Projects
- What Is a Tabular Data Model?
- Options for Creating an Analysis Services Tabular Data Model Project
- Using Visual Studio to Develop an Analysis Services Tabular Data Model
- The Workspace Database
Lesson 2: Creating a Tabular Model
- Importing Tables
- Defining Measures
- Demonstration: Creating a Tabular Data Model Project
- Managing Relationships
- Configuring Columns
- Demonstration: Managing Relationships and Columns
- Creating Hierarchies
- Demonstration: Creating a Hierarchy
- Extending Data Models with DAX
- Demonstration: Using DAX Expressions
Lesson 3: Using an Analysis Services Tabular Data Model in the Enterprise
- Perspectives
- Partitions
- DirectQuery Mode
- Tabular Data Model Security
- Deploying a Tabular Data Model
- Demonstration: Deploying a Tabular Data Model
Lab: Working with an Analysis Services Tabular Data Model
Lesson 1: PowerPivot for Excel
- What Is PowerPivot for Excel?
- The PowerPivot Ribbon
- Importing Tables from a Data Source
- Demonstration: Creating a PowerPivot Workbook
- Refreshing Data
- Adding Linked and Non-Linked Tables
- Creating and Managing Table Relationships
- Hierarchies
- Demonstration: Creating a Hierarchy
- Using a PowerPivot Data Model for Analysis
- Demonstration: Analysing Data in a PowerPivot Data Model
Lesson 2: PowerPivot for SharePoint Server
- What Is PowerPivot for SharePoint Server?
- Sharing PowerPivot for Excel Workbooks
- The PowerPivot Gallery App
- Demonstration: Sharing a PowerPivot for Excel Workbook
- Configuring Data Refresh
- Monitoring PowerPivot in SharePoint
Lesson 3: Power Query
- What Is Power Query?
- Power Query Data Sources
- Creating a Query
- Power Query Expressions
- Combining Multiple Queries
- Demonstration: Using Power Query
Lesson 4: Power View
- What Is Power View?
- Power View Visualizations
- Power View Considerations for Data Models
- Power View for SharePoint Server
- Demonstration: Using Power View in SharePoint Server
- Power View for Excel
- Demonstration: Using Power View in Excel
Lesson 5: Power Map
- What Is Power Map?
- Power Map Tours, Scenes, and Layers
- Geographic Data Fields
- Data Aggregation Visualizations
- Displaying Data Aggregations Over Time
- Adding Embellishments
- Demonstration: Using Power Map
Lab: Self-Service BI with Excel
Lesson 1: Overview of Cloud Computing
- What is Cloud Computing?
- Cloud Services
- Types of Cloud
- Microsoft Cloud Technologies
Lesson 2: Microsoft Azure Services for Data
- Introduction to Microsoft Azure
- Microsoft Azure Storage
- Backing Up Databases to Microsoft Azure
- Storing Database Files in Microsoft Azure
- Using an AlwaysOn Availability Group Replica in Microsoft Azure for High Availability
Lab: Using Microsoft Azure
Lesson 1: Introduction to Microsoft Azure SQL Database
- What Is Microsoft Azure SQL Database?
- Microsoft Azure SQL Database vs. SQL Server
- Provisioning Microsoft Azure SQL Database
- Demonstration: Using Microsoft Azure SQL Database
Lesson 2: Microsoft Azure SQL Database Security
- Overview of Microsoft Azure SQL Database Security
- Managing Firewall Rules
- Managing Logins and Users
- Managing Role Membership and Permissions
- Demonstration: Applying Security
Lesson 3: Implementing and Managing Databases
- Tools for Microsoft Azure SQL Database
- Implementing a Database in Microsoft Azure SQL Database
- Migrating a SQL Server Database to Microsoft Azure SQL Database
- Self-Service Restore
Lab: Using Microsoft Azure SQL Database
Lesson 1: Introduction to Microsoft Azure Virtual Machines
- Benefits of Virtual Machines in Microsoft Azure
- Virtual Machine Disks and Images
- Microsoft Azure Service Topology
- Demonstration: Creating a Microsoft Azure Virtual Machine
Lesson 2: Microsoft Azure Virtual Machine Connectivity and Security
- Firewall and Endpoint Configuration
- Authentication and Authorization
- The SQL Server Cloud Adapter Service
- Demonstration: Configuring Connectivity and Security
Lesson 3: Creating Databases in a Microsoft Azure Virtual Machine
- Guidelines for Creating Databases
- Migrating Databases to a Microsoft Azure Virtual Machine
- The Deploy Database to a Microsoft Azure VM Wizard
Lab: Using Microsoft Azure Virtual Machines
Lesson 1: Introduction to Big Data
- What Is Big Data?
- What Is Hadoop?
- Introduction to Map/Reduce
- Microsoft Big Data Solutions
Lesson 2: Microsoft Azure HDInsight
- How to Provision an HDInsight Cluster
- Introduction to HDFS
- Running a Map/Reduce Job
- Processing Data with Pig
- Creating Hive Tables
- Loading Data Into Hive Tables
- Querying Hive Tables with HiveQL
Lesson 3: Consuming HDInsight Results in Excel
- Accessing HDInsight from Excel
- Using the Hive ODBC Provider
- Using Power Query with HDInsight
Lab: Creating a Big Data Solution
This course is intended for BI professionals with experience of using SQL Server 2008 R2.
- Use SQL Server 2014 Integration Services.
- Use SQL Server 2014 Data Quality Services.
- Use SQL Server 2014 Master Data Services.
- Manage SQL Server 2012 Agent and Jobs.
- Implement a tabular data model in SQL Server 2014 Analysis Services.
- Implement a Self-Service BI solution with Microsoft Excel 2013.
- Describe key elements of cloud solutions for data.
- Implement a database in Microsoft Azure SQL Database.
- Implement a SQL Server database in a Microsoft Azure virtual machine.
- Implement a Big Data solution with Microsoft Azure HDInsight and the Microsoft Azure Marketplace.
About Microsoft On-Demand Learning
Build your Microsoft technical expertise while balancing the demands of your schedule. The new MOC On-Demand series courses combine high-quality video lectures, live hands-on labs and knowledge-checks in a self-paced format to help you build skills as your schedule allows—all at once or five minutes at a time.
MOC On-Demand at a Glance
Self-Paced Learning
The modular structure offered by MOC On-Demand courses lets learners complete training piecemeal or in an order they choose.
Immediate Feedback on Mastery
Knowledge checks at the end of each module and each course tell learners whether they’ve mastered the content and are ready to move on.
SATV Eligible
Similar to In-class Microsoft official curriculum courses, MOC On-Demand is eligible for payment via Software Assurance Training Vouchers (SATVs)
Direct From the Source
MOC On-Demand is brought to you by the people who wrote the software. The content is based on the official Microsoft official curriculum (MOC).
Learn Anywhere Anytime
The high-quality in-depth video content, labs and knowledge checks are all available to you via the cloud 24 X 7.
Live Hands-on
Labs
Unlike other on-demand offerings that offer simulated labs, MOC On-Demand gives customers a live, real-time environment for hands-on training.
OLLAnywhere is a revolutionary way of utilising cutting-edge technology to allow students to attend live instructor-led training from anywhere in or outside of Australia.