Preparing for 70-463 Exam : Implementing a Data Warehouse with Microsoft SQL Server 2012
After passing 70-461 exam last year I’m now preparing for 70-463 exam i.e. Implementing a Data Warehouse with Microsoft SQL Server 2012. This exam mainly focuses on implementing Data Warehouses with Dimension & Fact tables, working with SSIS packages and Data Quality solutions. Thus being a Database Developer (primarily) this exam will be bit tough for me as I’ve not worked with Dimensional modelling, SSIS and DQS.
For preparation I’m referring to Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012
This exam is primarily intended for ETL and Data Warehouse (DW) developers who create Business Intelligence (BI) solutions, and whose responsibilities include Data Cleansing, and Extract Transform Load and Data Warehouse implementation.
–> The Exam is divided into 5 modules:
1. Design and Implement a Data Warehouse | 11%
– Introducing Star and Snowflake Schemas
– Design and Implement Dimensions
– Design and Implement Fact tables
– Managing the Performance of a Data Warehouse
– PREP links: Introduction to dimensions (Analysis Services – multidimensional data) | Dimension relationships | Columnstore indexes
2. Extract and Transform data | 23%
– Define Connection managers
– Design Data Flow
– Implement Data Flow with Transformations
– Control Flow tasks and Containers, Precedence Constraints
– Manage SSIS package execution
– Implement Script tasks in SSIS
– PREP links: Integration Services (SSIS) connections | Data flow | Slowly changing dimension transformation
3. Load Data | 27%
– Design control flow
– Implement package logic by using SSIS variables and parameter
– Implement Control flow
– Implement Data Load options
– Implement Script components in SSIS
– Slowly Changing Dimensions
– Preparing a Package for Incremental Load
– Package Transactions, Checkpoints, Event Handlers
– PREP links: Integration Services transactions | Developing a custom task | Integration Services (SSIS) parameters
4. Configure and Seploy 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
– Data Mining Task and Transformation
– Preparing Data for Data Mining
– Implementing SSIS Fuzzy Transformations
– PREP links: Troubleshooting tools for package development | Load-balancing packages on remote servers by using SQL Server Agent | Integration Services (SSIS) logging
5. Build Data Quality Solutions (DQS) | 15%
– Install and maintain Data Quality services
– Creating and Maintaining a Knowledge Base
– Create a Data Quality project to clean data, Profiling Data and Improving Data Quality
– Using DQS and the DQS Cleansing Transformation
– Implement Master Data Management (MDM) solutions
– Using Master Data Services Add-in for Excel
– PREP links: Install Data Quality Services | Install Master Data Services | Master Data Services features and tasks
-–> You can visit following Study materials to prepare for this Exam:
Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-463.aspx
Book on AMAZON.com: Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012
I will try to update this blog post or will put up a new post with my learning while preparing for this exam.
All The Best!!!
Leave a comment
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 
Thank You Manoj for the overview of 70463 exam.
I also started prepating for this exam.
Thank you for the information. I’m also preparing for the exam. Did you already take the exam?
Thanks @Srikanth & @Ekkowekko, for your comments.
I’m planning to give this exam from long time, but my office work load is delaying this. I’ll take this exam very soon and post the experience here.
~manoj
Hi Manoj,
I passed this test today, and I have to say it was much more enjoyable thank the 70-461 exam. I look forward to your analysis of your experience that I could compare mine to.
-Patrick
Congratulations Partick and best of luck for the next exams!
Hi Patrick,
Congrats for passing the Data Warehouse Certification Exam! Could you please share with us what books did you refer to study for the exam? How long did you for the exam? I have already passed 70-461 exam and I have around 6 months of experience working in a Business Intelligence team. What do you think how hard the exam is?
Hi Poonam,
I used only the 70-463 Training Kit book and it was sufficient for me. I had been using SSIS with BIDS 2008 as well as Data Tools that came with SQL Server 2012 on a daily basis for my job for the 7 Months prior to taking the test. Since I had been using the environment so extensively, I felt comfortable in the test when asked to use the tools. I feel that you would be going into the exam with the same level of preparation I had and should come out successful as well.
Manoj – did you take the 463 exam yet? the training press received some poor reviews as far as not preparing folks for the exam – so wondering if this was your principal prep guide – and to see if it was sufficient. please let me know.
i passed 461 in oct, and scheduled for 462 on 1/5. the microsoft double shot was a great way to take a REAL practice exam – tweak the prep/studying then pass. bummer i didn’t know about it early enough to squeeze in the third exam.
really appreciate the info.
No @Alison, I did not get time to give that exam, and since then its not in my pref list.
Regarding the book I haven’t heard any bad review. But yes you should not be totally dependent on the book, and should also do good hands-on & practice with SQL Server.
Best of luck, cheers, Manoj.
Hi Manoj,
Do i need to give 70-461 exam or any other exam before giving this exam? Also can you please let me know the value of this certification.
No @Huzaifa, you can give any of the 3 exams in any order.
Value wise I’m not sure, but good for self-valuation at least 🙂
Thank you Manoj. What is the validity period for the cert.Will validity get extended if i give any other exam from MCSA or MCSE? (LIke for Cisco certs)
hello Guys..i want switch my profile to SQL server DBA ..and I’m very new to it..i have to read each and everything of it..i even have to study the basic queries..i have around 3 years of working experience in technical support….as i have started studying about SQL Server i feel i can some how will be able to attempt the 70-462 exam..i don’t think 70-461 will be easy for me even if i do hard work and i can not even think about 70-463…as i don’t have any work experience as a DBA, at any cost i need to have the certification…really need you guys suggessions..how do i work on it..is passing only the 70-462 exam will be considerable?…please put your valuables suggessions
UPDATE on SQL 2014:
~manoj
Hello Manoj, I am Rafael from Brazil, I have a little experience with Microsoft BI, I already have the 70-461 certification and I want to do the 70-463 soon. I even have to study to do some projects that are appearing in my work.
I would like to take a question with you.
I have the 10777A face-to-face training book and I’m wondering if I should study through this book or the Certification Training Kit. I’ve heard in some places that the Training Kit is not very good as it has several bugs and errata.
What did you think of the Training Kit, was it effective for your projects other than just studying for the test?
Hi @Rafael, I don’t think the Training kit book would contain any bug or misinformation. Go ahead with the Kit and I would recommend to do hands-on and exercises with all the topics. Best of luck !!!
Perfect, thanks for the tip!
Hi,
I have 70-461 and 70-462 certs and wondering making the last one 70-463 or start taking exams 70-76X to get MSCA cert? I get confused as Azure is very popular now.
I understand that paths 70-46X are to query, admin and bi but in 70-76X are grouped by Query, Admin and BI.