Published: September 30, 2008
Languages: English
Audiences: IT professionals
Technology: Microsoft SQL Server 2008
Credit toward certification: MCP, MCSA

Skills measured
This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam. View video tutorials about the variety of question types on Microsoft exams.

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

Implementing an SSIS solution (15-20%)
Implement control flow
Checkpoints; debug control flow; transactions; implement the appropriate control flow task to solve a problem; data profiling and quality
Implement data flow
Debug data flow; implement the appropriate data flow components
Implement dynamic package behavior by using property expressions
Implement package logic by using variables
System variables; user variables; variable scope
Implement package configurations
Implement auditing, logging, and event handling
Use system variables for auditing; use event handlers; propagate events; use log providers; data profiling
Extend SSIS packages by using .NET code
Use the script task; use the script component; use custom assemblies

Preparation resources
SQL Server Integration Services
SQL Server Integration Services “How do I?” videos
Extending Integrations Services with the script task and Script Task Plus

Configuring, deploying, and maintaining SSIS (15-20%)
Install and maintain SSIS components
Implement disaster recovery for SSIS
Deploy an SSIS solution
Deploy SSIS packages by using DTUTIL; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations
Manage SSIS package execution
Schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; execute packages by using the SSIS .NET API
Configure SSIS security settings
MSDB database roles; package protection levels
Identify and resolve issues related to SSIS solution deployment
Validate deployed packages; deploy packages and dependencies between servers

Preparation resources
Considerations for installing Integration Services
Security and protection (Integration Services)
Troubleshooting (Integration Services)

Implementing an SSAS solution (20-25%)
Implement dimensions in a cube
Translations; attribute relations; hierarchies
Implement measures in a cube
Measure groups
Implement a data source view
Named calculations; named queries
Configure dimension usage in a cube
Implement reference dimensions; implement many to many relationships; implement fact relationships; implement role-playing relationships; define granularity
Implement custom logic in a cube by using MDX
Actions; key performance indicators (KPI); calculated members; calculations
Implement data mining
Implement data mining structures and models; query data mining structures by using DMX; data mining views
Implement storage design in a cube
Aggregations; partitions; storage modes; proactive caching

Preparation resources
Lesson 2: Defining and deploying a cube
Querying multidimensional data (Analysis Services – multidimensional data)
Tutorials: Designing and implementing data mining models

Configuring, deploying, and maintaining SSAS (15-20%)
Configure permissions and roles in SSAS
Server roles; SSAS database roles; cube roles; enable client application access; implement custom access to data
Deploy SSAS databases and objects
Deployment wizard; BIDS; SSMS; SSIS analysis services execute DDL task
Install and maintain an SSAS instance
Disaster recovery
Diagnose and resolve performance issues
Use SQL Profiler; performance monitor counters; DMVs; usage based optimization wizard Implement processing options
Implement processing options

Preparation resources
Granting server-wide administrative permissions
Deployment (Analysis Services – multidimensional data)
Operations (Analysis Services – multidimensional data)

Implementing an SSRS solution (10-15%)
Implement report data sources and datasets
Query types; dynamic data sources; filter location (dataset vs. query)
Implement a report layout
Apply conditional formatting; page configuration; headers and footers
Extend an SSRS solution by using code
Custom .NET assembly; private code
Create an SSRS report by using an SSAS data source
MDX in an SSRS report; DMX in an SSRS report
Implement report parameters
Databound parameters; multi-value parameters
Implement interactivity in a report
Drilldown; drillthrough; interactive sorting
Implement report items
Matrix; table; chart; image; list; grouping
Embed SSRS reports in custom applications
Use the Windows forms report viewer; use the web forms report viewer; use the SSRS web service

Preparation resources
How to: Create a dataset (Report Builder 2.0)
Tutorial: Adding parameters to a report
Designing the report layout (Report Builder 2.0)

Configuring, deploying, and maintaining SSRS (13%)
Configure report execution and delivery
Subscriptions; report caching; schedules; snapshot history
Install and configure SSRS instances
Deploy an SSRS web farm
Configure authentication and authorization for a reporting solution
Configure server-level and item-level role-based security; configure Windows authentication and custom authentication
Deploy an SSRS solution
RS.exe scripts; report builder; BIDS
Configure SSRS availability
Key management; migrate SSRS databases

Preparation resources
Deployment (Reporting Services)
High availability (Reporting Services)
Configuring authentication in Reporting Services

You maintain a SQL Server 2008 Analysis Services (SSAS) instance.
You plan to process a cube by using an XML for Analysis (XMLA) script.
You need to ensure that aggregations are populated.
What should you do?

A. Execute a processIndex command.
B. Execute a processUpdate command.
C. Execute a processStructure command.
D. Execute a processData command.

Answer: A


You maintain a SQL Server 2008 Analysis Services (SSAS) instance.
You plan to run the Usage-Based Optimization Wizard. You need to enable query logging.
What should you do?

A. Set the LogDir server property to a valid path.
B. Set the QueryLogSampling server property to 10.
C. Set the AllowedBrowsingFolders server property to include the folder from the LogDir setting.
D. Set the QueryLogConnectionString server property to a valid connection string.

Answer: D


You maintain a SQL Server Analysis Services (SSAS) database. The database is configured by
using multiple security roles.
The database is accessed by a Microsoft ASP.NET application that runs on a remote computer.
The application is configured to use Windows Authentication.
You need to ensure that the users of the application can successfully access the SSAS database.
You also need to ensure that security restrictions of the roles are applied.
What should you do?

A. Configure Kerberos authentication
B. Configure Analysis Services for HTTP authentication
C. Set the AnonymousConnectionsEnabled policy to True
D. Set the Security\RequireClientAuthentication property to True

Answer: A


You are maintaining a SQL Server 2008 Analysis Services (SSAS) solution in the production environment. You modify the solution to include two new measure groups in the development environment. You need to ensure that only one measure group is deployed to the cube in the production environment. What should you do?

A. Use the Deployment Wizard.
B. Use Microsoft SQL Server Management Studio (SSMS) to issue an XMLA command.
C. Use Microsoft SQL Server Management Studio (SSMS) to issue an UPDATE MEMBER command.
D. Use Business Intelligence Development Studio (BIDS) along with the Deploy only changes option set to True.

Answer: B


You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a dimension named Customer.
You need to configure the Dimension Data settings to meet the following requirements:
– Deny access to the {[Customer].[Country].&[Germany],[Customer].[Country].&[France]} set of attribute members.
– New members added to the attribute are visible by default. What should you do?

A. Add all the country members except those of France and Germany to the Allowed Set property.
B. Add the following set to the Denied Set property. {[Customer].[Country].&[Germany],[Customer].[Country].&[France]}
C. Add the following set to the Denied Set property. Except([Customer].[Country].[Country],{[Customer].[Country].&[Germany],[Customer].[Country].&[ France]})
D. Add the following set to the Allowed Set property. Extract({[Customer].[Country].&[Germany],[Customer].[Country].&[France]},[Customer].[Country])

Answer: B


Click here to view complete Q&A of 70-448 exam
Certkingdom Review

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft 70-448 Training at