MCSA SQL BI Exam 70-767: Implementing a Data Warehouse using SQL (SSIS)

Overview of Data warehouse SQL Server

• Concepts of Data Warehousing
• Introduction to Data warehouse concepts, architecture consideration and Solution
• Data warehouse, Exploring data sources, ETL Process

Data Warehouse Infrastructure

• Hardware Considerations for data warehouse infrastructure
• Planning data warehouse hardware

Designing and Implementing a Data Warehouse

• Designing and implementing a schema for a data warehouse
• Designing dimension tables and fact tables

Physical Design for a Data Warehouse

• Implementing a Data Warehouse Schema and star schema
• Implementing a snowflake schema and time dimension table

Columnstore Indexes

• Columnstore Indexes, Creating Columnstore Indexes
• Working with Columnstore Indexes
• Create a Columnstore index on the FactProductInventory and FactInternetSales table
• Create a memory optimised Columnstore table

Implementing an Azure SQL Data Warehouse

• Describes Azure SQL Data Warehouses and implement them
• Advantages of Azure SQL Data Warehouse
• Implementing and developing an Azure SQL Data Warehouse
• Migrating to an Azure SQ Data Warehouse
• Copying data with the Azure data factory
• Create an Azure SQL data warehouse database
• Migrate to an Azure SQL Data warehouse database
• Copy data with the Azure data factory

Creating an ETL Solution

• ETL with SSIS
• Exploring and Implementing Source Data and Data Flow
• Data Flow in an SSIS Package
• Exploring source data
• Transferring data by using a data row task
• Using transformation components in a data row

Implementing Control Flow in an SSIS Package

• Introduction to Control Flow
• Creating Dynamic Packages
• Using Containers, Managing consistency
• Using tasks and precedence in a control flow
• Using checkpoints, transactions, variables and parameters

Debugging and Troubleshooting SSIS Packages

• Debugging an SSIS Package
• Logging SSIS Package Events
• Handling Errors in an SSIS Package
• Troubleshooting and Debugging an SSIS Package
• Logging SSIS package execution
• Implementing an event handler
• Handling errors in data flow

Implementing a Data Extraction Solution/Incremental ETL

• Incremental ETL
• Extracting and Loading Modified Data
• Temporal Tables
• Using a date time column to incrementally extract data
• Using change data capture + Using the CDC control task
• Using change tracking + 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

Enforcing Data Quality

• Using Data Quality Services to Cleanse Data
• Using Data Quality Services to Match Data
• Cleansing Data + De-duplicating Data
• Creating a DQS knowledge base
• Using a DQS project to cleanse data
• Using DQS in an SSIS package
• Creating a matching policy + Using a DS project to match data

Using Master Data Services

• Introduction to Master Data Services
• Implementing a Master Data Services Model
• Hierarchies and collections
• Creating a Master Data Hub and 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

Extending SQL Server Integration Services (SSIS)

• Using scripting in SSIS
• Using custom components in SSIS
• Using a script task

Deploying and Configuring SSIS Packages

• Overview of SSIS Deployment and SSIS Projects
• Planning and Creating SSIS Package Execution and SSIS Catalogue
• Creating environments for an SSIS solution
• Running an SSIS package in SQL server management studio
• Scheduling SSIS packages with SQL server agent

Consuming Data in a Data Warehouse

• Introduction to Business Intelligence
• An Introduction to Data Analysis
• Introduction to reporting
• Analysing Data with Azure SQL Data Warehouse
• Business Intelligence Tools
• Exploring a reporting services report
• Exploring a PowerPivot workbook and power view report

MCSA BI Exam 70-768 - Developing SQL Data Models (SSAS)

Business Intelligence and Data Modelling

• Overview to Business Intelligence
• Microsoft SQL Server Business intelligence platform
• Exploring a Data Warehouse and data model

Creating Multidimensional Databases

• Introduction to Multidimensional Analysis
• Creating Data Sources and Data Source Views
• Creating a Cube and Cube Security
• Configure and Monitoring SSAS
• Creating a multidimensional database
• Creating a Data Source, Modifying a data Source View and Modifying a Cube

Working with Cubes and Dimensions

• Configuring Dimensions
• Defining Attribute Hierarchies
• Sorting and Grouping Attributes
• Slowly Changing Dimensions
• Cubes and Dimensions
• Configuring Dimensions
• Defining Relationships and Hierarchies
• Sorting and Grouping Dimension Attributes

Working with Measures and Measure Groups

• Working with Measures and Measure Groups
• Configuring Measures
• Defining Dimension Usage and Relationships
• Configuring Measure Group Storage

Introduction to MDX

• MDX fundamentals
• Adding Calculations to a Cube
• Using MDX to Query a Cube
• Querying a cube using MDX
• Creating a Calculated Member

Customizing Cube Functionality

• Introduction to Business Intelligence
• The Implementing Key Performance Indicators
• Implementing Actions, Perspectives, Translations, KPI, action
• Customizing a Cube

Implementing a Tabular Data Model by Using Analysis Services

• Introduction to Tabular Data Models
• Creating a Tabular Data Model
• Using an Analysis Services Tabular Data Model in an Enterprise BI Solution
• Creating an Analysis Services Tabular Data Model
• Configure Relationships and Attributes
• Configuring Data Model for an Enterprise BI Solution

Data Analysis Expression (DAX)

• DAX Fundamentals
• Using DAX to Create Calculated Columns and Measures in a Tabular Data Model

Creating Calculated Columns and Measures by using DAX

• Creating Calculated Columns
• Creating Measures, KPI, a Parent - Child Hierachy

Performing Predictive Analysis with Data Mining

• 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
• Creating a Data Mining Structure and Model
• Exploring and Validating Data Mining Models
• Consuming a Data Mining Model
• Using the Excel Data Mining add-in