Microsoft Certifications - Tips, Links, and Study Materials for SQL Server 2016 MCSA and MCSE Tests

Views: 16.334 views

In this article

This post is the 2 part of 13 in the series. Microsoft Certification

Microsoft Certifications - Portuguese Study Material for Your First Certification (MTA)
Microsoft Certifications - Tips, Links, and Study Materials for SQL Server 2016 MCSA and MCSE Tests
Microsoft certification exams: 50% discount for students (Undergraduate, Graduate, Master, Doctorate)
SQL Server - How to Learn from Basics to Certification (Courses, Virtual Labs, Virtual Academy)
MCP Professional - How to Share Your Certifications and How to Find Certified Professionals
SQL Server - Study material for the 70-764 certification exam Administering a SQL Database
How was the Microsoft Certification Data Platform Webinar (Data Platform)
Microsoft Certification - Tips on how to take the test at home without a headache
My impressions and DP-300 exam study material - Administering Relational Databases on Microsoft Azure (beta)
My impressions and study material for the DA-100 certification exam - Analyzing Data with Microsoft Power BI (beta)
Certification exam simulations? RUN AWAY FROM THEM!
Microsoft Azure Data Fundamentals DP-900 Exam – Free Certification Exam Study Material
New Microsoft Certified Fundamentals portal for those looking to get their first certification

Reading Time: 78 minutes

Hello people,
All right with you ?

I'm a little absent here on the blog, but soon I'll be posting more often as soon as I finish some projects and consulting I'm doing. In the meantime, I couldn't help but share with you some tips, materials, and links that can help you prepare for your SQL Server 2016 MCSA and MCSE certification exams and exams, just as I've been preparing for the past few months.

If you are preparing for your first certification exam and want to start with MTA, I created the post Microsoft Certifications - Portuguese Study Material for Your First Certification (MTA) to help and clarify who is just starting out in this certification world and wants to know more about this certification.

First of all, let's take a look at Microsoft's current certifications for the Data Platform and start getting familiar with the acronyms:

Microsoft Technology Associate (MTA): This is Microsoft's first and easiest certification exam, designed for people just starting their careers and still getting familiar with the technology. She not is a prerequisite for taking the MCSA and MCSE tests and taking it is that is optional.
As an incentive to take the MTA test, Microsoft gives you a small discount on taking this exam (you pay $ 77 instead of $ 100 for other MCSA and MCSE tests)
Microsoft Certified Solutions Associate (MCSA): Medium / high level test, aimed at people who already have experience with the technologies involved.
MCSA certification is a prerequisite for MCSE certification.
Microsoft Certified Solutions Expert (MCSE): Microsoft's latest level of certification exams, MCSE exams are often the most difficult of Microsoft exams, and should be performed by highly-skilled professionals with extensive experience and expertise in the technologies in question.

Why make sure?

The BornToLearn blog, known worldwide by the Microsoft technical community, has posted a post where they cite 6 advantages that professionals can gain by becoming certified (You can check out the post this link here), which is summarized in the image below:

Prior to a certification, it is a challenge to start advancing in your technology career. After certification, you have a strong impetus for the next big hurdle. In fact, 33% of certified professionals said they got a job after certification. Also, getting certified is addictive.
Keeping up when you're new to work is a challenge. The good news is that certification provides you with a proven foundation of real-world and expert knowledge to build, so you're ready to get up and running 39% faster than your peers. If you are still unconvinced, 38% of IT professionals said that certification helped them perform complicated tasks with more confidence. It is science.
It is a struggle to differentiate yourself and show that you are the best person for the job when you are starting in the tech industry. Isn't it good to know that 91% of hiring managers consider certification as part of their hiring criteria? We think so. Going on interviews knowing that you are certified will make you feel great.
Stay current and never risk having obsolete skills. When you maintain your certifications, you can be confident that your skills are up to date. So you can be confident in the experience you have in gaining new tricks, such as new technologies and features, as industry standards change.
Getting impatient for the next promotion? The solution is quite simple. If you are certified, you get more salary increases and more chances to rise in your career. Plus, you get right out of the gate getting 15% more on average than your uncertified peers.
Perhaps the most important. When you are certified, you are recognized in the industry as an expert. Employers like their certified hires so much that they usually keep them around 15% more than other employees. When will you be certified ??

Video of MVP Rafael Bernares about the importance of certification

What do Microsoft certification exams look like?

If you have never done so, I would like to prepare you well, as Microsoft certification exams are often quite difficult as:

Some proofs have discursive questions, where you need to write SQL queries following various rules, as stated.
In some questions, the answer is a composition of 4 options, among 10, where you must mark the correct alternatives and align in the correct order of execution. The chance that you will hit a question like this "in the kick" is very small.
In some questions, all the answers are correct, but you should choose the option that has the best performance, ie the cheapest, financially, for example.
In some stages of the test, you have no chance to go back to the question and think better of the correct answer: You clicked forward, there is no turning back.
Usually the time is short: 3 mins per question and some have a statement with more than 10 lines of text.

Microsoft certification exams cost $ 100 (If you are a student, you get 50% off. See how to get your discount HERE) and you must get a grade equal to or greater than 700, worth 1000, to pass the exam (700 is not 70% of exam).

These exams are usually translated into Portuguese (minus Beta or newly released exams, where there is only English language option), but technical terms are often translated as well (View = Vision, Trigger = Trigger, etc.), which may end up confusing you during the race. In my opinion, a professional who wants to be certified and pursue a career in DBA can no longer afford not to know at least read English texts and articles.

NOTICE: If you have been in the IT field for some time, you may have heard of some tests that are sold / distributed on the internet with the same questions that are applied in the certification exam. Many IT “professionals” end up acquiring this type of test and, instead of studying and learning to take the test, they end up memorizing the questions and answers (they often do not even know what they are memorizing) and manage to pass the exams. Avoid this type of "study", because it is useless to have several certificates in the curriculum and when you join a company, which hired you because of these certifications, you end up "feeling ashamed" for not having all the knowledge that you seemed to have. In the technical community, this type of “study” is highly repudiated and discouraged.

And the beta tests? How do they work?

When Microsoft is preparing to launch certification exams, it makes some vouchers available to IT professionals (usually 300 proof vouchers, worldwide) so that they take these test exams and give their feedback on the questions.

Although they are BETA tests, their results are computed and are valid for the certification process, just as a paid test. As explained on this link, if you pass the beta test, you do not need to repeat the test when the final version comes out. If you do not pass the beta test, you will not be able to repeat the beta test again and you should wait for the final version to exit to repeat the exam.

Since the tests are still in the testing phase (São BETA), you should be aware of the possibility of errors in the questions. In addition, tests are generally available in English only and instead of some questions chosen at random, the test contains a lot more questions (usually 59-62 instead of 35-40 for a “normal” test), so the beta test usually be greater than the final exam.

Another difference from the final version of the tests to the beta version, is that in the final version, the result is generated as soon as you finish the test, while in Beta tests, the result is only released several weeks later (when the final version of the test is available) - Usually about 4 months.

How do I become an MCSA on Data Plataform?

As you may know, the SQL Server 2016 MCSA Certification Path has changed a lot compared to SQL Server 2012 / 2014, where you needed to pass 3 proofs, 1 from Database Dev, another from Bank Administration (DBA), and another BI, which didn't really make much sense, as they are distinct careers within Microsoft's Data Platform area.

Now in SQL Server 2016 MCSA Certification Path, you need to pass only 2 exams from the same area of expertise, be it DEV, DBA or BI. That is, if you pass only 1 proof of DEV and another of DBA, you not will have the title of MCSA.

Understand better by viewing the image below:

How do I become an MCSE Data Management and Analytics?

