Passed 70-466 and 70-467 Exams + Study Guides

Earlier this month, I passed the following two exams putting me 1 away (70-462) from the MCSE Business Intelligence certification.

70-466 – Implementing Data Models and Reports with Microsoft SQL Server 2012

70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Below is an outline of the material I worked through in preparation for these exams.

70-466 – Implementing Data Models and Reports with Microsoft SQL Server 2012

This exam focuses on SSAS/SSRS at a developer level.  For experienced BI developers you’ll want to spend time covering features and functionality in detail, doing quick lab-like activities, re-familiarizing yourself with functionality you don’t use frequently…basically refresh the mind’s eye for developer-tasks.

• Analysis Services
  Since this is the bulk of the exam, it is where I spent the most time…it also happens to be one of my favorite parts of the Microsoft BI stack.  For those who don’t feel very strong in SSAS, I’d recommend adding an additional book that focuses on Multidimensional to the following reading list:
  • Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model
  • DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX
  • SSAS Performance and Operations Guide
  • Many-to-Many Dimensions: Query Performance Optimization Techniques
  • Practical MDX Queries
  • Expert Cube Development
  • Many to Many Revolution 2.0
  • Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services (added 2015-04-06)
• Reporting Services
  Over the past year, I spent quite a bit of time reviewing and presenting this material to clients – focusing mostly on the new features with SQL Server 2012 (Data Alerts, Power View, etc).  Outside of those presentations (and a few hours on MSDN reviewing things like RSConfig.exe, RS.exe, etc) I really didn’t spend much time on this piece of the stack…I just don’t find it that interesting.Operational reporting has been around for decades and not a whole lot has changed. And the projects I’ve been only barely scratch the surface from a strong visually appealing report design.  Most clients just want me to “make it look like the old Bob-Jay reports”…and the few times I’ve tried to show them a few things (ex. bullet graphs > speed gauges, bar chart > pie chart), I’ve had my hand slapped (doesn’t mean I won’t keep trying).

70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012

This is an architecture level exam covering the whole stack (DB Engine, Data Warehouse Design/Implementation, SSIS, SSRS, SSAS, MDS/DQS, Sharepoint integration, etc). For experienced Microsoft BI practitioners, this exam is much easier to prepare for…and that’s because you can’t really prepare for it! The material covered in this exam can only be obtained from actual experience and deliberate practice.

For this exam, you’ll need to understand how the various pieces of the Microsoft BI stack fit together in a solution.  And you’ll need to have a deep enough understanding to determine the best solution (from a list of other valid, but less optimal, solutions) for a given task based on information (infrastructure, business requirements, technical requirements, etc) in the case studies.

That said, I did read the following whitepapers and use MSDN to confirm my understanding of a few features:

• Microsoft BI Authentication and Identity Delegation
  
• Manage Kerberos Authentication Issues in a Reporting Services Environment
• Reporting Services Scale-Out Architecture (SQL CAT Blog Series)
• Scale-Out Querying for Analysis Services

Overall Thoughts

Some may argue that my preparation is a bit overkill…after all, why not simply purchase one of the exam preparation kits, work through the practice tests, and study the areas where you answered incorrectly?  That’s a perfectly valid option and will certainly be a more efficient approach…if getting the certification is your ultimate goal.

On the other hand, if deep knowledge and understanding is the goal, then I’d argue you would be better served with more reading and more deliberate practice.  In the long run, only technical experts (with good personalities) will consistently land the coolest (and highest paying) projects.  Few feelings rival that of the fireman who shows up to put out the client fire…cramming the week before the exam with a preparation kit will not give you this ability.

By the way…no data mining in the 70-466 exam…seriously?  Perhaps Microsoft should consider rebranding the SSAS Maestro into a SSAS MCM track (written + lab) that includes deep data mining expertise.