Real-time dashboard using Web API

bi-developer — Tue, 03 Apr 2018 23:15:18 GMT

Hello!

Recently, I've struggling with real-time dashboards using API.

What I want: to push data (which changes frequently) into datasets. Create dashboards where that data is updated automatically.

Scenario:

There is open web api about bitcoins: https://api.cryptonator.com/api/ticker/btc-usd

I want that data which is displayed in that api to be display in a dashboard, so that it is always showing the newest data. Is it possible to achieve that somehow?

What I did:

1. GET https://api.cryptonator.com/api/ticker/btc-usd

2. POST https://api.powerbi.com/v1.0/myorg/............

C# Console application

I've already went through authorization - it is all good. I've also posted the data into power bi dataset, however, it is static and there is no current data displayed. Only the data once I get it from with GET.

Does anyone know how to make it work if it is possible? Or how to achieve real-time dashboards?

Btw, I've used google a lot, yet did not find a sample code (C# or smth) to achieve that. I would really appreaciate an assistant!

Thanks in advance!

using System;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Net;
using System.IO;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Collections.Generic;

namespace walkthrough_push_data
{
    class Program
    {
        private static string token = string.Empty;

        static void Main(string[] args)
        {
            token = GetToken();

            CreateDataset();                  
            string datasetId = GetDataset();

            AddRows(datasetId, "Bitcoin");
        }

        #region Get an authentication access token
        private static string GetToken()
        {
            string clientID = "******************";
            string redirectUri = "https://login.live.com/oauth20_desktop.srf";
            string resourceUri = "https://analysis.windows.net/powerbi/api";
            string authorityUri = "https://login.windows.net/common/oauth2/authorize";
            AuthenticationContext authContext = new AuthenticationContext(authorityUri);
            string token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectUri)).AccessToken;

            Console.WriteLine(token);
            Console.ReadLine();

            return token;
        }
        #endregion

        #region Create a dataset in Power BI
        private static void CreateDataset()
        {
            string powerBIDatasetsApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets?defaultRetentionPolicy=basicFIFO";

            HttpWebRequest request = WebRequest.Create(powerBIDatasetsApiUrl) as HttpWebRequest;
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;
            request.ContentType = "application/json";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Create dataset JSON for POST request
            string datasetJson = "{\"name\": \"Test2\", \"tables\": " +
                "[{\"name\": \"Bitcoin\", \"columns\": " +
                "[{ \"name\": \"base\", \"dataType\": \"string\"}, " +
                "{ \"name\": \"target\", \"dataType\": \"string\"}, " +
                "{ \"name\": \"price\", \"dataType\": \"string\"}," +
                "{ \"name\": \"volume\", \"dataType\": \"string\"}," +
                "{ \"name\": \"change\", \"dataType\": \"string\"}" +
                "]}]}";

            //POST web request
            byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(datasetJson);
            request.ContentLength = byteArray.Length;
            using (Stream writer = request.GetRequestStream())
            {
                writer.Write(byteArray, 0, byteArray.Length);
                var response = (HttpWebResponse)request.GetResponse();
                Console.WriteLine(string.Format("Dataset {0}", response.StatusCode.ToString()));
                Console.ReadLine();
            }
        }
        #endregion

        #region Get a dataset to add rows into a Power BI table
        private static string GetDataset()
        {
            string powerBIDatasetsApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets";
            HttpWebRequest request = WebRequest.Create(powerBIDatasetsApiUrl) as HttpWebRequest;
            request.KeepAlive = true;
            request.Method = "GET";
            request.ContentLength = 0;
            request.ContentType = "application/json";
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            string datasetId = string.Empty;

            using (HttpWebResponse httpResponse = request.GetResponse() as HttpWebResponse)
            {
                using (StreamReader reader = new StreamReader(httpResponse.GetResponseStream()))
                {
                    string responseContent = reader.ReadToEnd();
                    var results = JsonConvert.DeserializeObject<dynamic>(responseContent);
                    datasetId = results["value"][0]["id"];

                    Console.WriteLine(String.Format("Dataset ID: {0}", datasetId));
                    Console.ReadLine();

                    return datasetId;
                }
            }
        }
        #endregion

        #region Add rows to a Power BI table
        private static string AddRows(string datasetId, string tableName)
        {
            string powerBIApiAddRowsUrl = String.Format("https://api.powerbi.com/v1.0/myorg/datasets/{0}/tables/{1}/rows", "**********************", tableName);

            HttpWebRequest request = WebRequest.Create(powerBIApiAddRowsUrl) as HttpWebRequest;
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;
            request.ContentType = "application/json";
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            string rowsJson = "{\"rows\":" + "[" + GetJson() + "]}";

            byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(rowsJson);
            request.ContentLength = byteArray.Length;

            using (Stream writer = request.GetRequestStream())
            {
                writer.Write(byteArray, 0, byteArray.Length);
                var response = (HttpWebResponse)request.GetResponse();

                Console.WriteLine("Rows Added");
                Console.ReadLine();

                return response.ToString();
            }
        }
        #endregion

        private static string GetJson()
        {
            string url = "https://api.cryptonator.com/api/ticker/btc-usd"; // url pabandymui
            HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
            request.KeepAlive = true;
            request.Method = "GET";
            request.Accept = "application/json";

            using (HttpWebResponse httpResponse = request.GetResponse() as HttpWebResponse)
            {
                using (StreamReader reader = new StreamReader(httpResponse.GetResponseStream()))
                {
                    string responseContent = reader.ReadToEnd();

                    var results = JsonConvert.DeserializeObject<dynamic>(responseContent);
                    var json = JsonConvert.SerializeObject(results.ticker);

                    return json;
                }
            }
        }
    }
}

Re: Real-time dashboard using Web API

v-micsh-msft — Mon, 09 Apr 2018 06:20:43 GMT

Hi,

If you mean the real-time streaming in Power BI, then please check:

Real-time streaming in Power BI

Regards,

Michael

Re: Real-time dashboard using Web API

bi-developer — Mon, 09 Apr 2018 10:42:13 GMT

Hi,

thank you:)

Our aim is to create real-time streaming with push dataset. The the question is: how?

Thanks in advance!

Re: Real-time dashboard using Web API

Anonymous — Tue, 09 Oct 2018 21:29:50 GMT

I have same requirement. Did you find any solution?

Re: Real-time dashboard using Web API

TedPattison — Tue, 09 Oct 2018 21:53:12 GMT

You cannot create a real-time dashboard by making a single call into the Power BI Service API to create rows. Instead, you must call into the Power BI Service API any time the underlying data changes. This means you must find a way to periodcally trigger the code that retrieves the latest data and updates the dataset in Power BI

For example, you could create an Azure function with a schedule that executes once a minute. When this Azure function executes, it would call to the bitcoins API to get the latest data and then call to the Power BI Service API to push the changes into your dataset.

It's easier to create a realt-time dashboard if the API you are using to get the data provides some callback mechanism such as a webhook. If yoy can register a webhook, you don't have to periodically poll the API to detect changes because the webhook callback can trigger the code which gets the latest values and updates the real-time dataset in Power BI.

topic Re: Real-time dashboard using Web API in Developer

Real-time dashboard using Web API

Re: Real-time dashboard using Web API

Re: Real-time dashboard using Web API

Re: Real-time dashboard using Web API

Re: Real-time dashboard using Web API