If you want to become a Microsoft Certified Solutions Expert on Microsoft Data Platform, you will need to meet the 2 requirements below:

Become an MCSA SQL Server 2012 / 2014 OR MCSA SQL Server 2016 (BI, DEV, or DBA)
Pass one or more of the following tests:
Exam 70-464 - Developing Microsoft SQL Server Databases (DEV)
Exam 70-465 - Designing Database Solutions for Microsoft SQL Server (DBA)
Exam 70-466 - Implementing Data Models and Reports with Microsoft SQL Server (BI)
Exam 70-467 - Designing Business Intelligence Solutions with Microsoft SQL Server (BI)
Exam 70-473 - Designing and Implementing Cloud Data Platform Solutions (DBA)
Exam 70-475 - Designing and Implementing Big Data Analytics Solutions (BI)

New elective tests for MCSE. To win the title, there are some criteria. It is not enough to take the test and obtain the title, as happens in the tests above:
Exam 70-762 - Developing SQL Databases (DEV)
70-767 Exam: Implementing a Data Warehouse Using SQL (BI)
70-768 Exam: Developing SQL Data Models (BI)
70-773 Exam: Analyzing Big Data with Microsoft R (BI)
70-774 Exam: Perform Cloud Data Science with Azure Machine Learning (BI)
70-775 Exam: Perform Data Engineering on Microsoft Azure HDInsight (BI)

UPDATE: In June 2017, Microsoft started to recognize as MCSE professionals who have 2 or more MCSA's in the same area, that is, if you have 2 MCSA of Data Platform, you are already an MCSE as soon as you pass the 2nd test of your 2nd MCSA.

Note: In MCSE testing, your MCSA's area of expertise is indifferent to obtaining the title. Since you are MCSA, you can take any of the MCSE exams to be recognized as a Microsoft Certified Solutions Expert on the Microsoft Data Platform. That is, if you are a BI MCSA (Passed on 70-767 and 70-768), for example, and pass on 70-464, which is theoretically focused on Database Developers, you will become MCSE Data Management and Analytics.

Microsoft Technology Associate (MTA)

Certification Link

98-364 Exam: Database Fundamentals (Exam Link)
Study Links

Understanding Core Database Concepts

Understand how data is stored in tables
- Tables
- Tables, Records, and Fields
Understand relational database concepts
Understand data manipulation language
Understand data definition language (DDL)
- Data Definition Language (DDL) Statements (Transact-SQL)
- Data Definition Language (DDL)

Create Database Objects

Choose data types
Understand tables and how to create them
Create views
Create stored procedures and functions

Manipulate Data

Understand Data Storage

Understand normalization
- Database Normalization Basics
- Database normalization
Understand primary, foreign, and composite keys
Understand indexes

Administer a Database

Understand database security concepts
Understand database backups and restore

MCSA SQL Server 2012 / 2014

Certification Link

If you want to become a SQL Server MCN Certified 2012 / 2014 Professional (Yes, it is a certification for both versions), you will need to pass the 3 exams below.

70-461 Exam: Querying Microsoft SQL Server 2012 / 2014 (Exam Link)
Study Links

Create database objects

Create and alter tables using T-SQL syntax (simple statements)
Create and alter views (simple statements)
Design Views
Create and modify constraints (simple statements)
Create and alter DML triggers

Work with data

Modify data

Create and alter stored procedures (simple statements)
Modify data by using INSERT, UPDATE, and DELETE statements
Match datasets
Work with functions

Troubleshoot and optimize

Optimize queries
- - SQL Server Execution Plans
Graphical Query Plan Tutorial
Manage transactions
- SQL Server Concurrency: Locking, Blocking, and Row Versioning
- Introduction to the SQL Server Transaction Log Tutorial
Evaluate the use of row-based operations vs. set-based operations
Implement error handling

70-462 Exam: Administering Microsoft SQL Server 2012 / 2014 Databases (Exam Link)
Study Links

Install and configure

Plan installation
Install SQL Server and related services
Implement a migration strategy
Configure additional SQL Server components
Manage SQL Server Agent

Maintain instances and databases

Optimize and troubleshoot

Identify and solve concurrency problems
Collect and analyze troubleshooting data
Audit SQL Server instances.

Manage Data

Implement security

Implement high availability

70-463 Exam: Implementing a Data Warehouse with Microsoft SQL Server 2012 / 2014 (Exam Link)
Study Links

Design and Implement a Data Warehouse (11%)

Design and implement dimensions

Introduction to Dimensions (Analysis Services - Multidimensional Data) http://msdn.microsoft.com/en-us/library/ms175439.aspx
Attributes and Attribute Hierarchies http://msdn.microsoft.com/en-us/library/ms174760.aspx
Attribute Relationships http://msdn.microsoft.com/en-us/library/ms174557.aspx
SQL Server Analysis Services Interview Questions Part II - Dimensions http://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii–dimensions/
Conformed dimensions http://www.jamesserra.com/archive/2011/11/conformed-dimensions/
Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
Analysis Services - 11 Star and Snowflake Schemas http://www.youtube.com/watch?v=ewX1nsjX-uA
Defining Dimension Granularity within a Measure Group http://msdn.microsoft.com/en-us/library/ms166573.aspx

Design and implement fact tables

Defining a Fact Relationship http://msdn.microsoft.com/en-us/library/ms167409.aspx
Defining a Many-to-Many Relationship http://msdn.microsoft.com/en-us/library/ms170463.aspx
Columnstore Indexes http://msdn.microsoft.com/en-us/library/gg492088.aspx
Different Fact Tables for Partitions http://msdn.microsoft.com/en-us/library/ms174755.aspx
Configure Measure Properties http://msdn.microsoft.com/en-us/library/ms175623.aspx
Define Semiadditive Behavior http://technet.microsoft.com/en-us/library/ms175356.aspx
Use Aggregate Functions http://technet.microsoft.com/en-us/library/ms365396.aspx

Extract and Transform Data (23%)

Load Data (27%)

Design control flow

Control flow http://msdn.microsoft.com/en-us/library/ms137681.aspx
Integration Services (SSIS) Variables http://msdn.microsoft.com/en-us/library/ms141085.aspx
Control Flow vs Data Flow http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://bidn.com/blogs/BradSchacht/ssis/305/control-flow-vs-data-flow
SSIS Designer http://msdn.microsoft.com/en-us/library/ms137973.aspx
Integration Services Tasks http://msdn.microsoft.com/en-us/library/ms139892.aspx
Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx

Implement package logic by using SSIS variables and parameters

SSIS 2012: Parameters and Variables, what is the difference? http://www.rafael-salas.com/2011/12/ssis-2012-parameters-and-variables-what.html#!/2011/12/ssis-2012-parameters-and-variables-what.html
Integration Services (SSIS) Parameters http://msdn.microsoft.com/en-us/library/hh213214.aspx
SSIS Basics: Introducing Variables http://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/
Package Configurations http://msdn.microsoft.com/en-us/library/ms141682.aspx
Using Parent Package Variables in Package Configurations http://www.sqlis.com/sqlis/post/Using-Parent-Package-Variables-in-Package-Configurations.aspx
Easy Package Configuration http://www.sqlis.com/sqlis/post/Easy-Package-Configuration.aspx
Change Package Properties Using Parameters-SSIS 2012 http://www.sqlservercentral.com/blogs/samvangassql/2012/04/24/change-package-properties-using-parametersssis-2012/
Use Property Expressions in Packages http://msdn.microsoft.com/en-us/library/ms141214.aspx

Implement control flow

