Insights in Power BI dataset parameters and refresh schedule

We have all been in a situation where you want to get more insights in the specific settings for a published dataset. But when this dataset is owned and published by someone else, you have to take-over the dataset first to get all the details available. In this blog, I will elaborate on what is available to you in the service and share some code snippets that help you to explore other dataset settings like refresh schedules and configured parameter values.

For those that do not want to read the full blog and just get their hands on the script, you can directly go to my GitHub repository for Power BI Automation.

Dataset settings

In order to access the dataset settings menu, first you need to have workspace permissions of course. Simply because a published Power BI App does not include the direct connections to the dataset, but only the related reports and dashboards. Potentially dataset build permissions are configured, but that will still not help you to access all information you might be looking for.

Some things that are available to you, are;

  • Dataset description
  • Query caching
  • Server settings (XMLA Endpoint)
  • Q&A settings for natural language query
  • Featured Q&A questions
  • Endorsement and dataset discovery settings
  • Request access settings and messages
  • Storage type for datasets, related to large dataset storage
  • Dataset image to be represented in the datasets hub.
Power BI Dataset settings in the Power BI Service

Though, some things are still missing. Like parameter values configured in the dataset or the refresh schedule. Of course you can see the latest and the next refresh date and time in the workspace overview and datasets hub, but you might be interested in the frequency of refreshes. Same case applies for parameter values that potentially defines which dataset is loaded and from what source system etc.

The way how you can investigate these settings is by taking-over the dataset. By doing so, the configured data source connection credentials will be removed, potential gateway connection will get lost and scheduled refreshes will be disabled. This is not the situation where you want to be in. As it can happen that you personally do not have access to the data sources, but the owner of the dataset does. Not even speaking about the contact details for the dataset that can be based on the configured owner.

Well, in fact you do not have to take-over the dataset to get information about the dataset parameters and refresh schedule.

Power BI REST API to the rescue

As mentioned above, you do not have to take-over the dataset. But how do we get those details available to us? With the PowerShell code snippets shared below, you can easily explore the applied configuration. These code snippets make use of the Power BI REST API, called based on the PowerShell cmdlets for Power BI.

The cmdlets help to easily authenticate to the Power BI service, using OAuth authentication. With that, it uses your credentials to get the information out of the dataset settings in the workspace you have access to. Also, the Power BI REST APIs can easily be called using the cmdlets.

I shared both code snippets in my GitHub library for Power BI Automation. Both require the Power BI cmdlets library to be installed up front. If you don’t have it yet, please see this documentation on how to install them on your computer.

Finally, both code snippets require you to specify two parameters. First the Workspace ID and secondly the Dataset ID. To get those variables, please go into the dataset settings of the dataset where you want to explore the dataset configuration. Then, you will find the Workspace ID as well as the Dataset ID specified in the URL like in the example below.

https://app.powerbi.com/groups/{workspace id}/settings/datasets/{dataset id}

Below the sample outputs for each script. As I might continue to update these scripts, I prefer to reference to my GitHub Library for Power BI Automation to get the latest version of the script.

Get dataset parameter values

The script for the dataset parameter values can be found in GitHub directly here. In order to get these details, the Power BI REST API for Get Parameters in Group is used. All specified parameters will be displayed in the PowerShell window in order among each other.

name            : ParameterName1
type            : Text
description     : 
isRequired      : True
currentValue    : CurrentParameterValue1
suggestedValues : {}


name            : ParameterName2
type            : Text
description     : 
isRequired      : True
currentValue    : CurrentParameterValue2
suggestedValues : {}

Get dataset refresh schedule

In order to get the details, the REST API to Get Refresh Schedule in Group is used. The refresh schedule is displayed in a single object in the PowerShell window. This includes the days where the schedule applies to, the time if specified, if it is enabled and notify options. The script can be found directly here.

@odata.context  : http://wabi-north-europe-c-primary-redirect.analysis.windows.net/v1.0/myorg/groups/{WorkspaceId}/$metad
                  ata#Microsoft.PowerBI.ServiceContracts.Api.V1.RefreshSchedule
days            : {Sunday, Monday, Tuesday, Wednesday...}
times           : {07:00}
enabled         : True
localTimeZoneId : UTC
notifyOption    : MailOnFailure

6 thoughts on “Insights in Power BI dataset parameters and refresh schedule

  1. Pingback: Getting Power BI Dataset Information without Takeover – Curated SQL

  2. SARAVANAN R

    Hi Marc,

    API are real safe guards for monitoring the Artifacts in PBI service.
    For additional control, I am in a need of getting the list os users for whom the report link is shared (External users receiving email), how to confirm whether these users received email or not. Is there any API to track it in PBI or Azure Portal ?.
    Could you please advise any workarounds for this.

    Thanks,
    Saravanan R

    Like

  3. Gerrit

    Hi Marc,

    Thanks for the blog post.

    How do I change the -url for calling the dataset settings via the API?
    I tried this but does not work.
    -Url “groups/$WorkspaceID/settings/datasets/$DatasetID” | ConvertFrom-Json

    Futhermore, I tried your github script for getting the refresh and that worked like a charm.
    It worked with PowerShell7, not with Windows PowerShell ISE. The error for the latter was: AADSTS50011: The reply URL specified in the request does not match the reply URLs configured for the application: ‘an application id’.
    Any thoughts?

    Like

    1. Hi Gerrit,

      Sorry to hear that it’s not working for you. The URL is the basic part of the Power BI REST API, being: “https://api.powerbi.com/v1.0/myorg/”
      I haven’t heard any others about this yet, but if it comes back more often I’ll try to find out what is causing it and update the script.

      –Marc

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s