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.
- 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.
