PL-300 Practice Questions & Answers

Loading your practice set…

A business analyst needs to connect to an external SQL Server database to retrieve sales data for analysis in Power BI. The data is updated frequently and should be refreshed automatically without impacting the performance of the existing reports. Which mode should the analyst use for this scenario, and why?

Import mode
DirectQuery mode (Correct)
Dual mode
Power Query mode

Explanation: DirectQuery mode is the best choice for this scenario because it allows the data to be queried directly from the source, ensuring that the latest data is always available. This mode also offloads the querying process to the source database, which helps maintain the performance of existing reports. Import mode would store the data locally, which could lead to performance issues due to the need to refresh the data, and Dual mode combines both modes but is not necessary for this specific requirement.

An organization wants to integrate data from multiple sources into a single Power BI report, including a local Excel file and a live connection to an Azure SQL Database. To ensure that the Excel data is refreshed manually while the SQL data is refreshed automatically, what approach should be taken using Power Query in Power BI Desktop?

Use Import mode for both data sources
Use DirectQuery mode for both data sources
Use Import mode for the Excel file and DirectQuery for the SQL Database (Correct)
Use Power Query mode for both data sources

Explanation: The analyst should use the 'Get Data' feature in Power BI Desktop to import the Excel file in Import mode and then use DirectQuery for the Azure SQL Database. This way, the Excel data can be refreshed manually, and the SQL data can be refreshed automatically without affecting the performance of the existing reports. Using Import mode for both would not allow automatic refreshing for the SQL data, and using DirectQuery for both would not allow manual refreshing for the Excel data.

You are working on a data transformation project using Azure Data Factory and need to clean, transform, and load data from an Excel file into a SQL Server database. The Excel file contains a column with mixed data types (numeric and string values). You want to ensure that all numeric values are converted to integers before loading them into the database. Which M language script should you use to achieve this transformation?

type(text)
type(int) (Correct)
type(number)
type(float)

Explanation: The correct answer is B because the `type` function in M can be used to convert the data type of a column. In this case, `type(int)` will convert the column to integer type, ensuring that only numeric values are kept and non-numeric values are removed or handled as per the requirement. Option A uses `type(text)` which does not perform any conversion. Option C uses `type(number)` which might not remove non-numeric values. Option D uses `type(float)` which also does not guarantee integer conversion.

During a data transformation task, you encounter a dataset where certain columns need to be pivoted to transform rows into columns. For example, you have a dataset with a column named 'Category' and a column named 'Value', and you need to pivot the 'Category' column so that each unique category becomes a separate column with its corresponding value. Which M language function would you use to accomplish this transformation?

unpivot
pivot (Correct)
flatten
split

Explanation: The correct answer is B because the `pivot` function in M is used to transform rows into columns based on a specified column. It takes two arguments: the column to pivot and the column to aggregate the values by. Option A uses `unpivot`, which is the opposite of what is needed. Option C uses `flatten`, which is used to expand nested arrays or records. Option D uses `split`, which is used to divide strings into smaller parts.

In designing a star schema for a retail analytics system, you need to model the relationship between customers and their purchases. Each customer can make multiple purchases, but each purchase is made by only one customer. Which cardinality relationship should you use in your data model, and how would you represent this in the star schema?

Many-to-many relationship between Customers and Purchases
One-to-many relationship from Customers to Purchases (Correct)
One-to-one relationship from Customers to Purchases
No relationship between Customers and Purchases

Explanation: The correct answer is B. In a star schema, the fact table (e.g., `Purchases`) will have a foreign key to the dimension table (e.g., `Customers`). This represents a 1-to-many relationship where each customer can have multiple purchases, but each purchase is linked to only one customer. Option A is incorrect because it suggests a many-to-many relationship, which is not appropriate here as each purchase is uniquely linked to a single customer. Option C is incorrect because it suggests a one-to-one relationship, which does not fit the scenario where a customer can make multiple purchases. Option D is incorrect because it suggests no relationship, which contradicts the given scenario.