Using Sequence Containers in SSIS http://www.bidn.com/blogs/TomLannen/bidn-blog/2620/using-sequence-containers-in-ssis
Restart Packages by Using Checkpoints http://msdn.microsoft.com/en-us/library/ms140226.aspx
Execute SQL Task (Transaction control) http://msbiworld.wordpress.com/2012/02/12/ssis-execute-sql-task-transaction-control/
Debugging Control Flow http://msdn.microsoft.com/en-us/library/ms140274.aspx
Precedence Constraints http://msdn.microsoft.com/en-us/library/ms141261.aspx
Designing Your SSIS Packages for Parallelism http://technet.microsoft.com/en-us/sqlserver/ff686759.aspx
How to pass parameter values to the stored procedure dynamically in ssis http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3973ca4c-195f-498f-ba1d-ee334eccd368
Execute Package Task http://msdn.microsoft.com/en-us/library/ms137609.aspx
Are SSIS Package Template Useful in 2012 http://stackoverflow.com/questions/11347545/are-ssis-package-template-useful-in-2012
Save a Package as a Package Template http://msdn.microsoft.com/en-us/library/ms345191.aspx

Implement data load options

SSIS - Package design pattern for loading a data warehouse http://sqlblog.com/blogs/jorg_klein/archive/2010/01/04/ssis-package-design-pattern-for-loading-a-data-warehouse.aspx
SSIS Design Pattern - Incremental Loads http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis_design-pattern-incremental-loads.aspx
Change Data Capture (SSIS) http://msdn.microsoft.com/en-us/library/bb895315.aspx
Perform an Incremental Load of Multiple Tables http://msdn.microsoft.com/en-us/library/bb895287.aspx
SSIS Design Pattern - ETL Instrumentation, Part 1 http://sqlblog.com/blogs/andy_leonard/archive/2007/08/03/ssis-design-pattern-etl-instrumentation-part-1.aspx
SSIS Design Pattern - ETL Instrumentation, Part 2 http://sqlblog.com/blogs/andy_leonard/archive/2007/11/11/ssis-design-pattern-etl-instrumentation-part-2.aspx
SSIS Design Pattern - ETL Instrumentation, Part 3 http://sqlblog.com/blogs/andy_leonard/archive/2007/11/18/ssis-design-pattern-etl-instrumentation-part-3.aspx

Implement script components in SSIS

Data Warehousing - Slowly Changing Dimensions http://sqlserverpedia.com/wiki/Data_Warehousing_-_Slowly_Changing_Dimensions
Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
Configure Outputs Using the Slowly Changing Dimension Wizard http://msdn.microsoft.com/en-us/library/ms141662.aspx
Component script http://msdn.microsoft.com/en-us/library/ms137640.aspx
Developing Specific Types of Script Components http://msdn.microsoft.com/en-us/library/ms345170.aspx
Web Service Task http://msdn.microsoft.com/en-us/library/ms140114.aspx
SSIS Nugget: Get error descriptions http://consultingblogs.emc.com/jamiethomson/archive/2005/08/08/1969.aspx

Configure and Deploy SSIS Solutions (24%)

Troubleshoot data integration issues

Troubleshooting Tools for Package Development http://msdn.microsoft.com/en-us/library/ms137625.aspx
Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx
Runtime Debugging Using Data Taps in SSIS 2012 http://blog.gnetgroup.com/bi/2012/11/08/runtime-debugging-using-data-taps-in-ssis-2012/
Debug a Package by Setting Breakpoints on a Task or a Container http://msdn.microsoft.com/en-us/library/ms141754.aspx

Install and maintain SSIS components

Install Integration Services http://msdn.microsoft.com/en-us/library/ms143731.aspx
SQL Server Integration Services 2012 - Project Deployment Model http://www.element61.be/e/resourc-detail.asp?ResourceId=545
Running and Managing Packages Programmatically http://msdn.microsoft.com/en-us/library/aa337077.aspx
Loading and Running a Remote Package Programmatically http://msdn.microsoft.com/en-us/library/ms403355.aspx
SSIS Catalog http://msdn.microsoft.com/en-us/library/hh479588.aspx
Quick Reference: SSIS in 32- and 64-bits http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html#!/2009/10/quick-reference-ssis-in-32-and-64-bits.html

Implement auditing, logging, and event handling

Audit Transformation http://msdn.microsoft.com/en-us/library/ms141150.aspx
Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx
Logging and Defining Log Entries in a Data Flow Component http://msdn.microsoft.com/en-us/library/ms345169.aspx
Execute package in SSIS catalog taking advantage of the new project deployment model, and the logging and reporting feature http://geekswithblogs.net/LifeLongTechie/archive/2012/11/14/time-to-stop-using-ldquoexecute-package-taskrdquondash-a-way-to.aspx
What Events are Included in the SSIS Catalog Log Levels http://www.mattmasson.com/index.php/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/
SSIS 2012 Logging http://svangasql.wordpress.com/2012/06/12/t-sql-tuesday-31-ssis-2012-logging/
SSIS Catalog: Part 5 - Logging and Execution Reports http://www.rad.pasfu.com/index.php?/archives/78-SSIS-Catalog-Part-5-Logging-and-Execution-Reports.html
Easy Package Execution monitoring in SSIS 2012 hhttp: //visakhm.blogspot.com/2012/09/easy-package-execution-monitoring-in.html

Deploy SSIS solutions

Deployment of Projects and Packages http://msdn.microsoft.com/en-us/library/hh213290.aspx
Deploy Packages by Using the Deployment Utility http://msdn.microsoft.com/en-us/library/ms141693.aspx
SSIS 2012 Deployment Models http://www.youtube.com/watch?v=viaMhHKOoEY
SSIS Catalog http://msdn.microsoft.com/en-us/library/hh479588.aspx
Validating SSIS 2012 packages programmatically via SSIS Catalog Managed Object Model http://stackoverflow.com/questions/12179934/validating-ssis-2012-packages-programatically-via-ssis-catalog-managed-object-mo
dtutil Utility http://msdn.microsoft.com/en-us/library/ms162820.aspx
dtexec Utility http://msdn.microsoft.com/en-us/library/hh231187.aspx

Configure SSIS security settings

Security Overview (Integration Services) http://msdn.microsoft.com/en-us/library/ms137833.aspx
Integration Services Roles (SSIS Service) http://msdn.microsoft.com/en-us/library/ms141053.aspx
SSIS Catalog Access Control Tips http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx
Folder Security in SSIS 2012 http://www.biadmin.com/2012/08/folder-security-in-ssis-2012.html

Build Data Quality Solutions (15%)

Install and maintain Data Quality Services

Introducing Data Quality Services http://msdn.microsoft.com/en-us/library/ff877917.aspx
Install Data Quality Services http://msdn.microsoft.com/en-us/library/gg492277.aspx
Run DQSInstaller.exe to Complete Data Quality Server Installation http://msdn.microsoft.com/en-us/library/hh231682.aspx
DQS Security http://msdn.microsoft.com/en-us/library/hh213045.aspx
Manage DQS Users in SSMS http://msdn.microsoft.com/en-us/library/hh510409.aspx
Grant DQS Roles to Users http://msdn.microsoft.com/en-us/library/gg492283.aspx
Data governance hhttp: //en.wikipedia.org/wiki/Data_governance

Implement master data management solutions

Install Master Data Services http://msdn.microsoft.com/en-us/library/ee633752.aspx
Master Data Services Overview http://msdn.microsoft.com/en-us/library/ff487003.aspx
Managing Data Warehouse Dimensions with MDS (part 1) http://technet.microsoft.com/en-us/sqlserver/hh709039.aspx
SQL 2012 Master Data Services - Hierarchies and Collections http://technet.microsoft.com/en-us/sqlserver/hh780975.aspx
Deploying Models (Master Data Services) http://msdn.microsoft.com/en-us/library/ff486956.aspx
Administrators (Master Data Services) http://msdn.microsoft.com/en-us/library/ff487051.aspx
Security (Master Data Services) hthttp: //msdn.microsoft.com/en-us/library/hh231026.aspx

