Passed 70-446 BI Exam

 
After passing the required exams, I'm now a Microsoft Certified Technology Specialist in Microsoft SQL Server 2005 Business Intelligence (MCTS: SQL Server 2005 BI) which won't fit on my business cards but that is okay.

The last exam was brutally long (5 case studies, 9-10 questions per) and I will probably have nightmares tonight about Datum corp.

The funniest part was I got a perfect mark in the Data Mining category, and that was the one I was most concerned with. 

My studying strategy for this one was similar to 70-445, though I skimped on some of the research because of work commitments:

1. Get the outline from MS's site.
2. Export it to Excel and select the items I'm not familiar with.
3. Bring them into One Note.
4. Create tabs for each item to expand on.
5. Cross them out as I get the research.
6. Google each outline item with the category or "MSDN" or "Technet" to get more info.
7. Search Blogsearch & Technorati.
8. Search for the specific items I'm not familiar with using the same wording as the ms outline.
9. Read the books.  (The Kimball Data Warehousing book was excellent)

The exam took about 2 1/2 hours.  At the very end of the exam, after I found out my mark, the application hung and wouldn't print the score.  We spent about 10 minutes trying to get it to print, and I came home with 10 copies of my result.

Here's the outline of the exam.

1.    Select appropriate BI technologies.
2.    Specify the appropriate SQL Server edition.
3.    Design dimensional models.
4.    Design dimensions for each subject area.
5.    Design fact tables for each subject area.
6.    Identify current dimensions that can be reused.
7.    Identify elements that must be added to existing dimensions or fact tables.
8.    Design new physical objects based on a logical model.
9.    Design an indexing strategy.
10. Design a surrogate key strategy.
11. Identify appropriate business keys.
12. Design a partitioning strategy.
13. Identify design constraints.
14. Identify changed data in the source system.
15. Decide the strategy for decoding textual values.
16. Decide whether to implement fast load.
17. Design appropriate destination components to handle new and updated records during incremental loads.
18. Identify appropriate transformations and transformation options.
19. Design data flow.
20. Identify appropriate control flow items.
21. Design the control flow sequence.
22. Identify appropriate uses and placement of event handlers.
23. Identify appropriate uses and placement of checkpoints.
24. Identify appropriate uses of logging.
25. Identify appropriate uses of data flow error handling.
26. Select appropriate uses of shared data sources.
27. Select appropriate uses of stored procedures or user-defined functions.
28. Define appropriate security roles.
29. Specify folder security.
30. Specify field-level security.
31. Select appropriate uses of Report Designer.
32. Select appropriate uses of Report Definition Language (RDL).
33. Select appropriate uses of Report Builder.
34. Decide appropriate uses of datasets.
35. Decide appropriate uses of subreports.
36. Decide the appropriate placement of extensive business logic.
37. Identify appropriate uses of report snapshots.
38. Identify appropriate uses of on-demand reports.
39. Identify appropriate uses of on-demand-from-cache reports.
40. Identify appropriate uses of standard subscriptions.
41. Identify appropriate uses of data-driven subscriptions.
42. Identify appropriate report-delivery methods for subscriptions.
43. Identify appropriate uses of the Reporting Services Web Service library.
44. Identify appropriate uses of the Reporting Services Configuration tool.
45. Select appropriate uses of named queries.
46. Select appropriate uses of named calculations.
47. Select appropriate uses of denormalization strategies.
48. Identify appropriate uses of attribute relationships.
49. Identify appropriate uses of column binding to support a user-defined reporting hierarchy.
50. Select a design for implementing a ragged hierarchy.
z51. Select an appropriate strategy to implement member properties.
52. Identify appropriate uses of calculated members.
53. Identify appropriate uses of actions.
54. Identify appropriate uses of key performance indicators (KPIs).
55. Identify appropriate uses of perspectives.
56. Identify appropriate uses of translations.
57. Identify appropriate uses of drillthrough.
58. Identify a relationship type.
59. Identify appropriate uses of role-playing dimensions.
60. Choose an appropriate strategy to handle unknown dimension members.
61. Define appropriate security roles.
62. Design dimension security.
63. Design cell security by using Multidimensional Expressions (MDX).
64. Design a partitioning strategy for optimal data availability.
65. Decide whether proactive caching is an appropriate solution.
66. Design partition storage settings.
67. Select a dimension storage mode.
68. Identify appropriate algorithms to meet requirements.
69. Classify data as input, key, predict, and ignore.
70. Select appropriate uses of SSRS Data Mining Extensions (DMX) queries.
71. Select appropriate uses of ActiveX Data Objects (Multidimensional) (ADOMD).
72. Select appropriate uses of SSIS Data Mining Query tasks.
73. Select appropriate uses of data mining viewer controls for Microsoft .NET Framework-based applications.
74. Select appropriate uses of full processing.
75. Select appropriate uses of structure processing.
76. Select appropriate uses of default processing.

The exam focused heavily on Integration Services, data model & data mining exhibits, with quite a few questions around transactions and security.

My next exam will probably be the PerformancePoint beta, or maybe Sharepoint... or ... Oracle?

Source: MCTS: SQL Server 2005 Business Intelligence