Mixed Deck — All Microsoft Certified Data Analyst Associate Topics Flashcards

Which relationship cardinality type is most commonly used between fact tables and dimension tables in a star schema?

Answer: One-to-many

One-to-many relationships are the standard in star schema designs, where one dimension record relates to many fact records.

What is the difference between a measure and a calculated column in Power BI?

Answer: Measures are computed dynamically at query time; calculated columns are computed at refresh and stored

Measures evaluate dynamically based on filter context at query time, while calculated columns are computed during refresh and stored in the model.

You want to prevent report users from seeing the underlying data by right-clicking a visual. Which feature should you configure?

Answer: Disable 'See data' in visual options

You can disable the 'See data' and 'Export data' options per visual in the visual's header icons or at the report level in settings.

A business hosts gatherings. Customers must be able to see forthcoming conferences that your business will be hosting. Which component of the Power Apps should you use?

Answer: Power Apps portal

A Power Apps portal is the ideal component for hosting public-facing websites that allow external users, like customers, to view information and interact with business data. Unlike canvas or model-driven apps, which are typically for internal users, portals are designed for broad public access and can display forthcoming events or conferences.

What does the 'Merge Queries' operation in Power Query do?

Answer: Joins two tables based on matching columns, similar to a SQL JOIN

Merge Queries combines two tables based on matching key columns using join types such as Left Outer, Inner, or Full Outer.

What does BLANK() represent in DAX?

Answer: A null/missing value that propagates through calculations

BLANK() represents the absence of a value (similar to NULL), and most DAX functions treat blank as zero in arithmetic but as missing in text operations.

What is the purpose of the ALLEXCEPT function in DAX?

Answer: Removes all filters from a table except those applied to specified columns

ALLEXCEPT removes all context filters from a table except for filters on the columns you explicitly list, useful for ratio calculations that need to preserve certain filter context.

Which sort of data workload stores a lot of aggregated data and is best for read operations?

Answer: analytical

Analytical data workloads are specifically designed for processing and querying large volumes of historical and aggregated data. They are optimized for read-heavy operations, enabling users to perform complex analyses, generate reports, and derive insights from the data. This contrasts with transactional workloads, which prioritize rapid, individual write operations.

What does the SWITCH function do in DAX?

Answer: Evaluates an expression against a list of values and returns the corresponding result

SWITCH evaluates an expression, compares it to a list of values, and returns the result associated with the first match, similar to a CASE statement.

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.

What function does Power BI's Row Level Security (RLS) serve?

Answer: Restricting data access for the given users ( By assigning users to roles)

Row-Level Security (RLS) in Power BI is designed to restrict data access at the row level based on the user executing the report. It works by defining roles and rules within Power BI Desktop, which are then applied to users or groups in the Power BI service, ensuring they only see the data they are authorized for. This prevents unauthorized users from viewing sensitive data rows.

Dynamics 365 Field Service is implemented by a corporation. You must use data that already exist to generate a work order. Which two records from the table can be changed into a work order? Select all applicable answers.

Answer: Case

In Dynamics 365, both 'Case' and 'Opportunity' records can be converted into 'Work Orders'. A Case often represents a customer issue requiring field service, making it a logical precursor. An Opportunity, especially for service contracts or installations, can also directly lead to a work order for service delivery or setup.

What happens when you have an ambiguous relationship path between tables in Power BI?

Answer: Power BI requires you to use USERELATIONSHIP in DAX to specify which path to use

Ambiguous paths occur when multiple active relationship routes exist; DAX functions like USERELATIONSHIP allow explicit path selection to resolve ambiguity.

In Power BI, what does the 'Spotlight' feature do when activated from a visual's context menu?

Answer: It focuses attention on one visual by dimming all other visuals on the report page

The Spotlight feature dims all other visuals on the report page, drawing the viewer's attention to the selected visual — useful when presenting specific insights.

Dynamics 365 Field Service is used by a corporation. The creation of work orders by hand is required for service delivery at clients' locations. For a work order to be created, which two fields must be filled in? Select each appropriate response.

Answer: Service account

When manually creating a work order in Dynamics 365 Field Service, the 'Service account' is mandatory as it identifies the customer receiving the service. The 'Work order type' is also a required field, categorizing the work (e.g., repair, installation) and potentially triggering specific business processes or pricing rules.

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.

What is a Power BI workspace?

Answer: A collaborative environment in the Power BI service for sharing datasets, reports, and dashboards

Workspaces are containers in the Power BI service where teams collaborate on datasets, reports, and dashboards with shared access.

What does the 'Group By' transformation do in Power Query?

Answer: Aggregates rows with the same key column values using operations like Sum, Count, or Average

Group By aggregates data by specified key columns, computing summary statistics like sum or count for each group.

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.

What is the purpose of setting a column as a 'Key Column' in a Power BI table?

Answer: To uniquely identify each row in the table

Marking a column as a Key Column indicates it uniquely identifies each row, which is important for data integrity and relationships.