Q1. - (Topic 2)
You need to configure the UserRegions role.
Which Multidimensional Expressions (MDX) function should you use?
A. ANCESTOR ( )
B. USERNAME ( )
C. FIRSTSIBLING ( )
D. LEAD ( )
E. COUSIN ( )
Answer: B
Q2. - (Topic 10)
You are restructuring an existing cube. One of the measures in the cube is Amount. The Sum aggregation function is used for the Amount measure. The cube includes a dimension named Account and the dimension's Type property is set to Accounts. The Account dimension includes an account type attribute.
You need to ensure that the Amount measure aggregates correctly according to the account type classification. Development effort must be minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Add the ByAccount attribute to the account dimension.
B. Use SQL Server Data Tools to change the AggregateFunction property value of the Amount measure to ByAccount.
C. Develop a .NET application that uses Analysis Management Objects (AMO) to change the existing AggregateFunction property value of the Amount measure to ByAccount and then use the application.
D. Develop a .NET application that uses Analysis Management Objects (AMO) to change the existing AggregateFunction property value of the Amount measure to FirstNonEmpty and then use the application.
Answer: B
Q3. HOTSPOT - (Topic 9)
...
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements: Include multiple properties in a configuration. Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats. You need to select the appropriate configuration.
Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.
Answer:
Q4. - (Topic 9)
A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is archived each day at midnight for regulatory compliance purposes. The shared datasets may continue to cache data that should not be used in reports. Shared report schedules are often paused during nightly server maintenance windows.
Reports must not return archived data.
You need to create a fully automated solution to ensure that reports do not deliver archived data.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Write a script that calls the flushcache method to clear individual items from the SSRS cache. Create a SQL Server Agent job that runs rs.exe with the script as an input file, and schedule the job to run every day after the archive process completes.
B. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to truncate the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the archive process completes.
C. Create a SQL Server Agent job that restarts the SQL Server Reporting Services service. Schedule the job to run every day after the archive process completes.
D. Create a shared schedule. Configure the datasets to expire on the shared schedule.
Answer: A
Q5. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
A model contains tables and columns that must not be visible to the user. The columns and tables cannot be removed because they are used in calculations.
You need to hide the tables and columns.
What should you do?
A. Right-click the applicable tables and columns and select the Hide option.
B. in the Properties window for the applicable tables and columns, set the Enabled property to False.
C. in the Properties window for the applicable tables and columns, set the Visible property to True.
D. Right-click the applicable tables and columns and select the Hide from Client Tools option.
Answer: D
Q6. - (Topic 10)
You are designing a SQL Server Reporting Services (SSRS) report for a bank. The bank has Automated Teller Machines (ATMs) in several regions. ATM operational data is stored in a SQL Azure database.
The report must use a map to display the location and status of the ATMs as shown in the following exhibit. (Click the Exhibit button.)
You need to ensure that the report displays only a user selected map region.
Which source of spatial data should you use for the map?
A. SQL Server spatial query
B. Bing Maps layer
C. ESRI shape file
D. Map gallery
Answer: A
Q7. DRAG DROP - (Topic 9)
You are administering a SQL Server Analysis Services (SSAS) database on a server. The database hosts a financial cube based on a SQL Azure database.
You need to grant read access to the financial cube for all users in the group USA\PowerUsers.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q8. - (Topic 6)
You need to create the factCustomerContact table.
Which type of table should you create?
A. A fact table with a non-additive measure
B. A factless fact table
C. A periodic snapshot fact table
D. A fact table with an additive measure
Answer: B
Q9. - (Topic 9)
You are redesigning a SQL Server Analysis Services (SSAS) database that contains a cube named Sales. Before the initial deployment of the cube, partition design was optimized for processing time. The cube currently includes five partitions named FactSalesl through FactSales5. Each partition contains from 1 million to 2 million rows.
The FactSales5 partition contains the current year's information. The other partitions contain information from prior years; one year per partition. Currently, no aggregations are defined on the partitions.
You remove fact rows that are more than five years old from the fact table in the data source and configure query logs on the SSAS server.
Several queries and reports are running very slowly.
You need to optimize the partition structure and design aggregations to improve query performance and minimize administrative overhead.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use the Aggregation Design Wizard to create aggregations for the current partitions.
B. Combine all the partitions into a single partition. Use the Usage-Based Optimization Wizard to create aggregations.
C. Combine all the partitions into a single partition. Use the Aggregation Design Wizard to create aggregations.
D. Use the Usage-Based Optimization Wizard to create aggregations for the current partitions.
Answer: D
Q10. - (Topic 9)
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
. The deployment model must support changing the content of connection strings by
using parameters at execution time.
. The deployment model must automatically log events to the SSISDB database.
. Maximize performance at execution time.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a project deployment model. Modify connection manager properties to use project parameters.
B. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
C. Use a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
D. Use a project deployment model. Modify connection manager properties to use package parameters.
Answer: A
Explanation:
Ref: http://technet.microsoft.com/en-us/library/hh213290.aspx