Logo of TBS

In January 2017 at the Microsoft campus in Reading, United Kingdom, Technological Business Solutions (TBS) and Microsoft teamed up for a three-day engineering hackfest to recreate intelligent reports in Microsoft Power BI Desktop, which would then be embedded into an existing web application. The aim of the hackfest was to replace their existing reporting tool, SSRS, with Power BI. The idea was to build out a proof of concept to help decision makers at TBS decide whether they should switch to Power BI or continue to use SSRS.

We used the following key technologies:

Names and roles involved in the hackfest:

  • Microsoft
    • Lilian Kasem (@liliankasem) – Technical Evangelist
    • Amy Nicholson (@amykatenicho) – Technical Evangelist (Power BI Embedded Projects Technical Owner)
    • Anna Fear (@AVFear) – Audience Evangelism Manager (Power BI Embedded Projects Business Owner)
  • Technological Business Solutions
    • Daniel Gardner – Software Developer

Customer profile

Formed in 1994 and based in Derbyshire, United Kingdom, Technological Business Solutions Ltd (TBS) has specialized in the delivery of managed mobility solutions built using its own TaskMaster platform. With more than 22 years of experience in developing software for workforce solutions, TBS has become the world-class partner of choice for many businesses embarking on their mobility-based strategy.

Businesses across the globe are using TaskMaster to manage tens of millions of transactions per month, which ensures process compliance, optimizes business efficiency, reduces operating costs, and improves their customers’ experience. This, coupled with investment through a continual and aggressive R&D programme, makes TBS the “go to” provider for those companies connecting the enterprise to their workforce.

Problem statement

TBS has an enterprise mobility platform app for field workers to help move paper-based processes to digital; they reach across all sectors, with their highlight customers being in financial markets. They currently have no way to provide form data visualization, an important part of the application. They are currently using SSRS to display reports, which doesn’t meet all their needs. Therefore, the focus of this hackfest is to rebuild their SSRS reports in Power BI and embed those reports into their existing web application through Power BI Embedded.

Key points highlighted by TBS:

  • They want to update their existing service to provide a better dashboard experience for all their customers.
  • They can’t create some graphics and visuals in SSRS, but they found that Power BI has the support for more visuals and the ability to create custom visuals, which would be hugely valuable to TBS.
  • Allowing customers to make their own reports through the existing web application is the most important feature and a key goal for the hackfest.

“This opportunity has already accelerated our progress on this project and puts us in a great position to move forward quickly.” —Daniel Gardner, Lead Developer

Solution, steps, and delivery

The Microsoft and TBS teams talked through the main aims of the hackfest and collated a list of goals to achieve across the three days. To do this, we talked through the current solution and focused on how some of the features and management capabilities of Power BI Embedded could work well with their data pipeline architecture as well as accomplishing the aim of updating the SSRS reports. We found the following main goals:

  • Move from SSRS reports to Power BI Embedded .pbix files hosted in Azure
  • Implement a feature that allows users to upload their own .pbix files to the dashboard
  • Implement row-level security

Architecture diagram

One of the main requests was to prove the opportunity of using Power BI and Power BI Embedded to create modern reports that could be managed by TBS and would replace their existing implementation of SSRS reports.

The following two images show the same architecture diagram. The plan was to have the reports displayed in the existing application that TBS sells to customers. As we mentioned previously, the current application uses SSRS reports to visualize data and show a dashboard, and we needed to switch that over to Power BI. The application would then use Power BI Embedded to display the workspaces that were designed in Power BI Desktop. The Power BI workspaces use data from Azure SQL Database to make up the dashboard and visualize that data. The Admin user in the following diagram is initially a TBS emplpoyee who creates default dashboard visuals for all clients through Power BI Desktop; however, TBS also wants to allow users to be able to create their own visualizations using the data and upload those visualization directly to the existing application.

Full architecture diagram

Whiteboard architecture diagram

Solution

First, we used Power BI Desktop to recreate the SSRS reports in Power BI. This part was simple (although we encounter some challenges, which are explained later) and involved us loading the data from SQL Database, manually mapping the relationships to the tables, and recreating the dashboard using Power BI visuals. When the reports were ready, we exported the .pbix files and uploaded them to Power BI in Azure so that the reports were live and available to be embedded into any application using Power BI Embedded. Here are the final reports:

Power BI Desktop dashboard, view 1

Power BI Desktop dashboard, view 2

Power BI Desktop dashboard, view 3

Power BI Desktop dashboard, view 4

We then developed a simple ASP.NET application that used the Power BI Embedded SDK to display the reports. The following C# code is the main controller of the ASP.NET application. This code gets all the reports available in a Power BI workspace collection and puts them into a list. This list is then used in the view to display all the available reports and embeds the report selected into the iFrame on the view page.

The code also has an import function, which allows users to upload their own .pbix files. The reports from these .pbix files would first be uploaded to the Power BI workspace collection in Azure and then loaded into the previously mentioned list, which allows users to instantly access and view reports they uploaded.

