After taking a break from exams and certifications for a few years, I decided to resume studying. On May 29th, 2018, I shared my Updated Microsoft Certification Goals. On June 5th, 2018, I achieved my first goal by passing Microsoft Exam 70-767. On July 31st, 2018, I achieved my second goal! I passed Microsoft Exam 70-768: Developing SQL Data Models. By passing this exam, I became a Microsoft Certified Solutions Associate (MCSA): Business Intelligence Development :)
In this blog post, I share how I prepared for the exam and what my experience was like on the day of the exam.
(If you are simply looking for information about exam questions and answers, please leave. Sharing or using that kind of information is a violation of the Microsoft Exam Policy and Non-Disclosure Agreement.)
Preparing for Microsoft Exam 70-768: Developing SQL Data Models
It has been several years since I worked as a developer on Multidimensional SSAS projects. I have experience designing and developing cubes, but there were many things I did not do myself in previous projects. For example, I did not participate in planning or configuring servers, I never monitored performance, and I wrote very little MDX. (In fact, I tried to avoid it as much as possible!) I also never worked with Tabular SSAS in real projects. Before I began this journey, I knew I had a lot of studying in front of me.
Because I like to be organized and structured (that’s why I work with data!) I decided to study in a very specific way:
- Complete online training: two free online edX courses
- Review training material from SQLBI
- Work through each question in the official practice test in study mode
- Go through every single skill measured and ensure I knew how to explain the concept or complete the task
Online Training for Exam 70-768
Microsoft recommends two online courses in their exam preparation options:
- Developing a Multidimensional Data Model (course DAT224x)
- Developing a Tabular Data Model (course DAT225x)
These two courses build on the Delivering a Relational Data Warehouse course that I completed while studying for Exam 70-767. If you have not already completed that course, I recommend going through it first.
I spent quite a lot of time on labs in these two courses, but I also saved time by watching the videos in 1.5x or 2.0x speed :) Both of these courses are well-structured and easy to follow. The labs are great for working your way through the content, but I recommend experimenting on your own as well and not just blindly follow the lab instructions.
Total time spent on online training and labs: 25-30 hours
SQLBI Training Material
In 2015, the brilliant Alberto Ferrari and Marco Russo from SQLBI held two workshops for my former employer:
I was fortunate to attend both workshops, but… I’m very sad to admit that I was a horrible student. The workshops were held during busy project periods where I struggled to focus on learning new skills. In addition to that, I had never worked with SSAS Tabular or DAX before, and I never got the chance to get hands-on experience afterward. The skills I did learn during the workshops faded fairly quickly.
You can only guess how glad I was that I kept all my course material :) I referenced and reviewed all the slides and demos from the workshops. This helped me understand concepts better and learn even more skills.
While it might not be possible for you to invest in these specific workshops, I absolutely recommend going through the SQLBI content. There are hundreds of posts, articles, and videos available – for free! :)
Total time spent on reviewing training material: 10-15 hours
Practice Test for Exam 70-768
I invested in an official practice test from MeasureUp. This was the only thing I paid for while studying for this exam (excluding previous courses that my former employer paid for), and absolutely worth the money.
The practice test contained 150 questions covering all skills measured and all question types found in the actual exam. The question types include case studies, repeated answer choices, single choice, multiple choice, dropdown lists, option buttons, drag and drop snippets, as well as questions with attached screenshots. Getting familiar with the question types and learning how to interpret questions was one of the most valuable things for me.
You can see examples of exam question types in these videos from Microsoft Learning:
There are two modes in the practice test: Study Mode and Certification Mode. In Study Mode, you can choose the number of questions, the question types, which exam objectives you want questions from, and whether or not to automatically display answers. For each question, you will get detailed explanations of the correct and incorrect answers. These explanations also link to external articles and documentation going into more detail. The Study Mode settings allow you to choose specific areas to focus on. The Certification Mode simulates an actual exam. You get the same number of questions, the same amount of time, and you cannot view any answers until the test is completed.
Using the Practice Test to study for Exam 70-768
I started a Study Mode test and went through every single question in detail. I looked at the correct and incorrect answers, read the referenced articles and documentation, took even more detailed study notes, and experimented in my lab environment. Yes, some of it was simply memorizing options and syntax. No, I don’t think that’s the best way of measuring skills. The things you memorize are the things you can look up in less than a minute. The real value came from digging into the topics and really understanding why one answer was correct and another incorrect.
On the day before my exam, I completed three Certification Mode tests with an average score of over 95%. But beware. After going through each question multiple times, you may fall into the trap of just memorizing the correct answer. Make sure you actually know why it’s the correct answer and why the other answers are incorrect :)
Total time spent on practice tests: 20-25 hours
Skills Measured in Exam 70-768
Finally, I went through the list of skills measured to ensure I hadn’t missed anything. For each skill, I checked that I could explain the topic (for example In-Memory vs. DirectQuery) or complete the task (for example analyze query plans by using Extended Events and Profiler). If there were multiple ways of doing something, I practiced all options: using the GUI, stepping through wizards and/or writing code.
Total time spent on additional studying based on skills measured: 5-10 hours
Taking Microsoft Exam 70-768: Developing SQL Data Models
The first step of actually taking the exam is to accept and sign the Microsoft Exam Policy and Non-Disclosure Agreement. Because of that, I obviously can’t say much about the exam itself :) Instead, I want to share a few of my experiences taking the exam.
Question text and question types
Investing in a practice test, getting familiar with the question types, and learning how to interpret questions was one of the most important ways to prepare. (The most important thing is to know the content, obviously!) The questions often have a lot of text, irrelevant information for that specific question, or information that can confuse or distract you. I consider myself a good reader and fluent in English, but I still spent a lot of time just reading and re-reading the questions. The practice test helped me learn how to pick out the relevant and important information and focus on the question itself.
Tip: Read all questions bottom-up or from the end. Start by identifying the actual question, then look at the alternatives for the answer. Finally, work your way through the entire question text and background information to find keywords, hints, and information to support the answer you think is correct. For example, if the question is focused on Tabular Models, you can probably ignore all the background information about Multidimensional Cubes.
You have a total of 150 minutes for the exam, but only 120 minutes to answer all the questions. The remaining 30 minutes are for reading instructions, signing the NDA, and commenting on questions after you finish your exam (if you choose to do so). During the exam, you have a huge timer on the screen constantly counting down and showing you the remaining time. (I hate the Big Timer of Doom!)
There is a lot of information to process. You might get stuck on a few questions. You probably want to review some of the questions at the end. When you start your exam, look at how many total questions you have to answer. Make sure you have enough time for all questions. According to Microsoft, exams have 40-60 questions. That means you have 2-3 minutes per question. Let that sink in. Two to three minutes. To read, understand, interpret, compare, consider and answer. That is not a lot of time! My advice is that if you feel stuck on a question, mark it for review and return to it later. It’s better to get that one question wrong than not having time to answer the last five questions.
Help! I don’t know the answer?
Even after all your hours of preparation, you might come across a question that takes you by surprise. It could be something you have never had to look at before, or it could be a question on something that is not covered in the skills measured. (Don’t forget that Microsoft explicitly states: “Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.”) I got a few of these questions. My best advice is to use the elimination method to narrow down the options to the most likely answer, and if that doesn’t help… take your best guess :) Pick the answer that sounds most likely or logical, and move on to the next question. Remember that you don’t need 100% to pass the exam, so don’t worry too much about a single question.
Finally, don’t overthink it. There are no “it depends” answers in the Microsoft certification exam world. Even though I wish I could add “yes, but, if…” to many of my answers, you can’t. You need to go in with a mindset that there is either a right answer or a best answer. Read the text and stay as objective as possible. Use the facts in the question to make your decision, don’t fall into the “it depends” trap. If you’re unsure, pick the best answer. Again, don’t get stuck on any single question. Go back and review them at the end. You may even answer some other questions in the meantime that may trigger your memory.
In total, I spent around 60-80 hours preparing for Microsoft Exam 70-768 (Developing SQL Data Models):
- Courses and Labs (25-30 hours)
- Review Training Material (10-15 hours)
- Practice Test (20-25 hours)
- Skills Measured (5-10 hours)
You may have to spend half or double the time I did, depending on your background and experience.
My key takeaway and final piece of advice is: work through the entire list of skills measured. It doesn’t matter how you choose to do it, but do it. Not only will you be prepared for the exam, but you will be confident that you have the skills needed to develop Tabular and Multidimensional data models with confidence.
Best of luck! :)