Microsoft DA-100 Flashcards

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?

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.

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?

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'.

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.

The creation of a Power BI dashboard is required. What instrument should you use?

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.

A Power BI dashboard must be made from a published report. Which report choice should you choose?

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.

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.

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.

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.

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?

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.