Last week, I passed my final exam to acquire the MCSE BI, I would like to share with you this feedback especially on my preparation for all the exams, this is not a strict guideline of course, but it is is just what was best for me.
The MCSE certification consists of taking 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, I started with the MVA training (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, replication database, back-ups ... The training does not go very far into 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 By Ralph Kimball. Any serious BI professional should read this book. This book gives you all the details you need on dimensional modeling: star diagrams, slowly changing dimensions… Microsoft relies largely on the Kimball approach, in the design of the data warehouse, but also in Analysis Services Multidimensional. When it comes to Data Quality Services (DQS), I was amazed as I never did and answered well on 100% of the questions, luck !! The last item on the list for this exam is Master Data Services (MDS). Since it only takes a small percentage of questions, I haven't put a lot of research into this product. The exam itself went pretty well, although I struggled a bit with the repeated answer choices as it was the first time I saw these types of questions and took a few seconds before I realized this. that 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 great timing to revise with those 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 it all came 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 SharePoint integration to be successful. It all 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 !!