Create a quality data project to clean data

Create a Data Quality Project http://msdn.microsoft.com/en-us/library/hh510393.aspx
Run the Data Quality Client Application http://msdn.microsoft.com/en-us/library/hh213023.aspx
Data Cleansing http://msdn.microsoft.com/en-us/library/gg524800.aspx
Data Profiling and Notifications in DQS http://msdn.microsoft.com/en-us/library/hh213055.aspx
Creating a knowledge base and cleansing data using Data Quality Services in SQL Server 2012 - Part 3 http://www.mssqltips.com/sqlservertip/2577/creating-a-knowledge-base-and-cleansing-data-using-data-quality-services-in-sql-server-2012–part-3/
Getting Started with Data Quality Services of SQL Server 2012 Using SSIS - Part 4 http://www.mssqltips.com/sqlservertip/2593/getting-started-with-data-quality-services-of-sql-server-2012-using-ssis–part-4/
Demo: Deduplicating Data with SQL Server 2012 Data Quality Services http://channel9.msdn.com/posts/SQL11UPD05-REC-06

Bonus Content

Analysis Services Tutorials (SSAS) http://msdn.microsoft.com/en-us/library/hh231701.aspx
Data Mining Tutorials (Analysis Services) http://msdn.microsoft.com/en-us/library/bb677206.aspx
Reporting Services Tutorials (SSRS) http://msdn.microsoft.com/en-us/library/bb522859.aspx
SSIS Tutorial: Deploying Packages http://msdn.microsoft.com/en-us/library/de18468c-cff3-48f4-99ec-6863610e5886
Introduction to SQL Server Data Tools http://www.mssqltips.com/sqlservertip/2804/introduction-to-sql-server-data-tools/
SQL Server Data Tools Connected Database Development http://www.mssqltips.com/sqlservertip/2832/sql-server-data-tools-connected-database-development/
SSIS References articles by a Microsoft employee, including many on SSIS 2012 http://www.mssqltips.com/sqlserverauthor/30/arshad-ali/
Logging - Level 11 of the Stairway to Integration Services http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/95353/
SQL Server Reporting Services 2012 Drilldown Features http://www.mssqltips.com/sqlservertip/2831/sql-server-reporting-services-2012-drilldown-features/
Graphing the Sine Function in SQL Server Reporting Services http://www.mssqltips.com/sqlservertip/2843/graphing-the-sine-function-in-sql-server-reporting-services/
MDX Guide for SQL Folks: Part I - Navigating The Cube http://www.sqlservercentral.com/articles/MDX/91228/
MDX Guide for SQL Folks: Part II - Hierarchies and Functions http://www.sqlservercentral.com/articles/MDX/91730/

MCSA SQL Server 2016

Certification Link

As explained above, to become a SQL Server 2016 MCSA you will need to pass two exams from the same area of expertise, be it BI, DEV or DBA.

If you already have the MCSA SQL Server 2012 / 2014 title and would like to upgrade your certification to SQL Server 2016, simply make 1 proof of the category you want to obtain the MCSA SQL Server 2016 title as below.

MCSA SQL Server 2012 / 2014 + Proofing 70-762 (Developing SQL Databases) = MCSA SQL 2016 Database Development
MCSA SQL Server 2012 / 2014 + Provisioning SQL Databases (70-765) = MCSA SQL 2016 Database Administration
MCSA SQL Server 2012 / 2014 + Evidence 70-768 (Developing SQL Data Models) = MCSA SQL 2016 Business Intelligence Development

The upgrade exam for SQL Server 2016 certification is paid as normal as any other exam (When the SQL 2016 exams began, Microsoft released the free upgrade, but the offer has already expired).

MCSA SQL 2016: Database Development (Developer)

70-761 Exam: Querying Data with Transact-SQL (Exam Link)
Study Links

Manage data with Transact-SQL

Query data with advanced Transact-SQL components

Query data by using subqueries and APPLY
- SQL Server CROSS APPLY and OUTER APPLY
- FROM (Transact-SQL)
Query data by using table expressions
Group and pivot data by using queries
Query temporal data and non-relational data

Program databases by using Transact-SQL

Create database programmability objects by using Transact-SQL
Implement error handling and transactions
Implement data types and NULLs

70-762 Exam: Developing SQL Databases (Exam Link)
Study Links

Design and implement database objects

Design and implement a relational database schema
Design and implement indexes
Design and implement views
Implement columnstore indexes

Implement programmability objects

Ensure data integrity with constraints
Create stored procedures
Create triggers and user-defined functions

Manage database concurrency

Implement transactions
Manage isolation levels
Optimize concurrency and locking behavior
Implement memory-optimized tables and native stored procedures

Optimize database objects and SQL infrastructure

Optimize statistics and indexes
Analyze and troubleshoot query plans
Manage performance for database instances
Monitor and trace SQL Server baseline performance metrics

MCSA SQL 2016: Database Administrator (DBA)

70-764 Exam: Administering a SQL Database Infrastructure (Exam Link)
Study Links

Configure data access and auditing

Manage backup and restore of databases

Manage and monitor SQL Server instances

Monitor database activity
Monitor Queries
Manage indexes
Manage statistics
SQL Server instances monitor
Manage high availability and disaster recovery
Implement failover cluster instances
- Step by Step Installation of SQL Server 2014 on a New Failover Cluster
- Always On Failover Cluster Instances (SQL Server)

70-765 Exam: Provisioning SQL Databases (Exam Link)
Study Links

Implement SQL in Azure

Manage databases and instances

Deploy and migrate applications

Deploy and migrate applications
Deploy Applications to SQL Server on Azure Virtual Machines
Migrate client applications

MCSA SQL 2016: Business Intelligence Developer (BI)

70-767 Exam: Implementing a SQL Data Warehouse (Exam Link)
Study Links

Design and implement a data warehouse

Design and implement dimension tables
Design and implement fact tables
- Use Aggregate Functions
Design and implement indexes for a data warehouse workload
Design storage for a data warehouse
Design and implement partitioned tables and views

Extract, transform, and load data

Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package
Design and implement an ETL data flow by using an SSIS package
Implement an ETL solution that supports incremental data extraction
Implement an ETL solution that supports incremental data loading
Debug SSIS packages
Deploy and configure SSIS packages and projects

Integrate solutions with cloud data and big data

Integrate external data sources with SQL Server by using Polybase
Extract, transform, and load data from SQL Data Warehouse by using Polybase
Design and implement an Azure SQL Data Warehouse
Manage and maintain a SQL Data Warehouse

Build data quality solutions

70-768 Exam: Developing SQL Data Models (Exam Link)
Study Links

Design and implement a data warehouse

Create a multidimensional database by using Microsoft SQL Server Analysis Services (SSAS)
Design and implement dimensions in a cube
Implement measures and measure groups in a cube

Design to tabular BI semantic model

Design and publish a tabular data model
Configure, manage, and secure a tabular model
Develop a tabular model to access data in near real time

Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)

Create basic MDX queries
Implement custom MDX solutions
- SCOPE Statement (MDX)
- MDX Forum
Create formulas by using the DAX language

Configure and maintain SQL Server Analysis Services

MCSA SQL 2016: Machine Learning (BI)

70-773 Exam: Analyzing Big Data with Microsoft R (Exam Link)
Study Links

Read and explore big data

Process big data

Process data with rxDataStep
- rxDataStep: Data Step for RevoScaleR data sources
- Date-Time Classes

Perform complex transforms that use transform functions

Manage data sets

Process text using RML packages

Build predictive models with ScaleR

