Microsoft Certifications - Tips, links and study materials for MCSA and MCSE SQL Server 2016 exams

Hey guys,
Are you all right?

I'm a little absent here on the blog, but I'll soon be back posting much more frequently, as soon as I complete some projects and consultancies 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 SQL Server 2016 MCSA and MCSE certification exams and tests, just as I have been preparing for the last few months.

If you are preparing for your first certification test and want to start with the MTA, I created the post Microsoft Certifications – Study material in Portuguese to obtain your first certification (MTA) to help and answer questions for those who are just starting out in the world of certification and want to know more about this certification.

First of all, let's learn about Microsoft's current certifications for the Data Platform and begin to familiarize ourselves with the acronyms:

MTA (Microsoft Technology Associate): It is Microsoft's first certification test and the easiest, recommended for people who are starting their careers and are still getting familiar with technology. She no is a prerequisite for taking the MCSA and MCSE tests and taking it is optional.
As an incentive to take the MTA test, Microsoft gives a small discount on taking this exam (you pay 77 dollars instead of 100 dollars for the other MCSA and MCSE tests)
MCSA (Microsoft Certified Solutions Associate): Medium/high level test, aimed at people who already have experience with the technologies involved.
MCSA certification is a prerequisite for obtaining MCSE certification.
MCSE (Microsoft Certified Solutions Expert): The last level of Microsoft certification tests, the MCSE tests tend to be the most difficult among the Microsoft tests, and must be taken by highly trained professionals with great experience and experience in the technologies in question.

Why make sure?

The BornToLearn blog, world-renowned by the Microsoft technical community, made a post available where they mention 6 advantages that professionals can obtain by becoming certified (You can check the post on this link here), which is summarized in the image below:

Before a certification, it's a challenge to start advancing your technology career. After certification, you have strong momentum towards the next big hurdle. In fact, 33% of certified professionals reported having secured a job after certification. Plus, getting certified is addictive.
Keeping up when you're new to the job is a challenge. The good news is that certification gives you a proven foundation of specialized, real-world knowledge to build on, so you'll be ready to move up in the workplace 39% faster than your peers. If you're still not convinced, 38% of IT professionals said certification helped them perform complicated tasks with more confidence. It's science.
It's a struggle to differentiate yourself and show that you're the best person for the job when you're starting out 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 into interviews knowing that you are certified will make you feel great.
Stay up to date and never run the risk of 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 while gaining new tricks, such as new technologies and features, when industry standards change.
Getting impatient for the next promotion? The solution is quite simple. If you are certified, you receive more pay raises and more chances to move up in your career. Plus, you start right out of the gate earning 15% more on average than your non-certified 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 typically keep them around 15% longer than other employees. When will you be certified??

Video of MVP Rafael Bernares about the importance of certification

What are the Microsoft certification tests like?

If you have never taken it, I would like to prepare you well, as Microsoft certification tests tend to be very difficult, since:

Some tests have discursive questions, where you need to write SQL queries following several rules, according to the statement.
In some questions, the answer is a composition of 4 options, out of about 10, where you must mark the correct alternatives and align them in the correct order of execution. The chance of you getting a question like this right “on the fly” is very small.
In some questions, all the answers are correct, but you must choose the option that has the best performance, that is, the cheapest, financially, for example.
In some stages of the test, you don't have the chance to go back to the question and think better about the correct answer: You clicked on next, there's no going back.
Generally the time is short: 3 minutes per question and some have a statement with more than 10 lines of text.

Microsoft certification tests cost $100 (If you are a student, you have a 50% discount. 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 the test).

These exams usually have a translated version into Portuguese (except the Beta or recently launched exams, where there is only an English language option), but technical terms are usually translated as well (View = Vision, Trigger = Trigger, etc.), which can end up confusing you during the test. In my opinion, a professional who aspires to be certified and pursue a career as a DBA can no longer afford the luxury of not knowing how to at least read texts and articles in English.

NOTICE: If you have been working in the IT area 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 (often, they don't even know what they are memorizing) and manage to pass the exams. Avoid this type of “study”, as there is no point in having several certificates on your CV and when you go to join a company, which hired you because of these certifications, you end up “embarrassed” for not having all the knowledge you appeared to have. In the technical community, this type of “study” is highly repudiated and discouraged.

What about beta tests? How do they work?

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

Despite being BETA tests, their results are computed and are valid for the certification process, in the same way as a paid test. As explained in this link, if you pass the beta test, you will 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 must wait for the final version to be released before retaking the exam.

Since the tests are still in the testing phase (they are BETA), you should be aware of the possibility of errors in the questions. Furthermore, the tests are generally only available in English and instead of a few randomly chosen questions, the test contains many more questions (generally 59-62 instead of the 35-40 of a “normal” test), so the beta test is usually longer than the final test.

Another difference between the final version of the tests and 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 made available) – Generally around 4 months.

How do I become an MCSA on Data Platform?

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

Now in the SQL Server 2016 MCSA Certification Path, you need to pass just 2 tests from the same area of activity, be it DEV, DBA or BI. In other words, if you only pass 1 DEV test and another DBA test, you no 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 the 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 at least one 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 is the case in the tests above:
Exam 70-762 – Developing SQL Databases (DEV)
Exam 70-767: Implementing a Data Warehouse using SQL (BI)
Exam 70-768: Developing SQL Data Models (BI)
Exam 70-773: Analyzing Big Data with Microsoft R (BI)
Exam 70-774: Perform Cloud Data Science with Azure Machine Learning (BI)
Exam 70-775: Perform Data Engineering on Microsoft Azure HDInsight (BI)

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

Note: In the MCSE test, the area of activity of your MCSA is irrelevant to obtaining the title. Once 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. In other words, if you are a BI MCSA (Passed 70-767 and 70-768), for example, and pass 70-464, which is theoretically aimed at Database Developers, you will become an MCSE Data Management and Analytics.

