Study guide for Exam DP-500: Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI
Purpose of this document
This study guide should help you understand what to expect on the exam and includes a summary of the topics the exam might cover and links to additional resources. The information and materials in this document should help you focus your studies as you prepare for the exam.
| Useful links | Description |
|---|---|
| Review the skills measured as of November 2, 2023 | This list represents the skills measured AFTER the date provided. Study this list if you plan to take the exam AFTER that date. |
| Review the skills measured prior to November 2, 2023 | Study this list of skills if you take your exam PRIOR to the date provided. |
| Change log | You can go directly to the change log if you want to see the changes that will be made on the date provided. |
| How to earn the certification | Some certifications only require passing one exam, while others require passing multiple exams. |
| Certification renewal | Microsoft associate, expert, and specialty certifications expire annually. You can renew by passing a free online assessment on Microsoft Learn. |
| Your Microsoft Learn profile | Connecting your certification profile to Microsoft Learn allows you to schedule and renew exams and share and print certificates. |
| Exam scoring and score reports | A score of 700 or greater is required to pass. |
| Exam sandbox | You can explore the exam environment by visiting our exam sandbox. |
| Request accommodations | If you use assistive devices, require extra time, or need modification to any part of the exam experience, you can request an accommodation. |
| Take a practice test | Are you ready to take the exam or do you need to study a bit more? |
Updates to the exam
Our exams are updated periodically to reflect skills that are required to perform a role. We have included two versions of the Skills Measured objectives depending on when you are taking the exam.
We always update the English language version of the exam first. Some exams are localized into other languages, and those are updated approximately eight weeks after the English version is updated. Although Microsoft makes every effort to update localized versions as noted, there may be times when the localized versions of an exam are not updated on this schedule. Other available languages are listed in the Schedule Exam section of the Exam Details webpage. If the exam isn't available in your preferred language, you can request an additional 30 minutes to complete the exam.
Note
The bullets that follow each of the skills measured are intended to illustrate how we are assessing that skill. Related topics may be covered in the exam.
Note
Most questions cover features that are general availability (GA). The exam may contain questions on Preview features if those features are commonly used.
Skills measured as of November 2, 2023
Audience profile
As a candidate for this exam, you should have subject matter expertise in designing, creating, and deploying enterprise-scale data analytics solutions.
Your responsibilities for this role include performing advanced data analytics at scale, such as:
Cleaning and transforming data.
Designing and building enterprise data models.
Incorporating advanced analytics capabilities.
Integrating with IT infrastructure.
Applying development lifecycle practices.
As a professional in this role, you:
Help collect enterprise-level requirements for data analytics solutions that include Azure and Power BI.
Advise on data governance and configuration settings for Power BI administration.
Monitor data usage.
Optimize performance of the data analytics solutions.
As an Azure enterprise data analyst, you collaborate with other roles, such as:
Solution architects
Data engineers
Data scientists
AI engineers
Database administrators
Power BI data analysts
As a candidate for this exam, you should have advanced Power BI skills, including managing data repositories and data processing in the cloud and on-premises, along with using Power Query and Data Analysis Expressions (DAX). You should also be proficient in consuming data from Azure Synapse Analytics and should have experience querying relational databases, analyzing data by using Transact-SQL (T-SQL), and visualizing data.
Skills at a glance
Implement and manage a data analytics environment (25–30%)
Query and transform data (20–25%)
Implement and manage data models (25–30%)
Explore and visualize data (20–25%)
Implement and manage a data analytics environment (25–30%)
Govern and administer a data analytics environment
Manage Power BI assets by using Microsoft Purview
Identify data sources in Azure by using Microsoft Purview
Recommend settings in the Power BI admin portal
Recommend a monitoring and auditing solution for a data analytics environment, including Power BI REST API and PowerShell cmdlets
Integrate an analytics platform into an existing IT infrastructure
Identify requirements for a solution, including features, performance, and licensing strategy
Configure and manage Power BI capacity
Recommend and configure an on-premises gateway type for Power BI
Recommend and configure a Power BI tenant or workspace to integrate with Azure Data Lake Storage Gen2
Integrate an existing Power BI workspace into Azure Synapse Analytics
Manage the analytics development lifecycle
Commit Azure Synapse Analytics code and artifacts to a source control repository
Recommend a deployment strategy for Power BI assets
Recommend a source control strategy for Power BI assets
Implement and manage deployment pipelines in Power BI
Perform impact analysis of downstream dependencies from dataflows and datasets
Recommend automation solutions for the analytics development lifecycle, including Power BI REST API and PowerShell cmdlets
Deploy and manage datasets by using the XMLA endpoint
Create reusable assets, including Power BI template (.pbit) files, Power BI data source (.pbids) files, and shared datasets
Query and transform data (20–25%)
Query data by using Azure Synapse Analytics
Identify an appropriate Azure Synapse pool when analyzing data
Recommend appropriate file types for querying serverless SQL pools
Query relational data sources in dedicated or serverless SQL pools, including querying partitioned data sources
Use a machine learning PREDICT function in a query
Ingest and transform data by using Power BI
Identify data loading performance bottlenecks in Power Query or data sources
Implement performance improvements in Power Query and data sources
Create and manage scalable Power BI dataflows
Identify and manage privacy settings on data sources
Create queries, functions, and parameters by using the Power Query Advanced Editor
Query advanced data sources, including JSON, Parquet, APIs, and Azure Machine Learning models
Implement and manage data models (25–30%)
Design and build tabular models
Choose when to use DirectQuery for Power BI datasets
Choose when to use external tools, including DAX Studio and Tabular Editor 2
Create calculation groups
Write calculations that use DAX variables and functions, for example handling blanks or errors, creating virtual relationships, and working with iterators
Design and build a large format dataset
Design and build composite models, including aggregations
Design and implement enterprise-scale row-level security and object-level security
Optimize enterprise-scale data models
Identify and implement performance improvements in queries and report visuals
Troubleshoot DAX performance by using DAX Studio
Optimize a data model by using Tabular Editor 2
Analyze data model efficiency by using VertiPaq Analyzer
Optimize query performance by using DAX Studio
Implement incremental refresh (including the use of query folding)
Optimize a data model by using denormalization
Explore and visualize data (20–25%)
Explore data by using Azure Synapse Analytics
Explore data by using native visuals in Spark notebooks
Explore and visualize data by using the Azure Synapse SQL results pane
Visualize data by using Power BI
Create and import a custom report theme
Create R or Python visuals in Power BI
Connect to and query datasets by using the XMLA endpoint
Design and configure Power BI reports for accessibility
Enable personalized visuals in a report
Configure automatic page refresh
Create and distribute paginated reports in Power BI Report Builder
Study resources
We recommend that you train and get hands-on experience before you take the exam. We offer self-study options and classroom training as well as links to documentation, community sites, and videos.
| Study resources | Links to learning and documentation |
|---|---|
| Get trained | Choose from self-paced learning paths and modules or take an instructor-led course |
| Find documentation | Azure Cosmos DB documentation Azure documentation |
| Ask a question | Microsoft Q&A | Microsoft Docs |
| Get community support | Analytics on Azure - Microsoft Tech Community Azure Data Factory - Microsoft Tech Community Azure - Microsoft Tech Community |
| Follow Microsoft Learn | Microsoft Learn - Microsoft Tech Community |
| Find a video | Exam Readiness Zone Data Exposed Browse other Microsoft Learn shows |
Change log
Key to understanding the table: The topic groups (also known as functional groups) are in bold typeface followed by the objectives within each group. The table is a comparison between the two versions of the exam skills measured and the third column describes the extent of the changes.
| Skill area prior to November 2, 2023 | Skill area as of November 2, 2023 | Change |
|---|---|---|
| Audience profile | No change | |
| Implement and manage a data analytics environment | Implement and manage a data analytics environment | No change |
| Govern and administer a data analytics environment | Govern and administer a data analytics environment | No change |
| Integrate an analytics platform into an existing IT infrastructure | Integrate an analytics platform into an existing IT infrastructure | Minor |
| Manage the analytics development lifecycle | Manage the analytics development lifecycle | Minor |
| Query and transform data | Query and transform data | No change |
| Query data by using Azure Synapse Analytics | Query data by using Azure Synapse Analytics | No change |
| Ingest and transform data by using Power BI | Ingest and transform data by using Power BI | No change |
| Implement and manage data models | Implement and manage data models | No change |
| Design and build tabular models | Design and build tabular models | No change |
| Optimize enterprise-scale data models | Optimize enterprise-scale data models | Minor |
| Explore and visualize data | Explore and visualize data | No change |
| Explore data by using Azure Synapse Analytics | Explore data by using Azure Synapse Analytics | No change |
| Visualize data by using Power BI | Visualize data by using Power BI | No change |
Skills measured prior to November 2, 2023
Audience profile
Candidates for the Azure Enterprise Data Analyst Associate certification should have subject matter expertise in designing, creating, and deploying enterprise-scale data analytics solutions.
Responsibilities for this role include performing advanced data analytics at scale, such as cleaning and transforming data, designing, and building enterprise data models, incorporating advanced analytics capabilities, integrating with IT infrastructure, and applying development lifecycle practices. These professionals help collect enterprise-level requirements for data analytics solutions that include Azure and Power BI. They also advise on data governance and configuration settings for Power BI administration, monitor data usage, and optimize performance of the data analytics solutions.
Azure enterprise data analysts collaborate with other roles, such as solution architects, data engineers, data scientists, AI engineers, database administrators, and Power BI data analysts.
Candidates for this exam should have advanced Power BI skills, including managing data repositories and data processing in the cloud and on-premises, along with using Power Query and Data Analysis Expressions (DAX). They should also be proficient in consuming data from Azure Synapse Analytics and should have experience querying relational databases, analyzing data by using Transact-SQL (T-SQL), and visualizing data.
Skills at a glance
Implement and manage a data analytics environment (25–30%)
Query and transform data (20–25%)
Implement and manage data models (25–30%)
Explore and visualize data (20–25%)
Implement and manage a data analytics environment (25–30%)
Govern and administer a data analytics environment
Manage Power BI assets by using Microsoft Purview
Identify data sources in Azure by using Microsoft Purview
Recommend settings in the Power BI admin portal
Recommend a monitoring and auditing solution for a data analytics environment, including Power BI REST API and PowerShell cmdlets
Integrate an analytics platform into an existing IT infrastructure
Identify requirements for a solution, including features, performance, and licensing strategy
Configure and manage Power BI capacity
Recommend and configure an on-premises gateway type for Power BI
Recommend and configure a Power BI tenant or workspace to integrate with Azure Data Lake Storage Gen2
Integrate an existing Power BI workspace into Azure Synapse Analytics
Manage the analytics development lifecycle
Commit Azure Synapse Analytics code and artifacts to a source control repository
Recommend a deployment strategy for Power BI assets
Recommend a source control strategy for Power BI assets
Implement and manage deployment pipelines in Power BI
Perform impact analysis of downstream dependencies from dataflows and datasets
Recommend automation solutions for the analytics development lifecycle, including Power BI REST API and PowerShell cmdlets
Deploy and manage datasets by using the XMLA endpoint
Create reusable assets, including Power BI templates, Power BI data source (.pbids) files, and shared datasets
Query and transform data (20–25%)
Query data by using Azure Synapse Analytics
Identify an appropriate Azure Synapse pool when analyzing data
Recommend appropriate file types for querying serverless SQL pools
Query relational data sources in dedicated or serverless SQL pools, including querying partitioned data sources
Use a machine learning PREDICT function in a query
Ingest and transform data by using Power BI
Identify data loading performance bottlenecks in Power Query or data sources
Implement performance improvements in Power Query and data sources
Create and manage scalable Power BI dataflows
Identify and manage privacy settings on data sources
Create queries, functions, and parameters by using the Power Query Advanced Editor
Query advanced data sources, including JSON, Parquet, APIs, and Azure Machine Learning models
Implement and manage data models (25–30%)
Design and build tabular models
Choose when to use DirectQuery for Power BI datasets
Choose when to use external tools, including DAX Studio and Tabular Editor 2
Create calculation groups
Write calculations that use DAX variables and functions, for example handling blanks or errors, creating virtual relationships, and working with iterators
Design and build a large format dataset
Design and build composite models, including aggregations
Design and implement enterprise-scale row-level security and object-level security
Optimize enterprise-scale data models
Identify and implement performance improvements in queries and report visuals
Troubleshoot DAX performance by using DAX Studio
Optimize a data model by using Tabular Editor 2
Analyze data model efficiency by using VertiPaq Analyzer
Optimize query performance by using DAX Studio
Implement incremental refresh (including the use of query folding)
Optimize a data model by using denormalization
Explore and visualize data (20–25%)
Explore data by using Azure Synapse Analytics
Explore data by using native visuals in Spark notebooks
Explore and visualize data by using the Azure Synapse SQL results pane
Visualize data by using Power BI
Create and import a custom report theme
Create R or Python visuals in Power BI
Connect to and query datasets by using the XMLA endpoint
Design and configure Power BI reports for accessibility
Enable personalized visuals in a report
Configure automatic page refresh
Create and distribute paginated reports in Power BI Report Builder