Estimate linear models
Build and use partitioning models
Generate predictions and residuals
Evaluate models and tuning parameters
Create additional models using RML packages

Use R Server in different environments

Use different compute contexts to run R Server effectively
Optimize tasks by using local compute contexts
Perform in-database analytics by using SQL Server
Implement analysis workflows in the Hadoop ecosystem and Spark
Deploy predictive models to SQL Server and Azure Machine Learning

70-774 Exam: Perform Cloud Data Science with Azure Machine Learning (Exam Link)
Study Links

Prepare Data for Analysis in Azure Machine Learning and Export from Azure Machine Learning

Import and export data to and from Azure Machine Learning
Explore and summarize data
Cleanse data for Azure Machine Learning
Perform feature engineering
- Join Data
- Data Transformation / Manipulation
- Feature engineering in data science
- Feature Selection Modules
- Main Component Analysis
- Edit Metadata

Develop Machine Learning Models

Select an appropriate algorithm or method
Initialize and train appropriate models
Validate models

Operationalize and Manage Azure Machine Learning Services

Deploy models using Azure Machine Learning
Manage Azure Machine Learning projects and workspaces
Consume Azure Machine Learning models
Consume exemplary Cognitive Services APIs

Use Other Services for Machine Learning

Build and use neural networks with the Microsoft Cognitive Toolkit
Streamline development by using existing resources
Perform data sciences at scale by using HDInsights
Perform database analytics by using SQL Server R Services on Azure

MCSA SQL 2016: BI Reporting (BI)

70-778 Exam: Analyzing and Visualizing Data with Microsoft Power BI (Exam Link)
Study Links

Consume and Transform Data By Using Power BI Desktop

Model and Visualize Data

Configure Dashboards in the Power BI Service

Set up the dashboard
Publish dashboards
Configure security for dashboards
Configure organizational content packs and apps

70-779 Exam: Analyzing and Visualizing Data with Microsoft Excel (Exam Link)
Study Links

Consume and Transform Data by Using Microsoft Excel Connect to data sources

Model Data

Create and optimize data models
Create calculated columns, measures, and tables
Create performance KPIs
Create hierarchies

View Date

Create and manage PivotTables
Create and manage PivotCharts
Interact with Power BI
- Get data from Excel workbook files
- Import an Excel table into Power BI

MCSA SQL 2016: Data Engineering with Azure (BI)

70-775 Exam: Perform Data Engineering on Microsoft Azure HDInsight (Exam Link)
Study Links

Administer and Provision HDInsight Clusters

Implement Big Data Batch Processing Solutions

Implement Big Data Interactive Processing Solutions

Implement interactive queries for big data with Spark SQL
Perform exploratory data analysis by using Spark SQL
Implement interactive queries for big data with Interactive Hive
Perform exploratory data analysis by using Hive
Perform interactive processing by using Apache Phoenix on HBase

Implement Big Data Real-Time Processing Solutions

Create Spark streaming applications using DStream API
Create Spark structured streaming applications
Develop big data real-time processing solutions with Apache Storm
Build solutions that use Kafka
Build solutions that use HBase
- HBase Architecture, Use cases & Best practices in HDInsight
- What is HBase in HDInsight: A NoSQL database that provides BigTable-like capabilities for Hadoop
- HBase - Shell
- Get started with an Apache HBase example in HDInsight
- HDInsight HBase: 9 things you must do to get great HBase performance
- Configure HBase cluster replication within virtual networks

70-776 Exam: Perform Big Data Engineering on Microsoft Cloud Services (Exam Link)

MCSA SQL 2016: Azure Database Development (DEV)

70-777 Exam: Implementing NoSQL Solutions with DocumentDB and Azure Search (Exam Link)
70-473 Exam: Designing and Implementing Cloud Data Platform Solutions (Exam Link)
Study Links

Design and Implement Database Solutions for Microsoft SQL Server and SQL Database

Manage database management systems (DBMS) security

Design for high availability, disaster recovery, and scalability

Design and implement high availability solutions
Design and implement scalable solutions
Design and implement SQL Database data recovery

Monitor and manage database implementations on Azure

Monitor and troubleshoot SQL Server VMs on Azure
Monitor and troubleshoot SQL Database
Automate and manage database implementations on Azure

MCSE 2016 Data Management and Analytics

Certification Link

70-462 Exam: Designing Database Solutions for Microsoft SQL Server (Exam Link)
Study Links

Design a database structure

Design databases and database objects

Design database security

Design an application strategy to support security
Design database, schema, and object security permissions
Design instance-level security configurations

Design a troubleshooting and optimization solution

Design a maintenance strategy for database servers
Troubleshoot and resolve concurrency issues
Design and implement a high availability solution
Design a solution to monitor performance and concurrency
Design a monitoring solution at the instance level

70-464 Exam: Developing Microsoft SQL Server Databases (Exam Link)
Study Links

Implement database objects

Implement programming objects

Design and implement stored procedures
T-SQL Design table-valued and scalar functions
Create, use, and alter user-defined functions (UDFs)
Create and alter views

Design database objects

Optimize and troubleshoot queries

Optimize and tune queries
Graphical Query Plan Tutorial
Troubleshoot and solve performance problems
- Resource Usage Monitor (System Monitor)
- Correlate with Trace with Windows Performance Log Data (SQL Server Profiler)
Optimize indexes
Capture and analyze execution plans
Collect performance and system information

70-465 Exam: Designing Database Solutions for Microsoft SQL Server (Exam Link)
Study Links

Design a database structure

Design databases and database objects

Design for concurrency

Design T-SQL stored procedures

Design a management automation strategy

Design for transactions

Design database security

Design an application strategy to support security
Design database, schema, and object security permissions
Design instance-level security configurations

Design a troubleshooting and optimization solution

Design a maintenance strategy for database servers
Troubleshoot and resolve concurrency issues

Design and implement a high availability solution

Design a solution to monitor performance and concurrency

Design a monitoring solution at the instance level

70-466 Exam: Implementing Data Models and Reports (Exam Link)
Study Links

Design dimensions and measures

Given a requirement, identify the dimension / measure group relationship that should be selected.
Design patterns for representing business facts and dimensions (many-to-many relationships).
Handle degenerate dimensions in a cube.
Identify the attributes for dimensions.
Identify the measures.
- Measures and measure groups.
- SSAS Interview Questions on Measures, Actions, and Storage.
Aggregation behavior for the measures.
- SQL Server Analysis Services Aggregation Designs.
- Define Semi additive Behavior
Build hierarchies.
- SQL Server Analysis Services Attribute Relationships.
Defines granularity of dimension relationships.
- SQL Server Analysis Service Levels of Granularity.

Implement and configure dimensions in a cube

Translations
- SQL Server Analysis Services Interview Questions on Dimensions, Hierarchies and Properties - What are Dimension Translations?
- Defining and Browsing Translations
Define attribute relationships.
- SQL Server Analysis Services Attribute Relationships.
- Attribute Relationships.
Implement hierarchies.
- Creating a Hierarchy.
Implement SQL Server Analysis Services (SSAS) dimensions and cubes.
- Build a Cube From an Existing Data Source Using SQL Server Analysis Services.
- Multidimensional Modeling (Adventure Works Tutorial).
Create the Attribute Relationships that should be made for a given set of attributes in a dimension.
- Lesson 5: Defining Relationships Between Dimensions and Measure Groups.
- SQL Server Analysis Services Attribute Relationships.
Develop new custom attributes on dimensions.
- Custom Attribute Names in Role Playing Dimensions for SSAS.
- Add an Attribute to a Dimension.
Detect possible design flaws in attribute relationships.
- Analysis Service Performance Guide
Implement time dimensions in cubes.
Manage SSAS parent-child dimensions.
- Parent-Child Dimensions.
- SQL Server Analysis Services Interview Questions Part II - Dimensions.
Dimension types.
- SQL Server Analysis Services Interview Questions Part II - Dimensions.

