Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Exam material for Implementing a Data Warehouse with Microsoft SQL Server 2012 (Exam 70-463)


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

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

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.



Last Update:






About the author
MSSQLTips author Rick Dobson Rick Dobson is a Microsoft Certified Technical Specialist and well accomplished SQL Server and Access author.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, May 07, 2016 - 4:52:56 PM - kelly Back To Top

Thank you soooo much Rick!

Wish you all the best! 


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

 

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,

Scott

Tucson, AZ


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

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


Tuesday, December 08, 2015 - 6:09:54 AM - Irene Back To Top

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

Great! Thanks!


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

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


Sunday, November 02, 2014 - 1:08:45 PM - Aakaash Back To Top

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

This is extremely helpful and a job well done!


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

Thanks it will be helpful !

 


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

i want material for implementing datawarehouse in sql server 2012


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

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 07, 2014 - 9:50:43 AM - Vera Back To Top

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

Regards,

Vera


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

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

 

Regards

Ramanathan


Thursday, August 01, 2013 - 6:59:46 PM - Rick Dobson Back To Top

I recommend that you start with this link: http://www.microsoft.com/learning/en-us/mcse-sql-business-intelligence.aspx.  Then drill down on specific topics for whatever topics you decide you want a certification.


Thursday, August 01, 2013 - 7:50:27 AM - SURE Back To Top

WHERE CAN WE FIND SSAS CERTIFICATION DETAILS CAN ANY BODY HELP


Friday, June 07, 2013 - 5:22:38 AM - HK Back To Top

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


Thursday, May 09, 2013 - 12:30:59 PM - Chakradhar Back To Top

Excellent Effort! This is Outstanding Work


Thursday, May 02, 2013 - 6:33:05 PM - Dave Clary Back To Top

This is fantastic. Thank you! 


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

Well done. Looking forward more articles from you.


Friday, March 08, 2013 - 12:34:13 AM - LVPRASAD TUMMA Back To Top

Thank you , Very Nice Article . .

 

Any one looking for the practices tests of SQL SERVER Certification 

http://mssqltricks.com/category/certification-practice-tests/

This is mysite it may be helpful 

Thanks & Regards,

LVPRASAD


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

thank you so much for this great tip


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

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

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

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

Much appreciated!!


Saturday, February 09, 2013 - 12:04:50 AM - Rick Dobson Back To Top

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 08, 2013 - 3:45:18 PM - Scott Shaw Back To Top

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


Tuesday, February 05, 2013 - 5:37:32 PM - Nirmal Sahar Back To Top

Thank for time and effort. Appreciate it.


Tuesday, February 05, 2013 - 3:29:22 PM - Prabha Back To Top

Great Work!!! 

Thanks for all your efforts!!!

 

 

 

 

 

 

 


Tuesday, February 05, 2013 - 2:07:39 PM - Martyn Jones Back To Top

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 05, 2013 - 1:18:36 PM - Siva Ramasamy Back To Top

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 05, 2013 - 11:09:27 AM - Alex G Back To Top

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


Tuesday, February 05, 2013 - 10:24:59 AM - Henrik Back To Top

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


Tuesday, February 05, 2013 - 9:34:47 AM - Shiv Shankar Khanna Back To Top

WOW, what an effort sir ?

 


Tuesday, February 05, 2013 - 8:21:03 AM - Steve Back To Top

Thanks Rick for gathering all this information!

 


Learn more about SQL Server tools