Notes on SQL Server BI MCTS 70-448 Exam

Last year, I finally became certified as a Microsoft Technology Specialist for SQL Server 2008 DBA. Here are my notes from that exam along with my studying and certification prep work. As a SQL Server Technology Specialist for Microsoft, I also work a lot with customers and solutions around data warehouse and business intelligence. So, I spent the past couple of months studying up for the 70-448 MCTS exam to become certified in SQL Server 2008 BI as well. I found this one to be much tougher than DBA 70-432. In fact, it took me 3 tries to pass this exam.

Here are my reasons / justifications / excuses for finding BI 70-448 so much more difficult … And I will preclude this list by also saying that I was surprised that I needed to take it 3 times because I have spent a good portion of my career developing, architecting and maintaining data warehouse & BI solutions, yet I struggled through this one:

  1. My background is not only in SQL Server or Microsoft technologies. I started as a developer in C/C++, Java, Smalltalk and VB. My first data warehouse projects were in Oracle 8i and my first BI projects were written in PL/SQL and used client tools like Hyperion Workspace. If you work exclusively in Microsoft SQL Server BI, you may find it more natural than I did to pass this exam.
  2. This is all SQL Server BI. That’s a no-brainer, I know, but in my Microsoft BI solutions, I don’t necessarily spend a whole lot of time in SQL Server. I spend much more of it in Excel, SharePoint and Visual Studio.
  3. You need to know a lot of different technologies because SQL Server is an incredibly diverse platform, especially for BI. You need to know some MDX, SSIS, VB.NET, T/SQL, SSRS among others. Much of SQL Server’s BI platform came from different source and differen R&D teams and can be a real challenge to understand each enough to pass a certification exam that asks some very intricate questions.

I had actually expected that at most it would take 2 tries to get this cert. But my advice to you is that you should expect to take the exam more than 1 time to pass for your certification. A couple of reasons: 1) don’t get your expectations too high so that you don’t get too disappointed or disallusioned if you don’t make it on the first try and 2) I know a lot of very, very good DBAs & BI architects that work on the Microsoft platform exclusively more that I have and they struggled through 2 or 3 tries at these certs. So study but don’t get down if it takes a few tries. Even the best can struggle with cert testing.

Just like in my recommendations post for 7-432 that I linked to at the top of this entry, I recommend the Microsoft Press book study guide as a necessary part of your preperations. Make sure that you go through ALL of the questions on the test press software from that book. There are around 200 and I went through them all until I got to about 75% of them correct before going to the exam center.

You may find my methods to be over-preparation. Let say this: I’ve now certified in my career for VB, Java, SQL Server and PMP. In my mind, being over-prepared is the best way for me to be comfortable, relaxed and prepare for success. You may find that the questions are not what you thought would be asked or they may be areas of this very diverse SQL Server platform that you just don’t work with enough to know the answers. Therefore, go through all of the book and all of the questions. I know too many good DBAs who don’t prepare like I did and they struggle with these types of exams.

BTW, interesting note: The 70-448 BI study guide from Microsoft Press is written by something like 3 or 4 authors, just underscoring how difficult it can be to pass this test if you don’t know a good deal about cubes, ETL processing, building reports and maintaining these services.

Good luck and send me your questions, thoughts and stories in the comments so that we can share them with the rest of the community!

Many thanks, Mark

My Notes on 70-432 SQL Server Exam for DBAs

I’ve finally found some down time this weekend to write a few notes about the Microsoft Technology Specialist 70-432 exam for SQL Server 2008: Implementation & Maintenance. I had wanted to do this for some time, but have been very busy recently. My hope is that if you are a SQL Server DBA looking for gain certification by taking the 70-432 exam, that this will help you to prepare. And so, without any further ado, here are my notes for those taking the exam and helpful tips to be successful:

  1. The Microsoft Press book for 70-432, the MCTS training kit, is essential. The sample tests and the material is spot-on with what you can expect from the test. And beyond just the test, the book does a very good job of explaining concepts that any DBA can find useful in your day-to-day job. Which leads me to point #2 …
  2. Even the most seasoned SQL Server DBAs typically struggle with their first attempt of the cert. I know SQL Server DBAs that have been at it longer than I have and are better at database administration that I am who scored in the 400-600 range on their first attempt (you need 700 to pass). My first attempt was 661 and I studied for weeks prior to the test. After missing the 700 minimum requirement the first time, I went back to the book & prep tests, went through EVERY QUESTION in the book and the prep test again, and passed the 2nd time 2 weeks later. My bottom line is this: even if you think you know everything about SQL Server 2008 implementation and maintenance, STUDY the prep book and prep tests for several weeks FIRST.
  3. Study, study, study. As I just explained in #2 above, you may be a drop-dead awesome SQL Server DBA, but you must use material like the book in #1 above to learn about what the testers are looking for. I am also certified as a PMP in project management and I like to use that PMP test as a good example. Just as with the PMP, a certified DBA or project manager is not necessarily a GOOD DBA or project manager! That’s because the cert tests are looking for very specific by-the-book answers that you may have found in practice don’t always work the way that you want or expect. But the tests are testing your book answers, not your specific experience. So try not to get hung-up on answers that you think are not really correct and go by what you read and practiced in the prep tests.
  4. I can’t do any better at compiling resources to use for your studies than my colleague Buck Woody did at his MSDN blog here. Check it out and don’t assume that you already know it all. Do your work ahead of time. Buck and I are both Microsoft Technology Specialists and I can tell you that I needed to study for hours to pass this exam even though I work with SQL Server every day and have for a number of years.
  5. Remember, SQL Server is very, very large platform, so make sure that you have an understanding of areas that may seem out of the mainstream for long-time SQL Server DBAs, those that started with SQL Server prior to SQL Server 2008. Things like MDW, PBM and spatial indexes, for example, may be areas that you’ll need to brush-up on.

 

Best wishes and good luck! – Mark