Design a schema to support cube architecture

Multidimensional modeling starting from a star schema.
- Creating a Star Schema Using a Data Source View.
- Designing the Star Schema Database
Relational modeling for a data source view.
- DWH Schemas
- Introduction to the SQLBI Methodology
Choose or create a topology.
- DWH Schemas
Identify the appropriate data types with correct precision and size.
- Data Types in Analysis Services.
- Tips for Optimizing SQL Server OLAP / Analysis Services: Data Warehouse and Datamart Performance.

Create and configure measures

Logically group measures and configure Measure Group Properties.
- SSAS - Best Practices and Performance Optimization - Part 3 of 4.
- Measures and Measure Groups.
Select appropriate aggregation functions.
- Use Aggregate Functions.
Format measures.
- Configure Measure Properties.
Design the measure group for the correct granularity
- SQL Server Analysis Service Levels of Granularity.
- Defining Dimension Granularity within a Measure Group.

Implement a cube

Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube.
- Multidimensional Modeling (Adventure Works Tutorial).
- Creating a Sample SSAS Project and Cube
Use SSDT-BI to do non-additive or semi-additive measures in a cube.
- Defines Semiadditive Behavior.
Define measures.
- Defining the Cube.
- Creating a Cube using the Cube Wizard.
Specify perspectives.
- Lesson 9: Defining Perspectives and Translations.
- SQL Server Analysis Services Perspectives.
Defines dimension usage.
- Dimension Relationships.
Defines cube-specific dimension properties.
- Lesson 4: Defining Advanced Attribute and Dimension Properties.
Define measure groups.
- Create Measures and Measure Groups.
Implement reference dimensions.
- Dimension Relationships.
Implement many-to-many relationships.
Implement fact relationships.
- Dimension Relationships.
Implement role-playing relationships.
- Defining Role Playing Dimensions for SQL Server Analysis Services.
Create and manage linked measure groups and linked dimensions.
- Controlling the use of linked objects in SQL Server Analysis Services.
- Define Linked Dimensions
Create actions.
- Lesson 8: Defining Actions.

Create Multidimensional Expressions (MDX) Queries

Identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, and more).
Identify which MDX statement would return the required result.
- MDX Function Reference (MDX)
Implement a custom MDX or logical solution for a prepared case task.

Implement custom logic in a data model

Defines key performance indicators (KPIs).
- Testing KPIs for SQL Server Analysis Services.
- Lesson 7: Defining Key Performance Indicators (KPIs).
Define calculated members.
- Defining Calculated Members.
Create relative measures (growth, YoY, same period last year).
- Handling Relative Time Periods.
Percentage of total using MDX.
- Using MDX to Calculate Both Values and Percentages for Analysis Services.
- MDX for Percent of Total for a subset of a Hierarchy.
Named sets.
- Create Named Sets.
Implement ranking and percentile.
- Rank (MDX).
- Ranking results from MDX queries.
Defines MDX script to import partial PowerPivot model.
- Analysis Services MDX Query Designer (Power Pivot).

Implement storage design in a multidimensional model

Create aggregations.
Create partitions.
Storage modes.
- Partition Storage Modes and Processing.
- How To Implement Proactive Caching in SQL Server Analysis Services SSAS.
Defines proactive caching.
- How To Implement Proactive Caching in SQL Server Analysis Services SSAS.
- Proactive Caching (Partitions).
Manage write-back partitions.
- SQL Server Analysis Services Partition Maintenance and Processing, Write enable partition.
- Set Partition Writeback.
Implement linked cubes.
- Controlling the use of linked objects in SQL Server Analysis Services.
- Defines Linked Measure Groups.
Implement distributed cubes.
- Create and Manage a Remote Partition (Analysis Services).

Select an appropriate model for data analysis

Select Tabular versus Multidimensional based on scalability needs.
Traditional hierarchical.
- Comparing Tabular and Multidimensional Solutions (SSAS).
Volume date.
- Comparing Tabular and Multidimensional Solutions (SSAS).
Select appropriate organizational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status.
- Comparing Tabular and Multidimensional Solutions (SSAS).

Manage, maintain, and troubleshoot the SQL Server Analysis Services (SSAS) database

Analyze data model performance

Identify performance consequences of data source view design.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.3).
Optimize performance by changing the design of the cube or dimension.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.1 and 2.2).
Analyze and optimize performances of an MDX / DAX query.
Optimize queries for huge data sets.
Optimize MDX in the calculations.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.4).
Performance monitor counters.
- Performance Counters (SSAS).
Select appropriate Dynamic Management Views for Analysis Services.
Analyze and define performance counters
- Performance Counters (SSAS).
- SSAS Operation Guide, chapter 3.
Monitor growth of the cache
- Part 5: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services.
- Performance Counters (SSAS), Cache.
Define and view logging options
- Log operations in Analysis Services.
- SSAS - Best Practices and Performance Optimization - Part 4 of 4.

Process data models

Define processing of tables or partitions for tabular and multidimensional models
Defines processing of databases, cubes, and dimensions for multidimensional models.
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 4).
- SQL Server Best Practices Article.
Select full processing versus incremental processing.
Define remote processing;
- Remote Processing (Analysis Services).
Defines lazy aggregations.
- SSAS Processing Mode (Lazy Aggregations).
Automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA).
- Create SQL Server Analysis Services Partitions using AMO.
- Developing with Analysis Management Objects (AMO).
Process and manage partitions by using PowerShell.

Troubleshoot data analysis issues

Use SQL Profiler.
- Use SQL Server Profiler to Monitor Analysis Services
Troubleshoot duplicate key dimension processing errors.
- SQL Server Analysis Services SSAS Processing Error Configurations.
- You get “A duplicate attribute key has been found when processing ..” error message when processing a dimension.
Error logs and event viewer logs of SSAS.
Mismatch of data: Incorrect relationships or aggregations.
Dynamic security issues.
Validate logic and calculations.

Deploy SSAS databases

Deployment Wizard.
- Using the SQL Server Analysis Services (SSAS) Deployment Wizard.
- Running the Analysis Services Deployment Wizard.
Implement BIDS.
Deploy SSMS.
- Deploy Model Solutions Using XMLA.
Test solution post deployment.
- SQL Server: SQL Server Unit Testing OLAP Cubes Using C #
- SSAS Operation Guide, chapter 4.
Decide whether or not to process.
- Avoid Common SQL Server Analysis Services Problems.
Test different roles.
- SQL Server Analysis Services - Testing Cube Roles.
- Configuring permissions for SQL Server Analysis Services.

Install and maintain an SSAS instance

Install SSAS.
- Install Analysis Services in Tabular Mode.
- Install SQL Server 2012 from the Installation Wizard (Setup).
Install development tools.
- SQL Server Data Tools (SSDT) is Missing After Installing SQL Server 2014.
- Download SQL Server Data Tools (SSDT or SSDT-BI).
Identify development and production installation considerations.
- Deploying Analysis Services Development and Production Systems
Upgrade SSAS instances.
- Upgrade Analysis Services
Defines data file and program file location.
- SSAS Operation Guide, chapter 2.5.
- Analysis Services Configuration - Data Directories.
Plan for Administrator accounts.
- SSAS Operation Guide, chapter 5.4
Defines server and database level security.
- SSAS Operation Guide, chapter 5.
Support scale-out read-only.
- SSAS Operation Guide, chapter 7.5
Update SSAS (service packs).
- How to obtain the latest service pack for SQL Server 2012.
- How to obtain the latest service pack for SQL Server 2014.
- KB2755533 How to obtain the latest SQL Server 2012 service pack
- KB2527041 How to obtain the latest SQL Server 2008 R2 service pack
- Microsoft SQL Server support lifecycle
- KB321185 How to determine the version and edition of SQL Server and its components
- KB957826 The builds for all SQL Server versions
- KB822499 Naming schema and Fix area descriptions for SQL Server software update packages
- KB824684 Description of the standard terminology that is used to describe Microsoft software updates
Install and maintain each instance type of Analysis Services including PowerPivot.
Restore and import PowerPivot.
- Power Pivot and SSAS (Tabular) Restore
Back up and restore by using PowerShell.

