Free Microsoft DA-100 Questions and Answers — 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?
- Change the data type of the Date column to Text.
- Trim the Date column.
- Split the Date column into two columns, one that contains only the time and another that contains only the date. (Correct answer)
- Round the hour of the Date column to startOfHour.
Correct answer: Split the Date column into two columns, one that contains only the time and another that contains only the date.
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?
- 1319
- 21318 Lasalle Street
- 21319 Lasalle Street (Correct answer)
- 1318
Correct answer: 21319 Lasalle Street
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?
- Change the imported dataset to DirectQuery. (Correct answer)
- Implement row-level security (RLS).
- Increase the number of times that the dataset is refreshed.
- Split the visuals onto multiple pages.
Correct answer: Change the imported dataset to DirectQuery.
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?
- Tag the user in a comment.
- Create a data alert.
- Share the dashboard to the user.
- Create a subscription. (Correct answer)
Correct answer: Create a subscription.
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?
- Yes
- No (Correct answer)
Correct answer: No
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.
Question 6: A Microsoft Excel data source's file name and location are stored in a parameter you construct called DataSourceExcel. Instead of using numerous copies of the location that are hard-coded into each query definition, you must edit the query to reference the parameter. Solution: Replace all references to the Excel file in the Power Query M code with references to DataSourceExcel. Is the objective being met?
- Yes
- No (Correct answer)
Correct answer: No
Simply replacing hard-coded references with the parameter name in the M code is not sufficient for Power Query to correctly use the parameter. For the parameter to be properly integrated, the query's source step needs to be explicitly modified to *use* the `DataSourceExcel` parameter as its input for the file path. The solution implies a simple text replacement, which doesn't correctly configure the data source to dynamically reference the parameter.
Question 7: 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: You modify the SQL statement by include a WHERE clause. Is the objective being met?
- Yes (Correct answer)
- No
Correct answer: Yes
Modifying the SQL statement to include a `WHERE` clause is an effective way to import only a portion of the data from a large SQL Server table. This method pushes the filtering operation down to the source database, meaning only the filtered subset of data is transferred over the network and loaded into Power BI. This significantly reduces the amount of data processed and imported, directly meeting the objective of importing only a portion.
Question 8: The creation of a Power BI dashboard is required. What instrument should you use?
- The Power BI service (Correct answer)
- Power Query Editor
- Power BI Desktop
- The Power BI mobile app
Correct answer: The Power BI service
Power BI dashboards are created and managed exclusively within the Power BI service (app.powerbi.com). While reports are built in Power BI Desktop, visuals from these published reports are then 'pinned' to a dashboard in the Power BI service. Dashboards provide a consolidated, at-a-glance view of key metrics from multiple reports and datasets.
Question 9: A Power BI dashboard must be made from a published report. Which report choice should you choose?
- Spotlight
- Copy visual as image
- Export data
- Pin visual (Correct answer)
Correct answer: Pin visual
To create a dashboard from a published report, you 'pin' individual visuals or entire report pages from the report to a new or existing dashboard in the Power BI service. The 'Pin visual' option allows you to select a specific visual and add it as a tile to a dashboard. This is the fundamental way dashboards are constructed, consolidating key information from various reports.
Question 10: You intend to certify a Power BI dataset. You must determine at what organizational level the authority to verify a dataset is delegated. Which level should you choose?
- Workspace
- Dataset
- Tenant (Correct answer)
- Workbook
Correct answer: Tenant
The ability to certify datasets in Power BI is a tenant-level setting, configured by Power BI administrators through the Power BI Admin portal. This delegation of authority at the tenant level ensures that only authorized individuals can designate datasets as certified, maintaining data quality and trustworthiness across the entire organization. While promotion can be done by workspace members, certification requires higher-level permissions.
Question 11: A SharePoint server for your business is housed in a Montreal datacenter. You intend to use Microsoft Excel files kept on the SharePoint server in a report you'll make using the Power BI service. You must suggest a way to guarantee that the dataset for the report can be updated automatically each day. What should the recommendation contain?
- A Point to Site virtual private network (VPN)
- An on-premises data gateway (Correct answer)
- Azure Data Box
- A Site-to-Site virtual private network (VPN)
Correct answer: An on-premises data gateway
An on-premises data gateway is necessary to establish a secure connection between the cloud-based Power BI service and data sources located within an organization's private network, such as an on-premises SharePoint server. This gateway acts as a bridge, allowing Power BI to refresh datasets from these local sources automatically and securely on a daily basis. Without it, the Power BI service cannot access the data for scheduled refreshes.
Question 12: You have shared control over a Power BI workspace. The workspace includes the DS1 dataset. You intend to schedule DS1's refresh. What is the daily maximum for data refreshes?
- 1
- 4
- 8 (Correct answer)
- 24
Correct answer: 8
For datasets in a shared capacity Power BI workspace, the maximum number of daily scheduled refreshes is eight. This limit applies to datasets that are not hosted in a Premium capacity. Premium capacities offer increased refresh frequency, allowing up to 48 refreshes per day.
Question 13: You have shared control over a Power BI workspace. The workspace includes a report that makes use of the DS1 dataset. A DS1 incremental refresh must be implemented. What ought to you start with?
- Define the incremental refresh policy in Power BI Desktop.
- Upgrade to the Premium SKU for the Power BI workspace.
- Define filter parameters in Power BI Desktop. (Correct answer)
- the dataset should be published to the Power BI service.
Correct answer: Define filter parameters in Power BI Desktop.
The first step to implement incremental refresh in Power BI Desktop is to define `RangeStart` and `RangeEnd` parameters. These parameters, typically of type `DateTime`, are used to filter the data that will be incrementally refreshed. Power BI then uses these parameters to dynamically query only the new or updated data from the source, which is crucial for efficient incremental refresh.
Question 14: You are in charge of a Power BI workspace with a dataset containing sales information for your business. You design a report that shows sales information broken down by department. Row-level security must be used to guarantee that, after the report is released, only the sales data for each department's personnel will be seen. What should you do first?
- Define filter parameters in Power BI Desktop.
- The report should be deployed to the Power BI service.
- Upgrade to the Premium SKU for the Power BI workspace.
- Create a role in Power BI Desktop. (Correct answer)
Correct answer: Create a role in Power BI Desktop.
The initial step for implementing Row-Level Security (RLS) in Power BI is to define roles and their corresponding DAX filter expressions within Power BI Desktop. These roles specify which rows of data users assigned to that role can see. After defining roles, the report can be published, and users or security groups can then be assigned to these roles in the Power BI service to enforce the security.
Question 15: You have a Power BI dataset. The dataset must be made discoverable. Which of these two arrangements will enable the dataset to be designated as discoverable? Each accurate response offers an entire resolution. Select all applicable answers.
- Enable RLS on the dataset.
- Publish dataset to a Premium workspace.
- Promote the dataset. (Correct answer)
- Certify the dataset. (Correct answer)
Correct answer: Promote the dataset.
To make a Power BI dataset discoverable, you can either promote it or certify it. Both promotion and certification mark a dataset as authoritative and trustworthy, making it easier for other users in the organization to find and use it for their own reports. These actions increase the visibility and confidence in the dataset within the Power BI service, encouraging its adoption.
Question 16: You want to make a report with numerous visualizations using Power BI Desktop. You must produce a picture to illustrate the following: Users can search for values by which to filter the other visuals by using filters for other visuals on the same report page. Which type of visualization should you use?
- Matrix
- Slicer (Correct answer)
- Scatter chart
- Funnel
Correct answer: Slicer
A slicer is the ideal visualization for allowing users to interactively filter other visuals on the same report page by searching for specific values. Slicers provide an intuitive way to narrow down data, offering options like list, dropdown, or search boxes. This directly fulfills the requirement of enabling users to search for filter values, enhancing report interactivity.
Free Microsoft DA-100 Questions and Answers
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?