Step2Study

Microsoft Certified: Fabric Analytics Engineer Associate practice questions

216 free questions with answers and explanations.

Practice test
  1. 101.A data engineer is optimizing a large semantic model in Microsoft Fabric. The model contains a 'Sales' fact table with billions of rows, and users frequently query aggregated sales data (e.g., total sales by month, total sales by product category). However, drill-through to individual transaction details is also occasionally required. The current setup, using DirectQuery for the fact table, is too slow for aggregated queries. Which strategy offers the best balance between fast aggregated queries and access to granular data without importing the entire fact table?Implement and manage semantic models (30-35%)
  2. 102.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Product' dimension table. There is a many-to-many relationship between 'Products' and 'Sales' due to a 'ProductBundle' bridge table. The modeler needs to calculate the total sales for products included in specific bundles. Which DAX function is most appropriate for establishing a filter context across this many-to-many relationship for calculation purposes?Implement and manage semantic models (30-35%)
  3. 103.A financial services company is developing a highly sensitive semantic model in Microsoft Fabric. The model contains a 'Salaries' table with sensitive compensation data, which should only be accessible to a few authorized HR personnel. No other user, including administrators of the Fabric workspace, should be able to view the data in this specific table, even if they have full access to the workspace. How should this requirement be met?Implement and manage semantic models (30-35%)
  4. 104.A data modeler is creating a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Products' dimension table. There is a need to calculate the total sales for 'New Customers' (customers who made their first purchase in the current year) and 'Repeat Customers' (customers who made a purchase in a prior year and also in the current year). The modeler wants to achieve this without adding complex calculated columns to the customer dimension or duplicating the sales measure. Which DAX pattern or feature is most suitable for this scenario?Implement and manage semantic models (30-35%)
  5. 105.A company is migrating its existing on-premises SQL Server Analysis Services (SSAS) tabular models to Microsoft Fabric. The SSAS models use a custom XMLA endpoint for administrative tasks and deployments. Which feature in Microsoft Fabric provides similar capabilities for managing semantic models?Implement and manage semantic models (30-35%)
  6. 106.A data engineer is developing a semantic model in Microsoft Fabric. The model will consume data from multiple sources, including an Azure SQL Database and a REST API. The engineer needs to define relationships between tables from different sources. Which feature in the Power Query Editor or model view allows for the creation of these relationships?Implement and manage semantic models (30-35%)
  7. 107.A company has a requirement to refresh its semantic model in Microsoft Fabric daily at 3:00 AM UTC. Additionally, if any of the underlying source data (from a Data Lakehouse) changes outside of this schedule, the semantic model should be refreshed immediately. How should the refresh strategy be configured?Implement and manage semantic models (30-35%)
  8. 108.A data engineer is maintaining a large semantic model in Microsoft Fabric that is experiencing slow query performance for complex measures. Many measures involve time-intelligence calculations (e.g., Year-to-Date, Previous Year Sales) and currency conversions. The model currently uses individual DAX measures for each calculation. The engineer wants to simplify the model, improve maintainability, and potentially boost performance by reducing the number of explicit measures. Which feature should the engineer implement?Implement and manage semantic models (30-35%)
  9. 109.A data engineer is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' table and a 'Customers' table. The 'Sales' table contains 'CustomerID' and 'OrderDate', and the 'Customers' table contains 'CustomerID' and 'CustomerSegment'. Historically, the model has a one-to-many relationship from 'Customers' to 'Sales' based on 'CustomerID', with a single cross-filter direction. A new requirement states that filters applied to 'Sales' (e.g., filtering by 'OrderDate') must also affect 'CustomerSegment' in reports. Which action should the data engineer take to enable this new filtering behavior?Implement and manage semantic models (30-35%)
  10. 110.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Customers' table and a 'Sales' fact table. The business requirement states that when filtering by 'Customer Region' in a report, sales for that region should be displayed, but the filter should also propagate to a 'Marketing Campaigns' table to show which campaigns were active in that region. However, filtering 'Marketing Campaigns' should NOT affect 'Customers' or 'Sales'. Which cross-filter direction should be configured for the relationship between 'Customers' and 'Marketing Campaigns'?Implement and manage semantic models (30-35%)
  11. 111.A data engineer is developing a semantic model in Microsoft Fabric. The model will consume data from several large tables in a data warehouse. To optimize query performance for frequently accessed aggregations (e.g., total sales by month), the engineer wants to pre-calculate and store these summary tables within the semantic model. Which feature should the engineer implement?Implement and manage semantic models (30-35%)
  12. 112.A data engineer is designing a semantic model in Microsoft Fabric. The model needs to track customer interactions over time, and a key requirement is to analyze data for specific time periods, including year-to-date, quarter-to-date, and month-to-date. The underlying data source does not contain these specific time intelligence calculations. Which DAX function category is designed to create these types of calculations efficiently?Implement and manage semantic models (30-35%)
  13. 113.A data engineer is configuring a semantic model in Microsoft Fabric. The model sources data from a data warehouse that is updated every 15 minutes. The business users require the reports based on this semantic model to reflect the latest data with minimal latency. However, a full refresh of the entire model takes over an hour. Which refresh strategy should the data engineer implement to meet the latency requirement?Implement and manage semantic models (30-35%)
  14. 114.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Product' table, a 'Sales' fact table, and a 'SalesPerson' table. The 'Product' table has a 'ProductID' and 'Category' column. The 'Sales' table contains 'ProductID', 'SalesPersonID', and 'Amount'. The 'SalesPerson' table has 'SalesPersonID' and 'Region'. Users need to analyze 'Sales' by 'Category' and 'Region'. However, a 'SalesPerson' can sell products from multiple 'Categories', and a 'Category' can be sold by multiple 'SalesPersons'. The modeler initially created a direct one-to-many relationship from 'Product' to 'Sales' and from 'SalesPerson' to 'Sales'. When trying to filter 'Category' by 'Region', no direct path is found. What is the most appropriate way to establish a relationship that allows filtering 'Category' by 'Region' through 'Sales'?Implement and manage semantic models (30-35%)
  15. 115.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Product' dimension table, with a one-to-many relationship from 'Product' to 'Sales'. The modeler observes that filtering 'Sales' by a specific 'Salesperson' does not affect the 'Product' table, leading to incorrect calculations when trying to count distinct products sold by that salesperson. Which property needs to be adjusted on the relationship to allow filters to flow from 'Sales' to 'Product'?Implement and manage semantic models (30-35%)
  16. 116.A data modeler is optimizing a semantic model in Microsoft Fabric. The model contains a 'Sales' fact table and several dimension tables. The modeler observes that queries involving complex calculations across multiple dimensions are performing slowly. The underlying data is large, but the relationships between tables are correctly defined. Which optimization technique, if applied to the fact table, can significantly improve the performance of these complex multi-dimensional queries without altering the source data or redesigning the entire model?Implement and manage semantic models (30-35%)
  17. 117.A data engineer is designing a semantic model in Microsoft Fabric. The model will integrate data from an Azure Data Lake Storage Gen2 (ADLS Gen2) account containing Parquet files. The business requires near real-time analytics on this data, but also needs to support complex aggregations and DAX calculations that perform best with in-memory caching. Which storage mode should the engineer choose for the tables sourced from ADLS Gen2 to meet both requirements?Implement and manage semantic models (30-35%)
  18. 118.A data engineer is configuring the refresh schedule for a semantic model in Microsoft Fabric. The underlying data source is an Azure SQL Database that is updated continuously throughout the day. Business users require the reports to reflect the latest data with minimal delay, ideally within minutes of data being committed to the source. Which refresh strategy should the engineer employ?Implement and manage semantic models (30-35%)
  19. 119.A data engineer is configuring a semantic model in Microsoft Fabric. The model will be used for both high-level aggregated reporting and detailed, ad-hoc analysis. The underlying data resides in a data warehouse (Azure Synapse Analytics dedicated SQL pool). The engineer wants to achieve optimal performance for frequently accessed aggregated data while ensuring that less common, detailed queries still return real-time data from the source. Which storage mode configuration best supports these requirements?Implement and manage semantic models (30-35%)
  20. 120.A data engineer is designing a semantic model in Microsoft Fabric. The model will consume data from a large Azure SQL Database and needs to support both real-time analytical queries and historical reporting. The real-time queries require immediate data availability, while historical reports can tolerate slightly older data. Which storage mode should the data engineer choose for the semantic model to best meet these requirements?Implement and manage semantic models (30-35%)
  21. 121.A data architect is designing a semantic model in Microsoft Fabric. The model will be used by various departments, each with different data access needs. Some departments require highly granular, row-level filtering based on user roles, while others need to restrict access to specific sensitive columns or measures. The architect also wants to ensure that specific measures are only visible to users with certain permissions. How should the architect combine security features to achieve both row-level and object-level restrictions, including measures?Implement and manage semantic models (30-35%)
  22. 122.A data engineer is working with a semantic model in Microsoft Fabric that includes a 'Sales' table and a 'Products' table. There is a one-to-many relationship between 'Products' (one) and 'Sales' (many) based on a 'ProductID' column. The engineer needs to calculate the total sales amount for each product. Which cross-filter direction should be configured for the relationship to correctly filter sales by product?Implement and manage semantic models (30-35%)
  23. 123.A data engineer is designing a semantic model in Microsoft Fabric. The model will consume data from a large Azure Data Lake Storage Gen2 account, specifically from Delta Lake tables. Users need to interact with the data with very low latency, and the solution should minimize data movement and duplication. Which storage mode is specifically optimized for this scenario?Implement and manage semantic models (30-35%)
  24. 124.A data architect is designing a semantic model in Microsoft Fabric. The model will be consumed by various reports, some of which require real-time data, while others can tolerate slightly stale data. The data source is a large Azure SQL Database with frequent updates. You need to ensure optimal performance for all reports while minimizing refresh times and resource consumption. Which storage mode should you configure for the semantic model?Implement and manage semantic models (30-35%)
  25. 125.A data engineer is designing a semantic model for a global e-commerce platform in Microsoft Fabric. The model needs to support multiple currencies for sales transactions, allowing users to view sales amounts in their local currency. The raw data contains sales amounts in a base currency and a currency conversion rate table. To implement this efficiently and allow dynamic currency selection in reports, which feature should the engineer use?Implement and manage semantic models (30-35%)
  26. 126.A data modeler is optimizing a semantic model in Microsoft Fabric. The model contains a 'Sales' table with millions of rows and a 'Date' table. A frequently used measure is `Total Sales YTD = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD('Date'[Date]))`. Users report slow performance when using this measure, especially for historical years. The underlying 'Sales' table is in Import mode. Which optimization technique should the data modeler prioritize to improve the performance of this specific measure?Implement and manage semantic models (30-35%)
  27. 127.A data engineer is managing refresh operations for a large semantic model in Microsoft Fabric. The model contains a fact table with billions of rows, partitioned by month. Due to the size, a full refresh takes an unacceptably long time. The requirement is to efficiently refresh only the most recent 12 months of data, while historical data remains static. Which refresh strategy should the engineer configure?Implement and manage semantic models (30-35%)
  28. 128.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' table and a 'Promotions' table. A promotion can apply to multiple sales, and a sale can be influenced by multiple promotions (e.g., a customer buys an item on sale and also uses a loyalty discount). The modeler needs to accurately analyze the impact of promotions on sales. Which type of relationship should be established between 'Sales' and 'Promotions'?Implement and manage semantic models (30-35%)
  29. 129.A data modeler is designing a semantic model in Microsoft Fabric. The model includes a 'Sales' table and a 'Product' table. There is a one-to-many relationship between 'Product'[ProductID] and 'Sales'[ProductID]. Business users frequently filter sales data by product attributes (e.g., 'Product Category', 'Product Brand'), and also need to see total sales for products that have never been sold. What should be the cross-filter direction for this relationship?Implement and manage semantic models (30-35%)
  30. 130.A data engineer is optimizing a large semantic model in Microsoft Fabric that is experiencing slow query performance. The model contains several complex DAX measures that perform aggregations over millions of rows. The engineer observes that these measures are consistently recalculating for every visual interaction. Which optimization technique should the engineer investigate to improve the performance of these measures?Implement and manage semantic models (30-35%)
  31. 131.A data modeler is designing a semantic model in Microsoft Fabric. The model will contain sales data, customer information, and product details. The sales data is stored in a fact table, and customer and product details are in dimension tables. The modeler needs to ensure that when a user filters by a specific customer, all sales made by that customer are shown, and when filtering by a product, all sales of that product are shown. What is the most appropriate cross-filter direction to configure for the relationships between the fact table and its dimension tables?Implement and manage semantic models (30-35%)
  32. 132.A data modeler is designing a semantic model in Microsoft Fabric. The model will contain a 'Customers' table and an 'Addresses' table. A customer can have multiple addresses (e.g., billing, shipping), and an address can be associated with multiple customers (e.g., shared family address). How should the modeler represent the relationship between 'Customers' and 'Addresses' to accurately reflect this business rule?Implement and manage semantic models (30-35%)
  33. 133.A data engineer is configuring a semantic model refresh in Microsoft Fabric. The underlying data source is a large transactional database that updates constantly throughout the day. The business requires the latest data to be available in reports with minimal latency, ideally within minutes of changes occurring in the source. Which refresh type is most suitable for this requirement?Implement and manage semantic models (30-35%)
  34. 134.A data architect is designing a semantic model in Microsoft Fabric. The model will contain a large fact table and several dimension tables. To optimize query performance, especially for common aggregations (e.g., total sales by region, average order value by date), the architect wants to pre-calculate and store these results within the semantic model itself, while still allowing drill-through to the detailed data. Which feature should the architect implement?Implement and manage semantic models (30-35%)
  35. 135.A data engineer is tasked with designing a new semantic model in Microsoft Fabric. The model will consume data from a Data Lakehouse that is updated hourly. The business stakeholders require that the reports built on this model reflect data with no more than a 15-minute latency. Which refresh strategy and storage mode combination should the engineer recommend for the core fact tables?Implement and manage semantic models (30-35%)
  36. 136.A data engineer is designing a semantic model in Microsoft Fabric. The model needs to incorporate data from a large, frequently updated data lake (Delta Lake tables in OneLake) and also from a small, static SQL Server dimension table. The primary goal is to achieve the best possible query performance for reports that combine data from both sources, while ensuring the data lake data is always near real-time. Which storage mode combination should be used?Implement and manage semantic models (30-35%)
  37. 137.A data modeler is developing a complex semantic model in Microsoft Fabric. The model includes several measures that reference other measures, leading to a deep dependency chain. Users are reporting slow performance when interacting with reports that use these complex measures. The modeler wants to improve query performance without significantly altering the underlying data structure. Which optimization technique should the modeler consider first, specifically for these complex measures?Implement and manage semantic models (30-35%)
  38. 138.A data engineer is working on a Microsoft Fabric semantic model that contains sensitive customer data. The company's policy dictates that data analysts should only see customer data relevant to their assigned region. This security requirement must be enforced at the data source level, meaning the data returned to the semantic model itself must already be filtered. Which approach should the data engineer take to implement this security while minimizing data transfer and processing within the semantic model?Implement and manage semantic models (30-35%)
  39. 139.A financial services company uses Microsoft Fabric for its analytics. A new semantic model is being developed that contains sensitive financial transaction data. The company has a strict compliance requirement that mandates specific columns, such as 'Account Number' and 'Transaction ID', must be completely hidden from users in certain roles, even if they have access to other parts of the table. Which security feature should the data engineer implement to meet this requirement?Implement and manage semantic models (30-35%)
  40. 140.A data engineer is developing a semantic model in Microsoft Fabric. The model sources data from a data warehouse where a 'Date' table exists, but it is not explicitly marked as a date table in the semantic model. This omission is causing issues with DAX time intelligence functions, as they are not producing correct results for year-to-date and month-to-date calculations. What is the most direct action the engineer should take to resolve this issue?Implement and manage semantic models (30-35%)
  41. 141.A data modeler is working on a Microsoft Fabric semantic model. The model contains a 'Products' table, a 'Sales' table, and a 'Stores' table. The modeler needs to create a measure that calculates the 'Total Sales' for products that belong to a specific 'Category' and are sold in 'Stores' located in a particular 'Region'. This calculation must be dynamic, allowing users to select different categories and regions in a report. Which DAX function is most appropriate for applying these multiple filter conditions to the 'Sales' table?Implement and manage semantic models (30-35%)
  42. 142.A data analyst is developing a new semantic model in Microsoft Fabric. The model needs to incorporate data from both an Azure SQL Database and a Data Lakehouse. The analyst wants to ensure that the data refresh operations are efficient and that the model can handle incremental updates for a large fact table. Which data storage mode should be primarily used for the fact table in this scenario?Implement and manage semantic models (30-35%)
  43. 143.A company uses Microsoft Fabric to manage its analytics. A new semantic model is being developed for financial reporting. The company's security policy dictates that users in the 'Finance Managers' role should only see data for their specific region, while 'Finance Analysts' should see data for all regions but only for departments they are assigned to. All other users should have no access. How should Row-Level Security (RLS) be implemented in this semantic model?Implement and manage semantic models (30-35%)
  44. 144.A financial services company is developing a highly sensitive semantic model in Microsoft Fabric. The model contains a 'Salaries' table and a 'Customer Accounts' table. Due to strict compliance regulations, certain users should not be able to see the 'Salaries' table at all, while other users should only see specific rows in the 'Customer Accounts' table based on their regional assignment. Which security features should be implemented?Implement and manage semantic models (30-35%)
  45. 145.A data engineer is designing a semantic model in Microsoft Fabric. The model will consume data from an operational database that is updated frequently throughout the day. Reports built on this model require data freshness within minutes, but only for the most recent data (e.g., the last 30 days). Historical data (older than 30 days) can be refreshed less frequently, perhaps daily. Which refresh strategy should the data engineer implement to efficiently balance data freshness and refresh performance?Implement and manage semantic models (30-35%)
  46. 146.A data engineer is configuring a semantic model in Microsoft Fabric. The model sources data from an Azure SQL Database. To improve query performance for frequently accessed aggregated data, the engineer wants to ensure that specific measures always leverage pre-calculated values. Which feature should the engineer implement to achieve this without requiring users to directly interact with aggregated tables?Implement and manage semantic models (30-35%)
  47. 147.A data architect is designing a semantic model in Microsoft Fabric for a global retail company. The model contains a large 'Sales' fact table and several dimension tables. The architect wants to implement a solution that allows users to query sales data with high performance, even when performing complex aggregations and filtering over large date ranges, without impacting the source systems. Which approach should the architect prioritize?Implement and manage semantic models (30-35%)
  48. 148.A data modeler is designing a complex semantic model in Microsoft Fabric. The model includes multiple fact tables (e.g., Sales, Returns, Inventory) and shared dimension tables (e.g., Product, Customer, Date). The modeler needs to define relationships that allow filters to propagate correctly between these tables while avoiding ambiguity and circular dependencies. Which type of relationship cardinality is typically used when connecting a dimension table to a fact table in a star schema design?Implement and manage semantic models (30-35%)
  49. 149.A company is implementing row-level security (RLS) in a Microsoft Fabric semantic model. The requirement is that sales managers should only see sales data for their assigned region. The 'Sales' table contains a 'RegionID' column, and there's a 'Users' table with 'UserID' and 'RegionID' columns. You have already created a role named 'SalesManager' in the semantic model. Which DAX filter expression should you apply to the 'Sales' table within the 'SalesManager' role to enforce this RLS rule?Implement and manage semantic models (30-35%)
  50. 150.A data engineer is configuring a Data Pipeline in Microsoft Fabric to ingest CSV files from an Azure Data Lake Storage Gen2 account into a Lakehouse. The source ADLS Gen2 account has multiple containers and folders, and the file paths often include dynamic elements like dates (e.g., `/rawdata/sales/2023/10/26/data.csv`). The engineer needs to ingest files from a specific date range or a particular subfolder based on pipeline execution parameters. Which Data Pipeline feature should be used to achieve this flexible and dynamic file path ingestion?Prepare and transform data (20-25%)