Build a tabular data model

Configure permissions and roles in a tabular model

Configure server roles.
- Installing Analysis Services 2012 - Tabular Model.
Configure SSAS database roles.
- Roles and Permissions (Analysis Services).
- Securing the Tabular BI Semantic Model, Managing roles and permissions
Implement dynamic security (custom security approaches).
- Implement Dynamic Security by Using Row Filters.
- Securing the Tabular BI Semantic Model, Dynamic security
Role-based access.
- Roles (Tabular SSAS)
- Securing the Tabular BI Semantic Model, Managing roles and permissions
Test security permissions.
- Securing the Tabular BI Semantic Model
Implement cell-level permissions.
- Roles (Tabular SSAS).

Implement a tabular data model

Defines tables, import data.
- Getting started with Tabular Model in SQL Server 2012 - Part 1
- Lesson 2: Add Data
Define calculated columns.
- Lesson 6: Create Calculated Columns.
Define relationships.
- Lesson 5: Create Relationships.
Define hierarchies and perspectives.
Manage visibility of columns and tables.
- Getting started with the SQL Server 2012 Tabular Model - Part 2
Embed links.
- Getting started with the SQL Server 2012 Tabular Model - Part 2
Optimize BISM for Power View.
- Optimizing High Cardinality Columns in VertiPaq
- Power View: Explore, view, and present your data
Mark a date table.
- Getting started with the SQL Server 2012 Tabular Model - Part 2
- Lesson 4: Mark as Date Table.
Sort a column by another column.
- Sorting Dates Columns in PowerPivot v2 and Tabular BISM.

Implement business logic in a tabular data model

Implement measures and KPIs.
- Getting started with the SQL Server 2012 Tabular Model - Part 2
- Lesson 8: Create Key Performance Indicators
Implement Data Analysis Expressions (DAX).
Define relationship navigation.
- Relationships (Tabular SSAS)
- Many-to-Many Relationships in DAX.
Implement time intelligence.
- SQL Server Analysis Service DAX Time Intelligence.
- Time Intelligence Functions (DAX)
Implement context modification.
- Understanding the SQL Server 2012 BI Semantic Model (BISM)
- Filter Functions (DAX)

Implement data access for a tabular data model

Manage partitions.
- Getting started with the SQL Server 2012 Tabular Model - Part 2
- Lesson 11: Create Partitions
Processing.
- Processing tabular models 101
Select xVelocity versus DirectQuery for data access.
- Tabular query modes: DirectQuery vs. In-Memory

Build a report with SQL Server Reporting Services (SSRS)

Design a report

Select report components (crosstab report, Tablix).
- Tablix Data Region (Report Builder and SSRS).
- SQL Server 2012 Tutorials: Reporting Services.
Chart design.
- SQL Server Reporting Services Charts Tips.
Data visualization components.
- Create a Simple Report with the Wizard.
Design report templates (Report Definition Language).
- Report Definition Language (SSRS).
Identify the data source and parameters.
- SQL Server Reporting Services Parameters Tips.
- Data Connections, Data Sources, and Connection Strings (SSRS).
Design a grouping structure.
- Understanding Groups (Reporting Services).
Drill-down reports.
Drill-through reports.
- SQL Server Reporting Services Drill Through Tips.
- Drillthrough Reports (Report Builder and SSRS).
Determine if any expressions are required to display data that is not coming directly from the data source.
- Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.

Implement a report layout

Formatting
- Lesson 5: Formatting a Report (Reporting Services)
- SSRS Tutorial videos.
Apply conditional formatting.
- SQL Server Reporting Services Expressions Tips and Tricks.
Page configuration.
- Pagination in Reporting Services (Report Builder and SSRS)
Implement headers and footers.
- Add or Remove the Page Header or Footer (Report Builder and SSRS).
Implement matrixes, table, chart, images, list, indicators, maps, and groupings in reports.
Use Report Builder to implement a report layout.
- Page Layout and Rendering (Report Builder and SSRS).
Create a range of reports using different data regions.
- Data Regions and Maps (Report Builder and SSRS).
Defines custom fields (implementing different parts of the report).
- Dataset Fields Collection (Report Builder and SSRS).
Implement collections (global collections).
- Built-in Globals and Users References (Report Builder and SSRS).
Define expressions.
- Expressions (Report Builder and SSRS).
Implement data visualization components.
- Tables, Matrices, and Lists (Report Builder and SSRS).
Identify report parts.
- Report Design View (Report Builder)
implement group variables and report variables.
- Report and Group Variables Collections References (Report Builder and SSRS).
Design for multiple delivery extension formats.
- Interactive Functionality for Different Report Rendering Extensions (Report Builder and SSRS).

Configure authentication and authorization for a reporting solution

Configure server-level and item-level role-based security.
- SQL Server Reporting Services 2012 Permissions.
- Security and Protection (SSRS).
Configure reporting service security (setup or addition of role).
- SQL Server Reporting Services 2012 Permissions.
Authenticate against data source.
- Specify Credential and Connection Information for Report Data Sources.
Store credential information.
- Specify Credential and Connection Information for Report Data Sources.
Describe Report Server security architecture and site level security.
- OVERVIEW OF SQL SERVER REPORTING SERVICES 2012 ARCHITECTURE, FEATURES, AND TOOLS.
- Authorization in Reporting Services.
Create system level roles, item level security.
- Predefined Roles.
- SQL Server Reporting Services 2012 Permissions.
Create a new role assignment.
- SQL Server Reporting Services 2012 Permissions.
Assign Windows users to roles.
- SQL Server Reporting Services 2012 Permissions.
Secure reports using roles.
- SQL Server Reporting Services 2012 Permissions.
Configure SharePoint groups and permissions.
- Set Permissions for Report Server Items on a SharePoint Site (Reporting Services in SharePoint Integrated Mode)
Defines varying content for different role memberships.
- SQL Server Reporting Services 2012 Permissions.

Implement interactivity in a report

Drilldown.
Drillthrough.
- Drillthrough Reports (Report Builder and SSRS).
Interactive sorting.
- Interactive Sorting for SQL Server Reporting Services Report.
Parameters: (databound parameters, multi-value parameters).
- Working With Multi-Select Parameters for SSRS Reports.
Create dynamic reports in SSRS using parameters.
- Creating Dynamic Report Filters in SQL Server Reporting Services SSRS.
Implement show / hide property.
- Hide an Item (Report Builder and SSRS).
Actions (jump to report).
- Add a Bookmark to a Report (Report Builder and SSRS)
Filters
- Creating Dynamic Report Filters in SQL Server Reporting Services SSRS.
Parameter list.
- SQL Server Reporting Services Using Multi-value Parameters.
Fixed headers.
- SQL Server Reporting Services Reporting Manager Tips and Tricks
Document map
- SQL Server Reporting Services Bookmarks and Document Maps.
Embedded HTML.
- Add HTML into a Report (Report Builder and SSRS)

Troubleshoot reporting services issues

