Everyone works differently, so the best use of your free time really depends on you, your working style, and what’s on your to-do list. But it’s handy to have a list like this in order to quickly find a way to put that little spare time to work instantly, without any thought. Use the following list as a way to spark ideas for what you can do in a short amount of time.
Source: 20 Productive Ways to Use Your Free Time - lifehack.org
After offering free delta courses Microsoft now offers a whole bunch of free E-Learning courses, which will enable you to pass the Exam 70-445 for the Microsoft Certified Technology Specialist (MCTS) SQL Server 2005 Business Intelligence.
You will find the complete Course Catalog at www.microsoftelearning.com
There are also some other resources for free at the moment at a subsite from Microsoft SQL Server 2005 – Learning Portal they called Dig Deep. Especially the free e-book Microsoft SQL Server 2005 Administrator's Pocket Consultant is a very useful resource and worth reading.
There is another bunch of free E-Learning courses for the Sharepoint Technology, which can be found here.
And as expected developers are not left alone and there are also free E-Learning courses and a Dig Deep available for Visual Studio 2005 and .Net 3.0.
Self Test Practice Test is the tool you need to pass the test. Our realistic questions and detailed explanations help you figure out what you know and gives you the answers to what you don't. With the flexibility to use our test in both Learning or Certification mode, you'll have everything you need to get the job done right.
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
