Microsoft BI Certifications or Why SQL is a 4-Letter Word

Acronyms Aplenty

This article will discuss the Microsoft Business Intelligence Certification and exams 70-431, 70-445, and 70-446.  These exams revolve around the suite of SQL Server data management and analytic tools available in SQL Server 2005, and how to implement real-world solutions with the products.  I will try and cover the steps and resources I used to become certified, and will provide a high-level overview of the topics that may show up on your personalized exams.

Continuing down the certification path is something most people I know dread. Whether it's Oracle, Cisco, PMI or just plain old Microsoft Certifications, it usually takes studying with simulated exam software, training courses, MSDN, TechNet, reading blogs & articles, and lots of heavy books to succeed. A bit of real world experience helps too.

With Microsoft SQL Server 2005 comes a slew of 4-part acronyms. No longer just content with SQL or DTS, Microsoft now has SSIS, SSAS, SSRS, SSMS and BIDS to add to the marketing and technical jargon dictionary. All of this is wrapped in the Unified Dimension Model (UDM), marketing terminology for an Online Analytical Processing (OLAP) Cube and its associated Data Source View (DSV), which connects to multiple Data Sources (DS). The figure below shows how this all fits together.

Figure 1 - The UDM Architecture

This product suite would feel right at home with one of the larger companies I have done work for in the past, which actually had an Acronym Database just to keep track of their own tech speak.

Somehow the certification acronyms manage to be even worse.

Getting MCITPBID Certified

Recently I completed the certification exams for the SQL 2005 Business Intelligence suite of products. These exams were released in March of 2007, and I took them previously as beta exams in February of 2007. They must have worked the bugs out since I passed the real exams. I didn't do so well with the betas...

What are Beta Exams?

Prior to release of an exam, Microsoft usually offers select candidates access to beta exams.  These are rough drafts of the exams that will be eventually released to the public.  Just like a piece of beta software, these exams will usually have bugs and need to be vetted by the user community.  Even though the beta exams do not count towards certification, they are a good study tool to get an idea of what you may encounter.  Typically you would register with Microsoft Connectand become a beta tester of the software to become familiar with it.  Then you would watch the site and your for beta exam postings.  These beta exam postings also appear in the MCP NewsFlash letter.  You can subscribe here.

The main source of information that I use to study for these exams was the Skills Being Measured outlines of the exams themselves. By searching for key terms in the outlines, reviewing the resulting MSDN, BOL & TechNet articles, and crossing out each outline item, I seem to be able to piece together what the exam authors were really thinking when they wrote the questions. Since the most important part of any question is the answer, looking at these sources provided some insight and seeing similar phrases during the exams helped to jog my memory.

"OneNote is Awesomeness"

Using OneNote 2007is my recommended way of tracking all of this study material, in addition to capturing screens and web page snippets for future reference.  Qualifying students can now purchase this for $59.95 as part of the Office Ultimate 2007 offer.  Unfortunately studying for a Microsoft exam doesn't qualify you as a student.  The trial version is available here.

Here are the steps to take to setup OneNote as a powerful study guide tool:

1. Create a section in OneNote for each exam.
2. Use the Skills Being Measured outline to build a table of contents.  You may have to massage this in Excel to filter out headings and other information.
3. Setup pages under the table of contents to break down each topic into further details.
4. As you feel comfortable with each topic and have enough information for future reference, cross them off.
5. Print out the topics you don't feel comfortable with, and try using the features in the product.

You should end up with something like the following image (click to display large version):

Helpful OneNote goodness:

Cross list items off using CTRL - Minus shortcut, or alternatively set them up as tasks to check off.  Screen shots are available with Windows Key - S.  Install the Send to OneNote 2007 Powertoy to send copies of e-Books and reference documentation to your study guide from Explorer. There is also an icon to Send to OneNote from Internet Explorer for MSDN, TechNet and blog articles.

Once your guide is complete, you can Save your notes as HTML or Word documents, blog them, and even Share the notes with your study group if you have one.  Lots of other tips and Power Toys are available by searching blogs.msdn.com and on Daniel Escapa's OneNote blog and Chris Pratley's OneNote blog. Here is a more comprehensive list on Using OneNote as a Research Tool.

Using this study guide gave me the satisfaction of being able to measure my studying progress, since 5 outline items studied out of 74 is 6% complete when 5 items are crossed off.  The study guide is also useful for building a knowledge base for your company.

There are tons of resources available to you, and here are some more I have used and would suggest you use based on your skillset:

• Read lots of books
• Attend classroom or take online training
• Join a User Group and try solving other people's problems
• Create a blog on the topic and read material on other blogs
• Research articles on sites like www.SQLServerCentral.com by people like Brandie Tarvin, Steve Jones, Brian Kelley and many others.
• Subscribe to the SQL Server Central newsletter
• Try out the SQL 2005 Enterprise Edition Virtual PC image
• Try some practice exams from Self Test Software, Transcender, and others.
• Get the training guides, sometimes available for free from user groups or heavily discounted from Microsoft with partner benefits.

In the following sections, I will get down to business and discuss the exams themselves and provide my opinions on the complexity of each and "gotchas" to look for. 

The Exams

