Exam 70-779 topic 1 question 77 discussion

Actual exam question from Microsoft's 70-779

Question #: 77
Topic #: 1

You have a table in a Microsoft SQL Server database that has more than 5 columns. A sample of the data and some of the columns are shown in the following table.

The table contains more than two million rows. You have 100 clients and 10 products.
You need to load the data to Excel. The solution must minimize the amount of memory used by the model.
What should you do?

A. Load the data to the data model as three tables named Clients, Orders, and Products. Ensure that each table has only the relevant columns. Remove duplicate rows from Clients and Products.
B. Move the database to a Microsoft Azure SQL database. Load the table to the data model.
C. Load the data to one worksheet.
D. Load the data to three worksheets named Clients, Orders, and Products. Ensure that each worksheet has only the relevant columns. Remove duplicate rows from Clients and Products.

Show Suggested Answer

Suggested Answer: A 🗳️
Reference:
https://support.office.com/en-us/article/create-a-memory-efficient-data-model-using-excel-and-the-power-pivot-add-in-951c73a9-21c4-46ab-9f5e-14a2833b6a70

by trantrongw at Nov. 29, 2019, 6:59 a.m.

Comments

Submit Cancel

trantrongw

Highly Voted 5 years, 8 months ago

Yep A, I tesed

upvoted 13 times

nose

5 years, 5 months ago

How did you exactly test this? Why not option D? Is exactly the same thing as option A but into a worksheet... the question said to load the data into Excel, it does not mention into a data model or worksheet, just excel.... So, could both options be correct?

upvoted 1 times

M1ch3ll3

5 years, 5 months ago

Wouldn't option D hit the row limit in worksheets?

upvoted 8 times

Wyman

5 years, 2 months ago

Agreed answer cannot be D, the key of this question is 2 million rows where worksheet cannot handle that, while data model can load 1,999,999,997 rows

upvoted 3 times

...

hfgirona

4 years, 8 months ago

because you have more than 2 million rows?

upvoted 1 times

...

r8d1

5 years ago

I see the solution in that only A addresses the 1,000,000 row limit by removing rows and splitting the table.

upvoted 1 times

...

Shanice

Most Recent 5 years ago

The answer is A. The reason the answer cannot be D is found in this reference https://www.sqlchick.com/entries/2013/9/7/power-query-datashould-it-be-loaded-to-the-worksheet.html.

upvoted 2 times

...

daxy2020

5 years, 3 months ago

What is the maximum number of rows in Excel worksheet? Answer: 1,048,576 rows

upvoted 4 times

...