This is our third and final installment about the new certification track for the Microsoft Certified Solutions Associate (MCSA), which replaced the Microsoft Certified Technology Specialist (MCITP) last year. As mentioned in Part 1 and Part 2, the MCSA for SQL Server 2012 is the base level certification for Microsoft’s relational database solution, and is followed by the Microsoft Certified Solutions Expert (MCSE) and the Microsoft Certified Solutions Master (MCSM). In order to achieve the MCSA for SQL Server 2012, you must pass three exams – Querying Microsoft SQL Server 2012 (Exam 70-461), Administering Microsoft SQL Server 2012 Databases (Exam 70-462), and Implementing a Data Warehouse with Microsoft SQL Server 2012 (Exam 70-463). After covering the first two, it’s time to look at what is needed to pass the last one: Implementing a Data Warehouse with Microsoft SQL Server 2012 (Exam 70-463).
Overview: Exam 70-463, Implementing a Data Warehouse with Microsoft SQL Server 2012
For this exam, there are five main subject areas, three of which are more heavily weighted at right around 25% each, and the last two making up the final 25%. The most heavily weighted are Load Data, Configure and Deploy SSIS Solutions, and Extract and Transform Data. The last two are Build Data Quality Solutions, and Design and Implement a Data Warehouse.
For the Load Data section, you will need to know how to properly load data into a data warehouse, including designing and implementing control flow, package logic, data load options, and script components. You should understand and be able to design and implement SSIS packages, including precedence constraints, containers, tasks, variables, parameters, packages, and configuration types. Be prepared for questions relating to the use of package and project parameters.
In the Configure and Deploy SSIS Solutions section, you will see questions about installing and maintaining the components of SSIS, as well as how to deploy and configure those components. You will also be tested on how you would audit those resources, including setting up custom logging, tracking ETL errors and events using SSIS event handlers, and setting up alerts and notifications. You may see questions about the security settings for SSIS, so understand database roles, protection levels for packages, and how to configure secure Integration Services.
Extract and Transform Data is all about data flow, so for this section, you will be tested on your knowledge of connection managers, your ability to design and implement a data flow, managing the execution of a SSIS package, and implementing script tasks. You should understand when to use different types of joins or fuzzy lookups to merge data, and also what to do when there are problems with the data.
For the section on Building Data Quality Solutions, you will need to know how to install Data Quality Services (DQS), as well as how to create a data quality project so that you can clean data. You will also need to understand how to implement master data management solutions by installing and using Master Data Services (MDS).
Lastly, there is the section about Designing and Implementing a Data Warehouse. This section really just tests two main areas, dimensions and fact tables, so you will need to know how to design both and how to implement them. This will include the type of schema for your dimensions, how they will relate to the fact tables, and how you will create keys for the data. For the fact tables, you will need to know how to support many to many relationships, how to use columnstore indexes on them, and even how to determine the appropriate method for loading data into them.
Preparation
As we mentioned in the previous posts, you can prepare for this exam in any number of ways, but the best is always getting hands-on experience with the subject material. Download an evaluation copy of SQL Server 2012 and play around with each of these areas. You will find many resources and tutorials online that will include practice exercises, and many of these use the sample database environment that comes with the evaluation of SQL Server. I definitely recommend taking a class from a Microsoft Training Partner if you can, and there are links to available classes on the website listed below in the additional reading and reference area. If you are motivated and capable enough, you can always use the self-training method. Using the website below, you can also find numerous books, videos, and online training programs that are available to help you.
When you think you are ready for the exam, try some practice exams to check your knowledge level first. Some of these are free, and others can be purchased at a nominal cost, but it is a good idea to understand whether you are truly ready before you spend money on the real exam. Search online for a provider of practice exams, and at least check out whatever free options there are so that you have a feel for some of the types of questions you will be see, as well as how they will ask the questions.
After all this learning, studying, and practicing, we hope that you are able to sit through the exam and come out with a passing score. There is no better time than now to decide that you are going to take the final step towards your MCSA: SQL Server 2012! Good luck!
Additional Reading & Reference:
Exam 70-462: Administering Microsoft SQL Server 2012 Databases –http://www.microsoft.com/learning/en-us/exam-70-462.aspx