070-768 Exam Training Practice Tests (Microsoft Developing SQL Data Models)]

Question 1

Hotspot Question
A database named DB2 uses the InMemory query mode. Users frequently run the following query:

You need to configure SQL Server Profiler to determine why the query is performing poorly.
Which three event should you monitor on the SQL Server Profiler trace events configuration page? To answer, select the appropriate options in the answer area.

Correct Answer:

Explanation:
By using SQL Profiler, you can intercept two classes of trace events from Analysis Services, DAX Query Plan and DirectQuery events, both generated by the DirectQuery engine. Here, in this scenario we have a DAX Query.
DAX Query Plan events are generated by the DAX formula. By using the In-Memory mode, you store a copy of data in the xVelocity (VertiPaq) storage engine.
Figure: This is how a query is executed by using In-Memory mode.

Question 2

You are developing a SQL Server Analysis Services (SSAS) tabular database.
To maximize performance, queries must be resolved only by using cache.
You need to configure the appropriate query mode.
Which query mode should you select?

A. In-Memory B. DirectQuery with In-Memory C. In-Memory with DirectQuery D. DirectQuery

Correct Answer: A

Question 3

You are developing a SQL Server Analysis Services (SSAS) tabular project.
A model defines a measure named Profit and includes a table named Date.
The table includes year, semester, quarter, month, and date columns.
The Date column is of data type Date.
The table contains a set of contiguous dates.
You need to create a measure to report on year-over-year growth of profit.
What should you do? (Each answer presents a complete solution. Choose all that apply.)

A. Define the following calculation. Year Over Year Profit Growth:=CALCULATE([Profit], DATEADD('Date'[Date], 1, YEAR)) B. Use the Business Intelligence Wizard and then use the Define time intelligence enhancement. C. Define the following calculation. Year Over Year Profit Growth:=[Profit] - CALCULATE([Profit], SAMEPERIODLASTYEAR('Date'[Date])) D. Define the following calculation. Year Over Year Profit Growth:=[Profit] - CALCULATE([Profit], PARALLELPERIOD('Date'[Date], -12, MONTH))

Correct Answer: C,D

Question 4

Drag and Drop Question
You are writing a MDX query to retrieve data from a Microsoft SQL Server Analysis Services (SSAS) cube named Channel Sales. The cube defines two measures named Sales and Cost.
The cube also defines a Date dimension and a Product dimension.
You need to retrieve profit values for a year named CY2016.
How should you complete the MDX statement? To answer, drag the appropriate MDX segment to the correct locations. Each MDX segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

Explanation:
Box 1:WITH MEMBER...
Box 2:WHERE ([Date].[Year].[CY2016])
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/mdx/working- with-members-tuples-and-sets-mdx

Question 5

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You administer a Microsoft SQL Server Analysis Services (SSAS) tabular model for a travel agency that specializes in vacation packages. Vacation bookings and packages are stored in a SQL Server database. You use the model as the basis for customer emails that highlight vacation packages that are currently underbooked, or projected to be underbooked.
The company plans to incorporate cruise ship vacation packages. Cruise ship vacation packages include new features such as region availability and cruise line specialties that require changes to the tabular model.
You must ensure that the tabular model reflects the new vacation packages.
You need to configure the tabular data model.
What should you do?

A. Process the model in Process Defrag mode. B. Ensure that the Transactional Deployment property is set to True. C. Ensure that DirectQuery is enabled for the model. D. Ensure that DirectQuery is disabled for the model. E. Process the model in Process Full mode. F. Process the model in Process Data mode. G. Ensure that the Transactional Deployment property is set to False.

Correct Answer: E

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 6

You manage an instance of SQL Server Analysis Services (SSAS). Over the last several months, the usage on the server increased by 50% as users begin to create and run more analyses.
Queries are taking longer to run, and processor usage is consistently above 80%.
You predict that query usage will continue to increase at 5% per month. You need to scale SSAS to accommodate the present and future processing load.
Which three actions could you take?
Each correct answer presents a complete solution.

A. Create a second instance of SSAS on the same server and dedicate this instance to processing queries. B. Create a new Network Load Balanced (NLB) cluster. C. Create a second instance of SSAS on a different server and dedicate this instance to processing queries. D. Create a new local SSAS partition. E. Create a new remote SSAS partition.

Correct Answer: B,C,E

Question 7

You are designing a SQL Server Analysis Services (SSAS) cube.You need to create a measure to count unique customers.What should you do?

A. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension. B. Create several dimensions. Add each dimension to the cube. C. Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group. D. Create a dimension. Then add a cube dimension and link it several times to the measure group.

Correct Answer: C

Microsoft Developing SQL Data Models - 070-768 Exam Practice Test