public class DashboardController : Controller
{
	private readonly string workspaceCollection;
	private readonly string workspaceId;
	private readonly string accessKey;
	private readonly string apiUrl;

	public DashboardController()
	{
		this.workspaceCollection = ConfigurationManager.AppSettings["Power BI:WorkspaceCollection"];
		this.workspaceId = ConfigurationManager.AppSettings["Power BI:WorkspaceId"];
		this.accessKey = ConfigurationManager.AppSettings["Power BI:AccessKey"];
		this.apiUrl = ConfigurationManager.AppSettings["Power BI:ApiUrl"];
	}

	public ActionResult Index()
	{
		return View();
	}

	private IPower BIClient CreatePower BIClient()
	{
		var credentials = new TokenCredentials(accessKey, "AppKey");
		var client = new Power BIClient(credentials)
		{
			BaseUri = new Uri(apiUrl)
		};

		return client;
	}

	[ChildActionOnly]
	public ActionResult Reports()
	{
		using (var client = this.CreatePower BIClient())
		{
			var reportsResponse = client.Reports.GetReports(this.workspaceCollection, this.workspaceId);

			var viewModel = new ReportsViewModel
			{
				Reports = reportsResponse.Value.ToList()
			};

			return PartialView(viewModel);
		}
	}

	public async Task<ActionResult> Report(string reportId)
	{
		using (var client = this.CreatePower BIClient())
		{
			var reportsResponse = await client.Reports.GetReportsAsync(this.workspaceCollection, this.workspaceId);
			var report = reportsResponse.Value.FirstOrDefault(r => r.Id == reportId);
			var embedToken = Power BIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id);

			var viewModel = new ReportViewModel
			{
				Report = report,
				AccessToken = embedToken.Generate(this.accessKey)
			};

			return View(viewModel);
		}
	}
	
	public ActionResult Import()
	{
		return View();
	}

	/*
	*	The method below is what we used to allow users to upload their own PBIX files to the application
	*/
	public async Task<Import> ImportPBIX(string datasetName, string filePath)
	{
		using (var fileStream = System.IO.File.OpenRead(filePath.Trim('"')))
		{
			using (var client = this.CreatePower BIClient())
			{
				// Import PBIX file from the file stream
				var import = await client.Imports.PostImportWithFileAsync(workspaceCollection, workspaceId, fileStream, datasetName);

				// Polling the import to check when the import has succeeded.
				while (import.ImportState != "Succeeded" && import.ImportState != "Failed")
				{
					import = await client.Imports.GetImportByIdAsync(workspaceCollection, workspaceId, import.Id);
					Console.WriteLine("Checking import state... {0}", import.ImportState);                        
					Thread.Sleep(1000);
				}

				return import;
			}
		}
	}
}

The final step of this project was to embed the ASP.NET application in the Silverlight application. (The reasoning for this is described later in the challenges section.) This part was simple: We just updated the iFrame in the Silverlight application to point to the reports page in the ASP.NET application. (The Report Admin page also had an iFrame that pointed to the import page in the ASP.NET application.) The following image shows how this update was done: A value in SQL Database controls the lists for buttons in the application. We used localhost because we wanted to test everything locally before deploying the web application.

Silverlight iFrame update

Finished solution

This is the final view of reports embedded into the existing Silverlight web application.

Power BI Embedded reports

The following image shows the page that allows users to upload their own Power BI reports. The idea is that users will use Power BI Desktop with sample data provided by TBS to make their own reports. They would then use this tool to upload the reports (as .pbix files). Using the Power BI Embedded APIs, we will change the connection string to point to the real data instead of the sample data.

Power BI report upload tool

Challenges, learnings, and workarounds

Silverlight support

The main challenge we faced during this project was that the current Power BI Embedded SDK did not support Silverlight applications. The obvious solution was to create our own service using a Silverlight HTTP client and the Power BI APIs; however, given we had little time, we decided the fastest way to get a proof of concept ready for the end of the hackfest was to build an ASP.NET application (which does have a Power BI Embedded SDK) and embed the reports we developed into a blank page. We then used an iFrame in the existing application to display the ASP.NET application, which used Power BI Embedded to display the reports.

This is not the best way to use Power BI Embedded. Another reason for this approach was that TBS is currently planning to switch their entire Silverlight application into an ASP.NET application; therefore, all of the code we have written for the ASP.NET application can be reused in the next version of their application. Writing our own Silverlight Power BI Embedded service would have been a waste of time for the hackfest and in the long term too.

Overall, this solution worked really well for the demo we presented the next day. There were no notable issues, and the solution wasn’t slower than the standard Power BI Embedded solution.

The following image shows the plain ASP.NET application that is using Power BI Embedded in an iFrame to display the reports.

ASP.NET application Power BI report

The following image shows the Silverlight application that is using an iFrame to show the ASP.NET page shown previously.

Silverlight application Power BI report

No find-and-replace regex support