When designing a star schema for an online marketplace, you need to model the relationship between products and categories. Each product can belong to multiple categories, and each category can contain multiple products. How should you represent this many-to-many relationship in the star schema, and what additional table is required?

One-to-many relationship from Products to Categories
Many-to-many relationship with a junction table (ProductCategories) (Correct)
One-to-one relationship from Products to Categories
No relationship between Products and Categories

Explanation: The correct answer is B. In a star schema, a many-to-many relationship requires an additional junction table (e.g., `ProductCategories`) that links the `Products` table to the `Categories` table. This junction table contains foreign keys to both the `Products` and `Categories` tables. Option A is incorrect because it suggests a one-to-many relationship, which does not fit the scenario where a product can belong to multiple categories. Option C is incorrect because it suggests a one-to-one relationship, which does not fit the scenario where a category can contain multiple products. Option D is incorrect because it suggests no relationship, which contradicts the given scenario.

In a Power BI report, you need to calculate the total sales for the current year and compare it with the previous year's sales. Which DAX function should you use to ensure that the comparison is done on the same period of the previous year, considering the fiscal year starts in April?

CALCULATE
SAMEPERIODLASTYEAR (Correct)
FILTER
TOTAL

Explanation: The correct answer is `SAMEPERIODLASTYEAR` because it aligns the dates from the current year with the corresponding dates from the previous year, taking into account the fiscal year start date. `CALCULATE` alone would not adjust the context based on the fiscal year, `FILTER` would only apply a condition without changing the context, and `TOTAL` would sum up all values without considering the time context.

You are working on a financial dashboard and need to calculate the sales amount for the current month while excluding any rows where the product category is 'Electronics'. Which DAX function should you use to filter out the rows before calculating the sales amount?

CALCULATE
FILTER (Correct)
CALCULATETABLE
ROWCONTEXT

Explanation: The correct answer is `FILTER` because it allows you to specify conditions to filter the rows before performing the calculation. `CALCULATE` can also be used but it changes the context of the entire expression, whereas `CALCULATETABLE` returns a table rather than a scalar value, and `ROWCONTEXT` is not a valid DAX function.

In Power BI, you have created a report that includes a visual interaction between a line chart and a table. You want to ensure that when a user clicks on a point in the line chart, the corresponding row in the table is highlighted. Which feature should you use to achieve this functionality?

Shared Filters
Shared Measures (Correct)
Drill-through
Bookmarks

Explanation: The correct answer is B - 'Shared Measures'. Shared Measures allow you to create a dynamic relationship between visuals in a report, so that when a user interacts with one visual, it affects another. Option A (Shared Filters) would only filter the data based on the selected value, not highlight the corresponding row. Option C (Drill-through) is used to navigate to a different report or page, not to highlight a specific row. Option D (Bookmarks) allows users to save and recall specific views but does not dynamically highlight rows.

You are designing a dashboard in Power BI and want to include a KPI card that displays the current sales target and the actual sales figures. The KPI card should visually indicate whether the sales target has been met by using a traffic light system (green for meeting the target, yellow for near the target, and red for not meeting the target). How can you configure the KPI card to achieve this?

Set a fixed value
Set thresholds and colors (Correct)
Use a custom visual
Use a measure

Explanation: The correct answer is B - 'Set thresholds and colors'. In Power BI, you can set thresholds and assign specific colors to different ranges of values in a KPI card to visually represent performance levels. Option A (Set a fixed value) would not allow for a dynamic representation of performance. Option C (Use a custom visual) could be an alternative but is more complex and not as straightforward as setting thresholds. Option D (Use a measure) would not directly control the visual appearance of the KPI card.

In a scenario where you are analyzing customer churn in an Azure Synapse Analytics environment, you want to identify key influencers contributing to the churn rate. Which of the following would best help you achieve this using the Decomposition Tree pattern? Assume you have access to customer data including demographics, purchase history, and customer service interactions.

