5B. Manage semantic models in Power BI

Question 1

What are two ways of managing the sharing of semantic models for optimal organizational performance?

Answer 1

• You can automate the refresh process so that it becomes more efficient and users always have access to the latest data.
• You can also promote certain semantic models over others so that users can clearly identify the best semantic models to use.

Question 2

How can parameters help with managing semantic models?

Answer 2

• You can use parameters to change the server or database name of your semantic model.
• You can use parameters to change the file path for a data source.
• You can use parameters to configure incremental refreshes of your data.
• You can use parameters to run “what-if” scenarios and conduct scenario-type analysis on the data.

Question 3

What are gateways used for when managing semantic models?

Answer 3

Gateway software acts like a bridge; it allows organizations to retain databases and other data sources on their on-premises networks and access that on-premises data in cloud services, such as Power BI and Microsoft Azure Analysis Services.

They are used so that you and other users can access your on-premises data source from the cloud.

Question 4

What are two types of on-premises gateways?

Answer 4

• Organization mode - Allows multiple users to connect to multiple on-premises data sources and is suitable for complex scenarios.
• Personal mode - Allows one user to connect to data sources. This type of gateway can be used only with Power BI and it can’t be shared with other users, so it is suitable in situations where you’re the only one in your organization who creates reports. You will install the gateway on your local computer, which needs to stay online for the gateway to work.

Question 5

What is the first step to using a gateway?

Answer 5

Before you can connect to your on-premises data source, you need to install the on-premises data gateway, and then configure it to suit your organizational needs. Usually, this task is completed by an admin in your organization.

When the on-premises gateway is installed and configured, you can start the gateway and then sign in by using your Microsoft 365 organization account.

Question 6

What steps take place when you are interacting with an element connected to an on-premises gateway from the cloud?

Answer 6

• The cloud service creates a query and the encrypted credentials for the on-premises data source. The query and credentials are sent to the gateway queue for processing.
• The gateway cloud service analyzes the query and pushes the request to Microsoft Azure Service Bus.
• Service Bus sends the pending requests to the gateway.
• The gateway gets the query, decrypts the credentials, and then connects to one or more data sources with those credentials.
• The gateway sends the query to the data source to be run.
• The results are sent from the data source back to the gateway and then to the cloud service. The service then uses the results.

Question 7

What are the benefits of scheduled refreshes?

Answer 7

• Scheduling the refresh of your data will save you time because you don’t have to manually refresh the data
• It also ensures that users can access the most up-to-date data.

Question 8

What aspects of scheduled refreshes can you configure?

Answer 8

• The refresh frequency (i.e. daily, etc).
• The time(s) you want the refresh to occur. You can configure up to eight daily time slots, if your semantic model is on shared capacity, or 48 time slots on Power BI Premium.

Remember to make sure that your time zone is set up correctly.
While you can set a time for the refresh, be aware that the refresh might not take place at that exact time. Power BI starts scheduled refreshes on a best effort basis. The goal is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can’t allocate the required resources sooner.

Question 9

How do on-demand refreshes interact with scheduled refreshes?

Answer 9

In addition to the scheduled refreshes, you can refresh a dataset at any time by performing an on-demand refresh. This type of refresh doesn’t affect the next scheduled refresh time.

Question 10

What is a good health check on scheduled refreshes and what can happen if they fail, and why might refreshes fail?

Answer 10

You can check the refresh status and history at any time. This feature is useful if you want to find out when the last refresh occurred and when the next one is scheduled. It is also good practice to check the status of your semantic models occasionally to check if refresh errors have occurred.

Power BI deactivates your refresh schedule after four consecutive failures or when the service detects an unrecoverable error that requires a configuration update, such as invalid or expired credentials. It is not possible to change the consecutive failures threshold.

Question 11

Why is incremental refresh a popular feature, and what are important requirements to use it?

Answer 11

The Incremental refresh feature in Power BI is a popular feature because it allows you to refresh large semantic models quickly and as often as you need, without having to reload historical data each time.

Incremental refresh should only be used on data sources and queries that support query folding. If query folding isn’t supported, incremental refresh could lead to a bad user experience because, while it will still issue the queries for the relevant partitions, it will pull all data, potentially multiple times.

Question 12

Where do you configure incremental refresh?

Answer 12

Power BI Desktop. The refresh policy is applied when you publish to Power BI service, which then does the work of managing partitions for optimized data loads.

Question 13

What are the benefits of incremental refresh?

Answer 13

• Quicker refreshes - Only data that needs to be changed gets refreshed. For example, if you have five years’ worth of data, and you only need to refresh the last 10 days because that is the only data that has changed, the incremental refresh will refresh only those 10 days of data. Undoubtedly, the time it takes to refresh 10 days of data is much shorter than five years of data.
• More reliable refreshes - You no longer need to keep your long-running data connections open to schedule a refresh.
• Reduced resource consumption - Because you only need to refresh the smaller the amount of data, the overall consumption of memory and other resources is reduced.

