Creating a Power BI report using MVC Web API

Here we will see how we can create MVC Web API and use it in Power BI.  However, before that a small introduction on Power BI.  `Power BI is a reporting tool available in windows version and web version. Using windows version you can create reports and then publish it to cloud environment of power BI and can be viewed in web version Power BI’.  URL : https://powerbi.microsoft.com/en-us/

First, we should have a Northwind SQL database, In this case I am using SampleDB database with five tables:

Create a MVC Web API project as below steps

Open Visual Studio 2013 Professional Edition – File – New Project – Templates Web Visual Studio 2012 – Asp.Net MVC 4 Web Application – Click Ok

Choose Web API

Right click on Solution – Add – New Project – Class Library

Right click on Class Project – Add – New Item – ADO.NET Entity DataModel – Click Add

Choose EF Designer from database – Click Next

Create New Connection – Provide connection details – Test Connection

Choose data connection – Click Next

Select database objects – Click Finish

Now build the class library and give reference in MVCApplication

Now build the class library and give reference in MVCApplication

using ClsLib;
using System.Data;
using System.Reflection;
using System.IO;
using System.Text;

Add two Methods in SampleController

---

public HttpResponseMessage GetDBInfo()
 {
 using (SampleDBEntities entities = new SampleDBEntities())
 {
 entities.Configuration.ProxyCreationEnabled = false;

DataTable DtCustomers = new DataTable(“Customers”);
DtCustomers = LINQToDataTable(entities.Customers.ToList());
DtCustomers.Columns.Remove(“Orders”);

DataTable DtOrders = new DataTable(“Orders”);
DtOrders = LINQToDataTable(entities.Orders.ToList());
DtOrders.Columns.Remove(“Customer”); // Removing column because it is relation object and xml serialization will not happen
DtOrders.Columns.Remove(“OrderItems”);

DataTable DtOrderItems = new DataTable(“OrderItems”);
DtOrderItems = LINQToDataTable(entities.OrderItems.ToList());
DtOrderItems.Columns.Remove(“Order”);
DtOrderItems.Columns.Remove(“Product”);

DataTable DtProducts = new DataTable(“Products”);
DtProducts = LINQToDataTable(entities.Products.ToList());
DtProducts.Columns.Remove(“OrderItems”);

DataTable DtSuppliers = new DataTable(“Suppliers”);
DtSuppliers = LINQToDataTable(entities.Suppliers.ToList());
DtSuppliers.Columns.Remove(“Products”);

DataSet dsFinal = new DataSet(“SampleDB”);
dsFinal.Tables.Add(DtCustomers);
dsFinal.Tables[0].TableName = “Customers”;

dsFinal.Tables.Add(DtOrders);
dsFinal.Tables[1].TableName = “Orders”;

dsFinal.Tables.Add(DtOrderItems);
dsFinal.Tables[2].TableName = “OrderItems”;

dsFinal.Tables.Add(DtProducts);
dsFinal.Tables[3].TableName = “Products”;

dsFinal.Tables.Add(DtSuppliers);
dsFinal.Tables[4].TableName = “Suppliers”;

StringWriter sw = new StringWriter();
dsFinal.WriteXml(sw, XmlWriteMode.IgnoreSchema);
string strOutput = sw.ToString();

return new HttpResponseMessage() { Content = new StringContent(strOutput, Encoding.UTF8, “application/xml”) };
}
}

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
// column names
PropertyInfo[] oProps = null;

if (varlist == null) return dtReturn;

foreach (T rec in varlist)
{
// Use reflection to get property names, to create table, Only first time, others will follow
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}

DataRow dr = dtReturn.NewRow();

foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}

dtReturn.Rows.Add(dr);
}
return dtReturn;
}

---

Now run the application and navigate to …/api/Sample/GetDBInfo in browser it will display result in xml format

Now open the Microsoft Power BI Report and follow below steps

Power BI desktop provides around 65 get data source options through which you can get data and create reports.  The data can be fetched from various data sources like Excel, Text, XML, SQL Server, Web, Hadoop file etc.  In the below image you can see the options:

Here is one option on how to get data from Web API.

1. Provide the Web API URL and click `OK’

2.  A list of tables will be shown which is returned from Web API URL:

3.  On clicking `Load’ you will be able to see the screen as below:

4.   Now choose the type of report that you wish to create from Visualizations and then choose the column fields.

The above shown report is in table format.  Now format the report as per your requirements:

5.    Add more visualization if required.  Pie chart is added here:

6.    Save the report and Publish the report by clicking `Publish’ option (authentication required).

7.     Now you should be able to see the published report in https://apps/powerbi.com (authentication required).

8.    Click on the report name (TestB). We created this report using desktop edition and able to see in https://app.powerbi.com web edition.

Using HTML iframe tags you can embed in web based application.