If you want to get certified on the SQL Server 2005 Business Intelligence Platform, the exams you will need to take are the following:

• Technology Specialist (TS) Exam 70–431: TS: Microsoft SQL Server 2005 - Implementation and Maintenance

  Actually you don't need to take this one anymore to get your BI certification, though it will get you certified as a Microsoft Certified Technology Specialist: SQL Server 2005 and I would recommend it as a stepping stone to the BI exams and SQL Developer exams. Oddly enough, it sounds strangely similar to the one below...

• Technology Specialist (TS) Exam 70-445: TS: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance

  This exam is the standard multiple-choice fare familiar to the 2+ million MCP's out there, though it covers a broad array of components, though there are some new interactive simulation questions with mock SSMS & BIDS interfaces.

Here is the breakdown of content, and some comments from my study notes:

Managing SSAS (12 percent)

• How to create and optimize partitions & aggregations
• Dimension and partition storage modes
• Study cell, dimension and cube security
• MDX

Developing SSAS Solutions by Using BIDS (18 percent)

• Pick up a few books on Data Warehousing and Analysis Services
• Know your cubes, dimensions, hierarchies, attributes & attribute relationships, the UDM, KPIs, Actions, and any other tabs and properties in the BIDS interface.
• Study how to hide members in a hierarchy
• Data Sources & Data Source Views
• And more MDX

Implementing data mining by Using BIDs (12 percent)

• Print out Books Online's Data Mining topics and try each of the mining models to get an idea of how they can be applied.
• Download the Microsoft Data Mining Plugin for Excel 2007 and try it out
• Know about DMX

Managing SSRS (13 percent)

• Create & deploy reports and data sources
• Setup security roles
• Create subscriptions & snapshots
• Know about Data Driven Subscriptions
• Deploy models & work with encryption keys

Developing Reporting Solutions by Using SSRS (19 percent)

• Data regions, rectangles, tables and matrix controls.
• Custom coding and assemblies
• Functions and expressions
• Parameters, properties, filters & groupings

Developing Business Intelligence Solutions by Using SSIS (16 percent)

• Slowly Changing Dimensions
• Analysis Services Processing Tasks
• Script Components & Variables
• Control Flow, Data Flow and Containers
• Transactions & checkpoints
• Debugging packages & error handling

Administering SSIS Packages (11 percent)

• Command line tools
• SQL Agent tasks
• BIDS functionality with SSIS
• Logging & Configuration

See the exam learning web site for more topics.

The final exam I wrote was by far the toughest, if only because of the amount of content to read:

Professional (PRO) Exam 70-446: PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

This one beast of a BI exam and reminded me of the Exam 70-300 Analyzing Requirements and Defining Microsoft .NET Solution Architectures, which was another heavy read.

Mine had a seemingly endless amount of case studies and questions, took over 2 hours to write, and at the end of the exam the computer crashed. Luckily my score was saved, and I ended up with 10 copies of my passing transcript after we got the printer working at the testing centre.

Completing exam 70-445 and studying the associated materials should allow you to go into exam 70-446 with the ability to apply your knowledge to various case studies as a generalist in Business Intelligence, and a specialist in SQL Server 2005.

Why Bother?

Some people suggest that certification exams are really marketing drivel with simplistic questions that are not applicable in the real world.  Some of the exams I have written in the past gave me the same opinion.  However, Microsoft has tried to address opinions like these with the newer exam and certification programs, and it seems to show with these exams.  The Business Intelligence exams require a diverse knowledge of each component of the SQL 2005 platform and how it applies to real-world solutions.  Oddly enough, my best results came with the topic I was most afraid of, Data Mining. I focused heavily on that topic because of my low comfort level with it and it seemed to make a difference. The real story here is not writing the exam and passing - it is studying the material itself and understanding technologies and features you didn't know before.  By researching the materials in these exams using the above methods, you should come out with:

• A comprehensive knowledge of the product
• A designation that you can add to your CV and experience history
• A knowledge framework and reference documentation around the key features of the platform
• An involvement in the user community
• Benefits and recognition from your company and clients

If your company is a Microsoft Partner, as outcomes of being certified they gain "partner points", discounts on licensing costs, marketing material, customer references, and many other benefits.  It really pays to have a certification strategy for your company and its employees if you are offering Microsoft-based products and solutions, or implementing them internally.  There is more information on the Microsoft Partner Program Business Intelligence Specialization requirements available here.

If at first you don't succeed...

As a key motivator, I usually book my exams a few weeks out from studying for them.  That way I have a definite target date to achieve.  If I don't feel that I'm up to writing, I reschedule a few days before the date.

There's no reason to worry about failing your first time out, even if you don't write the beta exams.  Get a second chance to pass your Microsoft Certification exam—free!

Author's Profile

Andrew Sears is a Senior Solutions Consultant and Microsoft Certified Technology Specialist: Business Intelligence Developer (MCITBID) at T4G Limited, with over 10 years experience in the Analytics group implementing Reporting, Data Warehousing and Business Intelligence Solutions. T4G Limited is a leading full-service, project-based technology services company and a member of the Microsoft Gold Certified Partner Excellence Program.

Andrew currently maintains various blogs on topics including Silverlight,PerformancePoint, and SQL Server Business Intelligence Solutions.