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

By:   |   Comments (35)   |   Related: 1 | 2 | More > Professional Development Certifications


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?


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
  • Design and implement dimensions
  • Design and implement fact tables
Extract and Transform Data
  • Define connection managers
  • Design data flow
  • Implement data flow
  • Manage SSIS package execution
  • Implement script tasks in SSIS
Load Data
  • 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
  • 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
  • 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

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


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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rick Dobson 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 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

Comments For This Article

Saturday, May 7, 2016 - 4:52:56 PM - kelly Back To Top (41430)

Thank you soooo much Rick!

Wish you all the best! 

Monday, February 22, 2016 - 5:36:56 PM - Scott Back To Top (40750)


This is an amazing study sheet - thank you for taking the time to put it together.

Are you aware of an equivalent study sheet for 70-462?

Thank you,


Tucson, AZ

Sunday, December 13, 2015 - 10:28:37 AM - Jaga Back To Top (40243)

Thank you sir!.i just start preparing for the exam. This helps a lot!

Tuesday, December 8, 2015 - 6:09:54 AM - Irene Back To Top (40212)

Thank you for this. I'm just starting to study for 70-463. This will come in very handy indeed. 

Wednesday, March 25, 2015 - 8:15:41 PM - Pratima Back To Top (36701)

Great! Thanks!

Saturday, December 20, 2014 - 12:06:12 AM - David Akinwale Shakin Back To Top (35696)

Thanks so much for this tips, waiting for a resources like these for a long time for my mcsa SQL server exams.

Sunday, November 2, 2014 - 1:08:45 PM - Aakaash Back To Top (35158)

Thank you Sir very much. You must have spent so much effort in creating this. Appreciate it!


Wednesday, October 29, 2014 - 2:41:59 AM - Rene Voller Back To Top (35104)

This is extremely helpful and a job well done!

Thursday, October 23, 2014 - 5:21:24 PM - Juan Pablo Back To Top (35049)

Thanks it will be helpful !


Monday, June 23, 2014 - 12:50:51 PM - Vani Dornadula Back To Top (32354)

i want material for implementing datawarehouse in sql server 2012

Friday, May 23, 2014 - 6:30:13 AM - Nanshu Arora Back To Top (30903)

Hello Rick.

this was really very helpful. Great information for SQL users. can you please help me the same way in collecting data for

Exam material for  Querying Microsoft SQL Server 2012 Microsoft SQL Server 2012 (Exam 70-463)

Friday, February 7, 2014 - 9:50:43 AM - Vera Back To Top (29367)

Thank you for a great job well done, I can imagine this took you a lot of hours.



Friday, December 27, 2013 - 4:12:34 AM - Ramanathan Iyer Back To Top (27890)

Hi All,

I would like to inform you all that today I cleared this exam after planning it for almost 4-5months,Thanks to Rick for providing us with this wonderful, exhaustive  resource which was of a great help,

Your effort is really appreciated


Thanks a ton




Thursday, August 1, 2013 - 6:59:46 PM - Rick Dobson Back To Top (26093)

I recommend that you start with this link:  Then drill down on specific topics for whatever topics you decide you want a certification.

Thursday, August 1, 2013 - 7:50:27 AM - SURE Back To Top (26085)


Friday, June 7, 2013 - 5:22:38 AM - HK Back To Top (25330)

Awesome! Huge effort. This is going to be really useful. Thanks!

Thursday, May 9, 2013 - 12:30:59 PM - Chakradhar Back To Top (23839)

Excellent Effort! This is Outstanding Work

Thursday, May 2, 2013 - 6:33:05 PM - Dave Clary Back To Top (23690)

This is fantastic. Thank you! 

Wednesday, March 13, 2013 - 9:24:37 AM - Suresh nadesan Back To Top (22778)

Well done. Looking forward more articles from you.

Friday, March 8, 2013 - 12:34:13 AM - LVPRASAD TUMMA Back To Top (22650)

Thank you , Very Nice Article . .


Any one looking for the practices tests of SQL SERVER Certification

This is mysite it may be helpful 

Thanks & Regards,


Wednesday, February 27, 2013 - 1:28:42 PM - kerany Back To Top (22449)

thank you so much for this great tip

Sunday, February 24, 2013 - 3:32:43 PM - Erik Back To Top (22387)

With 94 icons over on the left, error testing, scripting, 20+ connection managers, DQS, logging, error handling, security, DW concepts, and more this test is an absolute beast, and just a mess to organize for studying.  And the official training kit just isn't very good.

So every little bit helps and this is more than a little bit.

Great work!

Thursday, February 21, 2013 - 6:00:13 PM - Mike Donnellan Back To Top (22348)

Many thanks, Rick.  This is a wonderful resource for those of us working to build this skillset.  Looking forward to the next in the series.  Your concept of a sort of DB-Wiki is very promising, a really great idea.

All the best -- Mike

Friday, February 15, 2013 - 10:33:53 AM - Kyle Back To Top (22158)

You've out done yourself on this one Rick. Thank you so much.

Thursday, February 14, 2013 - 5:15:56 AM - Devi Prasad Back To Top (22116)

Much appreciated!!

Saturday, February 9, 2013 - 12:04:50 AM - Rick Dobson Back To Top (22020)

You guys are so kind.  I wrote the tip for you, and I hope that it surpasses your expectations about it.


BTW, I have a follow-up on the same topic that will appear short.  It has what I hope you will believe is an even better set of references.  In addition, the links are coordinated with study topics in a database.  As a result, you will be able to easily add to it and modify it according to your own special needs.  I hope you like the upcoming tip as much as this one.

Friday, February 8, 2013 - 3:45:18 PM - Scott Shaw Back To Top (22019)

This is great! Thank you for the time and effort it took to put this together.

Tuesday, February 5, 2013 - 5:37:32 PM - Nirmal Sahar Back To Top (21939)

Thank for time and effort. Appreciate it.

Tuesday, February 5, 2013 - 3:29:22 PM - Prabha Back To Top (21938)

Great Work!!! 

Thanks for all your efforts!!!








Tuesday, February 5, 2013 - 2:07:39 PM - Martyn Jones Back To Top (21935)

Very good effort, thank you so much!

Are you going to do one for the other two exams too ;)

Thanks again, very useful indeed.

Tuesday, February 5, 2013 - 1:18:36 PM - Siva Ramasamy Back To Top (21933)

Wow. Thanks a lot. This is a great service to SQL Server community. Thanks for your time and effort..Thank you so much...!!!!!

Tuesday, February 5, 2013 - 11:09:27 AM - Alex G Back To Top (21929)

Excellent! That's exactly what I need in my preparation for the exam!

Tuesday, February 5, 2013 - 10:24:59 AM - Henrik Back To Top (21927)

Thanks Rick, a lot of work to provide a lot of support!

Tuesday, February 5, 2013 - 9:34:47 AM - Shiv Shankar Khanna Back To Top (21923)

WOW, what an effort sir ?


Tuesday, February 5, 2013 - 8:21:03 AM - Steve Back To Top (21922)

Thanks Rick for gathering all this information!


get free sql tips
agree to terms