[Post Invitado] Trabajando con conjuntos de datos en Power BI

En este artículo, Javier Fernández Corres, consultor de ilitia Technologies, te explica paso a paso cómo crear un conjunto de datos personalizado en Power BI para explotar después su información.

Como ejemplo, vamos a extraer la información de una colección de sitios de SharePoint Online, la añadiremos a un conjunto de datos y la mostraremos en una tabla en un informe de Power BI.

Para extraer la información y añadirla al conjunto de datos, debemos crear una aplicación de consola en Visual Studio y seguir los siguientes pasos:

1. Registrando la aplicación

2. Creación de un conjunto de datos

3. Añadiendo información al conjunto de datos

4. Explotando la información

1. Registrando la aplicación

Para poder acceder a nuestro entorno de Power BI y realizar las distintas operaciones con conjuntos de datos, es necesario registrar la aplicación en Azure AD.. Al realizar el registro obtendremos un id. de cliente, (Sin un id. de cliente, Azure AD no puede autenticar la aplicación de consola) que será el que utilicemos en nuestra aplicación de consola para crear el conjunto de datos y añadir la información.

Para ello accedemos a la herramienta de registro de aplicaciones del centro de desarrollo de Power BI:

https://dev.powerbi.com/apps

En primer lugar, insertamos nuestro usuario y contraseña.

En segundo lugar, completamos la información de nuestra aplicación:

· App Name, escribimos un nombre. Por ejemplo, “SharePoint Online Info”.

· App Type, seleccionamos Native app

· Redirect Url. El URI estándar para una aplicación cliente es:

https://login.live.com/oauth20\_desktop.srf

En tercer lugar, seleccionamos los permisos de la aplicación. En nuestro caso vamos a seleccionar Read and Write All Datasets.

Por último, pulsamos el botón Register App y obtendremos nuestro id. de cliente, que utilizaremos posteriormente en nuestra aplicación de consola.

2. Creación de un conjunto de datos

Como primer paso, creamos una aplicación de consola en Visual Studio 2015.

Una vez hecho esto, instalamos la librería de autenticación de Directorio Activo (ADAL, https://www.nuget.org/packages/Microsoft.IdentityModel.Clients.ActiveDirectory). Para ello, seleccionamos Herramientas > Administrador de paquetes de NuGet > Consola de Administrador de Paquetes y escribimos lo siguiente:

Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612

Antes de crear nuestro conjunto de datos, necesitamos obtener un token de acceso de autenticación empleando el siguiente método, que recibirá como parámetro el id de cliente obtenido anteriormente.

public 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; return token; }

Para la creación del conjunto de datos haremos una llamada REST a la siguiente dirección, enviando una cadena JSON. La dirección URL que identifica un conjunto de datos es:

https://api.powerbi.com/v1.0/myorg/datasets

Si se va a crear dentro de un grupo, la dirección será:

https://api.PowerBI.com/v1.0/myorg/groups/{group_id}/datasets

Antes de nada, añadiremos las siguientes referencias a nuestra aplicación de consola:

 
using System.IO;
using System.Net;
using Microsoft.IdentityModel.Clients.ActiveDirectory;

Vamos a utilizar el siguiente método para crear el conjunto de datos:

 
public static string CreateDataset(string token, string json)
{

string statusCode = string.Empty;

string powerBIDatasetsApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets";

//Para grupos: https://api.PowerBI.com/v1.0/myorg/groups/{group_id}/datasets

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

request.KeepAlive = true;

request.Method = "POST";

request.ContentLength = 0;

request.ContentType = "application/json";

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

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

request.ContentLength = byteArray.Length;

using (Stream writer = request.GetRequestStream())

{

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

var response = (HttpWebResponse)request.GetResponse();

statusCode = response.StatusCode.ToString();

}

return statusCode;

}

Añadimos lo siguiente al método Main de nuestra aplicación de consola:

 
string token = GetToken(clientID);

Console.WriteLine("Creating DataSet");

string json = "{\"name\": \"SiteCollection\", \"tables\": " +

"[{\"name\": \"Info\", \"columns\": " +

