Building BI dashboards: From SQL to JavaScript to User Experience

Chart

 

 

Modern Business Intelligence Development

If you are a Business Intelligence Developer, you are probably dedicated to one of the leading vendors Microsoft, Qlikview, Tableau or similar. They deliver complete applications, at a high cost, for you to extract, transform, load, and then finally visualize your data. Each step is done in their developing studio, from the back-end database to the front-end user interface. There are wizards and drag-and-drop experiences to get you started quickly. Your customer is impressed with the charts and the users are happy because they can drill down, slice, and dice.

But one day you are asked to publish some charts on the web or create a mobile app. Your reply: “Well yeah… yeah it is possible with some limitations.”

You use the predefined (still expensive) web client and try to defend the limitations and costs. An even worse scenario is that you use ancient web-based reporting tools such as SQL Server Reporting Services because it’s what’s been around for 20 years plus.

It is time to start using true modern web-based components for reporting and dashboards. It is easier than you think even if you have never tried web programming or advanced programming languages such as C# etc.

As a BI-developer you are used to these steps

  • Connect to a datasource
  • Create a dataset
  • Add som widgets (charts, tables, matrix etc) and connect it to the dataset
  • Add som filters and drill down
  • Publish the report

With ancient reporting tool Reporting Services (SSRS) you can do the above, but the reports will be terrible slow, rendered on a server, unresponsive and probably quite ugly. To Microsoft’s defense, they deliver modern alternative components. I am a big fan of .NET Web API. Combine this with open source HTML5 and Highcharts and you can provide the most modern reports and dashboards on the market.

Below I will describe the modern alternative.

With the new HTML5 approach you will have to learn a bit of JavaScript and CSS. When you get up and running you’ll be able create reports and dashboards according to your customers’ exact requirements without the need of investing in expensive BI-products. Let’s get started.

Connect to a datasource and create datasets

As a starting point, you have a database. Let’s say Microsoft SQL Server. Since you are a BI-developer you know a little bit of SQL. You have to transfer the data from the database to the web client. Already you’re stuck. Don’t worry, there are easy ways to publish SQL data to the format JavaScript apps prefer, JSON. It is human readable and quite simple to learn. If you are in the Microsoft world, the solution to transfer SQL data to the web is called .NET Web API.

Since you don’t want to do any programming on the server-side, let’s find a solution to do the query in the web client. The solution is called OData, it is a standardized way to query a web API and retrieve your custom data-set ready to use in the charts and tables. It is very simple, you send a URL containing parameters on how to filter, order, select, and even group fields. Similar to what you would have done in SQL. This way you may publish the whole table or view, but select only a subset of data, which will speed up transfer and rendering.  (If you are confident with your SQL skills, you could even build your own Data Warehouse and publish your ready-to-use star schema in the OData API. That would give you all you need to create all kinds of data-sets for the reporting and dashboards.)

To get the web API working, you need to run a web server (IIS) with the .Net Framework. (For setting up an OData service, there are many tutorials available. Here is one example. There are a little bit of C# code lines, but don’t worry you can copy-paste them.) When you have published your data-model to the OData Web API, you can get a list of all available tables from the OData root:

And now you can start to query them by the parametrized URLs:

http://localhost/odata/GetLocations?$select=TownSE&$expand=Region($select=RegionSE)&$filter=(Region/RegionSE eq 'Storstockholm')

The result above is a JSON object containing locations and its associated Regions. Similar to the SQL command

To get you started on OData queries check out these examples.

Add some charts and connect it to the datasets

Now the fun stuff starts! But first you need to learn some JavaScript and JSON. Read chapter 1 to 5 in this online book, “Eloquent JavaScript” – and because you want to simplify your life, learn some jQuery as well.

There are numerous examples on how to create the charts once you have your data in JSON format as described above: highcharts.com/demo

In a simple case, you can use the jQuery ajax function to retrieve the data-set from your API and then directly create a chart. But in many cases, you might want to modify your data before chart creation. For example, you might want to filter additionally in the web client and select specific fields. In this case the jQuery each function is helpful. You could even use jQuery to group and aggregate data from your JSON objects.

If the data sent from the API is relatively small, this can be a good idea while still maintaining good performance. However, if you want to retrieve some data from your Data Warehouse published in the API, containing millions of rows, you are well-advised to group and aggregate your data server-side.

With OData V4 you have the possibility to group data. For example, you can let the user select in a list of product categories and by this selection group the Sales Revenue from last year.

http://localhost:62349/odata/GetTransactions?$apply=groupby((PriceType,SnapshotLocation),aggregate(AreaSqm with sum as TotalArea, PriceMSEK with sum as SumPrice))&$orderby=SnapshotLocation desc

So there you have your data formatted as you want and fits perfectly into the charts. Maybe you want to display your data in a table with search, sort, pagination etc. There are nice open-source JavaScript tables out there. My favorite is datatables.net

Congratulations, you have created your first modern web chart. But it looks like something from the ’90s, it’s time for the next fun stuff. It’s time to awake the graphic designer in you. Find some inspiration by logging into Pinterest and search for keywords like UX, UI, Web Design, etc. You will learn about concepts like white space, typography, color palettes, flat design, etc. Don’t be surprised if you realize it takes more time than the coding to achieve the perfect looking modern design. In practice, it will be a combination of configuring the charts by option objects in JSON format or CSS, and designing the rest of the application in CSS3. Mobile-first and responsive of course, which is supported out of the box with Highcharts.

Good luck and have fun!