Business Intelligence (BI) is a mystery to many SQL Server developers and administrators. The MCITP qualification offers a great way to learn about it, especially if you have a project you can run hand in hand with your studying. In the second half of 2011, with the development and administration MCITP qualifications under my belt (you can read about development here and administration here), I decided to tackle the BI albatross around my neck. This guide explains what is involved in the BI exams and how to study for them.

Even though SQL Server 2012 is just around the corner and Microsoft will be upgrading the exams, a lot, if not all, of what you study here will still be relevant, so by studying for these exams you are investing in the future.

What is SQL Server Business Intelligence?

Business Intelligence effectively means providing data in a format that allows your business to assess where it has been, where it is at, and where it is going. This is done by taking data from the various data sources available to you and merging them into one central database, then reporting on that data.

From this, you can deduce that there are three areas of Business Intelligence:

• Collecting data and creating a mechanism to merge it into a central datasource (SQL Server Integration Services (SSIS))
• Creating a central data source, from which statistical data can be generated and analysed (SQL Server Analysis Services (SSAS))
• Creating end user reports, which present the statistical data in a pleasant and easily digested manner (SQL Server Reporting Services (SSRS))

BI differs from other areas of SQL Server, in that there are three distinct technologies to learn. I recommend any SQL Server professional, be they a DBA or developer, to at least learn how to use SSIS – it has lots of functionality that can be useful in general database work. If you have time, learn all three technologies – understanding these gives you a greater understanding of the entire database system as a whole.

The Qualifications

As with the development and administration exams, there are two levels of SQL Server 2008 Business Intelligence certification:

• Microsoft Certified Technology Specialist (MCTS)
• Microsoft Certified IT Professional (MCITP)

To obtain the MCTS in Database Administration, you need to pass exam 70-448. To obtain the MCITP, you must pass both 70-448 and 70-452. My understanding is you can take the exams in any order, although passing only 70-452 does not give you the MCITP qualification if 70-448 has not been passed. I recommend against this – passing 70-448 will give you a much greater understanding of 70-452.

Note that neither of these exams are pre-requisites for the SQL Server 2008 Microsoft Certified Master (MCM) qualification – only the four database and development exams are required to attempt this qualification. If you do plan to attempt this qualification spend some time with the Business Intelligence suite – whilst I don’t believe it figures specifically, it will give you more knowledge about SQL Server and how its various components interact.

Exams

As with all Microsoft exams, questions are presented in multiple-choice format. Both exams ask you 50 questions, and you are given two hours and fifteen minutes to answer them. If you study properly this should give you ample time.

Note that when you book each exam you will be told you have three hours and thirty minutes to take the exam. This extra time includes time allocated to answer survey questions and provide exam feedback, you will NOT have three and a half hours to answer questions.

70-448 is the basic exam and covers the three SQL Server BI technologies, principally from a development point of view. 70-452 takes the concept further; development questions are still in there but many of the questions involve architecture and design, e.g. which SSIS task suits the given scenario best?

If you study well for exam 70-448 you will be well on the way to passing 70-452. I personally found 70-448 much the harder exam, as I was effectively starting from nothing. By the time I began studying for 70-452 I was quite comfortable with the technologies and found I could answer many of the questions through deduction and application of my existing knowledge. The point here is don’t stop if you pass 70-448 – carry on and obtain the advanced qualification too!

What You Need To Study

If you plan to take these exams, there are a couple of things you’ll need:

• SQL Server 2008/2008 R2 Developer Edition (SQL Server Express does not support most BI features and is not a suitable study environment)
• An up-to-date copy of Books Online
• The study guides for each exam from Microsoft’s Web site (links provided above)

How to Develop BI Solutions In SQL Server 2008

BI development differs from standard T-SQL development. Whereas most database developers use SQL Server Management Studio to develop database solutions, BI developers have to use Business Intelligence Development Studio. This is effectively the Visual Studio 2008 Integrated Development Environment (IDE). Anybody who has used Visual Studio before will immediately be at home.

For newcomers it may take a little adjustment (but not too much). The exception to this rule is writing SSAS queries in MDX or DMX (more on these later) – this is done in SQL Server Management Studio.

Books

