MCSE: Data Management and Analytics Course overview
Earning an MCSE:Data Management and Analytics certification qualifies you for a position as a network or computer systems administrator or as a computer network specialist, and it is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).
Developing Microsoft SQL Server Databases (20464)
Designing Database Solutions for SQL Server (20465)
Implementing Data Models and Reports with Microsoft SQL Server (20466)
Designing Business Intelligence Solutions With Microsoft SQL Server (20467)
COURSES TO COMPLETE THIS CERTIFICATION
Module 1: Introduction to Database Development
Lessons
• Introduction to the SQL Server Platform
• SQL Server Database Development Tasks
Lab: Introduction to Database Development
Module 2: Designing and Implementing Tables
Lessons
• Designing Tables
• Data Types
• Working with Schemas
• Creating and Altering Tables
• Partitioning Data
• Compressing Data
Lab: Designing and Implementing Tables
Module 3: Ensuring Data Integrity through Constraints
Lessons
• Enforcing Data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity
Lab: Ensuring Data Integrity through Constraints
Module 4: Introduction to Indexing
Lessons
• Core Indexing Concepts
• Data Types and Indexes
• Single Column and Composite Indexes
Lab: Implementing Indexes
Module 5: Designing Optimized Index Strategies
Lessons
• Covering Indexes
• Managing Indexes
• Working with Execution Plans
• Using the DTE
Lab: Designing Optimized Index Strategies
Module 6: Columnstore Indexes
Lessons
• Introduction to Columnstore indexes
• Creating Columnstore Indexes
• Working with Columnstore Indexes
Lab: Using Columnstore Indexes
Module 7: Designing and Implementing Views
Lessons
• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views
Lab: Designing and Implementing Views
Module 8: Designing and Implementing Stored Procedures
Lessons
• Introduction to Stored Procedures
• Working With Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context
Lab: Designing and Implementing Stored Procedures
Module 9: Designing and Implementing User-Defined Functions
Lessons
• Overview of Functions
• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Implementation Considerations for Functions
• Alternatives to Functions
Lab: Designing and Implementing User-Defined Functions
Module 10: Responding to Data Manipulation via Triggers
Lessons
• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts
Lab: Responding to Data Manipulation via Triggers
Module 11: Using In-Memory Tables
Lessons
• In-Memory Tables
• Native Stored Procedures
Lab: In-Memory OLTP
Module 12: Implementing Managed Code in SQL Server 2014
Lessons
• Introduction to SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration
Lab: Implementing Managed Code in SQL Server 2014
Module 13: Storing and Querying XML Data in SQL Server
Lessons
• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the T-SQL FOR XML Statement
• Getting Started with XQuery
Lab: Storing and Querying XML Data in SQL Server 2014
Module 14: Working with SQL Server 2014 Spatial Data
Lessons
• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications
Lab: Working with SQL Server Spatial Data
Module 15: Incorporating Data Files into Databases
Lessons
• Querying Data with Stored Procedures
• Implementing FILESTREAM and File Tables
• Searching Data Files
Lab: Implementing a Solution for Storing Data Files
Module 1: Introduction to Enterprise Data Architecture
As organizations grow to enterprise scale, their IT infrastructure requirements become more complex and the network environment often includes an increasing number of servers, client computers, network segments, and other components. Because data is fundamental to most IT operations, careful thought must be given to the provisioning and management of databases across the enterprise.
Lessons
- Considerations for Enterprise Data
- Assessing an Existing Infrastructure
Lab : Assessing an Existing Enterprise Data Infrastructure
After completing this module, you will be able to:
- Describe the considerations for enterprise data infrastructure.
- Use the MAP Toolkit to assess an existing enterprise data environment.
Module 2: Multi-Server Configuration Management
When an enterprise infrastructure includes multiple database servers, it can be useful to standardize and enforce configuration settings in order to ensure compliance and manageability. This module discusses Policy-Based Management in SQL Server, and describes how it can be used together with enterprise configuration management tools such as Microsoft System Center to aid enterprise database server management.
Lessons
- Policy-Based Management
- Microsoft System Center
Lab : Planning and Implementing Policy-Based Management
After completing this module, you will be able to:
- Implement Policy-Based Management
- Describe how System Center can be used to manage database infrastructure
Module 3: Monitoring SQL Server 2014 Health
This module describes Data Collector and the SQL Server Utility Control Point (UCP), two features of SQL Server 2014 that enable you to perform in-depth health monitoring across the enterprise.
Lessons
- Introduction to Health Monitoring
- Data Collector
- SQL Server Utility
Lab : Monitoring SQL Server Health
After completing this module, you will be able to:
- Describe the options for multi-server health monitoring in SQL Server 2014.
- Describe and configure Data Collector.
- Describe and configure SQL Server Utility.
Module 4: Consolidating Database Workloads with SQL Server 2014
This module provides an overview of the benefits of consolidating database workloads by using SQL Server 2014, and describes the different options for implementing a consolidation strategy. It also describes how you can manage a consolidated infrastructure in various scenarios.
Lessons
- Considerations for Database Server Consolidation
- Managing Resources in a Consolidated Database Infrastructure
Lab : SQL Server Consolidation
After completing this module, you will be able to:
- Describe the considerations for consolidating databases and database servers.
- Explain the options for managing resources in various consolidation scenarios.
Module 5: Introduction to Cloud Data Solutions
Cloud computing has risen to prominence very rapidly within the world of IT, and many organizations have implemented or are planning to implement cloud-based solutions that encompass all or part of their infrastructure. This module describes some of the fundamental concepts of cloud computing and outlines how to include SQL Server 2014 in a private cloud infrastructure.
Lessons
- Overview of Cloud Computing
- SQL Server in a Private Cloud
Lab : Preparing a SQL Server Installation in a Virtual Machine Template
After completing this module, you will be able to:
- Explain the fundamental concepts behind cloud computing, and describe the technologies that underpin Microsoft cloud solutions.
- Describe how to provide SQL Server based data services in a private cloud infrastructure.
Module 6: Introduction to High Availability in SQL Server 2014
Maintaining highly available database services is vital in a 24 hour operating environment. SQL Server 2014 includes many features that can help organizations to deliver the levels of service they require to drive their businesses. This module explains the different ways that you can implement high availability by using SQL Server 2014. It also describes how to use log shipping to promote resilience for individual user databases.
Lessons
- High Availability Concepts and Options in SQL Server 2014
- Log Shipping
Lab : Using Log Shipping
After completing this module, you will be able to:
- Describe the core concepts and options for implementing high availability in SQL Server 2014.
- Describe how to implement high availability for individual databases by using log shipping.
Module 7: Clustering with Windows Server and SQL Server 2014
SQL Server 2014 is closely integrated with the Windows Server Failover Clustering feature in Windows Server 2012 and Windows Server 2012 R2, enabling you to create enterprise-class clustering solutions that can deliver comprehensive high availability and disaster recovery solutions. This module explains how Windows Server Failover Clustering and SQL Server AlwaysOn Failover Cluster Instances work, and describes how to implement clustering to protect service availability.
Lessons
- Introduction to Windows Server Failover Clustering
- SQL Server AlwaysOn Failover Cluster Instances
Lab : Implementing an AlwaysOn Failover Cluster Instance
After completing this module, you will be able to:
- Describe the key benefits and features of Windows Server Failover Clustering.
- Describe how to use SQL Server AlwaysOn Failover Cluster Instances to maintain high availability for SQL Server instances.
Module 8: AlwaysOn Availability Groups
SQL Server 2014 includes AlwaysOn Availability Groups to provide high availability for groups of databases. This module describes AlwaysOn Availability Groups in SQL Server 2014, explains the key concepts of AlwaysOn Availability Groups, and describes how you can use them to maintain highly available databases.
Lessons
- Introduction to AlwaysOn Availability Groups
- Working with AlwaysOn Availability Groups
- Considerations for Using AlwaysOn Availability Groups
Lab : Implementing and Testing an AlwaysOn Availability Group
After completing this module, you will be able to:
- Describe the fundamental concepts and terminology for AlwaysOn Availability Groups.
- Explain how work with AlwaysOn Availability Groups.
Module 9: Planning High Availability and Disaster Recovery
This module describes the planning considerations for high availability and disaster recovery, and provides common implementation scenarios for on-premises, hybrid, and Microsoft Azure environments.
Lessons
- High Availability and Disaster Recovery with SQL Server 2014
- SQL Server High Availability and Disaster Recovery Solutions
Lab : Planning High Availability and Disaster Recovery
After completing this module, you will be able to:
- Explain the considerations for implementing high availability and disaster recovery by using SQL Server 2014, and describe some common scenarios.
- Explain the considerations for implementing high availability and disaster recovery by using SQL Server 2014 and Microsoft Azure services, and describe some common scenarios.
Module 10: Replicating Data
SQL Server replication enables you to copy and distribute data and database objects to other computers and locations in your enterprise, which can improve availability and scalability. This module provides an overview of SQL Server replication and explains the agents used to implement replication. It also describes some common replication scenarios, how to design an appropriate replication system for your requirements, and how to monitor and troubleshoot replication.
Lessons
- SQL Server Replication
- Planning Replication
Lab : Planning and Implementing Replication
After completing this module, you will be able to:
- Describe SQL Server replication.
- Identify an appropriate replication solution for a particular scenario.
Module 1: Introduction to Business Intelligence and Data Modeling
As a SQL Server database professional, you may be required to participate in, or perhaps even lead, a project with the aim of implementing an effective enterprise BI solution. Therefore, it is important that you have a good understanding of the various elements that comprise a BI solution, the business and IT personnel typically involved in a BI project, and the Microsoft products that you can use to implement the solution.
Lessons
- Introduction to Business Intelligence
- The Microsoft Enterprise BI Platform
Lab : Exploring a BI Solution
After completing this module, you will be able to:
- Describe the elements of a typical BI solution.
- Select appropriate Microsoft technologies for a BI solution.
- Describe key considerations for planning a BI project.
Module 2: Creating Multidimensional Databases
This module provides an introduction to multidimensional databases and introduces the core components of an Online Analytical Processing (OLAP) cube.
Lessons
- Introduction to Multidimensional Analysis
- Creating Data Sources and Data Source Views
- Creating a Cube
- Overview of Cube Security
Lab : Creating a Multidimensional Database
After completing this module, you will be able to:
- Describe the considerations for a multidimensional database.
- Create data sources and data source views.
- Create a cube.
- Implement security in a multidimensional database.
Module 3: Working with Cubes and Dimensions
This module describes how to create and configure dimensions and dimension hierarchies in an Analysis Services multidimensional data model.
Lessons
- Configuring Dimensions
- Defining Attribute Hierarchies
- Sorting and Grouping Hierarchies
Lab : Working with Cubes and Dimensions
After completing this module, you will be able to:
- Configure dimensions.
- Define attribute hierarchies.
- Sort and group attributes.
Module 4: Working with Measures and Measure Groups
This module describes measures and measure groups. It also explains how you can use them to define fact tables and associate dimensions with measures.
Lessons
- Working with Measures
- Working with Measure Groups
Lab : Configuring Measures and Measure Groups
After completing this module, you will be able to:
- Configure measures.
- Configure measure groups.
Module 5: Introduction to MDX
This module describes the fundamentals of MDX and explains how to build calculations, such as calculated members and named sets.
Lessons
- MDX Fundamentals
- Adding Calculations to a Cube
- Using MDX to Query a Cube
Lab : Using MDX
After completing this module, you will be able to:
- Describe MDX.
- Add calculations to a cube.
- Describe how to use MDX in client applications.
Module 6: Customizing Cube Functionality
This module describes how to enhance a cube with Key Performance Indicators (KPIs), actions, perspectives, and translations.
Lessons
- Implementing Key Performance Indicators
- Implementing Actions
- Implementing Perspectives
- Implementing Translations
Lab : Customizing a Cube
After completing this module, you will be able to:
- Implement Key Performance Indicators.
- Implement Actions.
- Implement Perspectives.
- Implement Translations.
Module 7: Implementing an Analysis Services Tabular Data Model
This module describes Analysis Services tabular data models and explains how to develop a tabular data model using the SQL Server Data Tools for Business Intelligence (BI) add-in for Visual Studio.
Lessons
- Introduction to Tabular Data Models
- Creating a Tabular Data Model
- Using an Analysis Services Tabular Data Model in an Enterprise BI Solution
Lab : Implementing an Analysis Services Tabular Data Model
After completing this module, you will be able to:
- Describe Analysis Services tabular data model projects.
- Implement an Analysis Services tabular data model.
- Use an Analysis Services tabular data model.
Module 8: Introduction to Data Analysis Expression (DAX)
This module explains the fundamentals of the DAX language. It also explains how you can use DAX to create calculated columns and measures, and how you can use them in your tabular data models.
Lessons
- DAX Fundamentals
- Using DAX to Create calculated Columns and Measures in a Tabular Data Model
Lab : Creating Calculated Columns and Measures by using DAX
After completing this module, you will be able to:
- Describe the fundamentals of DAX.
- Use DAX to create calculated columns and measures.
Module 9: Implementing Reports with SQL Server Reporting Services
This module introduces Microsoft SQL Server Reporting Services and discusses the tools and techniques that a professional BI developer can use to create and publish reports.
Lessons
- Introduction to Reporting Services
- Creating a Report with Report Designer
- Grouping and Aggregating Data in a Report
- Showing Data Graphically
- Filtering Reports Using Parameters
Lab : Creating a Report with Report Designer
After completing this module, you will be able to:
- Describe the key features of Reporting Services.
- Use Report Designer to create a report.
- Group and aggregate data in a report.
- Publish and view a report.
Module 10: Automating Report Execution and Delivery
This module describes how to apply security and report execution settings, and how to create subscriptions to deliver reports.
Lessons
- Managing Report Security
- Managing Report Execution
- Delivering Reports with Subscriptions and Data Alerts
- Troubleshooting Reporting Services
Lab : Implementing Report Subscriptions
After completing this module, you will be able to:
- Configure security settings for a report server.
- Configure report execution settings to optimize performance.
- Use subscriptions and alerts to automate report and data delivery.
- Troubleshoot reporting issues.
Module 11: Delivering BI with SharePoint PerformancePoint Services
This module introduces Microsoft SharePoint Server as a platform for BI, and then focuses on building BI dashboards and scorecards with PerformancePoint Services.
Lessons
- Introduction to SharePoint Server as a BI Platform
- Planning Security for a SharePoint Server BI Solution
- Planning for PerformancePoint Services
Lab : Implementing PerformancePoint Services
After completing this module, you will be able to:
- Describe SharePoint Server as a BI platform.
- Use PerformancePoint Services to deliver BI functionality.
- Configure PerformancePoint Data Sources.
- Create Reports, Scorecards, and Dashboards.
Module 12: Performing Predictive Analysis with Data Mining
This module introduces data mining, describes how to create a data mining solution, how to validate data mining models, how to use the Data Mining Add-ins for Microsoft Excel, and how to incorporate data mining results into Reporting Services reports.
Lessons
- Overview of Data Mining
- Using the Data Mining Add-in for Excel
- Creating a Custom Data Mining Solution
- Validating a Data Mining Model
- Connecting to and Consuming Data Mining Data
Lab : Using Data Mining to Support a Marketing Campaign
After completing this module, you will be able to:
- Describe the key data mining concepts and use the Data Mining Add-ins for Excel.
- Create a data mining solution.
- Validate data mining models.
- Use data mining data in a report.
Module 1: Planning a BI Solution
This module enables students to plan the components of a BI Solution.
Lessons
- Elements of a BI Solution
- Planning a BI Project
- The Microsoft BI Platform
Lab : Planning BI Solutions
After completing this module, you will be able to:
- Describe the components of a BI Solution.
- Identify the components of a BI Solution.
- Plan a BI Project.
Module 2: Planning SQL Server Business Intelligence Infrastructure
This module describes how to use plan appropriate BI Infrastructure given a set of BI Parameters.
Lessons
- Considerations of BI Infrastructure
- Planning Data Warehouse Hardware
Lab : Planning BI Infrastructure
After completing this module, you will be able to:
- Describe the components of a BI Infrastructure and where you would use them.
- Plan for the hardware requirements of a data warehouse implementation.
Module 3: Designing a Data Warehouse
This module describes how to design a data warehouse given a set of user requirements.
Lessons
- Data Warehouse Design Overview
- Designing Dimension Tables
- Design FACT tables
Lab : Designing a Data Warehouse Logical Schema
After completing this module, you will be able to:
- Design a data warehouse.
- Design dimension tables.
- Design Fact tables.
- Design a logical schema for a data warehouse.
Module 4: Designing an ETL solution
This lesson describes how to design an extract, transform and load (ETL) solution.
Lessons
- ETL Overview
- Planning for Data Extraction
- Planning for Data Transformations
- Planning for Data Loading
Lab : Designing an ETL Solution
After completing this module, you will be able to:
- Describe the components of an ETL solution.
- Design an ETL solution.
Module 5: Designing Analytical Data Models
This module describes how to design analytical data models for specific BI scenarios.
Lessons
- Introduction to Analytical data Models
- Designing Analytical Data Models
Lab : Designing Analytical Data ModelLab : Designing Dimensions and Hierarchies
After completing this module, you will be able to:
- Describe the features of an analytical data model.
- Design an analytical data model.
Module 6: Planning a BI Delivery Solution
This module describes how to choose an appropriate delivery solution for a given scenario.
Lessons
- Considerations for BI delivery
- Common Reporting Scenarios
- Choosing a Reporting Tool
Lab : Planning a BI Delivery Solution
After completing this module, you will be able to:
- Describe the components of a BI delivery solution.
- Describe some common reporting scenarios.
- Choose an appropriate delivery solution for a given scenario.
Module 7: Designing a Reporting Services Solution
This module describes how to design a reporting services solution.
Lessons
- Planning a Reporting Solution
- Designing Reports
- Planning Report Consistency
Lab : Designing a Reporting Services Solution
After completing this module, you will be able to:
- Plan for a reporting Services solution.
- Design reports for a reporting services solutions.
- Plan for consistency of reporting
Module 8: Designing an Excel Based reporting Solution
In this module students will learn how to design a reporting solution using Excel.
Lessons
- Using Excel for Data Reporting and Analysis
- PowerPivot in Excel
- Power View in Excel
Lab : Designing and Excel Reporting Solution
After completing this module, you will be able to:
- Describe key features of excel reporting solutions.
- Use PowerPivot in Excel to create reports.
- Use Power View in excel to create reports.
Module 9: Planning a SharePoint Server BI Solution
This module introduces the use of SharePoint Server as a BI solution.
Lessons
- Introduction to SharePoint Server as a BI Platform
- Planning Security for a SharePoint Server BI Solution
- Planning Reporting Services Configuration
- Planning PowerPivot Configuration
- Planning for PerformancePoint Services
Lab : Implementing a SharePoint Server BI SolutionLab : Implementing PerformancePoint Services
After completing this module, you will be able to:
- Describe the components of a SharePoint Server BI solution.
- Plan security for a SharePoint Server BI solution.
- Plan reporting solutions for a SharePoint Server BI solution.
- Describe the components of a PerformancePoint Solution.
- Implement PerformancePoint services.
Module 10: Monitoring and Optimizing a BI Solution
At the conclusion of this module you will be able to optimize and monitor a BI solution.
Lessons
- Overview of BI Monitoring
- Monitoring and Optimizing the Data Warehouse
- Monitoring and Analyzing Analysis Services
- Monitoring and Optimizing Reporting Services
Lab : Monitoring and Optimizing a BI Solution
After completing this module, you will be able to:
- Describe the options for monitoring and optimizing a BI solution.
- Be able to monitor and optimize a BI solution.
Module 11: Operating a BI Solution
At the conclusion of this module you will be able to plan management and maintenance operations of a BI solution.
Lessons
- Overview of BI Operations
- ETL Operations
- Data Warehouse Operations
- Analysis Services Operations
- Reporting Services Operations
Lab : Operating a BI Solution
After completing this module, you will be able to:
- Describe the components to be managed in a BI Solution.
- Be able to manage the components of a BI solution.
Join Over 10,000 Students that have studied with MasterGrade IT Now
Become Part of MasterGrade IT to Further Your Career.