Free Microsoft DA-100 Questions and Answers

Question 1

You are working with a sizable dataset that has more than a million rows. The table contains a Date datetime field.
Without sacrificing data access, the data model needs to be shrunk.
What ought you to do?

Accepted Answer

Split the Date column into two columns, one that contains only the time and another that contains only the date.

Answer

Storing datetime values can consume significant memory, especially with millions of rows, due to their complex data type. Splitting the 'Date' column into separate 'Date' and 'Time' columns reduces the cardinality and storage footprint of the original datetime column. This allows Power BI to optimize storage for two simpler data types while still providing full access to both date and time components for analysis, thereby shrinking the model without losing information.

Question 2

You use Power Query Editor to generate the next step.
Table.ReplaceValue(SalesLT_Address,""1318"",""1319"",Replacer.ReplaceText, ""AddressLine1""
21318 Lasalle Street is the value for a row in the AddressLine1 column.
What will the value be after applying the step?

Accepted Answer

21319 Lasalle Street

Answer

The `Table.ReplaceValue` function, when used with `Replacer.ReplaceText`, performs a text-based replacement. It searches for the exact string '1318' within the specified 'AddressLine1' column and replaces all occurrences with '1319'. In the value '21318 Lasalle Street', the substring '1318' is found and replaced, resulting in '21319 Lasalle Street'.

Question 3

You have a report from Microsoft Power BI. The PBIX file is 550 MB in size. By using an App workspace in a shared capacity on powerbi.com, one can see the report.
One fact table from an imported dataset is used in the report. There are 12 million rows in the fact table. At 8:00 and 17:00, the dataset will refresh twice daily.
The report is one page long and has 10 default visuals in addition to 15 AppSource visuals.
When users access and engage with the report, they complain that the visuals take a long time to load.
You must offer a suggestion for enhancing the report's functionality.
What ought to you suggest?

Accepted Answer

Change the imported dataset to DirectQuery.

Answer

The report's slowness, especially with a large imported dataset (12 million rows) and many visuals, suggests that the imported data model is causing performance bottlenecks during user interaction. Switching to DirectQuery means data is queried directly from the source database each time a visual is interacted with. This reduces the memory footprint in the Power BI Service and can significantly improve interactive performance if the source database is optimized for fast queries, as it avoids loading the entire dataset into memory.

Question 4

You use the Microsoft Power BI Service to generate a dashboard. A card visual on the dashboard displays the overall sales for the current year.
Using the Viewer role on the workspace, you may provide users access to the dashboard.
The user requests daily alerts for the number displayed on the card visual.
Notifications must be automated.
What ought you to do?

Accepted Answer

Create a subscription.

Answer

To provide automated daily alerts for a specific visual on a dashboard, creating a subscription is the appropriate method in Power BI Service. Subscriptions allow users to receive periodic snapshots of reports or dashboards via email. Data alerts are for specific threshold breaches, not for regular daily updates of a value, and sharing or tagging do not provide automated notifications.

Question 5

Using Microsoft Power BI, you are modeling data that includes a sizable Microsoft SQL Server table called Order with more than 100 million records.
You must import a portion of the Order table's data during the development process.
Solution: Import the table from Power Query Editor, then add a filter step to the query.
Is the objective being met?

Accepted Answer

No

Answer

Adding a filter step *after* importing the entire table in Power Query Editor means that all 100 million records are first pulled into Power Query's memory before the filter is applied. This approach does not import *a portion* of the data at the source level; it imports everything and then filters it. This is inefficient and can lead to performance issues or memory exhaustion during development, failing to meet the objective of importing only a portion.

Microsoft Certified Data Analyst Associate Practice Test

Microsoft Certified Data Analyst Associate Practice Test

Free Microsoft DA-100 Questions and Answers