Question 14

What are the high-level steps to defining an incremental refresh policy?

Answer 14

• Define the filter parameters.
• Use the parameters to apply a filter.
• Define the incremental refresh policy.
• Publish changes to Power BI service.

Question 15

How do you define filter parameters for incremental refresh?

Answer 15

• Open your semantic model in Power Query Editor.
• On the Home tab, select Manage Parameters.
• On the Parameters window that displays, add two new parameters, RangeStart and RangeEnd, ensuring that for both parameters, the Type is set to Date/Time and the Suggested Value is set to Any value.
• Regarding the Current Value, for the RangeStart parameter, enter the date on which you want to begin the import, and for the RangeEnd parameter, enter the date on which you want the import to end. Later on, PowerBI will configure these values automatically.

Note that the parameters must have these names exactly.

These parameters have a dual purpose. In Power BI Desktop, they are the filtering window because they restrict the used data to the range that is listed in the start and end dates. After they have been published to the service, the parameters are taken over to be the sliding window to determine what data to pull in.

Question 16

How do you apply filter for incremental refresh?

Answer 16

When you have defined the new parameters, you can apply the filter by following these steps:
- Go to the applicable Date column and then right-click that column and select Custom Filter.
- In the Filter Rows window that displays, to avoid the double counting of rows, make sure that you keep rows where OrderDate is after or equal to the RangeStart parameter and before the RangeEnd parameter.
- Select Close and Apply from the Power Query Editor.
You should now see a subset of the semantic model in Power BI Desktop.

Question 17

How do you define an incremental refresh policy?

Answer 17

When you have filtered the data, you can define the incremental refresh policy for the data table, which sets up the refresh process.
- Right-click the applicable table and then select Incremental refresh.
- On the Incremental refresh window that displays, turn on the Incremental refresh option. Then, configure the refresh as required.

Question 18

What are two ways that you can endorse semantic models in Power BI, and why is it important to do this?

Answer 18

This is important to do to support a good data culture and support the use of single source(s) of truth.

The two ways of doing this are:
- Promotion - Promote your semantic models when they’re ready for broad usage.
- Certification - Request certification for a promoted semantic model. Certification can be a highly selective process, so only the truly reliable and authoritative semantic models are used across the organization.

Question 19

Who can promote semantic models, and who can certify semantic models?

Answer 19

• To promote content, you must have write permissions on the workspace where the content you want to promote is located.
• Content certification is a big responsibility, and only authorized users can certify content. Other users can request content certification.

Question 20

What happens after you endorse or certify a semantic model?

Answer 20

• When you return to your workspace, a badge in the Endorsement column for that semantic model will appear, indicating that it’s either certified or promoted.
• Additionally, after a semantic model is certified, accessing that model requires permission from the owner of the semantic model.

Question 21

What content types can be endorsed?

Answer 21

• Semantic models
• Dataflows
• Reports
• Apps

Question 22

For what connections does the use of a gateway not apply?

Answer 22

Cloud services, such as SharePoint, do not require a gateway because the data is already in the cloud. You only need to provide your authorization credentials to set up a data source connection.

Question 23

What is a simple step to troubleshooting connectivity issues with data in cloud services?

Answer 23

If your report fails to refresh, ensure that your data source credentials are up to date.

Question 24

What is query caching and what can it help with>

Answer 24

With the Query Caching feature, you can use the local caching services of Power BI to process query results. Instead of relying on the semantic model to calculate queries, you use cloud resources on your Premium capacities in the Power BI service to load your report. Query caching ensures constant performance instead of overloading the semantic model resources.

Question 25

What license is required to use query caching?

Answer 25

Power BI Premium or Embedded

Question 26

For what types of reports is query caching a good fit?

Answer 26

Query caching reduces load time and increases query speed, especially for semantic models that
- aren’t refreshed often and
- are accessed frequently.

Question 27

What are some benefits/features of query caching?

Answer 27

Query caching results are user-specific, and apply specific page of a report only. Several benefits to using query caching include:
- Improvement of the performance of reports, dashboards, and dashboard tiles.
- It respects bookmarks and default filters.
- Cached query results are specific to the user.
- All security labels are followed.
- It reduces the load on your dedicated capacity.

Question 28

Where do you set up query caching?

Answer 28

• Go to a semantic model in your workspace and open its Settings page.
• Select the semantic models tab and expand the Query Caching options.
• On the Query Caching page, choose one of the available options. The default option is that query caching is turned off; however, you can also select Off, which turns off query caching for the specific semantic model in question. If you select On, query caching is turned on for this specific semantic model only. For this example, you’ll select On for your semantic model because you want to apply query caching to your specific semantic model.

Question 29

Despite its many benefits, what is one event where query caching can reduce performance of a semantic model and/or data source?

Answer 29

If many semantic models have query caching enabled, and a refresh occurs, a reduction in performance might occur because a large number of queries are being processed at once.