Skip to main content
Creating Power BI report using MVC Web API

Creating 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:

Northwind SQL database

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

Asp.Net MVC 4 Web Application

Choose Web API

Web API

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

Add - New Project - Class Library

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

ADO.NET Entity DataModel

Choose EF Designer from database – Click Next

 EF Designer from database

Create New Connection – Provide connection details – Test Connection

Test Connection

Choose data connection – Click Next

data connection

Entity data model wizard

Select database objects – Click Finish

Database Objects

MVC Applications

Now build the class library and give reference in MVCApplication

 ASP .NET

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

XML Format

Now open the Microsoft Power BI Report and follow below steps

Microsoft Power BI Report

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:

Power BI desktop

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

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

Web API URL

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

Visual Studio

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

Web Application

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

ASP.NET

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:

Pie Chart

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

Power BI

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

Desktop Edition

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.

Web Edition

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

Mallikarjun M K

Mallikarjun M K

Mallikarjun M K works as Technical Leader with Trigent Software. He has 8+ years experience in software development and over the years has developed several Windows/Mobile/Web-based applications.

Leave a Reply

Your email address will not be published. Required fields are marked *

one + 6 =