Using a simple pivot table to summarize churn rates by customer segment
Applying the Decomposition Tree to break down churn factors into demographic, purchase history, and customer service interactions (Correct)
Implementing a machine learning model without segmenting the data
Creating a dashboard with visualizations of overall churn rate trends

Explanation: The Decomposition Tree pattern helps in breaking down complex problems into smaller, more manageable parts. In this case, it can be used to isolate and analyze different factors that contribute to customer churn. Option B is correct because it directly applies the Decomposition Tree to break down the problem into specific segments (e.g., demographic factors, purchase history, customer service interactions) to identify key influencers. Options A, C, and D do not specifically apply the Decomposition Tree pattern to the problem at hand.

During a performance analysis of an Azure SQL Database, you notice a sudden drop in query response times. You suspect that certain queries might be causing this issue. Which approach would best help you quickly identify the key queries responsible for the performance degradation using the Quick Insights feature?

Running a full system health check manually
Using Quick Insights to analyze recent query performance (Correct)
Reviewing the database logs for error messages
Checking the server configuration settings for any changes

Explanation: Quick Insights in Azure SQL Database provides a way to quickly identify potential issues and their root causes. Option B is correct because it directly uses Quick Insights to pinpoint the problematic queries. Options A, C, and D do not leverage the Quick Insights feature effectively to address the performance issue.

In a scenario where you need to ensure that only users with specific roles can access certain rows in a dataset based on their role, which feature should you implement to enforce this requirement without modifying the application code? You have a dataset named `SalesData` and you want to restrict access so that only users in the `SalesManager` role can see sales data from their respective regions, while all other users can only see their own region's sales data.

Create a view that filters the data based on the user's role.
Implement Row-Level Security (RLS) by creating a policy that filters the rows based on the user's role. (Correct)
Use a stored procedure to filter the data based on the user's role.
Configure the dataset to automatically filter data based on the user's role.

Explanation: Row-Level Security (RLS) is designed to enforce row-level access control policies without changing the application code. By creating an RLS policy that filters the rows based on the user's role, you can ensure that only users in the `SalesManager` role can see sales data from their respective regions, and all other users can only see their own region's sales data. Other options like using views or stored procedures would require changes to the application code, which goes against the requirement of not modifying the application code.

Your organization uses Azure Synapse Analytics and you need to perform an incremental refresh on a dataset to update only the new or modified records since the last refresh. Which method should you use to achieve this? You have a dataset named `CustomerOrders` and you want to ensure that only the new or updated orders since the last refresh are added to the dataset.

Perform a full refresh of the dataset.
Use the incremental refresh method to update only the new or modified records. (Correct)
Manually update the dataset by inserting new records.
Configure the dataset to automatically update records based on timestamps.

Explanation: Incremental refresh is a feature in Azure Synapse Analytics that allows you to update only the new or modified records in a dataset since the last refresh. By using the incremental refresh method, you can efficiently update the `CustomerOrders` dataset with only the new or updated orders, without having to reprocess the entire dataset. Other methods like full refresh or manual updates would process the entire dataset, which could be inefficient if only a small portion of the data has changed.

In a scenario where you need to manage access control for a workspace in Azure DevOps, which role should you assign to a user who needs to create and manage deployment pipelines but not contribute to the code or view sensitive information?

Project Admin
Contributor (Correct)
User Admin
Viewer

Explanation: The correct answer is B because the Contributor role allows users to create and manage resources, including deployment pipelines, but does not grant permission to view sensitive information or modify code directly. The Project Admin role would provide too much access, while the User Admin role focuses on managing users rather than project resources.

When deploying an application to Azure App Service using Azure DevOps, which pipeline stage should you configure to ensure that the application is tested before it is deployed to production?

Development slot
Staging slot (Correct)
Production slot
Local slot

Explanation: The correct answer is B because the Staging slot in Azure App Service is designed for testing purposes before the application is promoted to the Production slot. The Production slot is used for live traffic, while the Development slot is typically used for local development and testing within the organization.