"[{ \"name\": \"Title\", \"dataType\": \"string\"}, " +

"{ \"name\": \"Description\", \"dataType\": \"string\"}," +

"{ \"name\": \"Created\", \"dataType\": \"DateTime\"}," +

"{ \"name\": \"Size\", \"dataType\": \"Int64\"}" +

"]}]}";

string statusCode = CreateDataset(token, json);

Console.WriteLine(string.Format("Dataset {0}", statusCode));

Console.ReadLine();

Para poder extraer la información de la colección de sitios, vamos a crear un conjunto de datos “SiteCollection”, con una tabla “Info” y las columnas título, descripción, fecha de creación y tamaño en bytes.

Ejecutamos la aplicación. Nos va a pedir permisos para leer y escribir conjuntos de datos, y para iniciar sesión y leer nuestro perfil.

Aceptamos y el conjunto de datos se creará en Power BI.

3. Añadiendo información al conjunto de datos

Vamos a añadir a nuestro conjunto de datos la información de una colección de sitios de SharePoint Online.

Añadimos la siguiente referencia:

 
using Microsoft.SharePoint.Client;

En primer lugar, tenemos que obtener la información y para ello necesitaremos el siguiente método:

 
public static SiteInfo GetSiteInfo(string siteUrl, string userName, string password)

{

SiteInfo info = new SiteInfo();

using (var ctx = new ClientContext(siteUrl))

{

ctx.RequestTimeout = 1000000;

ctx.Credentials = new SharePointOnlineCredentials(userName, ToSecureString(password));

Site site = ctx.Site;

ctx.Load(site, s => s.RootWeb.Title, s => s.RootWeb.Description, s => s.RootWeb.Created, s => s.Usage);

ctx.ExecuteQuery();

info.Title = site.RootWeb.Title;

info.Description = site.RootWeb.Description;

info.Created = site.RootWeb.Created;

info.Size = site.Usage.Storage;

}

return info;

}

public static SecureString ToSecureString(string plainString)

{

var secureString = new SecureString();

foreach (char c in plainString)

{

secureString.AppendChar(c);

}

return secureString;

}

Previamente hemos definido la clase SiteInfo:

 
public class SiteInfo

{

public string Title { get; set; }

public string Description { get; set; }

public DateTime Created { get; set; }

public long Size { get; set; }

}

Para la añadir filas a un conjunto de datos haremos una llamada REST a la siguiente dirección, enviando una cadena JSON. La dirección URL que identifica un conjunto de datos es:

https://api.powerbi.com/v1.0/myorg/datasets/{dataset_id}/tables/{table_name}/rows

Si se va a insertar datos dentro de un grupo, la dirección será:

https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/tables/{table_name}/rows

Antes de añadir filas a nuestro conjunto de datos, necesitaremos obtenerlo. Para eso utilizaremos el siguiente método:

