This past September, I attempted (and recently received a passing score for) Microsoft’s new certification exam, 70-768, “Developing SQL Data Models” during its Beta period. This brand new exam is a requirement toward the new MCSA SQL 2016: Business Intelligence Development certification. Last weekend, I took and passed 70-466, “Implementing Data Models and Reports with Microsoft SQL Server” which counts toward MCSE: Data Management and Analytics. Both of these exams overlap heavily in the topics covered, so if you’re interested in taking them both, its a good idea to study for both and take them back to back.
Brand new exams, as well as higher level specialty exams in general can be a bit of a tricky beast due to the lack of available resources. For exams which are mainstream (such as Windows Server exams) and have been out for a while, you can count on resources such as MS Press Books targeted toward the specific exam, practice tests from MeasureUp and Transcender, and other useful resources. Unfortunately, for brand new exams, or many of the high level SQL Server exams, none of this exists. Having successfully studied for and passed both 70-768 and 70-466. Below are some tips and resources I used to prepare for each exam.
General Tips for Both Exams
The first tip is just to know the question types that the exams typically cover. Microsoft list all of their question types here, with examples of each. It’s typically a good idea to pay special attention to the “Build List” type of question, which emphasizes knowing the steps, and order, that task components should be performed in. There seems to be a lot of love for this question type.
For both exams, the best starting place is the bulleted section of the “Skills Measured” section of the official exam page. Modern Microsoft exams follow this section very closely — you can practically guarantee there will be a question that ties back to each sub-item for each category. I like to go through this section and all of the bulletpoints and break it down into small words or phrases that I can then use as a checklist while studying. I’ve included an example for each exam below in the exam specific sections.
Resources for 70-768: Developing SQL Data Models — Primary topics covered: Multidimensional Design, Tabular Design, MDX, DAX, SSAS Installation and Maintenance
- 70-768 “Skills Measured” Exam Objective Breakdown – This is a copy of the “Skills Measured” section from the official website rearranged and reformatted to make it more into a list of items to study.
70-466: Implementing Data Models and Reports — Primary topics covered: Multidimensional Design, Tabular Design, MDX, DAX, SSAS Maintenance, SSRS
- 70-466 “Skills Measured” Exam Objective Breakdown – This is a copy of the “Skills Measured” section from the official website rearranged and reformatted to make it more into a list of items to study.
Resources Applicable to Both Exams
- Transcender’s 70-466 Practice Exam – This is the only targeted 3rd party resource I found for either exam. While this is targeted toward 70-466 and certain things, like Case Studies, apply only to that, the exam objectives between the two are extremely similar and the practice questions for the majority of this product (except for SSRS) apply to both exams well. This is a commercial product, but you can frequently find sales at Transcender for a discount.
- Video: Implementing Data Models and Reports – This free Microsoft Virtual Academy video series offers an introduction to SSAS Multidimensional and other basic exam concepts.
- Video: Implementing Tabular Datda Models – This free Microsoft Virtual Academy video series offers an introduction to SSAS Tabular and other basic exam concepts.
- Book: SQL Server Analysis 2012 Cube Development Cookbook – This book was my primary resource for basic SSAS Multidimensional concepts.
- Book: Practical MDX Queries for Microsoft SQL Server Analysis Services 2008 – This book is an excellent beginner’s language reference for learning MDX to be used querying against Multidimensional cubes.
- Book: Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling – This book was my primary resource for basic SSAS Tabular concepts.
- Book: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model – This book was an additional reference resource for SSAS Tabular concepts.
- Book: DAX Formulas for PowerPivot – This book is an excellent beginner’s language reference for learning basic DAX to be used querying against Tabular models. This book is a little dated and there is a newer edition currently available.
For both exams, there is no substitute for experience, so download the AdventureWorks Data Warehouse and SSAS samples and get to building some real models to practice with. Many questions tend to require familiarity with wizards and dialog screens, so actually seeing and being familiar with what they look like is critical.