The last Microsoft certification exam I took was 70-448: SQL Server 2008 Business Intelligence. Because that’s the freshest in my mind, and because I’m focusing more on the BI side of SQL lately, I’ve decided that my next exam would be the new 70-463 Exam. This post is my review of the Microsoft Press Training Kit for 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012, written by Grega Jerkic, Matija Lah, and Dejan Sarka.
You’ll notice a difference between 70-463 and the older 70-448 exam. When the new exams came out for SQL 2012 Business Intelligence got its own track. There’s now a MCSE just for BI that requires the core tests (70-461, 70-462, and 70-463) plus 70-466 that covers Analysis and Reporting Services, and 70-467 on designing a Business Intelligence solution. 70-463 covers designing a data warehouse and using SSIS for ETL. You can read up on the exam requirements for 70-463 here. Information on all the new SQL 2012 Business Intelligence exams can be found here.
If you’ve ever used a Microsoft Press training kit to study for an exam you’ll be familiar with the layout. There are five sections that match the exam objectives; Design and Implement a Data Warehouse, Extract and Transform Data, Load Data, Configure and Deploy SSIS Solutions, and Build Data Quality Solutions. Each section of the book contains chapters covering topics pertaining to the objective. The chapters are comprised of lessons that have walkthroughs and a short quiz. Each chapter also has case studies that are a little bit more involved. When you buy the physical book or an electronic copy from O’Reilly you’ll also get a CD (or the files) that contain the code samples used in the book as well as a practice test engine from MeasureUp.
This book, like the exam, covers mostly loading a data warehouse using Integration Services. Most of the concepts are the same as they were in earlier versions of SQL; control flows, data flows, and the different transforms available to each, error handling, using variables, logging, transactions and checkpoints are all covered. The new features, like using the new SSISDB catalog, are also well covered
What I like about this training kit is the 25% or so of the exam that’s not directly related to SSIS. The first section covers the design of a data warehouse. It’s a good introduction to the concepts of dimension and fact tables, of star and snowflake schemas. You’ll see how data compression and columnstore indexes are helpful. You’ll also learn about creating SSIS package templates, something I didn’t learn about as part of the 70-448 test.
So far I find the training kit to be well written and easy to follow. I like the examples presented in the case scenarios, they make me think a little about the question. They’re closer to the kind of questions you may find on the exam. (NOTE: I haven’t taken the test yet so I have no knowledge of the actual questions or exam format. I’m basing my opinion solely on past Microsoft exams I’ve taken). I haven’t taken any of the included practice exams yet as I want to wait until I’m ready to take the exam. My past experience is that if I take the practice exams too early I tend to remember the question and not the concepts.
So if you plan on taking Microsoft’s 70-463 exam I recommend you use the MS Press Training Kit as part of your studies. If you have no other experience with SSIS you may want other resources. I feel that with my past experience I can learn enough about the 2012 version from this book.