private static dataset[] GetDatasets(string token) { string powerBIApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets"; //Para grupos: https://api.PowerBI.com/v1.0/myorg/groups/{group_id}/datasets HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiUrl) as System.Net.HttpWebRequest; request.KeepAlive = true; request.Method = "GET"; request.ContentLength = 0; request.ContentType = "application/json"; request.Headers.Add("Authorization", String.Format("Bearer {0}", token)); using (HttpWebResponse httpResponse = request.GetResponse() as System.Net.HttpWebResponse) { using (StreamReader reader = new System.IO.StreamReader(httpResponse.GetResponseStream())) { string responseContent = reader.ReadToEnd(); JavaScriptSerializer jsonSerializer = new JavaScriptSerializer(); Datasets datasets = (Datasets)jsonSerializer.Deserialize(responseContent, typeof(Datasets)); return datasets.value; } } }

Y las siguientes clases:

public class Datasets { public dataset[] value { get; set; } } public class dataset { public string Id { get; set; } public string Name { get; set; } }

Vamos a utilizar el siguiente método para añadir filas al conjunto de datos:

public static void AddRows(string token, string json, string datasetName, string tableName) { dataset[] datasets = GetDatasets(token); string datasetId = (from d in datasets where d.Name == datasetName select d).FirstOrDefault().Id; string powerBIApiUrl = String.Format("https://api.powerbi.com/v1.0/myorg/datasets/{0}/tables/{1}/rows", datasetId, tableName); //Para grupos: https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/tables/{table_name}/rows HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiUrl) as System.Net.HttpWebRequest; request.KeepAlive = true; request.Method = "POST"; request.ContentLength = 0; request.ContentType = "application/json"; request.Headers.Add("Authorization", String.Format("Bearer {0}", token)); byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(json); request.ContentLength = byteArray.Length; using (Stream writer = request.GetRequestStream()) { writer.Write(byteArray, 0, byteArray.Length); } }

Añadimos lo siguiente al método Main de nuestra aplicación de consola. Vamos a añadir a nuestro conjunto de datos la información de dos colecciones de sitio de SharePoint Online.

string clientID = "aa5b6616-8c17-45ba-9d8c-8f89c78f2de9"; string token = GetToken(clientID); Console.WriteLine("Adding Rows"); SiteInfo info = SharePointOnline.GetSiteInfo("https://ilitia.sharepoint.com", "xxx@ilitia.com", "xxx"); SiteInfo info2 = SharePointOnline.GetSiteInfo("https://ilitia.sharepoint.com/sites/dev", "xxx @ilitia.com", "xxx"); string row1 = "{"Title":"" + info.Title + "","Description":"" + info.Description + "","Created":"" + info.Created.ToString("MM/dd/yyyy") + "","Size":" + info.Size + "}"; string row2 = "{"Title":"" + info2.Title + "","Description":"" + info2.Description + "","Created":"" + info2.Created.ToString("MM/dd/yyyy") + "","Size":" + info2.Size + "}"; string json = "{"rows":[" + row1 + "," + row2 + "]}"; string datasetName = "SiteCollection"; string tableName = "Info"; AddRows(token, json, datasetName, tableName); Console.WriteLine("Rows Added"); Console.ReadLine();

Si accedemos a nuestro sitio de Power BI, al conjunto de datos “SiteCollection”, seleccionamos las columnas de la tabla Info y el resultado obtenido es el siguiente:

Si quisiéramos eliminar las filas del conjunto de datos, tendríamos que ejecutar el método siguiente:

public static void DeleteRows(string token, string datasetName, string tableName) { dataset[] datasets = GetDatasets(token); string datasetId = (from d in datasets where d.Name == datasetName select d).FirstOrDefault().Id; string powerBIApiUrl = String.Format("https://api.powerbi.com/v1.0/myorg/datasets/{0}/tables/{1}/Rows", datasetId, tableName); //Para grupos: https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/tables/{table_name}/rows HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiUrl) as System.Net.HttpWebRequest; request.KeepAlive = true; request.Method = "DELETE"; request.ContentLength = 0; request.ContentType = "application/json"; request.Headers.Add("Authorization", String.Format("Bearer {0}", token)); using (HttpWebResponse httpResponse = request.GetResponse() as System.Net.HttpWebResponse) { using (StreamReader reader = new System.IO.StreamReader(httpResponse.GetResponseStream())) { string responseContent = reader.ReadToEnd(); } } }

4. Explotando la información

Ahora vamos a explotar la información de nuestro conjunto de datos desde Power BI Desktop.

Abrimos Power BI Desktop y accedemos a las opciones:

Seleccionamos “Características de la versión preliminar” y marcamos la opción “Conexión dinámica al servicio Power BI”:

A continuación, vamos a Obtener datos y seleccionamos la opción “Servicio de Power BI” en la sección “Servicios en línea”.

Seleccionamos nuestra área de trabajo o grupo y cargamos nuestro conjunto de datos:

Ya podemos pintar nuestro informe:

5. Conclusión

En este artículo hemos visto cómo crear un conjunto de datos de personalizado y cómo llenarlo de información, para explotarlo después desde nuestros informes de Power BI. Para el ejemplo hemos obtenido la información de nuestro sitio de SharePoint Online. Veremos en próximos posts cómo obtener esta información directamente desde Power BI, utilizando la API de REST de SharePoint.