There are a plethora of books available for SQL Server BI. Remember earlier when I said there were three different areas of BI (SSIS, SSAS, SSRS)? Well, they are so big they each have dedicated books available. However, unless you plan to specialise in a particular area (and why do that – learn how it all works!), you shouldn’t need to purchase any of these books. Instead, go for a general BI development book.

Before recommending books, I should point out that you do not need to purchase any books to pass these exams. All the information you need is available in Books Online, on MSDN, or via a search engine. However, I did find it more difficult to find information on certain subjects in Books Online than on the Internet at times; also, development and administration subjects are better covered in Books Online than the BI technologies (the documentation is still pretty good though).

So, although I state you do not need to purchase a book, I definitely recommend you do – it is very useful to have a reference handy whilst studying. Also, the book will continue to be of use in your day-to-day work once the exams have been passed.

I used three books whilst studying:

• Delivering Business Intelligence with SQL Server 2008
• Knight’s Microsoft Business Intelligence 24-Hour Trainer
• Microsoft SQL Server 2008 MDX Step-by-Step

The Delivering BI book is written by Brian Larson and can be heartily recommended. This is an overview book that takes you through a BI solution from start to finish. It’s a task-based book, and walks you through the creation of a BI solution for a fictional company, Maximum Miniatures. It also explains various aspects of the BI suite – for instance, the tasks available for use in SSIS. I would go as far to say that this book could pretty much provide everything you need to pass the exam, with the exception of MDX. This is covered, but not to a huge degree. For that, look to Books Online or a dedicated MDX book.

I went with Microsoft’s own Step-By-Step book. This is another book that asks you to complete various tasks (most BI books do). It covers just about everything you can do with MDX, even down to embedding MDX in SSRS reports (something you’ll need to know for the exams).

It’s debatable whether you need to purchase a dedicated MDX book to pass the exams. As I’ve already mentioned, everything you need to know is available in Books Online or on the Web. Definitely think about doing this though, as having a reference book will come in handy when you’re working on projects.

The last book I bought to study for the exam was Knight’s 24-Hour BI Trainer. This is a different type of book; it’s pretty thin, and generally consists of brief overviews of the various BI components, along with a set of tasks. The thing that sets it apart is a DVD, containing a video per chapter. These videos are (for the most part) quite good, and usually consist of PowerPoint presentations and practical demos. The book also explains how to hook BI up to Excel and SharePoint, areas which are both covered on exam 70-452.

As usual, it all comes down to the classic question – do I need to buy a book? The short answer is no, you don’t. Everything you need can be found for free. But if you’re starting from scratch, a book will act as both a teacher and a guide. Brian Larson’s book is especially effective in this area and I can’t recommend it highly enough. A 2012 edition is scheduled for release in a few months’ time.

Studying

It’s reasonably fair to say that BI is thought of as the mystical part of SQL Server. Virtually every SQL developer I’ve ever met can do a little bit of SSIS, but that’s as far as it goes. So, from my experience. there are probably two broad areas of people looking to take these exams.

The first area is existing BI developers, who obviously use the SQL Server BI tools on a daily basis. The second area includes “standard” (i.e. T-SQL) developers, who would like to improve their skillset and/or move into BI. For this second group, studying for the exams is a little tricky; there won’t be much you can integrate into the day job. That said, if you create import routines in T-SQL or SQL Agent administration jobs, you can use SSIS automation.

I’d recommend creating a clean SQL Server Developer Edition instance on a virtual machine, before installing the Adventure Works BI databases. These databases give you an introduction to the various aspects of BI, and you can modify them as much you like without any risk to your infrastructure.

Have a play!

Testing Yourself

It’s important you try out some test questions before you take any of the exams. Test questions let you see how questions and answers are presented. In general, most questions are multiple-choice, where you have to pick one answer. Often you will come across questions where you need to pick two or three answers (or an unspecified number of answers). A typical multi-answer question may present a scenario and ask you which three tasks should be chosen to correctly create an SSIS package, for example.

You can, if you wish (I’m sure you don’t!), spend a lot of money on test questions. However, it is possible to find some for free. Here’s a list:

