How many times has my Power BI dataset been refreshed?

Have you ever wondered how many times your Power BI dataset has been refreshed? And if you are using Power BI Pro, you are limited to 8 refreshes a day.

This week’s blog post I am going to demonstrate how you can use the Power BI API to view all your data refreshes for a particular dataset.

If you need a recap here is a link to my previous blog post Using the Power BI API with PowerShell scripts, in which I demonstrated how to refresh your Power BI dataset using PowerShell and the Power BI API.

If you are looking for information on how to get the GroupID, DatasetID & ClientID, please refer to my previous blog post Using the Power BI API with PowerShell scripts

Example

For this particular example, I am busy working on a presentation for SQL Saturday Sydney (which I would recommend going to, if you are in Sydney on 07 July 2018)

Part of what I am doing is refreshing the data using an Azure Function, Microsoft Flow and Power Apps (Which will be another blog post), and I wanted to know how many times I have refreshed the dataset.

I need to know if I am going to hit my 8 times a day refresh limit when testing! This PowerShell Script and Power BI report came in handy as it allowed me to know if I was going to exceed my refresh limit.

As with all examples you can download the PowerShell file here: PUBLIC – Power BI Dataset Refresh with Output to CSV.ps1

NOTE: Before starting you will have to have updated the PowerShell script where YOU have put in your GroupID, DatasetID and ClientID

Updating PowerShell Script for Data Refreshes

The only actual change that I made was to put in the location of where I wanted to store my CSV File.

This is on line 93

Once I had updated the above to a folder and file location I ran the script.

Below is what it looked like once I opened the CSV File

It is interesting to see that it also tells you what Refresh Type was done. In this instance I was doing it via an Azure Function which was making the API call.

Power BI Report with output from CSV

What I then did was I saved the CSV file into my OneDrive for Business folder location. This enabled me to load the CSV file into an existing Power BI report.

As you can see below, I now know how many refreshes I have completed on my dataset.

Wrapping Up

I have demonstrated how I leveraged the Power BI APIs to download how many refreshes my dataset has had.

And then being able to report on this allowed me to be able to know how many refreshes I had left.

Once again here is the PowerShell Script that I used: PUBLIC – Power BI Dataset Refresh with Output to CSV.ps1

If there are any comments or suggestions, please leave them in the comments below.

Bonus – If you are interested in what the PowerShell Script is doing?

Below are additional details on how I got the data down from the Power BI API call

  • Lines 82 – 83
    • This is where I am creating the URI and sending it to the Power BI API
  • Line 85
    • This is where I am getting the output from the API Call and converting it to Json
  • Line 87
    • I am then converting it back from Json. This might seem a bit counterintuitive, but my logic is when converting it from Json I am then able to Query the Json.
  • Line 89
    • Because I am converting it from Json, I am now able to select specific Json properties.
    • On line 89 I am then selecting the “value” property, which is the data that is returned from the Power BI API call.
  • Line 91
    • I am selected the specific items from the “value” property
    • This has all the details that I require for my data refreshes.
  • Line 93
    • I am then exporting the data to a CSV file.

Using the Power BI API with PowerShell scripts – Refreshing your dataset

I know when I started trying to use the Power BI APIs, I was unsure of what I was doing, and how I could get it working.

Not only that, but I could see that as with building any cloud platform the APIs have to be there before they can put the skin on and make it easy to use.

There is always new features or functionality that is available via the Power BI APIs.

In this series of Blog posts, I am going to show how you can use additional features in Power BI by using the API and PowerShell.

Let me start by saying that I am no coding or scripting expert.

My goal in this blog post, is to show that using the PowerShell scripts can be fairly easy, once someone (me, choose ME) has shown you how to put in the required details.

Example

As always, I like to use an example, which I find makes it easier to follow.

In this example, I am going to be using the Power BI Refresh API to refresh a dataset using PowerShell.

My dataset will be “Fourmoo Google Analytics”

Getting the details required for the PowerShell Script

I got the details on how to refresh a Power BI Dataset from the following location below. Here it explains what the Power BI API expects to receive in order to refresh the dataset.

https://msdn.microsoft.com/en-us/library/mt784652.aspx

I then downloaded the PowerShell Script from here: HTTP

Would you believe that there are only 3 pieces of information required in order to update the PowerShell Script and get it running?

Below I will explain how to get this data and put it onto the PowerShell Script

Registering for a ClientID

The first step I had to do was to register for a ClientID

  • To do this I went to the following URL:
  • https://dev.powerbi.com/apps
  • If I was not logged in I would be prompted to log in with my Power BI Account.
  • I put in the following below in order to get the ClientID that I need in my PowerShell script.
  • I then clicked on Register App
  • Once that completes I then got my ClientID
    • It will be displayed in the ClientID section below, which I then copied and pasted and stored in a secure location.

GroupID

NOTE: Group is what it was called in Power BI before it was changed to App Workspaces. So when anything refers to Group, it is actually now referring to an App Workspace.

This is the Group where your dataset currently is in.

To find the GroupID you can complete the following steps below.

  • Log into the Power BI Service, and then I went to the App Workspace where I have the dataset that I want to refresh.
  • In my example I went into the “Site Analytics” Workspace
  • Then in the URL you will see that it will say groups, and the section immediately after that is the GroupID
    • As you can see above my GroupID starts with 0084
    • Make a note of the GroupID

DatasetID

Next is where I needed to find the Dataset ID, which I show in the steps below.

  • I made sure I was already in my Site Analytics App Workspace.
  • Then I clicked on Settings and then Settings and then Settings again
  • I then clicked on Datasets, and then clicked on the dataset that I want to refresh.
    • As in my example it was called “Fourmoo Google Analytics”
  • Now in the URL I now had my DatasetID which is which is after datasets, and highlighted as shown below.
  • I then made a note of the DatasetID

Updating the PowerShell Script

Now I am ready to update the PowerShell script with the details I got from above.

NOTE: The PowerShell script does have a lot of documentation in it, so if you get lost there is a lot of comments to assist you.

  • I opened the PowerShell ISE
  • I then opened my PowerShell script.
  • Now where it says $groupID I put in my GroupID that I had noted earlier.
  • Then where it said $datasetID, I put in my DatasetID that I had noted earlier.
  • The final piece is to put in the $clientID, which I had noted earlier
  • Then I saved the PowerShell Script file

Testing the PowerShell script and my dataset refresh

The final step is to now run the PowerShell script, ensure that it runs, and my dataset actually gets refreshed.

  • As you can see my last refresh is shown below
  • I then run the PowerShell Script.
  • I got a prompt asking for the App Permissions
    • I clicked Accept
  • Then I got the following result showing it was successfully executed
  • I could now see that it was In progress
  • And finally, it was successfully refreshed.

Conclusion

Whilst this has been a bit of a longer blog post, I do hope that you can see that if you follow the steps how you can leverage the Power BI APIs for your datasets.

I do plan on doing additional blog posts on the different Power BI APIs. If there are any that you are interested in, please leave a comment below.

And as I say in every blog post, please leave any comments or questions.

Link to the PowerShell Script here: ManageRefresh.ps1

Power BI – On-Premise Gateway Configuration steps to an Oracle Database

I was recently working in an environment where the requirement was to connect to an Oracle database using the On-Premise Gateway, so that we could either DirectQuery or import the data and then refresh it using the On-Premise Gateway. So the steps below detail how to complete this successfully.

The example below was connecting to an Oracle databased called TRID

(more…)