Exam 70-767 topic 1 question 48 discussion

Actual exam question from Microsoft's 70-767

Question #: 48
Topic #: 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 that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server Analysis Services (SSAS) instance. The data warehouse includes the Fact.Order table as shown in the following table definition. The table has no indexes.

You must minimize the amount of space that indexes for the Fact.Order table consume. You run the following queries frequently. Both queries must be able to use a columnstore index:

You need to ensure that the queries complete as quickly as possible.
Solution: You create a measure for the Fact.Order table.
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
You should use a columnstore index.
Columnstore indexes are the standard for storing and querying large data warehousing fact tables. This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage.
References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017

by tubis at June 18, 2020, 11:52 a.m.

Comments

Submit Cancel

Cococo

4 years, 9 months ago

48, 49 and 50 are part of one subset and 50 is the right one

upvoted 1 times

...

solopub

4 years, 10 months ago

This is a tricky question

upvoted 1 times

...

tubis

4 years, 12 months ago

Why B? the question didn't mention that the table does not have column store index. And we add measures to improve the sum operation performance. So I think the answer is A

upvoted 1 times

anon456

4 years, 10 months ago

The question mentions this though "Both queries must be able to use a columnstore index" and it also says "the table has no indexes" so in my understanding that's why creating a measure isn't a sufficient solution and a columnstore index has to be created.

upvoted 7 times

...