• Pass Guaranteed (amongst the cheapest, $69 per exam – 10 questions per exam can be downloaded for free)
• Transcender ($109 for 30 days online access – 10 questions per exam can be downloaded for free)
• Self Test Software (70-452 demo questions available for download. Good value - $99 to download 155 questions, or $69 for 30 days access)

Try downloading the test questions and see what you think. Most of the question engines are very similar and ape the actual Microsoft software used for the exams themselves quite well.

If you decide to buy a dedicated study guide for the exams you should find numerous sample questions in there too.

Here’s an example of the kind of question you can expect to see (I made this one up, it is not taken directly from any of the exams):

You are creating an SSIS package and need to insert data from a text file into two tables. What task should be used to accomplish this?

Have a look at the SSIS tasks available to find out the answer!

Do you need practice questions? Yes, 100%. They give you a good idea of how the exams work and what to expect. There are certain patterns you’ll begin to note as your knowledge increases. The big thing to be aware of is single answer questions. Generally, each question presents four possible answers. If you know what the question is asking, 99% of the time you will be able to eliminate two of the answers immediately, significantly increasing your chances of answering the question correctly.

DO NOT be tempted to look at Brain Dumps. A Brain Dump contains real exam questions. If you use a Brain Dump you are breaking your agreement with Microsoft. If caught, your certification will be rescinded and you may be banned from taking future exams. Apart from all this, there’s no achievement in learning answers verbatim – and you won’t know what to do when project time comes around. Study properly and learn the subject instead.

Booking The Exams

You have to use Prometric to book your exams. It’s a pretty straightforward process. Prices for all Microsoft exams went up in July 2011 – it now costs £99 GBP to take an exam.

Keep your eye out for the second shot offer advertised by Prometric from time to time – search the Web for “Microsoft certification second shot” or similar. The second shot deal usually gives you a free second attempt at the exam should you fail it the first time around (or allows you to have a second attempt at a much lower cost).

General Things To Be Aware Of For The Exams

The next two sections will outline some of the key areas you should investigate whilst studying for the two exams. But as a broad overview, make sure you do the following:

• Be aware of the various tasks available in SSIS
• Investigate how SSIS, SSAS, and SSRS integrate with each other
• Check out how reports and data can be delivered to the end user, using Excel, SharePoint, ProClarity and PerformancePoint
• Gain at least a basic knowledge of MDX, and its main functions
• Be aware of how .NET can be utilised
• Learn the fundamentals of Data Mining, including the algorithms

You may find that you can answer quite a few questions without actually knowing the subject in a detailed manner; your general computing knowledge will help you. An example: exam 70-452 has questions on source control. These questions are not BI-specific, they literally just ask about the best way of using source control (from a Microsoft perspective, of course). If you know anything about this area you’ll be able to answer these questions.

Unfortunately there are not enough questions of this type to see you safely through the exam!

The 70-448 Exam

Passing the 70-448 exam will earn you a MCTS certification – Microsoft Certified Technology Specialist. This is the base qualification. Here are the areas covered by this exam, along with their weightings:

• Implementing an SSIS solution (17%)
• Configuring, deploying, and maintaining SSIS (15%)
• Implementing an SSAS solution (21%)
• Configuring, deploying, and maintaining SSAS (17%)
• Implementing an SSRS solution (17%)
• Configuring, deploying, and maintaining SSRS (13%)

Did you notice a pattern?! This exam really does cover the nuts and bolts of BI. It wants to know how you, as a BI developer or administrator, do things. There’s no real theory or design questions here; you’ll be asked how to ensure an SSIS package executes using a single transaction, or what a particular SSAS function does.

Take no real notice of the percentages; just learn everything as well as you can. There are some things you should know which apply to all areas of the exam:

• Managing BI using SQL Server Management Studio - You can manage SSAS and SSIS using SQL Server Management Studio. Be aware of what you can do in Management Studio. Practice manipulating your cubes and SSIS packages.

• Failover clusters - Clusters can affect the way SSIS, SSAS, and SSRS operate, and each element requires configuration to work on clusters. You don’t need to be an expert on this, but you do need to have a basic knowledge of how SSIS, SSAS, and SSRS should be modified to run on clusters.

