Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. HOTSPOT - (Topic 9)
You are designing aggregations for a SQL Server Analysis Services (SSAS) cube. You need to ensure that every aggregation excludes the Category attribute. Which option should you select?
To answer, select the appropriate option in the answer area.
Answer:
Q2. DRAG DROP - (Topic 9)
Your network contains a development environment, a staging environment, and a production environment.
You have a SQL Server Integration Services (SSIS) project. All of the packages in the project load data from files in a shared network folder. The packages use indirect XML configurations to set the location of the network folder.
The project is deployed to the three environments. Each environment has a different set of source files and a different network folder for the source files.
Currently, if an environment variable is missing, the package will use the network folder specified in the package, not the folder specified in the XML configuration file.
You need to ensure that each time a package is executed, the network folder location specified in the package is NOT used.
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:
Q3. - (Topic 3)
You need to create the calculation for SalespersonName.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Create a computed column in the data warehouse's DimSalesperson table. Include the column in the Sales Reporting model's Salesperson table.
B. Modify the data warehouse's DimSalesperson table and add a new column. Use an UPDATE statement to populate the new column with values. Update the SSIS package developed to populate the data warehouse's DimSalesperson table to use a Derived Column transformation to produce the calculation.
C. Configure the Sales Reporting model's Salesperson table properties to be based on a query. Define a derived column in the query.
D. Add a calculated column to the Sales Reporting model's Salesperson table by using the Data Analysis Expressions (DAX) language CONCATENATE function.
E. Create a view in the data warehouse that defines a derived column based on the DimSalesperson table. Base the Sales Reporting model's Salesperson table on the view. Include the column in the Sales Reporting model's Salesperson table.
F. Add a calculated column to the Sales Reporting model's Salesperson table by using the Data Analysis Expressions (DAX) language ADDCOLUMNS function.
Answer: B,E
Q4. - (Topic 9)
You have a SQL Server Reporting Services (SSRS) 2008 instance integrated with an Internet-facing Microsoft SharePoint 2010 farm. PerformancePoint Services and Excel Services are installed and configured.
You need to upgrade to SSRS 2012 with the minimum downtime and hardware requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Migrate SSRS content to a separate instance of SSRS 2012 on the same server.
B. Perform an in-place upgrade of the SSRS environment.
C. Migrate SSRS content to new servers that run SSRS 2012.
D. Install SSRS 2012 on a new SharePoint farm and migrate content to the new farm.
Answer: C
Q5. DRAG DROP - (Topic 9)
Several reports are based on the same SQL Server Analysis Services (SSAS) cube. Each report has several datasets defined with complex Multidimensional Expressions (MDX) queries. The company maintains separate development, test and production environments.
The reports are running slowly. You plan to analyze report performance. You have the following requirements:
. Monitor query statistics on the production server with as little server overhead as possible.
. Gather, replay, and analyze statistics on the test server with as little administrative
effort as possible.
. Identify the longest-running queries on both servers.
. Document statistics on disk reads on both servers.
You need to gather statistics and meet the requirements.
Which features should you use?
To answer, drag the appropriate feature or features to the correct location or locations in the answer area. (Use only features that apply.)
Answer:
Q6. - (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
Q7. - (Topic 1)
You need to deploy the StandardReports project.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Deploy the project from SQL Server Data Tools (SSDT).
B. Use the Analysis Services Deployment utility to create an XMLA deployment script.
C. Use the Analysis Services Deployment wizard to create an MDX deployment script.
D. Use the Analysis Services Deployment wizard to create an XMLA deployment script.
Answer: A,D
Explanation: There are several methods you can use to deploy a tabular model project. Most of the deployment methods that can be used for other Analysis Services projects, such as multidimensional, can also be used to deploy tabular model projects.
A: Deploy command in SQL Server Data Tools
The Deploy command provides a simple and intuitive method to deploy a tabular model
project from the SQL Server Data Tools authoring environment.
Caution:
This method should not be used to deploy to production servers. Using this method can
overwrite certain properties in an existing model.
D: The Analysis Services Deployment Wizard uses the XML output files generated from a
Microsoft SQL Server Analysis Services project as input files. These input files are easily
modifiable to customize the deployment of an Analysis Services project. The generated
deployment script can then either be immediately run or saved for later deployment.
Incorrect: not B: The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT).
Q8. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube for the sales department at your company.
The sales department requires the following set of metrics:
Unique count of customers
Unique count of products sold
Sum of sales
You need to ensure that the cube meets the requirements while optimizing query response time.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
A. Use the Define Semiadditive Behavior page to enable semi-additive behavior.
B. Use ROLAP storage for all partitions.
C. Use the Define Semiadditive Behavior page to disable semi-additive behavior.
D. Use the Distinct Count and Sum measure aggregation functions.
E. Place the distinct count measures in separate measure groups.
F. Create a single measure group containing all measures
Answer: A,E
Explanation: A: Semiadditive Function Select the aggregation function for the selected measure. The aggregate functions available include DistinctCount, Aggregated using the DistinctCount function. E (not D, Not F): Typically, the best performance occurs when each distinct count measure is in its own measure group, and that measure group has the same dimensionality as the initial measure group.
Q9. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional project. The project file includes two cubes named Finance and Operations. The project also includes a dimension named Date. The Date dimension includes two hierarchies named Fiscal and Calendar. The Date dimension has been added to both cubes.
You need to disable the Fiscal hierarchy in the Operations cube without impacting other database objects.
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:
Q10. - (Topic 9)
You have two servers named Served and Server2. Both servers have SQL Server 2012 installed. Both servers have a SQL Server Integration Services (SSIS) catalog.
You are preparing a disaster recovery plan for a business intelligence (BI) solution.
You need to ensure that the SSIS catalog from Server1 can be restored to Server2.
Which two actions should you perform on Server1? Each correct answer presents part of the solution.
A. Back up the master database.
B. Run sp.configure 'clr enabled', 0.
C. Back up the master key of the SSISDB database.
D. Back up the master key of the master database.
E. Back up the SSISDB database.
F. Run sp_configure 'clr enabled', 1.
Answer: C,E
Q11. - (Topic 8)
You need to implement the SalesCommission model to support the planned reports and queries.
What should you do?
A. Create a date table that contains only one row for each date on which a sale is recorded.
B. Use the existing transaction date column in the sales table for date calculations.
C. Create a date table that contains a row for every date since data started being recorded.
D. Create a new calculated date column in the sales table for date calculations.
Answer: C
Q12. - (Topic 2)
You need to configure per-user security authentication for reporting against the Sales cube.
What should you do? (Each correct answer presents part of the complete solution. Choose all that apply.)
A. Create Service Principal Names (SPNs).
B. Configure account delegation.
C. Enable forms-based authentication.
D. Enable mixed-mode authentication.
Answer: A,D
Explanation: * From scenario:
/ Administrative staff from around the world will produce sales reports with Microsoft Excel
2010 based on the Sales cube.
/ Security requirements
When possible, the system must use Windows authentication for all database connections.
The system must prevent users from querying data from outside of their region.
The system must allow certain users to query data from multiple regions.
A: To use Kerberos authentication with SQL Server requires both the following conditions to be true: The client and server computers must be part of the same Windows domain, or in trusted domains. A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.
D: For windows authentication we need to enable mixed-mode authentication
Q13. - (Topic 10)
You are planning to develop a SQL Server Analysis Services (SSAS) tabular project. The
project will be deployed to a SSAS server that has 16 GB of RAM.
The project will source data from a SQL Server 2012 database that contains a fact table named Sales. The fact table has more than 60 billion rows of data.
You need to select an appropriate design to maximize query performance.
Which data access strategy should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Configure the database to use DirectQuery mode. Create a columnstore index on all the columns of the fact table.
B. Configure the database to use In-Memory mode. Create a clustered index which includes all of the foreign key columns of the fact table.
C. Configure the database to use In-Memory mode. Create a columnstore index on all the columns of the fact table.
D. Configure the database to use DirectQuery mode. Create a clustered index which includes all of the foreign key columns of the fact table.
Answer: A
Q14. - (Topic 10)
You are managing a SQL Server Reporting Services (SSRS) instance in native mode. A role named Folder Access Controller is present on the server.
The Folder Access Controller role consists of only the Set security for individual items task. When role members open Report Manager, they cannot view folders.
You need to modify the Folder Access Controller role so that the role members can view folders.
Which task should you add to the Folder Access Controller role?
A. Manage data sources
B. Manage folders
C. View models
D. View reports
Answer: B
Q15. - (Topic 6)
You need to configure permissions for the Customers dimension.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. In SQL Server Management Studio, configure the BusinessUsers role to disallow the reading of all definitions.
B. In SQL Server Data Tools, configure the BusinessUsers role to disallow the reading of the Customers dimension definition.
C. In SQL Server Management Studio, deny the member set for the Customers dimension data by using the Multidimensional Expressions (MDX) expression Filter([BusinessUsers]).
D. In SQL Server Management Studio, configure the BusinessUsers role to disallow the reading of the Customers dimension definition.
Answer: B,D
66. - (Topic 6)
You need to implement the date dimension in the Operations database.
What should you do?
A. Create three database dimensions. Add each database dimension as a cube dimension by setting the Referenced relationship type.
B. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Regular relationship type for each cube dimension.
C. Create three database dimensions. Add each database dimension as a cube dimension by setting the Regular relationship type.
D. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Referenced relationship type for each cube dimension.
Q16. - (Topic 3)
You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort.
What should you do?
A. Use the PowerPivot Configuration Tool and select the Upgrade Features, Services, Applications and Solutions option.
B. Use the PowerPivot Configuration Tool and select the Configure or Repair PowerPivot for SharePoint option.
C. Reinstall SSAS in PowerPivot for SharePoint mode by using the SQL Server 2012 installation media.
D. In SharePoint Central Administration, create a target application and configure the
PowerPivot service application settings to use the target application.
Answer: B