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.

Creating Visual Reports with Microsoft Power BI

Power BI is a reporting tool available in both Web and Windows versions. Using the Windows version you can create reports and then publish it to a Cloud environment of Power BI and this can be viewed in the Web version of Power BI. It is a business analytic tool to analyze data and share insights, monitor a business and get answers quickly with rich dashboards on all devices.

URL : https://powerbi.microsoft.com/en-us/

Power BI Desktop provides around 65 `get data source’ options which you can get data and create reports.  The data can be fetched from various data sources such as Excel, Text, XML, SQL Server, Web, Hadoop files, etc.  In the following image you can view available options.

Here I have shown one option on getting data from Web API:

Provide the Web API URL and click `OK’/

A list of tables will be shown which is returned from WEB API URL:

On clicking `load’ yo

u will be able to see the following screen:

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

The above shown report is in table format. You can now format the report based on your requirements.

Add more visualization, if required:

Save the report and publish it by clicking the `publish’ option (authentication required).

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

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

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

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

Different Types of Mobile Applications – Native, Hybrid and Web Apps

Every day we use so many Apps (Applications) on our mobile devices for various purposes. Generally we classify these apps into three different categories like Native, Hybrid and Web. Reading this blog content will help you to understand and differentiate these apps.

These days we see the mobile devices mainly running on Android,  iOS or Windows 10. These are known as operating systems or platforms. Native apps are developed to target one specific platform like Android, iOS or Windows. Hybrid apps are developed to target multiple platforms whereas web apps are mobile-optimized web pages that look like an app.

Native apps are built for a specific operating system. A native app developed for iOS operating system won’t work on Android devices and vice-versa. If an app is developed for iOS, it will remain exclusive to that operating system. If at all the app has to support Android version, new app has to be built again for Android operating system. Softwares’ used to develop native apps generally would be Objective-C or Swift for iOS, Java and ADT for Android operating system and .NET(C#) for Windows operating system.

Mobile web apps are the web applications to render/deliver pages on web browsers running in mobile devices. Since these apps target browsers, they work on different mobile operating systems. You can view a mobile web app on Android, iOS or Windows tablets and phone devices. They also work on PC web browsers. Softwares’ used to develop these applications are generally HTML, CSS, Javascript, JQuery

Hybrid apps are a mixture of both native and mobile web apps. This type of application has cross-platform compatibility but can still access phone’s hardware. Softwares used to develop these apps are generally HTML, CSS, Javascript, JQuery, Mobile Javascript frameworks, Cordova/PhoneGap etc.

Native Apps

Hybrid Apps

Web Apps

Skills required Objective-C, Swift, iOS SDK, Java, ADT, .NET(C#) HTML, CSS, Javascript, Cordova/PhoneGap, Cross platform Mobile Development Frameworks HTML, CSS, Javascript, JS frameworks
Distribute Apple iTunes, Google Play store, Windows App store, Amazon App Store Web
Development effort More Medium Less
Performance Good Average Good in PC’s and Average in mobile browser
Good for Games or consumer-focused apps where performance, graphics and overall user experience are more important Apps that do not have high performance requirements, but need full device access Apps that do not have high performance requirements, and do not need push notifications or access to device hardware/functionality

Thanks for reading blog.

Exit mobile version