• BIDS - The Business Intelligence Management Studio is a cut-down version of Visual Studio 2008, and it used to develop all types of BI solutions. Make sure you know your way around this tool.

• Migrating between environments - You’ll see exam questions involving migration. Migration from development to staging, staging to production and so on. BIDS and various other tools offer some efficient ways of doing this, so read up on it.

Now let’s take a look at what you need to know regarding SSIS.

Control Flow and Data Flow -  The Control Flow dictates the path followed by your SSIS package. You can execute different control flows concurrently in the same package. The Data Flow is used to manage data inputs, transformations, and outputs. It’s also possible to make conditional Control Flows. Be aware of what you can do with these two flows – they’re vital to your understanding of SSIS.

Tasks and Containers - Tasks and containers are added to the Control Flow. Broadly speaking, there are two kinds of task; “Standard” tasks and Maintenance Plan tasks. It is unlikely you will venture across any questions related to Maintenance Plans. Just be aware of what the tasks do without spending too much time on them (unless you actually want to use them, of course).

The “standard” tasks, as in the tasks you will use day-to-day, are really what you are interested in. You’ll have lots of questions on these. There are around thirty tasks. Don’t try to be an expert in all of them! Concentrate on learning the ins and outs of the main tasks and have a working knowledge of the others. Here’s a few to start you off:

• FTP Task
• Web Service Task
• Data Flow Task
• Data Profiling Task

Containers are effectively sub-packages. They group different control flow tasks together, and can be linked together or executed independently of each other. There are three types of container, and you need to be aware of them all, and what they are used for:

• For Loop Container
• Foreach Loop Container
• Sequence Container

Make sure you know the difference between the loops!

Data Flow Sources and Destinations - Both of these are used on the Data Flow. The point of the Data Flow is to take some data, do something to it, and deposit it in a particular location. A Data Source indicates where the data is coming from – a SQL Server or Oracle database, or perhaps an XML file. The destination is exactly what it says – the place where your transformed data will end up. Knowing about these is crucial.

Data Transformations - There are lots of different Data Transformations, all of which are made available to Data Flows. These take data from a data source and perform some kind of action on it. You’ll encounter a few questions on this subject. Here are some of the more commonly used transformations:

• Lookup
• Merge
• Merge Join
• Multicast
• Sort
• Fuzzy Grouping
• Fuzzy Lookup

  Each has their own idiosyncrasies. For instance, Merge Join takes two inputs to merge them into one, but will not do it unless both inputs are sorted. Learn these inside out.

Data Viewers - A data viewer allows an SSIS developer to view data during package execution. This is great for debugging packages and ensuring the correct data has been obtained in your Data Flow. And it’s also great to know as there’ll be exam questions about it!

Variables - There are two types of variable; user variables and system variables. User variables are variables that are created by the developer of the package. You may create a variable to store a dynamically created file name generated from an expression. These variables provide your package with a huge amount of flexibility. System variables can be used to obtain audit information for your package, such as the machine name a package was executed on and the date/time the package was executed. You can only read system variables, but you can read and write to user variables.

Expressions - Expressions are used in tandem with variables (mainly user variables). You may have a for each loop that exports data to a collection of text files. Files may be named “file1.txt”, “file2.txt”, and so on. An expression can be implemented to generate the file name for each iteration of the loop. Expressions can be as simple or as complicated as you like. Be aware of what they can do and common uses for them.

Checkpoints - A checkpoint is kind of like a transaction. They allow packages to restart from the point at which they failed, e.g. data was transformed but could not be sent to its intended destination because the target server had crashed. You need to know how checkpoints work, which fifteen minutes reading of this MSDN article should resolve.

Package Protection Levels - You set the package protection level from BIDS when you create your package. This dictates whether sensitive information is encrypted in your package, or even saved in your package. This can be changed when you deploy the package. If you don’t know these protection levels and how they are used, you can kiss a fair number of marks goodbye.

Related to this, check out digitally signing packages. It’s information that may well come in handy.

Package Transaction Handling - Each task has a TransactionOption property, which dictates how the task will behave within transactions when the package is executed. You can have different tasks execute within the same transaction or start their own transactions; the type of behaviour required is dictated by the requirements of your package. This is key knowledge for the exam.

