Study material exam 70-463 Implement Data Warehouse with SQL Server

Problem

Would you like to know Microsoft's tips for passing the Implementing a Data Warehouse exam for the MCSA SQL Server 2012 certification? Would you like access to a collection of free, quality sources for preparing for the exam? Even if you do not plan on taking the exam, would you like access to high quality resources that offer guidelines for building and maintaining data warehouses with SQL Server 2012?

Solution

There are many ways to prepare for any Microsoft certification exam, such as the 70-463 exam for implementing a data warehouse. One preparation strategy is to grow your mastery for what Microsoft says will be covered by the exam. Even if you already have another set of exam training material, you may find it beneficial to study material that is available for free -- especially if it is on one of the Microsoft web sites or specifically targets content that Microsoft says you need to know.

The Microsoft Learning web site includes a list of major topics for the 70-463 exam. These major topics are further segmented into a collection of sub-topics. Each major topic is listed with a percentage that denotes the weight of that topic on the exam. By focusing on a succession of individual sub-topics, you can concentrate your preparation time on discrete units of content. The Microsoft Learning web site also includes text further elaborating the type of content within the scope of a sub-topic. However, this text always begins with the phrase "may include but is not limited to". Therefore, be on guard to learn thoroughly the topics and sub-topics and not just specific items mentioned in the text.

This tip has two main objectives.

The first is to review the major topics and sub-topics for the 70-463 exam.
The second is to provide a collection of about 130 URLs that are keyed to exam sub-topics.

In compiling the list of URLs a conscious effort was made to focus on content for SQL Server 2012 from Microsoft web sites. This is because the exam is for SQL Server 2012 proficiency with implementing a data warehouse, and you will be tested on your knowledge of the Microsoft way to implement data warehouses. I also selectively included URLs for non-Microsoft web sites and content that did not specifically target SQL Server 2012 when I felt the URL's content could materially improve your grasp of exam major topics and sub-topics.

Exam Major Topics and Sub-topics

The following table includes three columns: one for major topics, a second for percent exam weight, and a third for sub-topics within the scope of a major topic. There are five major topics.

The first major exam topic introduces data warehouses by focusing on the design of dimension tables and fact tables. When implementing these elements of a data warehouse, you will primarily use Analysis Services. This major topic has just an 11 percent exam weight.
The second, third, and fourth exam topics focus on using Integration Services for extracting, transforming, and loading values into a data warehouse as well as configuring and deploying SSIS solutions to extract, transform, and load values into a data warehouse. These topics comprise nearly 74 percent of the exam weight. So one critical take-away is to make sure you know SSIS.
The last major topic covers how to use Data Quality Services and Master Data Services to manage the data on which to build a data warehouse. You can use both of these services together to manage enterprise data on which to build a data warehouse. These two services are relatively new versus Integrations Services -- especially if you consider its DTS heritage. The exam weight for the two newer services together is just 15 percent.

Major Topics	Percent Weight	Sub-topics
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 setting
Build Data Quality Solutions	15	Install and maintain Data Quality Services Implement master data management solutions Create a data quality project to clean data

The preceding table lists between two and five sub-topics per major topic. Across all five major topics, there are 20 sub-topics. Mastering these 20 sub-topics will allow you to pass the exam. While learning about each of these sub-topics independently is of value, you will also need to know how they complement one another. For example, the architecture of a data warehouse consists of dimension tables and fact tables. The Design and Implement a Data Warehouse major topic has two sub-topics -- one for dimension tables and another for fact tables. Of course, when designing a data warehouse you need to know how to use the two types of tables of tables together. URLs for these two sub-topics selectively highlight the interplay between dimension tables and fact tables.

Many SQL Server professionals will have exposure to a subset of Integration Services features in the context of one or more applications that they designed and maintain. However, the preceding table itemizes a specific set of Integration Services features that are critical for the exam because they are essential for data warehouse applications. No matter how long you have been using Integration Services, your mastery of Integration Services for the exam will not be adequate until it includes in-depth knowledge of the content within the scope of the sub-topics for the second, third, and fourth major topics.

Chances are that even if you do not routinely rely on Data Quality Services, you are invoking some ad hoc measures to manage data quality. For example, you may assign a NULL to a value for one column when a corresponding value in a second column does not match at once item in a set of pre-defined legitimate values. Data Quality Services includes a rich set of data quality management features which are especially appropriate for large scale solutions. When Data Quality Services is used with Master Data Services you can define re-usable consistent data resources that are accurate for multiple data warehouse applications throughout the units of an enterprise. The sub-topics for the last major topic has these kinds of issues within its scope.

URLs for Sub-topics

My first attempt to discover and organize URLs with content pertaining to the scope of the 20 sub-topics for the 70-463 exam resulted in an overall count of 134 URLs. These URLs are listed below:

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 Design Pattern - Incremental Loads https://andyleonard.blog/2007/07/ssis-design-pattern-incremental-loads/
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 https://andyleonard.blog/2007/08/ssis-design-pattern-etl-instrumentation-part-1/
SSIS Design Pattern - ETL Instrumentation, Part 2 https://andyleonard.blog/2007/11/ssis-design-pattern-etl-instrumentation-part-2-2/
SSIS Design Pattern - ETL Instrumentation, Part 3 https://andyleonard.blog/2007/11/ssis-design-pattern-etl-instrumentation-part-3/

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
Script Component 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 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 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/
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/

The additional URLs denoted as "Bonus Content" are items I encountered that did not strictly pertain to the exam sections, but I thought they might of interest to you.

This article is the first in a two-part series on URLs for the for passing the Implementing a Data Warehouse exam. A second article will soon be forthcoming with a URL database with references for the exam. This will be enhanced with more references and several examples will be provided of how to query the database to help you prepare better for the exam. Another purpose for the database in the forthcoming article is to provide a free, open source study aid for the 70-463 exam that will allow others to add references and/or whole web sites that they deem of merit. In this way, the database can reflect the collective wisdom of many SQL Server professionals studying for the exam as well as others who have already passed the exam. In the meantime, I solicit your feedback on the workbook associated with this tip so that I can make the database better for all in its first edition.

Next Steps

This article drills down on the type of content that you can expect to encounter on the SQL Server 2012 Implementing a Data Warehouse exam. In particular, the tip highlights the major topics and sub-topics that Microsoft indicates to be within the scope of the exam. A cursory review of these topics indicates you should become familiar with Analysis Services, Data Quality Services, Master Data Services, and especially Integration Services.

Twenty exam sub-topics were identified as key areas for you to focus on during your exam preparation. In addition, 134 URLs are available to help you get started learning more about the 20 sub-topics. When using URLs that point to Microsoft web sites be sure and view the index to search for other related material that may help you master a sub-topic.

Please consider posting a comment about ways to improve the URL resources. For example, leave comments to this article about other URLs that you deem of particular value or interest. Feel free to suggest that URLs be dropped if you find better ones with which to replace them or you find gross errors that will cause those taking the 70-463 exam to provide a wrong answer.

Here are some links referenced in this tip.

Microsoft Learning web site with major topics and sub-topics for the 70-463 exam
Overview of SQL Server 2012 certifications and their associated exams

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2013-02-05

Study material for exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012