Peter Koller's BI blog - Exams 70-445 & 70-446

 

70-445 TS: Microsoft SQL Server 2005 Business Intelligence - Implementation and Maintenance

General

• A lot of arranging tasks in the right order to achieve some goal (like adding a user group with specific rights to SSRS)
• Other than that, standard multiple choice questions and some "select all that apply" variants
• No cases, only specific questions

Topics Remembered (I have goldfish memory)

Reporting Services

• Formatting report items
• Handling reporting services security
• Subscriptions (especially data driven ones)
• Linked Reports
• Drilldown reports both for matrix and table items
• Deployment (deploy to a remote server, deployment manifest etc.)
• Groupings and subtotals (ie. sum())
• Filters
• Setting up parameters in various ways
• Moving a RS installation (including encryption keys. ouch)
• Report Models for Report Builder (I think I only got one question on Report Builder and that was it)
• External code (i got a question on the syntax of calling a function in an assembly)

SSIS

• DTUtil.exe and its various parameters
• Transactions in SSIS (surprisingly many questions on this)
• Precedence constraints (nothing fancy, just AND-OR stuff)
• Generally handle the flow in the dataflow to achieve some objective
• Setting up logging (what events etc.)
• Package configurations
• Running packages through SQL Server agent (how to set values in configurations etc)
• How to configure package checkpoints (a couple of questions on this, pretty detailed ie. what properties to set where etc.)
• Debugging (Where / how to set up various viewers)

SSAS

• Some simple MDX
• Some (probably) simple DMX
• Fact / Dimension relationships (Many-to-Many, Reference etc.)
• Attribute relationships (Defining them, troubleshooting errenous relationships)
• MDX definitions of the various components of KPIs (ie set up trend indicator by using PrevPeriod etc)
• Various properties of dimension attributes such as HIdeMemberIf etc.
• Datasource Views, named quieries and computed columns
• Data Security (Cell and dimension)
• Security (Setting up various roles, integrating with AD etc.)
• Processing options for the UDM
• Processing options for Data Mining
• Installation issues (clustering, instances)
• Parent-Child hierarchies
• Logging (Where, what, how)
• Generating SSAS metadata for backup and recovery
• Storage modes (MOLAP, HOLAP, ROLAP) in various scenarios
• Aggregation design
• Actions (I got a question on selecting where to define the action)
• Creating calculated members (oddly, I only got one question on this)
• All kinds of data mining stuff (I need to read up on this, I was pretty blank)

Database Engine topics

• Mostly locking issues and the avoidance thereof
• Partitioning (how to set up)

PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

Unfortunatly I am quite a bit more scetchy on this one, it was the first one I took today.

General

• All questions are related to cases (like in the other SQL Server 2005 PRO exams). I feel the cases were a bit more complicated than in the other exams for SQL Server 2005.
• No simulations, only multiple choice and "choose all that apply"

Topics Remembered

General datawarehouse / mart design

• Designing various permutations of star-schemas (ie. add a dimension table and relate it to the fact table etc.)
• When to use surrogate keys (Apparantly Microsofts answer is to only use surrogate keys in SCD2 scenarios)
• Partitioning of load tables
• Staging areas and how they can be useful
• SCD design in dimension tables

SSAS design

• A lot of choose between various ways to retrieve data to the DSV type questions (views, named queries etc.)
• Unknown dmiension member handling both when processing and in dimension design
• Setting up roles to restrict data access to users in various ways
• Lazy Aggregations property
• Choose between various storage modes / proactive caching alternatives to achieve some goal
• All kinds of datamining topics that i more or less guessed on

SSRS design

• Various ways to create RDL (BIDS, Report Builder, Custom application)
• Choose betwwen various ways to handle drilldown (subreports, hiding groups etc.)
• Report snapshots
• Choose where to put code (in-line or external assembly)
• Choose between various maintenance strategies

SSIS design

• Quite a few SCD2 scenarios
• Functionality of Merge, Merge Join, Left Join components
• Data flow error handling (set up a lookup transformation not fail on no match errors etc.)

All in all, the preparation guides from Microsoft for 70-445 and 70-446 do a good job at describing the topics covered in the exams. Reading about each topic in BOL should provide you with sufficient knowledge to pass both exams if you have some experience with implementing BI solutions on SQL Server 2005 and some basic datawarehousing theory background.

Source: Peter Koller's BI blog