Package Configuration Files - Package configurations act as an INI file, or like a configuration file in a .NET application. They store values that are accessed by the package at runtime. You may store a database server name within the configuration file, as it is possible the server could change over time. The configuration is stored as an XML file and you can store a variety of items in there. It’s well worth creating a couple of test packages and adding configuration files to them to see how they can be used.

SSIS Utilities – DTUTIL, DTEXEC, DTEXECUI - There are three command line utilities that can be used to manage and execute SSIS packages. DTEXEC is a command line utility to execute packages, and DTEXECUI is the same package with a front-end GUI. Make sure you know what these can do, the command line parameters, and how to use them in both 32-bit and 64-bit environments.

DTUTIL is used to copy, move, delete or verify packages.

Package Installation & Storage - There are three ways to store packages – on the file system, in the MSDB database, or in the SSIS Package Store (effectively a combination of the first two options). Learn all about these, and the various ways of installing and managing packages. Also read up on deployment manifests, which are used to provide straightforward package installation.

Configuring Package Execution Permissions - Once you’ve deployed the package to SQL Server, you need to configure execution permissions – especially if you are going to execute the packages using SQL Agent. Read up on proxy accounts and how to configure packages for use in SQL Agent.

Phew! That was a lot of ground to cover. Now let’s take a look at SSAS (and we still have SSRS to come!).

General Cube Architecture - Listen carefully, I shall say this only once. If you do not know the mechanics of SSAS cubes – I’m talking fact tables, measures, measure groups, hierarchies, dimensions and such like – don’t even think about taking this exam. You won’t even understand the questions. And make sure you know how to create cubes and work with them in BIDS.

Other Cube Stuff - If you know the ins and outs of cubes you are well on the way to passing this exam. I’m talking about dimension relationships, partitions, processing options, calculations, Key Performance Indicators (KPIs), and named queries to name a few. You should also be aware of how cubes can be updated too.

MDX - There are a fair number of MDX questions. MDX is the equivalent of SQL in the SSAS world. Some of the statements look similar – in fact, there’s even a SELECT statement. Investigate the functions available to you, especially those that allow you to manipulate time dimensions (LASTPERIODS for one). You also need to be up on the various axes that can be utilised, such as PAGES, ROWS, and CHAPTERS (hint – that’s not a complete list). Make sure you know how the SELECT statement works, especially with functions like TOPCOUNT and BOTTOMCOUNT.

ROLAP, MOLAP, and HOLAP - I’m not swearing at you in some strange alien language. These are the storage mechanisms used for cubes. ROLAP stores cube data in a relational format, MOLAP in a multidimensional format, and HOLAP is a mix of the two. Learn these well (oh, and check out Proactive Caching whilst you’re there too)!

Using SSAS via Management Studio - Another key area (aren’t they all?). You can manage your SSAS cubes via SSMS. Storage settings, processing modes and so on can all be configured here. Plus, this is where you’ll run your MDX and DMX scripts. There’s even an option to directly migrate SQL Server 2000 cubes using a built-in wizard (yes, this is covered by the exam).

SSAS roles, permissions, and security - Access to cube data can be limited by creating roles and permissions. The control you can have over these is quite granular. Read up on the ways you can manage these permissions – it will definitely come in useful. It’s also worth knowing how security can be configured for ASP.NET access to cubes too.

Wizards - There are a number of wizards available in BIDS that make managing cubes a whole lot easier. There’s the Deployment Wizard, the Synchronize Database Wizard, and the Usage-based Optimization Wizard. Remember the old adage (not so old, I’ve just made it up): where there’s a wizard, there’s a question.

Data Mining - The questions on data mining in this exam are fairly minimal (there are a lot in 70-452). You need to know the DMX statements for creating and altering mining structures, and there are also a couple of questions about joins (these are different to SQL joins), mining models and attributes. It’s worth learning the mining models anyway – there are a lot of questions about these on the 70-452 exam.

Managing SSAS and SSAS Performance - The exam does ask some administration questions. These involve knowing about Dynamic Management Views (DMVs), performance counters, and using profiler to capture SSAS events.

Now let’s take a look at the last and, in my view, the easiest part of this exam – SQL Server Reporting Services (SSRS).

