Last week, I passed my final exam to acquire the MCSE BI, I want to share with you this feedback especially on my preparation for all exams, it is not a strict guideline of course, but it just what was best for me.
The MCSE certification consists of passing 5 separate exams: the first 3 give you the MCSA SQL Server certification:
MCSA exams are the same for everyone; it does not matter if you are going for the MCSE - Business Intelligence or MCSE - DATA Platform.
o 70-461: Microsoft SQL Server Querying 2012 / 2014
Anyone working with SQL Server, whether a DBA, a database developer, or a BI developer, should know at least the basics of T-SQL. So this review should not be too difficult for most people.
To prepare myself, I started with the formation of MVA (https://mva.microsoft.com/en-US/training-courses/querying-microsoft-sql-server-2012-databases-jump-start-8241). I can absolutely recommend this training to everyone to get a good level with the windowing features in SQL Server. Next, I read MSDN articles on new T-SQL features like TRY_PARSE, LANCER and LAG / LEAD. MSDN gives a good overview.
The exam may contain questions that require you to write code.
I passed the exam the first time, but I have to admit that code writing issues are more difficult than it seems when you do not have MSDN pages available to check your syntax.
o 70-462: Microsoft SQL Server Administering 2012 / 2014 Databases
This is the exam I feared the most. I am certainly not DBA. I took my time preparing for the exam, following the MVA training (https://mva.microsoft.com/en-US/training-courses/administering-microsoft-sql-server-2012-jump-start-8259): I set up a virtual environment with VMWare to do all the DBA stuff, such as mirroring, database replication, back-ups ... The training does not go very far in the details, Prepare to do some research.
And as I was afraid of not passing this exam I passed it with the second shot offer just in case, but fortunately I did it on the first try, but my score was really not impressive. But hey, what are you waiting for from a BI guy.
o 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012 / 2014
It deals mainly with SSIS, but also has some sections on DQS, MDS and Dimensional Modeling. I started the preparation with MVA (https://mva.microsoft.com/en-US/training-courses/implementing-a-data-warehouse-with-sql-server-jump-start-8257), I read The book « Data Warehouse Toolkit Ralph Kimball. Any serious BI professional should read this book. This book gives you all the details you need about dimensional modeling: star schemas, slowly changing dimensions ... Microsoft relies heavily on the Kimball approach, in the design of the data warehouse, but also in Analysis Services Multidimensional. As for data quality services (DQS), I was surprised because I have never done it and I answered on 100% of questions, luck !! The last item on the list for this exam is Master Data Services (MDS). Since it only takes a small percentage of the questions, I have not put a lot of research on this product. The exam itself went pretty well, although I struggled a bit with repeated response choices because it was the first time I saw these types of questions and took a few seconds before I realized this was happening.
o 70-466: Implementing Data Models and Reports with Microsoft SQL Server
After a few days of well-deserved "non-studying for the exams", I decided to pick up the pace again and start preparing for the 70-466 exam.
I was between projects, so it was an excellent time to review with these projects. I started as always with MVA (https://mva.microsoft.com/en-US/training-courses/implementing-data-models-reports-with-microsoft-sql-server-8895), after I read the excellent book « Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model By the SSAS Maestros Alberto Ferrari, who comes to a huge 656 pages. It's a great book and it will teach you everything you need to know about tabular and DAX models, but in reality it's far too detailed for exam preparation. But hey, a little too much knowledge does not hurt anyone, right? So, if you just want to get up to speed with tabular SSAS you can skip a few chapters, such as Advanced DAX for example. I delved deeper into the 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 so I had only one exam for MCSE certification!
o 70-467: Business Intelligence Designing Solutions with Microsoft SQL Server
The exam was really interesting to study. What makes it so interesting is that everything is together for this review. You need to know about SSIS, SSAS (Multidimensional and Tabular), SSRS (Report Designer, Report Builder, Power View, PowerPivot, and PerformancePoint) and the integration of SharePoint to succeed. Everything just comes together.
To prepare, I took the MVA training (https://mva.microsoft.com/en-US/training-courses/designing-bi-solutions-with-microsoft-sql-server-8453) and the 20467D course: Designing Business Intelligence Solutions with Microsoft SQL Server 2012, which is a fascinating read. I revisit a few chapters on SSRS and SSAS administration and dig deeper into SharePoint integration.
The exam itself was not easy, but I managed to get through it, so now I'm the proud MCSE holder - Business Intelligence Certification!
Good luck everyone !!