{"id":18,"date":"2020-01-31T14:47:00","date_gmt":"2020-01-31T14:47:00","guid":{"rendered":"http:\/\/daxter.nl\/?p=18"},"modified":"2020-09-11T19:38:17","modified_gmt":"2020-09-11T18:38:17","slug":"unlimited-data-refresh-in-power-bi-using-the-rest-api-and-an-azure-function","status":"publish","type":"post","link":"http:\/\/daxter.nl\/en\/unlimited-data-refresh-in-power-bi-using-the-rest-api-and-an-azure-function\/","title":{"rendered":"Unlimited data refresh in Power BI using the REST API and an Azure Function"},"content":{"rendered":"\n<p>In the past few weeks, a lot of articles pop up about the&nbsp;<a href=\"https:\/\/powerbi.microsoft.com\/en-us\/blog\/refresh-your-power-bi-dataset-using-microsoft-flow\/\" target=\"_blank\" rel=\"noreferrer noopener\">Power BI Connector<\/a>&nbsp;in Power Automate, formerly known as Flow. A user-friendly solution that gives you, or your users, the ability to refresh a dataset with a single click from a PowerApp, when an event triggers or even with a&nbsp;<a href=\"https:\/\/bielite.com\/blog\/refreshing-a-power-bi-dataset-by-button-click-with-power-automate\/\" target=\"_blank\" rel=\"noreferrer noopener\">click on a button<\/a>&nbsp;within a Power BI report! A nice solution, without the need to write a single line of code. But, just like the scheduled refreshes configured in the Power BI Service, you\u2019re limited to refresh your data only eight times a day. But what if a user clicks the refresh button eight times in the morning? Or your event suddenly gets triggered more often than you expected? Unfortunately, then you\u2019re out of luck, out of refreshes.<\/p>\n\n\n\n<p>Luckily, there is a solution. Not only for the problem mentioned above, but for all the times when eight refreshes are just not enough. The Power BI connector is a \u2018just\u2019 a wrapper around the Power BI REST API, and that is where the solution lies: in this blog post I\u2019ll show you how you can refresh your dataset as often as you like, by making smart use of the API and an Azure Function.<\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p>So what\u2019s the magic trick? Instead of refreshing the dataset after eight times, we\u2019re going to download the dataset and publish it under a different name. That way, the \u2018refresh counter\u2019 gets reset and we\u2019re able to continue refreshing. After publishing the dataset, we\u2019ll rebind the original report to the newly published dataset, so your user or application won\u2019t notice the change.<\/p>\n\n\n\n<p>By the way, this method isn\u2019t new! Several (desktop) apps can do this. However, this blog I\u2019ll show you the principles, so you can build it your own. And, for free! We will be using Azure Functions to execute the API requests, and up to one million executions, you won\u2019t be charged.<\/p>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" width=\"1000\" height=\"317\" src=\"http:\/\/daxter.nl\/wp-content\/uploads\/2020\/08\/1_CHZ0g64IMSTqwCtQdL6lZQ.png\" alt=\"Image for post\" class=\"wp-image-19\" srcset=\"http:\/\/daxter.nl\/wp-content\/uploads\/2020\/08\/1_CHZ0g64IMSTqwCtQdL6lZQ.png 1000w, http:\/\/daxter.nl\/wp-content\/uploads\/2020\/08\/1_CHZ0g64IMSTqwCtQdL6lZQ-300x95.png 300w, http:\/\/daxter.nl\/wp-content\/uploads\/2020\/08\/1_CHZ0g64IMSTqwCtQdL6lZQ-768x243.png 768w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/figure><\/div>\n\n\n\n<h1 id=\"9cf3\">Prerequisites<\/h1>\n\n\n\n<ul><li><strong>Register your app in Azure AD<\/strong><br>Make sure you can access the Power BI Rest API. You must register your app within the Azure AD, and get an access code. Microsoft released howto guides for these topics and can be found&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/developer\/walkthrough-push-data\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>. You\u2019ll need steps 1 and 2.<\/li><li><strong>Create an Azure Function<\/strong><br>We\u2019ll be programming in C# (.NET Core). A tutorial can be found&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-functions\/functions-create-your-first-function-visual-studio\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/li><li><strong>Install&nbsp;<\/strong><a href=\"https:\/\/docs.microsoft.com\/en-us\/nuget\/quickstart\/install-and-use-a-package-in-visual-studio\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>Nuget package<\/strong><\/a><strong>&nbsp;Microsoft.PowerBI.Api<\/strong><br>We\u2019re not going to write our own API request, there\u2019s a package that can handle this for us.&nbsp;<a href=\"https:\/\/github.com\/microsoft\/PowerBI-CSharp\" target=\"_blank\" rel=\"noreferrer noopener\">More info<\/a>.<br>In the console:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">PM&gt; Install-Package Microsoft.PowerBI.Api<\/pre>\n\n\n\n<h1 id=\"3a32\">Steps<\/h1>\n\n\n\n<p>First, we need to initiate a new PowerBI client.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">var client = new PowerBIClient(tokenCredentials)<\/pre>\n\n\n\n<p>To download the report, we must obtain the&nbsp;<em>groupId&nbsp;<\/em>and&nbsp;<em>reportId<\/em>. You can find this id in the URL field of your browser, after opening the report on PowerBI.com.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">var group_id = \"{YOUR WORKSPACE\/GROUP ID}\";\nvar original_report_id = \"{YOUR REPORT ID}\";\nvar report_stream = \nclient.Reports.ExportReportInGroup(groupid, <strong>original_report_id<\/strong>);<\/pre>\n\n\n\n<p>After downloading the report, we can publish it in the same workspace as the original report. Choose a different name! In the example, I\u2019ve added a datetime stamp, so it\u2019s easy to track when the report (and dataset) was published. Publish the report with the&nbsp;<em>PostImport&nbsp;<\/em>call.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">var import = <br>await client.Imports.PostImportWithFileAsyncInGroup(group_id, <strong>report_stream<\/strong>, {NAME OF YOUR REPORT} + DateTime.Now.ToString(\u201cMMddyyyyHHmmss\u201d));<\/pre>\n\n\n\n<p>The upload task might take a while, you can check the status by calling&nbsp;<em>GetImportById<\/em>, using the id returned from the previous call:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import_status = <br>await client.Imports.GetImportByIdInGroupAsync(group_id, <strong>import.Id<\/strong>)<\/pre>\n\n\n\n<p>If the import was succesfull, there will be two new items in the Power BI service: a report and an associated dataset. The next step is to connect the original report to the new dataset. This is done with the&nbsp;<em>rebind&nbsp;<\/em>call:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">var dataset_new = import_status.Datasets[0].Id;client.Reports.RebindReportInGroup(group_id, <strong>original_report_id<\/strong>, new RebindReportRequest(<strong>dataset_new<\/strong>));<\/pre>\n\n\n\n<p>After rebinding, you can trigger the data refresh for the new dataset:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">await client.Datasets.RefreshDatasetInGroupAsync(group_id, <strong>dataset_new<\/strong>);<\/pre>\n\n\n\n<p>At last, the&nbsp;<strong>original dataset&nbsp;<\/strong>and the&nbsp;<strong>new report<\/strong>&nbsp;can be deleted. Make sure you delete the correct ones!<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">var delete_new_report = <br>client.Reports.DeleteReportInGroup(groupid, import_status.Reports.FirstOrDefault&lt;Report&gt;().Id);var delete_old_dataset = <br>client.Datasets.DeleteDatasetByIdInGroup(group_id, original_report.DatasetId);<\/pre>\n\n\n\n<p><strong>That\u2019s it!<\/strong><\/p>\n\n\n\n<h1 id=\"d04d\">Code<\/h1>\n\n\n\n<script src=\"https:\/\/gist.github.com\/GhandiHarder\/95c075b7741fd72da53ded351f33ab80.js\"><\/script>\n\n\n\n<blockquote class=\"wp-block-quote\"><p>Side note: I\u2019m not a professional programmer, so pardon my lack of naming conventions and other best practices. If you have suggestions on how to optimize the code, better async \/ stream handling, I\u2019m happy to hear from you!<\/p><\/blockquote>\n\n\n\n<h3>Notes, tips,&nbsp;tricks<\/h3>\n\n\n\n<ul><li>If you have multiple reports that use the dataset, you must rebind them all to the new dataset. You can use <em>GetReportInGroup()<\/em> to list all the reports in the workspace. Use LINQ to find a specific report (for example, the base report), or to exclude a report (for example, the Usage Statistics):<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">List&lt;Report&gt;().Where(x =&gt; x.Name != \"Usage Statistics\")<\/pre>\n\n\n\n<ul><li>You can add parameters (for example: <em>reportId <\/em>) to the Azure Function, in order to build a generic refresh function.<\/li><li>Use <em>log.LogInformation() <\/em>to write to the Azure Function Log output, so you can monitor the execution.<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">log.LogInformation(DateTime.Now + \u201c: Delete temporary report\u201d);<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/cdn-images-1.medium.com\/max\/1200\/1*J2vkB8qIHXYzSri5xnKyEA.png\" alt=\"\"\/><figcaption>Function Logging<\/figcaption><\/figure>\n\n\n\n<ul><li>You don\u2019t have to download (and publish) the report every time you want to trigger a data refresh; after each upload, you can refresh the dataset eight times. You can check the refresh count with GetRefreshHistoryInGroup(). If you\u2019re out of refreshes, you upload a new dataset, otherwise just refresh the dataset.<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">var check_refreshes = client.Datasets.GetRefreshHistoryInGroup(groupid, dataset_id);<\/pre>\n\n\n\n<ul><li>You can replace the Power BI Connector in Power Automate with a HTTP request to your function, or you can use a <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-functions\/functions-bindings-timer?tabs=csharp\" rel=\"noreferrer noopener\" target=\"_blank\">TimerTrigger<\/a> to trigger the refresh. I\u2019ve added the Function at the end of a Data Factory Pipeline, to trigger the refresh immediately after the new data arrives.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/cdn-images-1.medium.com\/max\/800\/1*b7KXyWpfBozyK5t7vB-27g.png\" alt=\"\"\/><\/figure>\n\n\n\n<ul><li>And, needless to say; the code is not production ready. Please add some security and error handling.<\/li><\/ul>\n\n\n\n<h3>Happy refreshing&nbsp;\ud83d\ude09<\/h3>\n\n\n\n<p><em>Thanks for reading! If you have any complaints or suggestions (both technical and\/or about my writing skills), please let me know!<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the past few weeks, a lot of articles pop up about the&nbsp;Power BI Connector&nbsp;in Power Automate, formerly known as Flow. A user-friendly solution that gives you, or your users, the ability to refresh a dataset with a single click from a PowerApp, when an event triggers or even with a&nbsp;click on a button&nbsp;within a&hellip;&nbsp;<a href=\"http:\/\/daxter.nl\/en\/unlimited-data-refresh-in-power-bi-using-the-rest-api-and-an-azure-function\/\" class=\"\" rel=\"bookmark\">Read More &raquo;<span class=\"screen-reader-text\">Unlimited data refresh in Power BI using the REST API and an Azure Function<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"off","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":""},"categories":[1],"tags":[4,3,2],"_links":{"self":[{"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/posts\/18"}],"collection":[{"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/comments?post=18"}],"version-history":[{"count":5,"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/posts\/18\/revisions"}],"predecessor-version":[{"id":97,"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/posts\/18\/revisions\/97"}],"wp:attachment":[{"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/media?parent=18"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/categories?post=18"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/daxter.nl\/en\/wp-json\/wp\/v2\/tags?post=18"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}