Developing SQL Data Models (70-768) Free Practice Test

Question 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You deploy a tabular data model to an instance of Microsoft SQL Server Analysis Services (SSAS). The model uses an in-memory cache to store and query data. The data set is already the same size as the available RAM on the server. Data volumes are likely to continue to increase rapidly.
Your data model contains multiple calculated tables.
The data model must begin processing each day at 2:00 and processing should be complete by
4:00 the same day. You observe that the data processing operation often does not complete before 7:00. This is adversely affecting team members.
You need to improve the performance.
Solution: Change the storage mode for the data model to DirectQuery.
Does the solution meet the goal?

A. No

B. Yes

Correct Answer: B

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

Question 2

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 3

You are managing a SQL Server Analysis Services (SSAS) tabular database.
The database must meet the following requirements:
- The processing must load data into partitions or tables.
- The processing must not rebuild hierarchies or relationships.
- The processing must not recalculate calculated columns.
You need to implement a processing strategy for the database to meet the requirements.
Which processing mode should you use?

A. Process Default

B. Process Clear

C. Process Add

D. Process Data

E. Process Full

Correct Answer: C

Question 4

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 new Network Load Balanced (NLB) cluster.

B. Create a new local SSAS partition.

C. Create a second instance of SSAS on the same server and dedicate this instance to processing queries.

D. Create a second instance of SSAS on a different server and dedicate this instance to processing queries.

E. Create a new remote SSAS partition.

Correct Answer: A,D,E

Question 5

What property do you set to hide the phone number attribute in the customer dimension from slicing and dicing operations, but allows it to be included in the dimension as a member property?

A. IsAggregatable

B. AttributeHierarchyVisible

C. AttributeHierarchyEnabled

D. AttributeHierarchyOrdered

Correct Answer: C

Question 6

How can you enhance the model to support analysis by date?

A. Create a date table in a new SQL Server database, and import it into the model by using the Table Import Wizard.

B. Create calculated columns in the Internet Sales and Direct Sales tables to derive year, quarter, and month from the InvoiceDate columns.

C. Create a calculated column in the Internet Sales and Direct Sales tables using the CALENDAR function on the InvoiceDate column.

D. Create a calculated table using the CALENDARAUTO function and add calculated columns to the calculated table to derive year, quarter, and month from the InvoiceDate columns.

Correct Answer: A

Question 7

You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Sales Persons and Territories defines a relationship between a salesperson's name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You need to use the least amount of development effort to achieve this goal.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Create a separate tabular model for each sales territory and assign each tabular model a corresponding sales territory name.
Grant each salesperson access to the corresponding tabular model of the assigned sales territory.

B. Add the Sales Persons and Territories table to the model, define the relationships, and then implement dynamic security by using row filters.
Grant each salesperson access to the model.

C. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member.
Then create a new role named Sales Persons with Read permission.
Add the group as a member to the new role.

D. Create a new role named Sales Persons with Read permission.
Add each salesperson's logon as a member to the role.

Correct Answer: B

Welcome to TestSimulate

Microsoft Developing SQL Data Models (70-768) Free Practice Test