Report Controls  (Especially TABLIX) - SSRS features a variety of controls you can put on your reports – text boxes, charts, tables and so on. The exams generally concentrate on charts and what are called the TABLIX controls – the table, list, and matrix controls. You should also learn the various ways to show or hide report controls, depending upon whether data is available or not.

Filtering at different levels for reports - The exam is very keen on how reports can be filtered. You can choose to filter at the stored procedure/query level or directly within the report. Think about when you may need to use each scenario and learn how to implement them.

Managing Report Parameters - Report parameters allow you to pass configurable values to your report. There are a number of ways to set them up – so check them out!

Use of .NET in SSRS (embedded code and assemblies) - There are a couple of ways of using .NET code in reports. You can reference assemblies or embed code directly into the report itself. If you think about what the questions are asking you when they appear, you should be able to figure out the answer most of the time. The .NET code can be used in expressions. Which leads us on to…

SSRS Functions and Expressions - There are a lot of in-built functions in SSRS – the IIF statement, for example, which allows different actions to occur depending upon whether a particular statement is true or not. Take a look at the various functions – but don’t learn them all, you’ll be there for months!

One definite thing to learn – that will stand you in good stead both professionally and for the exam. Check out how the IIF statement can be used to alternate table row background colours in a report. You’ll thank me later.

Interactive Report Elements - Reports can be configured to support drillthrough (linking to another report), drilldown (creating a treeview of data in the report), and interactive sorting (clicking on column headers to sort the report). There are also different actions that can be configured when a particular value is clicked. These are useful tools and well worth knowing about.

Report wizard - BIDS contains a wizard that walks you through the process of creating a report. Very handy.

Report Builder (Data Source Views) - Report Builder is intended for use by business professionals to create their own reports (it looks like a Microsoft Office application). To do this, database administrators will create Data Source Views (DSV). A DSV exposes certain tables or views, allowing users to report on them. Download Report Builder and have a play with it, and especially try creating a DSV.

ReportViewer Control - The ReportViewer Control is a separate tool that allows SSRS reports to be displayed in ASP.NET and Windows applications. It has a couple of rendering and configuration options, and it’s worth tinkering with.

Report Manager & General Report Management - Report Manager is a Web interface, used to manage SSRS and published reports and data sources. You can create linked reports, manage permissions, create snapshots, subscriptions (static and data-driven), and configure caching too. There are a number of built-in roles, learn how these can be used.

SSRS configuration files - There are a number of configuration files used to manage the different aspects of SSRS. The main file to know about is RSReportServer.config, which is where e-mail details, rendering details and all the principal key information is stored. Know what each file does, and know what can be configured in RSReportServer.config.

SSRS Report Server database - The Report Server database stores report definitions, caches, snapshots, and a host of other information. You’ll see some questions on this on exam 70-452 too.

SSRS Exports and Rendering - You can export SSRS data to Excel, PDF, Word, and CSV amongst other formats. Now there are some limitations on exporting data, such as setting the correct width to support Word export and so forth. Investigate how to export and also what needs to be done to prevent rendering from failing in certain formats.

Creating and Publishing Reports - BIDS 2008 is used to create reports, and it’s also from where you publish reports to your SSRS server. It’s not hard, try it a couple of times and you’ll be able to answer the questions on this.

SSRS Utilities - SSRS includes a bunch of tools and utilities, including some command line utilities (rs.exe, rskeymgmt.exe, and rsconfig.exe). There’s also a graphical configuration tool, where administrative settings are configured. Find out what they do and how to use them!

It’s a big list, and the study guide presents you with even more areas to cover. But the more you learn for 70-448, the more you will already know for 70-452.

As Columbo used to say, one more thing. I’ve heard the questions can be mixed up on this exam; that is, you’ll have two SSRS questions, an SSAS question, an SSIS question, another SSAS question, and so on. In other words; the questions are random. My experience was different. I had a set of SSRS questions, followed by SSAS questions, followed by SSIS questions. As long as you study properly it won’t matter what order they come in.

The 70-452 Exam

70-452 is the “professional” level exam. It’s aimed at architects and designers, along with advanced developers. You will be awarded a MCITP certification after passing this exam – Microsoft Certified IT Professional. However, you must also pass the 70-448 exam to earn this certification.

