Q1. - (Topic 10)
You are developing a BI Semantic Model (BISM) that retrieves data from several sources. These sources include a SQL Azure database and an OData data feed that includes rainfall data for towns on the east coast of Australia. The model will be deployed to a server with significantly more memory than the total size of the source data.
You have the data feed URL, which you will use when developing the model in SQL Server Data Tools (SSDT). The model must meet the following requirements:
. Performance must be maximized.
. Data latency of up to one month is acceptable.
You need to choose a project type and a data access mode to meet the requirements.
What should you do?
A. In SSDT, select the multidimensional project type and use the MOLAP storage mode.
B. In SSDT, select the multidimensional project type and use the ROLAP storage mode.
C. In SSDT, select the tabular project type and use the DirectQuery query mode.
D. In SSDT, select the tabular project type and use the In-Memory query mode.
Answer: D
Q2. HOTSPOT - (Topic 9)
A school stores information about teachers, students, classes, and enrollments in a Microsoft Azure SQL Database database. The database includes a table that maps the user IDs of teachers to the subjects they teach.
Teachers access reports in a SQL Server Reporting Services (SSRS) instance by using their credentials.
You are developing a report that displays a table of class enrollments for a specific subject. The report will prompt teachers to select from their mapped subjects. The table is based on a dataset named ClassData. To minimize report execution time, the ClassData dataset has been configured to cache all class enrollment data.
You need to ensure that the report displays the correct class enrollment data.
Which item should you configure?
To answer, select the appropriate item in the answer area.
Answer:
Q3. DRAG DROP - (Topic 9)
You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database.
The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
. The package must maximize parallel processing of the T-SQL statements. . After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package.
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:
Q4. HOTSPOT - (Topic 10)
You create a new report in SQL Server Data Tools (SSDT). The report queries a Windows Azure SQL Database database table.
In a report table showing sales by countries and cities, you need to enable users to hide or show cities by clicking the name of a country.
Which property page of the city text box should you use? (To answer, select the appropriate tab in the answer area.)
Answer:
281. - (Topic 10)
You manage a SQL Server Reporting Services (SSRS) instance. The ReportingServicesService.exe.config file has been modified to enable logging. Some users report that they cannot access the server.
You need to ascertain the IP addresses of the client computers that are accessing the server.
What should you do?
A. View the Report Server service trace log.
B. View the ExecutionLog view in the Report Server database.
C. View the Report Server HTTP log.
D. View the Windows System event log.
Q5. - (Topic 3)
You need to configure data refresh for the Manufacturing Performance PowerPivot workbook.
What should you do? (Each correct answer presents part of the solution. Choose ail that apply.)
A. Configure the PowerPivot Data Refresh Timer Job to run every 60 minutes.
B. Restore the PowerPivot workbook to an SSAS instance in tabular mode.
C. Script a process command and configure a SQL Server Agent job to execute the command every 60 minutes.
D. Restore the PowerPivot workbook to an SSAS instance in PowerPivot for SharePoint mode.
Answer: A
Q6. - (Topic 9)
You are designing a complex report that displays sales metrics for a customer hierarchy. The customer hierarchy has six levels and contains approximately 1 million members.
You have the following requirements:
Allow users to easily display and navigate data.
Minimize report processing time.
Simplify the report design and maintenance processes.
You need to design a report that meets the requirements.
How should you design the report? (More than one answer choice may achieve the goal. Select the BEST answer.)
...
A. Retrieve all customers and use drilldown groupings to show or hide hierarchy levels.
B. Add a report part for each hierarchy level.
C. Add a subreport for each hierarchy level.
D. Display only the children of the current hierarchy level and drill through to the selected member.
Answer: D
Q7. - (Topic 6)
You need to improve the performance of data warehouse queries.
What should you do?
A. Create columnstore indexes.
B. Create clustered indexes.
C. Create indexed views.
D. Create bitmap indexes.
Answer: A
Q8. - (Topic 10)
You maintain a multidimensional Business Intelligence Semantic Model (BISM) that was developed with default settings.
The model has one cube and the cube has one measure group. The measure group is based on a very large fact table and is partitioned by month. The fact table is incrementally loaded each day with approximately 800,000 new rows.
You need to ensure that all rows are available in the cube while minimizing the processing time.
Which processing option should you use?
A. Process Add
B. Process Clear
C. Process Default
D. Process Index
E. Process Data
F. Process Full
Answer: A
Q9. HOTSPOT - (Topic 9)
A company maintains separate environments for test, production, and quality assurance. The company uses the project deployment model for SQL Server Integration Services (SSIS) deployments.
You create an SSIS project to perform a weekly refresh of the company's data warehouse and cubes. The project has three packages.
You need to configure the project to ensure so that the test and production teams can run each package without manually adding server-specific information.
Which program should you use?
To answer, select the appropriate program in the answer area.
Answer:
156. - (Topic 9)
...
You are designing a self-service reporting solution based on published PowerPivot workbooks.
The reporting solution must allow users to perform the following tasks:
Easily create reports.
Create report queries by dragging and dropping fields.
Create presentation-quality reports with minimal effort.
You need to choose a reporting tool that meets the requirements.
Which reporting tool should you choose? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Report Builder
B. Report Designer
C. Power View
D. Microsoft Excel
Q10. - (Topic 9)
You are creating a calculated measure in a sales cube by using a Multidimensional Expressions (MDX) calculation. The cube includes a date dimension. The date dimension has a hierarchy named Calendar that includes Calendar Year, Month, and Day attributes.
The calculated measure must calculate the year-to-date sales for the current slicer. The MDX calculation must work for as many hierarchy levels as possible.
You need to create a calculated measure for the cube that meets the requirements.
Which expression should you use in the calculation designer? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B