7 January 2016

Real-Time Data to a Power BI Dashboard

Nowadays, the search for information for a personal or a business life, wants to instantly form and if possible without interaction from users.

I will talk about the integration of data with the Power BI in real time.

Through the recently launched Power BI Rest API, you can upgrade to a Dashboard in real time information whenever our data source suffers changes. Any programming language with REST support integrate with this API.

Despite the limitations that this API has, it is possible to use some of the most important features, including:

  • Authentication with Azure Active Directory OAuth2
  • Create/Remove datasets
  • Add/Remove data

The figure below describes the behavior of an application for Power BI:

Fonte – https://msdn.microsoft.com/en-us/library/dn877544.aspx

Para este exemplo foi criada uma aplicação consola em C#, que envia continuamente os dados de um computador local para o PowerBI. Para a interacção com a API os pedidos JSON são enviados através do protocolo HTTP.

Para podermos aceder à Power Bi Rest API, é necessário autenticarmos a nossa aplicação na Azure AD, previamente criada com as configurações necessárias.

(Fonte: https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-create-an-azure-active-directory-tenant/)

Authentication with Azure Active Directory (OAuth2)

The authentication method used was:

private TokenSingleton()

        {

            try

            {

                //Get access token:

                // To call a Power BI REST operation, create an instance of AuthenticationContext and call AcquireToken

                // AuthenticationContext is part of the Active Directory Authentication Library NuGet package

                // To install the Active Directory Authentication Library NuGet package in Visual Studio,

                //  run “Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory” from the nuget Package Manager Console.

                //Resource Uri for Power BI API

                // OAuth2 authority Uri = https://analysis.windows.net/powerbi/api

                string resourceUri = ConfigurationManager.AppSettings[nsCom.Consts.ResourceUri];

                // ClientID provided from Azure AD Application

                string clientId = ConfigurationManager.AppSettings[nsCom.Consts.ClientID];

                // Create an instance of AuthenticationContext to acquire an Azure access token

                // OAuth2 authority Uri = https://login.windows.net/common/oauth2/authorize

                string authorityUri = ConfigurationManager.AppSettings[nsCom.Consts.AuthorityUri]; ;

                AuthenticationContext authContext = new AuthenticationContext(authorityUri);

                UserCredential UserCredential = new UserCredential(ConfigurationManager.AppSettings[nsCom.Consts.Username], ConfigurationManager.AppSettings[nsCom.Consts.Password]);

                // Call AcquireToken to get an Azure token from Azure Active Directory token issuance endpoint

                //  AcquireToken takes a Client Id that Azure AD creates when you register your client app.

                //  To learn how to register a client app and get a Client ID, see https://msdn.microsoft.com/en-US/library/dn877542(Azure.100).aspx

                 token = authContext.AcquireToken(resourceUri, clientId, UserCredential);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

The token is then used for future calls to the API.

Dataset Creation

The next step is to create the dataset that will make up the Dashboard. The types that can be used for the columns of the tables are: int64, bool, DateTime, string and double. For creation of the dataset schema is necessary to serialize the object into JSON and send to PowerBI:

     /// <summary>

        /// Creates a dataset based on a DatasetSchema.

        /// </summary>

        /// <param name=”Schema”>Dataset Schema represents the definition of dataset including dataset name, tables and columns for each table.</param>

        /// <returns>Created dataset as .NET object.</returns>

        public static dataset GetDataset()

        {

            try

            {

                           // DatasetName = DataSet

                DataSet dataset = new DataSet() { name = ConfigurationManager.AppSettings[nsCom.Consts.DatasetName]};

                dataset.tables =new Table[1];

                dataset.tables[0] = new Table() { name = “IntGeneratorTable” };

                dataset.tables[0].columns = new Column[2];

                dataset.tables[0].columns[0] = new Column() { name = “DateTime”, dataType = “DateTime” };

                dataset.tables[0].columns[1] = new Column() { name = “Int”, dataType = “Int64” }

         // LIB used to serialize objects to JSON

                JavaScriptSerializer jsonSerializer = new JavaScriptSerializer();

                var str = jsonSerializer.Serialize(dataset);

         //Power BI Datasets Url

                string powerBIApiUrl = “https://api.powerbi.com/v1.0/myorg/datasets“;

                byte[] byteArray = Encoding.UTF8.GetBytes(str);

                HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiUrl) as System.Net.HttpWebRequest;

                request.KeepAlive = true;

                request.Method = “POST”;

                request.ContentLength = byteArray.Length;

                request.ContentType = “application/json”;

                //Add access token to Request header

                request.Headers.Add(“Authorization”, String.Format(“Bearer {0}”, nsToken.TokenSingleton.Instance.token.AccessToken));

                // Get the request stream.

                Stream dataStream = request.GetRequestStream();

                // Write the data to the request stream.

                dataStream.Write(byteArray, 0, byteArray.Length);

                // Close the Stream object.

                dataStream.Close();

                // Get the response.

                //Get HttpWebResponse from GET request

                using (HttpWebResponse httpResponse = request.GetResponse() as System.Net.HttpWebResponse)

                {

                    //Get StreamReader that holds the response stream

                    using (StreamReader reader = new System.IO.StreamReader(httpResponse.GetResponseStream()))

                    {

                        string responseContent = reader.ReadToEnd();

                        jsonSerializer = new JavaScriptSerializer();

                        return (dataset)jsonSerializer.Deserialize(responseContent, typeof(dataset));

                    }

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

This method created a table with an integer column and a date that will enable data storage. In the HTTP request response is possible to get the dataset ID that will allow us to access the dataset later (for editing operations / deletion of data).

In addition to creating a dataset also we are allowed to remove and get a dataset previously created.

Add Data

Finally, with the dataset created, is now possible to add data. A test case that CPU sends data continuously to the BI Power was created. Once again an object is created in C #, and is subsequently serialized JSON object, and sent by HTTP Request.

public static void SendData(dataset_dataset)

        {

            string powerBIApiUrl = String.Format(“https://api.powerbi.com/v1.0/myorg/datasets/{0}/tables/{1}/rows”, _dataset.Id, “IntGeneratorTable”);

            while (1 == 1)

            {

         // Data Generated

                PerformanceCounter cpuCounter = new PerformanceCounter();

                cpuCounter.CategoryName = “Processor”;

                cpuCounter.CounterName = “% Processor Time”;

                cpuCounter.InstanceName = “_Total”;

                // will always start at 0

                dynamic firstValue = cpuCounter.NextValue();

                System.Threading.Thread.Sleep(1000);

                // now matches task manager reading

                dynamic secondValue = cpuCounter.NextValue();

                          // My object

                TableRows rows = new TableRows();

                rows.rows = new ArrayList();

                rows.rows.Add(new IntGeneratorTable() { DateTime = DateTime.Now, Int = Convert.ToInt32(secondValue) });

                JavaScriptSerializer JavaScriptSerializer = new JavaScriptSerializer();

                var json = JavaScriptSerializer.Serialize(rows).ToString();

                byte[] byteArray = Encoding.UTF8.GetBytes(json);

                HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiUrl) as System.Net.HttpWebRequest;

                request.KeepAlive = true;

                request.Method = “POST”;

                request.ContentLength = byteArray.Length;

                request.ContentType = “application/json”;

                request.Headers.Add(“Authorization”, String.Format(“Bearer {0}”, nsToken.TokenSingleton.Instance.token.AccessToken));

                // Get the request stream.

                Stream dataStream = request.GetRequestStream();

                // Write the data to the request stream.

                dataStream.Write(byteArray, 0, byteArray.Length);

                // Close the Stream object.

                dataStream.Close();

                // Get the response.

                //Get HttpWebResponse from GET request

                using (HttpWebResponse httpResponse = request.GetResponse() as System.Net.HttpWebResponse)

                {

                    //Get StreamReader that holds the response stream

                    using (StreamReader reader = new System.IO.StreamReader(httpResponse.GetResponseStream()))

                    {

                        string responseContent = reader.ReadToEnd();

                    }

                }

            }

        }

So, if the data is sent correctly you can check the Dashboard being updated automatically:

References

 

.

.

.

.

       David Almas
Software Engineering
Blog