The MCITP exams for SQL Server administrators and developers are both harder than their associated MCTS exams. I would not say that is the case here. I don’t want to say the exam is easy (it isn’t), but it does feel like more of the same, with some design questions thrown in. I’ve already mentioned the source control questions – there are quite a few questions in this exam that are generic to computing in general, as opposed to being BI-specific.

The areas covered here are:

• Designing and managing reports (20%)
• Designing Data Mining models (10%)
• Administering a BI solution (15%)
• Designing the BI architecture (21%)
• Designing and deploying SSIS packages (16%)
• Designing an Analysis Services database (18%)

You’ll note this list is more general than the topic list for 70-448. This exam does not concentrate on the specifics of SSIS, SSAS, or SSRS. The aim of this exam is to ensure you have a solid understanding of BI from a SQL Server perspective, including how it integrates with the entire company network. There’s also more focus on deploying systems as opposed to implementing them.

The big addition in this exam is Data Mining. You’ll hardly see this mentioned in exam 70-448 (there’s a couple of questions, no more), but there’s a fair number of questions asked in exam 70-452.

Here’s what you need to be on top of for exam 70-452 (it’s a much shorter list as we’ve covered most of the ground required via 70-448):

Data Mining - For me, this is the big difference from the 70-448 exam.The 70-448 exam asks some basic Data Mining questions, but for this exam you need to know about DMX (think of it as SQL for Data Mining), Data mining structures, algorithms, content types, dimensions, filters, and drill-throughs. You should also be aware of how to use SSIS to develop accuracy in your mining models. In short, you need to have a good understanding of what Data Mining is and how it works with SQL Server.

More SSIS - As you’d expect, more advanced SSIS topics are covered on this exam. Amongst the areas you should know about are Data Profiling (a very useful feature to know about), logging in SSIS packages, the multicast and conditional split tasks, and parallelism in SSIS packages.

More SSAS - Just as with SSIS and SSRS, the exam asks some advanced questions about SSAS. Things to gen up on here include using translations for multi-language cubes, keeping cubes up to date (and how frequently this should be done in certain scenarios), perspectives, and hierarchy models.

More SSRS - SSRS features heavily on this exam. Apart from more detailed knowledge about permissions (especially My Reports and Linked Reports), you’ll be asked plenty of questions about reporting performance, the SSRS Web service, and configuring dynamic data sources.

External Tools - BI data can be viewed and manipulated via other Microsoft applications, such as SharePoint, Excel, PerformancePoint, and ProClarity. The exam has questions on all of these – you should know the best way to create a dashboard from a report, and how to integrate reports with SharePoint, for instance.

Performance and Scaling Out - You’ll be asked questions about performance for all three BI elements, especially around improving performance. All of the scale out questions involve scenarios around clustering and load balancing, so you’ll need to know which configuration settings support these features.

Administration - Whereas the 70-448 exam generally involves development questions, 70-452 also includes a fair number of administration-based questions. Backing up cubes, partitioning cubes, moving SSRS between servers, upgrading SSRS from 2005 to 2008, and deployment scenarios and configurations for SSIS are all things to be aware of.

Slowly Changing Dimensions - A Slowly Changing Dimension (SCD) is used to keep track of data that may change over time – e.g. a salesperson moving from area to area. They allow you to configure how data is maintained to ensure historical data can be analysed, and there are a few ways SQL Server supports this. You can configure SCDs in cubes and also manage them via an SSIS task.

Change Data Capture (CDC) - CDC is a SQL Server 2008 feature that captures all changes made to particular database tables. It can be useful for tracking certain types of data and makes a couple of appearances on the exam.

General Computing Issues - Strangely, the exam asks some questions that could be related to any area of computing, not just BI. These are questions around supporting development teams working concurrently and how best to use source control. Common sense should see you answer these questions correctly. Maybe Microsoft ran out of questions…?

Summary

So there you go – a broad overview of what you need to know for the MCITP administrative exams.  I suggest printing out the study guide and ticking each item off on there as you cover it. Don’t take the exam until you feel ready.

Good luck! Look for SQL Server 2012 articles from me in the next 12 months…