One of the issues we had during the initial creation of the reports (in Power BI Desktop) was that we couldn’t apply regex to find and replace. We needed to replace certain values in a column that began with “xyz”. However, without regex to help us pick the values to replace we had to find a workaround. The workaround was to create a new conditional column. We initially wanted to avoid creating new columns, but this was the best solution we could find. Using conditional columns, we were able to create a new column with the condition that any value beginning with “xyz” would be replaced with “x”. This worked exactly as expected and allowed us to better visualize the data.

Displaying an SVG image

Another issue we faced was displaying SVG images. Initially, we thought we had to create our own custom visual to do this, which was true; however, a custom visual for this already exists (Image Viewer in the Power BI Visuals Gallery). We then discovered that the SVG images TBS had in their database has been modified to their own custom format, which meant we couldn’t use the Image Viewer custom visual.

The only solution here was to reverse engineer what TBS had done and convert the images in the database to standard SVG, which would have required too much work, especially with our limited time. The original developers who had written the code to convert the SVG image in the first place no longer worked at the company, and the legacy code was not understandable.

In the end, we couldn’t display the images in the Power BI reports, which is an issue for TBS because the images were signatures that had to be displayed in the reports. Moving forward, TBS will work to resolve this issue from their own database.

Other learnings and feedback

Aside from the learnings mentioned in the challenges section of this report, we found that overall Power BI Embedded provided a positive solution and has many great features. But we also found areas where Power BI was lacking. Daniel, the developer from TBS, said that Power BI was limiting in comparison to SSRS in the following ways:

  • Users can’t drill through in a report in Power BI Embedded in the same way you can move from Dashboards to reports in Power BI. One example is being able to look at a report table, click an item, and go into a filtered report on that specific item. Another example is being able to go from one report to another by clicking on a table value—you could use Javascript APIs on other graphics in Power BI to accomplish this, but it’s not currently supported with tables.
  • Some things you can do easily in SSRS but can’t do in Power BI; for example, the SSRS queries feature to manipulate columns is easier to use.
  • Conditional highlighting within tables is not supported, so SSRS currently has a richer set of formatting features.
  • In general, the Power BI Embedded documentation is rich, clear, and easy to navigate. On the other side:
    • The SDK is lacking compared to the APIs available; they are not in sync.
    • There is also no support for Silverlight.
    • The forums and blog posts are detailed and useful; they have good answers and provide good support.
    • There are not enough parameter-based filters. We found that this is possible by using an extension (for easier filtering), but this should be a standard feature.
    • Ideally, it would be great to be able to add a simple text box for users for custom and smart filtering (for example, being able to use the SQLBI Smart Filter custom visual).
  • Visually, Power BI is a lot better and nicer to look at.
  • The ability to create an architecture with Power BI Embedded to allow users to upload .pbix files via REST calls is really powerful and useful for ISVs. The APIs are great!

Conclusion

From the beginning of this project, we knew it was a proof of concept to research whether it was worth the time and effort to switch an entire solution from using SSRS to using Power BI Embedded. As mentioned earlier, insights for this project came from these three key statements by TBS:

  • They want to update their existing service to provide a better dashboard experience for all their customers.
  • They can’t create some graphics and visuals in SSRS, but they found that Power BI has the support for more visuals and the ability to create custom visuals, which would be hugely valuable to TBS.
  • Allowing customers to make their own reports through the existing web application is the most important feature and a key goal for the hackfest.

From those statements, we came up with three goals to assess the success of this project:

  • Move from SSRS reports to Power BI Embedded .pbix files hosted in Azure
  • Implement a feature that allows users to upload their own .pbix files to the dashboard
  • Implement row-level security

We found that all of the above was possible, but we ran out of time and were unable to implement row-level security. We were able to successfully recreate all of the SSRS reports in Power BI Desktop with one exception: We were unable to display the signature images found in the database. For TBS, this is an important part of the reports. TBS is planning to switch to a different method for signature collection, which will result in standardized images of the signatures. This works well for their plan of updating the entire legacy solution.

The feature that allows users to import their own Power BI reports as .pbix files is a game changer. The idea is that users will use Power BI Desktop with sample data provided by TBS to make their own reports. They would then use this tool to upload the report. Using the Power BI Embedded APIs, we will programmatically change the connection string to point to the real data instead of the sample data. This will allow power users to fully create and customize their own reports and instantly view these reports live with real data. This was simple to implement because the Power BI Embedded APIs support uploading .pbix files.

Overall, the Power BI reports worked well and looked exactly as expected. Daniel, lead developer on this project from TBS, said he was impressed with the visual elements of Power BI, stating that he thinks customers would find this new dashboard more appealing and easy to use. Going forward, the plan TBS has is to upgrade their entire solution to a HTML5 web application, and to use Power BI Embedded reporting in the both the new HTML5 and old Silverlight solutions. The next steps are to enhance the reports created during this project, finish the report import feature, and work on converting the images in the database to a standard format. The hope is to produce a new, polished solution that provides their customers with a better experience.