Last week I took the final exam to acquire the MCSE – Business Intelligence certification. This blog post describes my preparation for all of the exams. It is not a strict guideline of course, it’s just what suited best for me.
The MCSE certification exists of 5 separate exams, of which the first 3 grant you the MCSA SQL Server certification. These are the following:
- 70-461: Querying Microsoft SQL Server 2012
- 70-462: Administering Microsoft SQL Server 2012 Databases
- 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
- 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
- 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
Before I started my certification spree on SQL Server 2012, I already had the three MCTS certifications for SQL Server 2008 (BI, DBA en database developer) and the MCITP: Business Intelligence Developer 2008. So I only had to refresh the older material – especially the DBA stuff – and learn all the new features. Why didn’t I took the upgrade exams? Because I like to challenge myself and I wanted to make sure I learned as much as possible in the progress.
70-463
This is the first exam that I took. It deals mostly with SSIS, but has also some sections about DQS, MDS and dimensional modeling. I took this exam a year ago in beta, so there were no preparation materials available. Luckily I consider myself quite proficient in SSIS, so I didn’t need much preparation. After all, if you are going for one of the MCSE certifications, you should have at least one specialization in the SQL Server stack.
I learned about the new SSIS features (which was Denali CTP1 at the time) by giving a session on the Belgian SQL Server Days in 2011:
Working with the New Project Deployment Model in SSIS for Dummies/Smarties
You learn a lot about something by trying to teach others about it. Of course I also read a lot of blog posts about the subject, of which most of them are mentioned in the video. I gave the same presentation again for the SQLLunchUK user group, but this time through a LiveMeeting.
A few years back I read The Data Warehouse Toolkit by Ralph Kimball. Every serious BI professional should read this book, even if you are an adept Inmon follower. This book gives you all the details you need about dimensional modeling: star schemas, slowly changing dimensions, table grains et cetera. Microsoft relies heavily on the Kimball approach, in the data warehouse design but also in Analysis Services Multidimensional.
Regarding Data Quality Services (DQS), I also mastered the basics by giving a session. This time a webinar for the Belgian 12 Hours of SQL:
An introduction to Data Quality Services (DQS)
Most information about DQS I got from MSDN articles, such as these How-To videos, and from TechEd videos, like this excellent one from Elad Ziklik: Using Knowledge to Cleanse Data with Data Quality Services.
The last item on the list for this exam is Master Data Services (MDS). Since it only takes a small percentage of the questions and I’m not really convinced by the product – to be honest – I didn’t put much investigation to it. I read the free e-book Introducing SQL Server 2012 training kit, so I at least knew how to handle the product.
The exam itself went pretty well, although I struggled a bit with the Repeated Answer Choices questions, since it was the first time I ever saw those type of questions and took a few seconds before I realized what was going on J
70-461
Every person working closely with SQL Server, be it a DBA, a database developer or a BI developer, should know at least the basics of T-SQL. So this exam shouldn’t be too hard for most people. To prepare myself, I read the book SQL Server 2012 High-Performance T-SQL Using Window Functions by Itzik Ben-Gan to learn more about the windowing functions introduced in SQL Server 2012. I can absolutely recommend this book to everyone to get up to speed with Windowing Functions in SQL Server. Itzik really does a great job by explaining all the concepts very clearly and uses a lot of practical examples. A must-read.
Next I read blog posts and MSDN articles about the new T-SQL functionality, such as TRY_PARSE, THROW and LAG/LEAD. This MSDN page gives a nice overview. Since the exam might contain questions that require you to write code, I refreshed my knowledge on the syntax of the several TSQL statements. Finally I re-read the chapters about T-SQL of the training kit for the 70-433 exam, especially the one about XML. Because you know, I use this everyday so I’m an expert at it (uh-hum. Irony).
I passed the exam on the first try, but I have to admit that the code writing questions are harder than it seems when you have no MSDN pages available to check your syntax.
70-462
This was the exam I feared the most. I’m certainly no DBA and although I already had the MCTS for administrating SQL Server 2008, I had to relearn almost everything again as I didn’t have much real-time practice regarding administration. So I took my time preparing myself for the exam, going over the training kit a few times and watching some TechEd videos on Channel9.
I did all the exercises of the training kit by setting up a virtual environment with a few servers using HyperV and doing all the DBA stuff such as mirroring, replication database, taking back-ups et cetera. The training kit briefly describes how to set this environment up, but it really doesn’t go in too much detail, so you’re pretty much on your own. If you have never heard of differencing disks for virtual machines before – like I did – prepare yourself to do some research. There was especially no information on how to set-up the networking, which appears to be crucial when you went to set-up clustering, as you need to have multiple networks. I briefly go over my set-up in this forum thread. The training kit and the matching practice tests also have some errors, so make sure to check out the Errata page of the book.
The new types of exam questions (check the section Exam formats and question types) also include drag-and-drop questions, so I focused on learning the correct sequence to do something. For example, how to set up mirroring, replication or Availability Groups, how to do a partial restore, how to upgrade a cluster et cetera.
All in all the training kit did a good job preparing me for the exam, as I passed it on the first try, but my score really wasn’t that impressive. But hey, what do you expect from a BI guy J Finishing this exam gave me the MCSA certification.
70-466
After a few months of well deserved “non-studying for exams” I decided to pick up the pace again and to start preparing for the 70-466 exam. I was in between projects at the time, so it was excellent timing to get some studying done.
I started by reading the excellent book Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model by SSAS Maestros Chris Webb, Alberto Ferrari and Marco Russo, which comes at a whopping 656 pages. It’s a great book and it will teach you everything you need to know about tabular models and DAX, but actually it’s much too detailed for just exam preparation. But hey, a little too much knowledge doesn’t hurt anyone, right? So if you just want to get up to speed with SSAS tabular you can skip a few chapters, such as DAX Advanced for example.
For SSAS multidimensional and Reporting Services, I just re-read the relevant chapters of the 70-448 training kit, giving more attention to features I don’t use a lot, like pro-active caching, MDX (I know, sorry Chris Webb) and administrating SSAS and SSRS servers. I skipped the data mining chapter, as it apparently didn’t make the exam Skills Measured section. I digged deeper into SSAS and SSRS administration by reading relevant blogs and MSDN articles, such as Reporting Services Execution and Trace Logging.
The exam went pretty well on the first try and thus I had only one exam left for the MCSE certification!
70-467
The exam was really interesting to study for. You can compare it a bit to the MCITP – Business Intelligence exam. What makes it so interesting is that everything comes together in this exam. You need to know about SSIS, SSAS (multidimensional and tabular), SSRS (Report Designer, Report Builder, Power View, PowerPivot and PerformancePoint) and SharePoint integration in order to succeed. Everything just comes together.
To prepare myself I went over course 20467A: Designing Business Intelligence Solutions with Microsoft SQL Server 2012, which is a fascinating read. I revisited some chapters about administration for SSRS and SSAS and I dug a bit deeper into SharePoint integration (but let’s face it, I’ll never get that Kerberos authentication thingy).
The exam itself wasn’t that easy, but I managed to get through, so now I’m the proud holder of the MCSE – Business Intelligence certification!
Conclusion
This was my entire preparation. I’m done now with exams for a while J
If you have interesting reading materials or videos that can help other preparing themselves for their exams, please share them in the comments!
Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.
36 Comments
Congratulations on passing all the exams
very interesting blog.
I completely agree on what you say about Kimball.
but you forgot to mention “The Datawarahouse ETL Toolkit” from the very same Ralph Kimball and Joe Casserta.
It is a bit old fashioned now ( it is a book from 2006 in the end ) but in a lot of principles of SQL 2012 DQS, i recognise a lot of stuff that’s also been described in this book (mind, written in 2006 allready)
@Bert, I have read the ETL Toolkit, but I’m not going to say it really helped me pass the exam. You can do just fine without reading the book.
Don’t get me wrong, it certainly is a very interesting read – and I recommend this book to everyone – but not exactly a prerequisite for the exams. The Data Warehouse Toolkit is much more essential.
Congratulations on passing – I’m working my way through the MCSA first, then on to the MCSE/BI.
I find it extremely annoying (near negligent) that no course study guides have appeared for 467/468 from Microsoft.
If they’re going to have a test – they really need to be sure a Study Guide appears at somewhat the same time.
s
Hey.. Congratulation..
I want to know can I directly write 70-466 exam without writting 70-461,70-462,70-463.
@Sas: sure. You can take the exams in any order you want. However, you can only get the MCSE certification if you also get the MCSA certification.
So taking only the 70-466 exam without doing any other exam is pointless regarding certifications.
Hi Koen,
Is there a training kit for 70-466 and 70-467.
Could you provide some links of free sources for these 2 exams.
Regards,
Shezan
@Shezan: There aren’t any training kits available for these exams – you can check that at the MS Learning site – and I don’t think they will ever come.
The only material availably by Microsoft is courses, which are of course not free.
If you are looking for free content, go over the skills measured section and look for articles/blog posts/tutorials for every topic you’re not comfortable with.
How long do you think a newcomer to MSCE data would take to get up to speed to pass these exams. I want to get into data and get these certs. Do you think it’s do-able in a year?
Everything is do-able, granted you have enough time/energy to put into it.
The MCSA exams would certainly be a reachable goal. The querying exam shouldn’t be too difficult. In my case, the DBA exam took quite some studying.
The last two exams for the MCSE are the most difficult. You don’t always have training kits for them and the questions expect you to have some experience in the field. If you have no experience at all, you’d need more preparation time.
Thanks for your reply.
I really want to complete the MSCE in one year as I want to get into BI and not just admin.
How long did it take you to do all five exams? Out of interest? Did you use the Training Kits? I have the 70-461 kit and i’m finding it very verbose. Chapter 2 goes into detail on the intricacies of page reads and leafs (which im not really au fait with). Is that stuff really necessary to pass the exam? What should I focus on? Can you give me any advice ^^ Thanks your post is amazing btw its helping me out a lot.
Hi David.
I did it in about one year, but I didn’t study all the time.
I did the 70-463 exam in beta (before it was publicly released) and I studied about one day for it. This is where the experience kicked in. I have a lot of SSIS experience, which is the biggest part of the exam, so I didn’t need to study a lot.
When the official exams were released, I immediately did the 70-461 exam. It’s about querying, and if you go for MCSE, SQL shouldn’t be a problem. So I didn’t really need to do much preparation, just go over the new stuff and over XML.
I studied a few months for the 70-462 exam, as this one was the hardest for me. I took a break for a few months, and did the last two exams in a few weeks. I studied about 1.5 weeks for an exam, and then did it. Again, I already have experience in BI and I did the exams for SQL Server 2008, so that’s why I can cut back on studying time.
I didn’t use any training kit, simply because they were not available at the time I did the exams.
The 70-461 training kit might be verbose, but it is written by Itzik Ben-Gan, one of the gurus of TSQL. So I would definately read it, you might learn a few tricks here and there.
Where do you need to focus on? Everything that is mentioned in the Skills Measured sections of the exams. The exams do not focus only on the big picture, but can also go into very deep details. The certifications are meant to show that you have experience with the SQL Server product, and some of those details you get to know by using the products a lot.
Since you are beginner as you said yourself, I’d expect you have a lot of studying to do. Try to not only read books (books alone won’t get you through the exams), but also do some tutorials and exercises. Really get to know the product and how it works.
Thanks for your reply, its great to have some insight from a pro!
When I say “verbose”, let me tell you what I mean. For example. The author states that a working knowledge of basic T-SQL is the only requirement to use the book. Then, in chapter two the author engages in an in depth discussion on the subjects of sequence fragmentation, clustered and on clustered index’s, table partitioning solutions and surrogate keys, all without any introduction or prior background on these topics. It feels like it should be left to a later chapter (or even exam topic! – like 70-462) rather than introducing it here. This is obviously coming from a beginners perspective.
Here’s another example:
What is the difference between the simple CASE expression and the searched CASE
expression?
A. The simple CASE expression is used when the database recovery model is simple,
and the searched CASE expression is used when it’s full or bulk logged. ( I don’t know anything about database recovery models, had to go do my own reading at this point as this is the first time the terminology was introduced)
B. The simple CASE expression compares an input expression to multiple possible
expressions in the WHEN clauses, and the searched CASE expression uses independent
predicates in the WHEN clauses.
So B is the answer.
Instead look here: http://www.oratable.com/simple-case-searched-case/
the topic is introduced with examples and I can clearly understand.
I’ll stop going on about it now but I just wanted to clarify why I’m struggling with this book.
Thanks again for your support.
I get it. Itzik can go quite deep sometimes 🙂
It’s very difficult to write a book for beginners, when you have years of experience with the subject. It’s easy to forget that some topics are not simple or basic for a beginner.
One advice that I can give you is to research everything you don’t understand. Don’t get recovery models? Read the relevant MSDN page or Google for a introductory article. It will be useful for later exams. You also don’t only have to understand why B is the answer, but also why A is incorrect. In this case, because recovery models don’t have anything to do with TSQL 🙂
Congrats Koen.
Thanks for this blog.It are very useful for me as I am preparing for MCSE.
I am DBA and have good knowledge of SSIS and beginner’s knowledge in SSRS and SSAS.I want to go for 70-466 and 70-467.
What do you think are the most important topics?
Which are the most difficult topics for you?
Which book/books you strongly recommend for 70-466?
@Pramod:
70-466: for important topics: check the skills measured section of the exams. The one with the highest percentages are the most important ones.
Most difficult for me are MDX, DAX, SSAS MD storage modes (rolap, holap, molap) and proactive caching.
70-467: this is a bit of an odd exam. You have to know everything for this one. SSIS, SSRS, SSAS and SharePoint integration as well. I’d recommand to do this exam as the last one.
Books for 70-466:
for SSAS MD and SSRS you can just read the training kit for the 2008 exams. There aren’t a lot of changes between 2008 and 2012. For SSAS Tabular you can read the book by Marco Russo, Alberto Ferrari and Chris Webb: http://www.amazon.com/Microsoft-Server-2012-Analysis-Services/dp/0735658188/ref=sr_1_1?ie=UTF8&qid=1378464853&sr=8-1
The book is a bit overkill, but you get a decent foundation.
Thanks a lot Koen.
Surely this is going to help me in my preparation.
How long do you think a newcomer to MSCE data would take to get up to speed to pass these exams. I want to get into data and get these certs. we have also a training school name CCTC.
@RajL: these exams are geared towards people who actually have some experience working with SQL Server. I’d expect a few months effort from a total newbie.
@Koen – Hi, I am a BI professional with 9+ yrs exp.Planning to do MCTS 70-448 certification. I know its traditional (2008) technology but i just want to brush up my technical skills through this certification and then later to do MCSE (2012)..
Your thoughts/Suggestions are most welcome.
@gopal: since the 2008 exams are about to retire (or have already retired?), they might not give you an actual certification.
I would directly go for the 2012/14 MCSE exams. A lot of the stuff is the same as in 2008 anyway.
Hi Koen Verbeeck,
I would like to get some opinions from you. Do you think it is worth to spend the efforts and resources(time+money) to get MCSE: Business Intelligence myself?
I mean if the certification not going to increase my salary much, i would not get it.
I am not familiar with the market demand for Business Intelligence. I would appreciate if you can share your experience on how demanding it is.
Thanks.
Hi Han.
If you have to pay yourself for the certification, I would not do it. It will not get you a substantial salary increase (I would be surprised if it did). It will probably improve your knowledge though, so it might help in the long run.
Do you have any experience in BI?
The market demand for BI is pretty good, at least where I work.
Hi Koen Verbeeck,
For your information, i have been working as a software engineer for 1.5 year. Currently, i am taking MCSA: Sql server 2012 for getting more knowledge.
So now I am trying to make a decision of whether to continue focus on software in future or I can switch to another area with better income.
Do you think if i got MCSE: Business Intelligence, will I be qualified for a better position or job role? For example, what kind of positions or job roles?
@Han: try focusing on doing the things you love/like in your job. The money will follow.
Getting the MCSE BI certificate will not land you magically a better job. You also need to have experience/knowledge. A certification is only one (small) piece of the puzzle.
Typically traditional BI jobs are ETL developer, report developer, cube developer and any combination of those three.
Good Article
Thanks Koen, I used your tips to take (and pass) 70-466 today. Don’t use SSRS in my capacity, so that was the most difficult portion for me, but the 70-448 book does seem to cover mostly everything you need for that. I imagine if you develop or administrate in an SSRS environment, you’d be just fine. For the tabular portion, I read your recommended book which proved invaluable.
Over all, my study tools were going through the AW2014 SSAS Mulitdimensional Tutorial, the Multidimensional SSAS and SSRS portions of the 70-448 training kit, and the BISM Tabular book you mentioned. I also had purchased Expert Cube Development with SSAS Multidimensional Models (http://www.amazon.com/Expert-Cube-Development-Multidimensional-Models/dp/1849689903), but did not get to it.
Thanks again Koen, I hope to round out the MCSE certification shortly with 70-467
Great to hear everything went well Patrick!
Good article on MCSE certification,thanks for sharing.
hi help me s ma fist tym doing mcse server structure how s the course
Hi Vincent,
I have no idea, I have only done the MCSE Business Intelligence cert.
Nice Post.. Excellent Info.. Really amazing.. This was a fantastic article… really superb….
Well done Koen. It’s been a while but hopefully you’re still monitoring these comments.
I currently work in a different IT field but want to start from the beginning and get the MCSE:BI in 2016. Since I do not have access to SQL server currently, will it be sufficient to purchase/download the developer edition for home use while studying/learning?
Does the developer edition (currently $59.95 from the Microsoft store) have everything necessary for the MCSE:BI?
Hi BD,
yes, developer edition is the same as enterprise edition, so it has all the features.
This article is an interesting & inspiring read.