MCSE: SQL 2012 Business Intelligence Boot Camp
Click Here to View Schedules & Pricing and to Enroll Online
Duration: 14 Days
Prove that you have the skills and techniques needed to design, build, and deploy solutions that deliver more data to more people across the organization. This 14-day boot camp provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This course also provides students with the knowledge and skills to maintain a Microsoft SQL Server 2012 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database. Data warehousing is a solution organizations use to centralize business data for reporting and analysis. This course focuses on teaching individuals how to create a data warehouse with SQL Server 2012, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services. Business intelligence (BI) is becoming increasingly important for companies of many different sizes and types because of the competitive edge it can help to give them. The combined capabilities of Microsoft SQL Server 2012 and Microsoft SharePoint 2010 make it easier than ever for companies to develop a BI solution that meets their specific needs, provides reduced total cost of ownership (TCO), and achieves a faster time to solution. This course teaches students how to empower information workers through self-service analytics and reporting. Students will learn how to implement multidimensional analysis solutions, create PowerPivot and tabular data models, deliver rich data visualizations with PowerView and SQL Server Reporting Services, and discover business insights by using data mining. This course teaches students how to design and implement a BI infrastructure. The course discusses design, installation. The course discusses design, installation, and maintenance of a BI platform.
Before attending this course, students must have:
•Working knowledge of relational databases.
•Basic knowledge of the Microsoft Windows operating system and its core functionality.
•Basic understanding of virtualization technology (Classroom labs utilize virtual machines)
•Basic knowledge of the Microsoft Windows operating system and its core functionality.
•Working knowledge of Transact-SQL.
•Some experience with database design.
•Designing a normalized database.
•Creating tables and relationships.
•Querying with Transact-SQL.
•Some exposure to basic programming constructs (such as looping and branching).
•An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
•Some basic knowledge of data warehouse schema topology (including star and snowflake schemas).
•A basic understanding of dimensional modeling (star schema) for data warehouses.
•The ability to create Integration Services packages that include control flows and data flows.
•The ability to create a basic multidimensional cube with Analysis Services.
•The ability to create a basic tabular model with PowerPivot and Analysis Services.
•The ability to create Reporting Services reports with Report Designer.
•The ability to implement authentication and permissions in the SQL Server database engine, Analysis Services, and Reporting Services.
•Familiarity with SharePoint Server and Microsoft Office applications, particularly Excel.
- Authorized Courseware
- Intensive Hands on Skills Development with an Experienced Subject Matter Expert
- Hands on practice on real Servers and extended lab support 1.800.482.3172
- Examination Vouchers & Onsite Certification Testing
- Academy Code of Honor: Test Pass Guarantee
- Optional: Package for Hotel Accommodations, Lunch and Transportation
The Academy is proud to be the #1 Microsoft Gold Certified Partner for Learning Solutions (CPLS) in Florida.
Introduction to Microsoft SQL Server 2012
This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.
Getting Started with SQL Azure
This module introduces you to the concepts of SQL Azure. If the virtual machines in your classroom are able to connect to the internet and you have a Windows Azure account you may be able to connect to your Azure server and database. Many of the labs in the rest of this course are enabled for you to perform the lab while connected to your own Azure database in the cloud.
Introduction to T-SQL Querying
This module introduces Transact SQL as the primary querying language of SQL Server. It discusses the basic structure of T-SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.
Writing SELECT Queries
This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.
Querying Multiple Tables
This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.
Sorting and Filtering Data
This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.
Working with SQL Server 2012 Data Types
This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.
Grouping and Aggregating Data
This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.
Programming with T-SQL
This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.
Improving Query Performance
This module introduces the concepts of system resource usage and the performance impact of querying SQL Server 2012. It will cover, at a high level, the use of indexes in SQL Server, the use of execution plans in SQL Server Management Studio, and the use of SET options to view system resource usage when executing queries. It will also compare set-based operations with cursor-based operations.
Working with Databases
This module describes how data is stored in databases, how to create databases, and how to move databases either within a server or between servers.
Understanding SQL Server 2012 Recovery Models
This module describes the concept of the transaction log and SQL Server recovery models. It introduces the different backup strategies available with SQL Server.
Backup of SQL Server 2012 Databases
This module describes SQL Server Backup and the backup types.
Authenticating and Authorizing Users
This module covers SQL Server security models, logins and users.
Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
Auditing SQL Server Environments
This module covers SQL Server Audit.
Automating SQL Server 2012 Management
This module covers SQL Server Agent, jobs and job history.
Configuring Security for SQL Server Agent
This module covers SQL Server agent security, proxy accounts and credentials.
Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Monitoring SQL Server 2012
This module introduces DMVs and the configuration of data collection.
Managing Multiple Servers
This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
Troubleshooting Common SQL Server 2012 Administrative Issues
This module covers common issues that require troubleshooting and gives guidance on where to start looking for solutions.
Data Warehouse Hardware
This module describes the characteristics of typical data warehouse workloads, and explains how you can use reference architectures and data warehouse appliances to ensure you build the system that is right for your organization.
Designing and Implementing a Data Warehouse
In this module, you will learn how to implement the logical and physical architecture of a data warehouse based on industry-proven design principles.
Creating an ETL Solution with SSIS
This module discusses considerations for implementing an ETL process, and then focuses on SQL Server Integration Services (SSIS) as a platform for building ETL solutions.
Debugging and Troubleshooting SSIS Packages
This module describes how you can debug SQL Server Integration Services (SSIS) packages to find the cause of errors that occur during execution. Then module then covers the logging functionality built into SSIS you can use to log events for troubleshooting purposes. Finally, the module describes common approaches for handling errors in control flow and data flow.
Implementing an Incremental ETL Process
This module describes the techniques you can use to implement an incremental data warehouse refresh process.
Incorporating Data from the Cloud into a Data Warehouse
In this module, you will learn about how you can use cloud computing in your data warehouse infrastructure and learn about the tools and services available from the Microsoft Azure Marketplace.
Extending SQL Server Integration Services
This module describes the techniques you can use to extend SQL Server Integration Services (SSIS). The module is not designed to be a comprehensive guide to developing custom SSIS solutions, but to provide an awareness of the fundamental steps required to use custom components and scripts in an ETL process that is based on SSIS.
Introduction to Business Intelligence and Data Modeling
This module provides an introduction to Business (BI) Intelligence. It describes common BI scenarios, current trends in BI, and the typical roles that are involved in creating a BI solution. It also introduces the Microsoft BI platform and describes the roles Microsoft SQL Server 2012 and Microsoft SharePoint 2010 play in Microsoft BI solutions.
Implementing Reports with SQL Server Reporting Services
This module discusses the tools and techniques a professional business intelligence developer can use to create and publish reports with SQL Server Reporting Services.
Creating Multidimensional Databases
The fundamental purpose of using SQL Server Analysis Services online analytical processing (OLAP) solutions is to build cubes that you can use to perform complex queries and return the results in a reasonable time. This module provides an introduction to multidimensional databases and introduces the core components of an OLAP cube.
Working with Measures and Measure Groups
A measure represents a column that contains quantifiable data, usually numeric, that you can aggregate. This module describes measures and measure groups. The module also explains how you can use measures to define fact tables and associate dimensions.
Introduction to MDX
Multidimensional Expressions (MDX) is the query language that you use to work with and retrieve multidimensional data in SQL Server Analysis Services. This module describes the fundamentals of MDX. It also explains how to build calculations, such as calculated members and named sets.
Customizing Cube Functionality
In this module, you will learn how to customize cube functionality by using several technologies available to you in SQL Server Analysis Services. These technology customizations include: Key Performance Indicators, Actions, Perspectives, and Translations.
Implementing a Tabular Data Model with Microsoft PowerPivot
This module introduces tabular data models, explains how to install and use the PowerPivot for Excel add-in, and describes how to share a workbook to PowerPivot Gallery.
Introduction to DAX
This module covers 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 these in your tabular data models.
Implementing an Analysis Services Tabular Data Model
With SQL Server 2012, you can install Analysis Services in Tabular mode and create tabular data models that information workers can access by using tools such as Excel and Power View. This module describes Analysis Services tabular data models and explains how to develop a tabular data model by using the SQL Server Data Tools.
Creating Data Visualizations with Power View
SQL Server 2012 introduces Power View, a SharePoint-based data exploration tool that provides a way for information workers to interactively create data visualizations that help them to better understand the data that they are working with. This module introduces Power View and describes how you can use it to create a range of different types of reports quickly and easily.
Performing Predictive Analysis with Data Mining
SQL Server Analysis Services includes data mining tools that you can use to identify patterns in your data, helping you to determine why particular things happen and to predict what will happen in the future. 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 Excel, and how to incorporate data mining results into Reporting Services reports.
Planning a BI Solution
This module discusses how to plan the components of a BI solution.
Planning a BI Infrastructure
This module explains how to plan an appropriate BI infrastructure for a given set of BI services.
Designing an ETL Solution
This module explains how to design an ETL solution for a data warehouse.
Designing Analytical Data Models
This module explains how to design analytical data models for specific BI scenarios.
Planning a BI Delivery Solution
This module explains how to choose an appropriate delivery solution for a given scenario.
Designing a Reporting Services Solution
This module explains how to design a Reporting Services solution.
Designing an Excel-Based Reporting Solution
This module explains how to design a reporting solution that uses Excel.
Planning a SharePoint Server BI Solution
This module explains how to design a SharePoint BI solution.
Monitoring and Optimizing a BI Solution
This module explains how to monitor and optimize a BI solution.
Planning BI Operations
This module explains how to plan management and maintenance operations for a BI solution.
At Course Completion:
After completing this course, students will be able to:
•Query: multiple tables, SQL Server metadata
•Use: built-in functions, subqueries, set operators, table expressions, window ranking, offset and aggregate functions
•Execute stored procedures
•Implement error handling and transactions
•Sort and filter data
•Program with T-SQL
•Improve query performance
•Plan and install SQL Server.
•Describes the system databases, the physical structure of databases and the most common configuration options related to them.
•Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
•Create SQL Server Backups.
•Restore SQL Server databases.
•Use the import/export wizards and explain how they relate to SSIS.
•Work with: SQL Server security models, logins and users; fixed server roles, user-defined server roles, fixed database roles and user-defined database roles; permissions and the assignment of permissions; SQL Server Audit, SQL Server Agent, jobs and job history.
•Implement SQL Server agent security, proxy accounts and credentials.
•Configure database mail, alerts and notifications.
•Create database maintenance plans.
•Work with SQL Profiler and SQL Trace stored procedures.
•Introduce DMVs and the configuration of data collection.
•Work with Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
•Troubleshoot SQL Server databases.
•Describe data warehouse concepts and architecture considerations.
•Select an appropriate hardware platform for a data warehouse.
•Design and implement a data warehouse.
•Implement: Data Flow in an SSIS Package, Control Flow in an SSIS Package, an SSIS solution that supports incremental data warehouse loads and changing data, data cleansing by using Microsoft Data Quality Services, Master Data Services to enforce data integrity
•Debug and Troubleshoot SSIS packages.
•Integrate cloud data into a data warehouse ecosystem infrastructure.
•Extend SSIS with custom scripts and components.
•Deploy and Configure SSIS packages.
•Describe how information workers can consume data from the data warehouse.
•Describe the components, architecture, and nature of a BI solution.
•Create: reports with Reporting Services, reusable report items that simplify self-service reporting.
•Manage report execution and delivery.
•Create a multidimensional database with Analysis Services.
•Implement: dimensions in a cube, measures and measure groups in a cube, a Tabular Data Model in PowerPivot, a Tabular Database.
•Use: MDX Syntax, DAX to query a tabular model, PowerView to create interactive data visualizations, Data Mining for Predictive Analysis
•Plan: the components of a BI solution, a BI infrastructure, analytical data models, a BI delivery solution, SharePoint Server BI solution, for BI operations.
•Design: a data warehouse, an ETL solution, Reporting Services solution, Excel reporting solution
•Monitor and optimize a BI solution.
70-461: Querying Microsoft SQL Server 2012
70-462: Administering Microsoft SQL Server 2012 Databases
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
Academy Code of Honor:
The Academy guarantees that students shall pass all vendor examinations during the training program or may re-attend within one year of the program completion date. Students will only be responsible for accommodations and vendor exam fees.