MCSA SQL 2016 Business Intelligence Development Training Boot Camp – 5 Days
What's Included
Microsoft Study Labs & Simulations
Instructor Led Classroom Training
The Microsoft Official MCSA SQL 2016 Business Intelligence Development certification boot camp is a 5 day comprehensive deep dive into the SQL Server covering topics such as extract, transform, and load (ETL) and data warehouse skills, along with those for implementing BI solutions using multidimensional and tabular data models and online analytical processing (OLAP) cubes.
The Microsoft SQL 2016 Business Intelligence Development boot camp incorporates Microsoft Official Courses
20767 Implementing a SQL Data Warehouse
20768 Developing SQL Data Models
Skills Gained:
Describe the key elements of a data warehousing solution
Describe the main hardware considerations for building a data warehouse
Implement a logical design for a data warehouse
Implement a physical design for a data warehouse
Create columnstore indexes
Implementing an Azure SQL Data Warehouse
Describe the key features of SSIS
Implement a data flow by using SSIS
Implement control flow by using tasks and precedence constraints
Create dynamic packages that include variables and parameters
Debug SSIS packages
Describe the considerations for implement an ETL solution
Implement Data Quality Services
Implement a Master Data Services model
Describe how you can use custom components to extend SSIS
Deploy SSIS projects
Describe BI and common BI scenarios
Describe the components, architecture, and nature of a BI solution
Create a multidimensional database with analysis services
Implement dimensions in a cube
Implement measures and measure groups in a cube
Use MDX syntax
Customize a cube
Implement a tabular database
Use DAX to query a tabular model
Use data mining for predictive analysis
Topics Covered in this Official Boot Camp
Introduction to Data Warehousing
This module describes data warehouse concepts and architecture consideration.
Lessons
- Overview of Data Warehousing
- Considerations for a Data Warehouse Solution
Lab : Exploring a Data Warehouse Solution
- Exploring data sources
- Exploring an ETL process
- Exploring a data warehouse
After completing this module, you will be able to:
- Describe the key elements of a data warehousing solution
- Describe the key considerations for a data warehousing solution
Planning Data Warehouse Infrastructure
This module describes the main hardware considerations for building a data warehouse.
Lessons
- Considerations for data warehouse infrastructure.
- Planning data warehouse hardware.
Lab : Planning Data Warehouse Infrastructure
- Planning data warehouse hardware
After completing this module, you will be able to:
- Describe the main hardware considerations for building a data warehouse
- Explain how to use reference architectures and data warehouse appliances to create a data warehouse
Designing and Implementing a Data Warehouse
This module describes how you go about designing and implementing a schema for a data warehouse.
Lessons
- Data warehouse design overview
- Designing dimension tables
- Designing fact tables
- Physical Design for a Data Warehouse
Lab : Implementing a Data Warehouse Schema
- Implementing a star schema
- Implementing a snowflake schema
- Implementing a time dimension table
After completing this module, you will be able to:
- Implement a logical design for a data warehouse
- Implement a physical design for a data warehouse
Columnstore Indexes
This module introduces Columnstore Indexes.
Lessons
- Introduction to Columnstore Indexes
- Creating Columnstore Indexes
- Working with Columnstore Indexes
Lab : Using Columnstore Indexes
- Create a Columnstore index on the FactProductInventory table
- Create a Columnstore index on the FactInternetSales table
- Create a memory optimized Columnstore table
After completing this module, you will be able to:
- Create Columnstore indexes
- Work with Columnstore Indexes
Implementing an Azure SQL Data Warehouse
This module describes Azure SQL Data Warehouses and how to implement them.
Lessons
- Advantages of Azure SQL Data Warehouse
- Implementing an Azure SQL Data Warehouse
- Developing an Azure SQL Data Warehouse
- Migrating to an Azure SQ Data Warehouse
- Copying data with the Azure data factory
Lab : Implementing an Azure SQL Data Warehouse
- Create an Azure SQL data warehouse database
- Migrate to an Azure SQL Data warehouse database
- Copy data with the Azure data factory
After completing this module, you will be able to:
- Describe the advantages of Azure SQL Data Warehouse
- Implement an Azure SQL Data Warehouse
- Describe the considerations for developing an Azure SQL Data
- WarehousePlan for migrating to Azure SQL Data Warehouse
Creating an ETL Solution
At the end of this module you will be able to implement data flow in a SSIS package.
Lessons
- Introduction to ETL with SSIS
- Exploring Source Data
- Implementing Data Flow
Lab : Implementing Data Flow in an SSIS Package
- Exploring source data
- Transferring data by using a data row task
- Using transformation components in a data row
After completing this module, you will be able to:
- Describe ETL with SSIS
- Explore Source Data
- Implement a Data Flow
Implementing Control Flow in an SSIS Package
This module describes implementing control flow in an SSIS package.
Lessons
- Introduction to Control Flow
- Creating Dynamic Packages
- Using Containers
- Managing consistency.
Lab : Implementing Control Flow in an SSIS Package
- Using tasks and precedence in a control flow
- Using variables and parameters
- Using containers
Lab : Using Transactions and Checkpoints
- Using transactions
- Using checkpoints
After completing this module, you will be able to:
- Describe control flow
- Create dynamic packages
- Use containers
Debugging and Troubleshooting SSIS Packages
This module describes how to debug and troubleshoot SSIS packages.
Lessons
- Debugging an SSIS Package
- Logging SSIS Package Events
- Handling Errors in an SSIS Package
Lab : Debugging and Troubleshooting an SSIS Package
- Debugging an SSIS package
- Logging SSIS package execution
- Implementing an event handler
- Handling errors in data flow
After completing this module, you will be able to:
- Debug an SSIS package
- Log SSIS package events
- Handle errors in an SSIS package
Implementing a Data Extraction Solution
This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.
Lessons
- Introduction to Incremental ETL
- Extracting Modified Data
- Loading modified data
- Temporal Tables
Lab : Extracting Modified Data
- Using a datetime column to incrementally extract data
- Using change data capture
- Using the CDC control task
- Using change tracking
Lab : Loading a data warehouse
- Loading data from CDC output tables
- Using a lookup transformation to insert or update dimension data
- Implementing a slowly changing dimension
- Using the merge statement
After completing this module, you will be able to:
- Describe incremental ETL
- Extract modified data
- Load modified data
- Describe temporal tables
Enforcing Data Quality
This module describes how to implement data cleansing by using Microsoft Data Quality services.
Lessons
- Introduction to Data Quality
- Using Data Quality Services to Cleanse Data
- Using Data Quality Services to Match Data
Lab : Cleansing Data
- Creating a DQS knowledge base
- Using a DQS project to cleanse data
- Using DQS in an SSIS package
Lab : De-duplicating Data
- Creating a matching policy
- Using a DS project to match data
After completing this module, you will be able to:
- Describe data quality services
- Cleanse data using data quality services
- Match data using data quality services
- De-duplicate data using data quality services
Using Master Data Services
This module describes how to implement master data services to enforce data integrity at source.
Lessons
- Introduction to Master Data Services
- Implementing a Master Data Services Model
- Hierarchies and collections
- Creating a Master Data Hub
Lab : Implementing Master Data Services
- Creating a master data services model
- Using the master data services add-in for Excel
- Enforcing business rules
- Loading data into a model
- Consuming master data services data
After completing this module, you will be able to:
- Describe the key concepts of master data services
- Implement a master data service model
- Manage master data
- Create a master data hub
Extending SQL Server Integration Services (SSIS)
This module describes how to extend SSIS with custom scripts and components.
Lessons
- Using scripting in SSIS
- Using custom components in SSIS
Lab : Using scripts
- Using a script task
After completing this module, you will be able to:
- Use custom components in SSIS
- Use scripting in SSIS
Deploying and Configuring SSIS Packages
This module describes how to deploy and configure SSIS packages.
Lessons
- Overview of SSIS Deployment
- Deploying SSIS Projects
- Planning SSIS Package Execution
Lab : Deploying and Configuring SSIS Packages
- Creating an SSIS catalog
- Deploying an SSIS project
- Creating environments for an SSIS solution
- Running an SSIS package in SQL server management studio
- Scheduling SSIS packages with SQL server agent
After completing this module, you will be able to:
- Describe an SSIS deployment
- Deploy an SSIS package
- Plan SSIS package execution
Consuming Data in a Data Warehouse
This module describes how to debug and troubleshoot SSIS packages.
Lessons
- Introduction to Business Intelligence
- An Introduction to Data Analysis
- Introduction to reporting
- Analyzing Data with Azure SQL Data Warehouse
Lab : Using a data warehouse
- Exploring a reporting services report
- Exploring a PowerPivot workbook
- Exploring a power view report
After completing this module, you will be able to:
- Describe at a high level business intelligence
- Show an understanding of reporting
- Show an understanding of data analysis
- Analyze data with Azure SQL data warehouse
Introduction to Business Intelligence and Data Modeling
This module introduces key BI concepts and the Microsoft BI product suite.
Lessons
- Introduction to Business Intelligence
- The Microsoft business intelligence platform
Lab : Exploring a BI Solution
- Exploring a Data Warehouse
- Exploring a data model
After completing this module, students will be able to:
- Describe BI scenarios, trends, and project roles.
- Describe the products that make up the Microsoft BI platform
Creating Multidimensional Databases
This module describes how to create multidimensional databases using SQL Server Analysis Services.
Lessons
- Introduction to Multidimensional Analysis
- Data Sources and Data Source Views
- Cubes
- Overview of Cube Security
- Configure SSAS
- Monitoring SSAS
Lab : Creating a multidimensional database
- Creating a Data Source
- Creating and Configuring a data Source View
- Creating and Configuring a Cube
- Adding a Dimension to a Cube
After completing this module, you will be able to:
- Describe considerations for a multidimensional database.
- Create data sources and data source views.
- Create a cube
- Implement security in a multidimensional database.
- Configure SSAS to meet requirements including memory limits, NUMA and disk layout.
- Monitor SSAS performance.
Working with Cubes and Dimensions
This module describes how to implement dimensions in a cube.
Lessons
- Configuring Dimensions
- Defining Attribute Hierarchies
- Implementing Sorting and Grouping Attributes
- Slowly Changing Dimensions
Lab : Working with Cubes and Dimensions
- Configuring Dimensions
- Defining Relationships and Hierarchies
- Sorting and Grouping Dimension Attributes
After completing this module, you will be able to:
- Configure dimensions.
- Define attribute hierarchies.
- Implement sorting and grouping for attributes.
- Implement slowly changing dimensions.
Working with Measures and Measure Groups
This module describes how to implement measures and measure groups in a cube.
Lessons
- Working with Measures
- Working with Measure Groups
Lab : Configuring Measures and Measure Groups
- Configuring Measures
- Defining Regular Relationships
- Configuring Measure Group Storage
After completing this module, you will be able to:
- Configure measures.
- Configure measure groups.
Introduction to MDX
This module describes the MDX syntax and how to use MDX.
Lessons
- MDX fundamentals
- Adding Calculations to a Cube
- Using MDX to Query a Cube
Lab : Using MDX
- Querying a cube using MDX
- Adding a Calculated Member
After completing this module, you will be able to:
- Use basic MDX functions.
- Use MDX to add calculations to a cube.
- Use MDX to query a cube.
Customizing Cube Functionality
This module describes how to customize a cube.
Lessons
- Implementing Key Performance Indicators
- Implementing Actions
- Implementing Perspectives
- Implementing Translations
Lab : Customizing a Cube
- Implementing an action
- Implementing a perspective
- Implementing a translation
After completing this module, you will be able to:
- Implement KPIs in a Multidimensional database
- Implement Actions in a Multidimensional database
- Implement perspectives in a Multidimensional database
- Implement translations in a Multidimensional database
Implementing a Tabular Data Model by Using Analysis Services
This module describes how to implement a tabular data model in Power Pivot.
Lessons
- Introduction to Tabular Data Models
- Creating a Tabular Data Model
- Using an Analysis Services Tabular Data Model in an Enterprise BI Solution
Lab : Working with an Analysis Services Tabular Data Model
- Creating an Analysis Services Tabular Data Model
- Configure Relationships and Attributes
- Configuring Data Model for an Enterprise BI Solution.
After completing this module, students will be able to:
- Describe tabular data models
- Describe how to create a tabular data model
- Use an Analysis Services Tabular Model in an enterprise BI solution
Introduction to Data Analysis Expression (DAX)
This module describes how to use DAX to create measures and calculated columns in a tabular data model.
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
- Creating Calculated Columns
- Creating Measures
- Creating a KPI
- Creating a Parent – Child Hierarchy
After completing this module, students will be able to:
- Describe the key features of DAX
- Create calculated columns and measures by using DAX
Performing Predictive Analysis with Data Mining
This module describes how to use data mining for predictive analysis.
Lessons
- Overview of Data Mining
- Creating a Custom Data Mining Solution
- Validating a Data Mining Model
- Connecting to and Consuming a Data-Mining Model
- Using the Data Mining add-in for Excel
Lab : Using Data Mining
- Creating a Data Mining Structure and Model
- Exploring Data Mining Models
- Validating Data Mining Models
- Consuming a Data Mining Model
- Using the Excel Data Mining add-in
After completing this module, students will be able to:
- Describe considerations for data mining
- Create a data mining model
- Validate a data mining model
- Connect to a data-mining model
- Use the data mining add-in for Excel
Certification Camps has developed a comprehensive training / delivery format which focuses on learning beyond the core content accessible to any Microsoft training provider. Our program incorporates interactive demonstrations with explanations which go beyond the content of the book. Additional content, videos, labs & demonstrations are provided to expand on advanced topics - providing additional insight and perspective. Certification Camps training is NOT your typical book & PowerPoint presentation found at any local training center.
As a Microsoft Certified Partner with Gold Learning Competency - we adhere to the strict guidelines, standards and requirements to use Microsoft's exclusive curriculum. More over - our standards go beyond the "minimum requirements" set forth by Microsoft Learning.
We leverage our partnership benefits of courseware customization to build end to end technology training solutions. Students gain practical skills which can be implemented immediately.
At most training centers - learning starts on the first day of class and ends on the last day. Our boot camp training program is designed to offer resources before, during and after.
During Camp - While attending camp - you will work hands on with an industry leading "real world experienced" Microsoft Certified Trainer.
CERTIFICATION CAMPS FACILITIES
CAMPUS - Certification Camps invested $150,000 into a complete training center construction build out. Larger classrooms, no sound transference between training rooms, new desk / executive leather chairs & great common areas.
CLASSROOM SERVERS - Students work on a dedicated Dell 8500 / 8700 with an Intel i7 (4th generation), minimum of 16GB memory (Most with 32 GB) and most with 512GB SSD drives.
CAMPUS INTERNET - The campus is connected with a 300Mbs Verizon Fios Business Connection which provides complete internet (including VPN) access for students.
COMMON AREA - Unlike any training facility ever - break room with Mame Arcade, high end "kitchen" with snacks, drinks (Starbucks Coffee, 100% juices, sodas, etc) and breathtaking terrace.
LODGING - We use the Marriott Fairfield Inn & Suites Lakewood Ranch. This "upgraded" hotel offers extremely comfortable beds, great breakfast and very fast (Verizon) internet access.
NEAR BY AMENITIES - Many shops, restaurants and grocery options are available within walking distance. Additionally - the hotel provided scheduled shuttle services. Restaurants like Bone Fish Grill, Ruby Tuesday's, Five Guys, Chipotle, Quiznos, Chili's and over 20 additional choices in the immediate area. All of these options are offered in a pedestrian walking village.
CERTIFICATION CAMPS DIFFERENCE
Certification Camps provides an in-depth hands on learning environment. Our instructors teach using demonstrations and explain concepts beyond the scope of the courseware. The best instructors are contracted from all across the US. These professionals are real world consultants who actually implement these technologies. Certification Camps knows our clients work in the "real world" and it only makes sense to have an instructor with the experience in the real world.
Facilities - Many of our competitors over-crowd classrooms by placing 2 to 3 students per desk and 20+ students per instructor. Often these companies "extend" the life of very old equipment with slow performance on the software used today. Our average class size is 8 students. Our facilities have the best equipment with the most comfortable, focused environment for learning.
Instructors - Our CEO, Chester Flake holds one of the rarest Microsoft Certifications - the Microsoft Certified Learning Consultant (MCLC) Certification. This certification reflects a commitment to make sure our instructors are also actively engaged in real world consulting projects. Students expect a professional who can answer questions and demonstrate the technology.
Face to Face Training - We believe the best way to interact and learn is face to face training. Many of our competitors which offer local training are simply providing students with headphones/mic to attend class with an instructor at a remote location.
Test Pass - We offer a free retake voucher (if needed) for each of the exams required for certification. Any training center offering a "100% pass guarantee" would have to use unethical practices or unlimited test vouchers (which is not likely or practical).
Distractions - Students often look for a "local" training center so they can be close to home and work. Boot camp requires a tremendous time commitment during the program. Long class hours combined with self study each evening. The goal of boot camp is to achieve certification in a fraction of the time. We strongly recommend students attend camp away from home and work in a focused, distraction free environment.
Boot Camp - Boot camp is an accelerated training focused on teaching technology and testing students on the official exams. Most training providers "added" boot camps to the existing standard training classes they offer. These training centers think a boot camp is just a regular class where you send the student home with a voucher on the last day. Certification Camps administers exams throughout the camp. Equally important - not every trainer can teach a boot camp and not all courseware is designed for boot camp format. Our trainers know the boot camp format and our courseware is designed for accelerated learning.
Florida - Certification Camps has one of the most scenic locations boasting some of the best weather in US. We believe students should have a comfortable and inviting atmosphere while attending these otherwise intense programs. We place our location, facilities and amenities up against that any competitor.