Query the ReportServer database.
- Retrieve SSRS report server database information.
View Reporting Services log files.
- Reporting Services Logging.
- SQL Server Reporting Services (SSRS) Log Files for Troubleshooting.
Use Windows Reliability and Performance monitor data for troubleshooting.
- Using PerfMon for SQL Server Reporting Services Performance Management.
- Performance Counters for the ReportServer: Service and ReportServerSharePoint: Service Performance Objects.
Use the ReportServer: define service and web service objects.
- Report Server Web Service.
Monitor for long-running reports.
Rendering, and connectivity issues.
- SQL Server Reporting Services 2012 SP2 CU3 Report Rendering Issues.
- Troubleshooting Reports: Report Performance.
Use SQL Profiler.
- Using SQL Profiler for SSRS Reports - Missing main dataset SQL Query in trace.
Perform data reconciliation for incorrect relationships or aggregations.
- TESTING SSRS REPORTS
Detect dynamic security issues.
- SQL Server Reporting Services Security Tips
Validate logic and calculations.
- SQL Server Reporting Services Best Practices Tips.

Manage a report environment

Manage subscriptions and subscription settings.
- Subscriptions and Delivery (Reporting Services).
Defines data-driven subscriptions.
- Data-Driven Subscriptions.
Manage data sources.
- Data Sources Supported by Reporting Services (SSRS).
Integrate SharePoint Server.
- Install Reporting Services SharePoint Mode for SharePoint 2013.
Defines email delivery settings.
- Configure Report Server for E-Mail Delivery (Reporting Services).
Manage the number of snapshots.
- Working with Report Snapshots in SQL Server Reporting Services SSRS.
- Creating, Modifying, and Deleting Snapshots in Report History.
Manage schedules.
- Schedules
Running jobs, and report server logs.
- Manage a Running Process.
- Report Server Execution Log and the ExecutionLog3 View.
Manage report server databases.
- Administer the Report Server Database.
Manage the encryption keys.
- SQL Server Reporting Services (SSRS) Encryption Key.
- Configure and Manage Encryption Keys.
Set up the execution log reporting.
- SQL Server Report Server 2008 R2 Execution Log Reports.
- Report Server Execution Log and the ExecutionLog3 View.
Review the reports.
- SQL Server Reporting Services Best Practices Tips.
Configure site-level settings.
- Site Settings Page (Report Manager)
Design report lifecycle.
- Report Life Cycle: Creating, Managing, and Delivering Reports.
Automate management of reporting services.
Create a report organization structure.
- Reporting Services Concepts (SSRS)
Install and configure reporting services.
- Install Reporting Services Native Mode Report Server (SSRS)
Deploy custom assemblies.
- SQL Server Reporting Services Custom Code Assemblies.
- Deploying a Custom Assembly.

Configure report data sources and datasets

Select appropriate query types (stored procedure versus table versus text only).
- Pros and Cons: Stored Procedures – vs.- Embedded Queries – vs.- Views in an SSRS Dataset
Configure parameterized connection strings (dynamic connection strings).
- Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.
Defines filter location (dataset versus query).
- MSBI (SSRS) Training –SSRS Report Parameters, Filters, Query Parameters.
- Add a Filter to a Dataset (Report Builder and SSRS).
Configure data source options, for example, extract and connect to multiple data sources.
- Data Connections, Data Sources, and Connection Strings (SSRS)
Shared and embedded data sources and datasets.
- Create, Modify, and Delete Shared Data Sources (SSRS)
- Embedded and Shared Datasets (Report Builder and SSRS)
Use custom expressions in data sources.
- Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.
Connect to Microsoft Azure SQL database.
- SQL Azure Connection Type (SSRS)
Connect to Microsoft Azure Marketplace.
- SSRS Report using dataset.
- Microsoft Azure Marketplace Language.
Implement DAX and MDX queries to retrieve appropriate data sets.
Work with non-relational data sources, such as XML or SharePoint lists.
- SharePoint List Connection Type (SSRS)
- XML Connection Type (SSRS).
Connect to HDInsight Server.
- SQL Server Reporting Services

70-467 Exam: Designing Business Intelligence Solutions with Microsoft SQL Server (Exam Link)
Study Links

Plan business intelligence (BI) infrastructure

BI infrastructure design

Design a reporting solution

Design an ETL solution

SSIS package execution design
Plan deploy SSIS solutions
Design package configurations for SSIS packages
- Create Package Configurations

70-475 Exam: Designing and Implementing Big Data Analytics Solutions (Exam Link)
Study Links

Design big data batch processing and interactive solutions

Big data design real-time processing solutions

Operationalize end-to-end cloud analytics solutions

If you would like to join a Whatsapp group focused on Microsoft certifications, please visit the link https://chat.whatsapp.com/invite/0pnHXak46QCHOMfGXOD2OC (Whatsapp) or https://t.me/certificacao (Telegram) on your mobile and enjoy the content and tips that are sent there!

Want to know ALL about data area certification exams? Watch the Data Platform Certifications Webinar, which I participated with Caio Lover, Danilo Cardoso, Maruan Aawar and Meirieli Ribeiro:

Here are some links that can help you in your studies for certification exams:

Regards and see you next post.

Why make sure?

What do Microsoft certification exams look like?

And the beta tests? How do they work?

How do I become an MCSA on Data Plataform?

How do I become an MCSE Data Management and Analytics?

Microsoft Technology Associate (MTA)

MCSA SQL Server 2012 / 2014

Create database objects

Work with data

Modify data

Troubleshoot and optimize

Install and configure

Maintain instances and databases

Optimize and troubleshoot

Manage Data

Implement security

Implement high availability

Design and Implement a Data Warehouse (11%)

Design and implement dimensions

Design and implement fact tables

Extract and Transform Data (23%)

Define connection managers

Design data flow

Implement data flow

Manage SSIS package execution

Implement script tasks in SSIS

Load Data (27%)

Design control flow

Implement package logic by using SSIS variables and parameters

Implement control flow

Implement data load options

Implement script components in SSIS

Configure and Deploy SSIS Solutions (24%)

Troubleshoot data integration issues

Install and maintain SSIS components

Implement auditing, logging, and event handling

Deploy SSIS solutions

Configure SSIS security settings

Build Data Quality Solutions (15%)

Install and maintain Data Quality Services

Implement master data management solutions

Create a quality data project to clean data

Bonus Content

MCSA SQL Server 2016

MCSA SQL 2016: Database Development (Developer)

MCSA SQL 2016: Database Administrator (DBA)

MCSA SQL 2016: Business Intelligence Developer (BI)

MCSA SQL 2016: Machine Learning (BI)

Read and explore big data

Process big data

Build predictive models with ScaleR

Use R Server in different environments

Prepare Data for Analysis in Azure Machine Learning and Export from Azure Machine Learning

Develop Machine Learning Models

Operationalize and Manage Azure Machine Learning Services

Use Other Services for Machine Learning

MCSA SQL 2016: BI Reporting (BI)

Consume and Transform Data By Using Power BI Desktop

Model and Visualize Data

Configure Dashboards in the Power BI Service

Consume and Transform Data by Using Microsoft Excel Connect to data sources

Model Data

View Date

MCSA SQL 2016: Data Engineering with Azure (BI)

Administer and Provision HDInsight Clusters

Implement Big Data Batch Processing Solutions

Implement Big Data Interactive Processing Solutions

Implement Big Data Real-Time Processing Solutions

MCSA SQL 2016: Azure Database Development (DEV)

Design and Implement Database Solutions for Microsoft SQL Server and SQL Database

Manage database management systems (DBMS) security

Design for high availability, disaster recovery, and scalability

Monitor and manage database implementations on Azure

MCSE 2016 Data Management and Analytics

Design a database structure

Design databases and database objects

Design database security

Design a troubleshooting and optimization solution

Implement database objects

Implement programming objects