Microsoft Technology Associate (MTA)

Certification Link

Exam 98-364: 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 an MCSA certified professional for SQL Server 2012/2014 (Yes, it is a certification for both versions), you will need to pass the 3 tests below.

Exam 70-461: 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
Combine 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

Exam 70-462: 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 resolve competition problems
Collect and analyze troubleshooting data
Audit SQL Server instances.

Manage Data

implement security

Implement high availability

Exam 70-463: 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/
Conform 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
Defines 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
ScriptComponent 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-bit 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-taskrdquundash-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 programatically 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 data quality 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 cleaning 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/
References SSIS 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 already explained above, to become a SQL Server 2016 MCSA, you will need to pass two tests from the same area of activity, be it BI, DEV or DBA.

If you already have the title of MCSA SQL Server 2012/2014 and want to update your certification to SQL Server 2016, simply take 1 test in the category you want to obtain the title of MCSA SQL Server 2016, as shown below.

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

The upgrade test for SQL Server 2016 certification is paid normally, like any other exam (When the SQL 2016 tests started, Microsoft released the free upgrade, but the offer has already expired).

MCSA SQL 2016: Database Development (Developer)

Exam 70-761: 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 using table expressions
Group and pivot data by using queries
Query temporal data and non-relational data

Program databases using Transact-SQL

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

Exam 70-762: 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 competition

implement transactions
Manage isolation levels
Optimize competition 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)

Exam 70-764: 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
Monitor SQL Server instances
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)

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

Implement SQL in Azure

Manage databases and instances

Deploy and migrate applications

MCSA SQL 2016: Business Intelligence Developer (BI)

Exam 70-767: 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

Integrate solutions with cloud data and big data

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

Build data quality solutions

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

Design and implement a data warehouse

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

Design a 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 using the DAX language

Configure and maintain SQL Server Analysis Services

MCSA SQL 2016: Machine Learning (BI)

Exam 70-773: 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 transformations 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 using SQL Server
Implement analysis workflows in the Hadoop ecosystem and Spark
Deploy predictive models to SQL Server and Azure Machine Learning

Exam 70-774: 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
- Date Transformation/Manipulation
- Feature engineering in data science
- Feature Selection Modules
- Principal 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 using HDInsights
Perform database analytics using SQL Server R Services on Azure

MCSA SQL 2016: BI Reporting (BI)

Exam 70-778: 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

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

Exam 70-779: 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)

Exam 70-775: 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 using Spark SQL
Implement interactive queries for big data with Interactive Hive
Perform exploratory data analysis 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

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

MCSA SQL 2016: Azure Database Development (DEV)

Exam 70-777: Implement NoSQL Solutions with DocumentDB and Azure Search (Exam Link)
Exam 70-473: 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

Exam 70-462: 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 competitive issues
Design and implement a high availability solution
Design a solution to monitor performance and competition
Design a monitoring solution at the instance level

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

Implement database objects

Implement programming objects

Design and implement stored procedures
Design T-SQL 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 resolve performance problems
- Monitor Resource Usage (System Monitor)
- Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
Optimize indexes
Capture and analyze execution plans
Collect performance and system information

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

Design a database structure

Design databases and database objects

Design for competition

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 competitive issues

Design and implement a high availability solution

Design a solution to monitor performance and competition

Design a monitoring solution at the instance level

Exam 70-466: 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 degenerates dimensions in a cube.
Identify the attributes for dimensions.
Identify the measures.
- Measures and measurement groups.
- SSAS Interview Questions on Measurements, Actions, and Storage.
Aggregation behavior for the measures.
- SQL Server Analysis Services Aggregation Designs.
- Defines 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
Defines 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.
Defines measures.
- Defining a 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.
Defines 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).
Defines 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).
Date volume.
- 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 a 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.
Set and view logging options
- Log operations in Analysis Services.
- SSAS – Best Practices and Performance Optimization – Part 4 of 4.

Process data models

Defines 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.
Defines 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: Unit Testing SQL Server 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 read-only scale-out.
- 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 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 (SSAS Tabular)
- Securing the Tabular BI Semantic Model, Managing roles and permissions
Test security permissions.
- Securing the Tabular BI Semantic Model
Implement cell-level permissions.
- Roles (SSAS Tabular).

Implement a tabular data model

Define tables, import data.
- Getting started with Tabular Model in SQL Server 2012 – Part 1
- Lesson 2: Add Data
Defines calculated columns.
- Lesson 6: Create Calculated Columns.
Defines relationships.
- Lesson 5: Create Relationships.
Defines 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, visualize, 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 BISM Tabular.

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).
Defines relationship navigation.
- Relationships (SSAS Tabular)
- 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.
Design chart.
- 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 a Page Header or Footer (Report Builder and SSRS).
Implement matrices, tables, charts, images, lists, 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).
Defines 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 a 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 Report 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.
Set email delivery settings.
- Configure the 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

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

Plan business intelligence (BI) infrastructure

Design BI infrastructure

Design a reporting solution

Design an ETL solution

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

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

Design big data batch processing and interactive solutions

Design big data real-time processing solutions

Operationalize end-to-end cloud analytics solutions

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

Do you want to know EVERYTHING about the data certification tests? Watch the Webinar on Data Platform Certifications, which I participated in together with Caio Amante, Danilo Cardoso, Maruan Aawar and Meirieli Ribeiro:

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

A hug and see you in the next post.

Dirceu Resende

Database & BI Architect · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram View my certifications

Comentários (0)

Carregando comentários…

Why make sure?

What are the Microsoft certification tests like?

What about beta tests? How do they work?

How do I become an MCSA on Data Platform?

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 data quality 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