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 back-end database to 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 ehh… yeah it is possible with some limitations.”
You use the predefined (still expensive) web client and try to defend the limitations and cost. 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.
Connect to a datasource and create datasets
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 is 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')
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
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
Congratulations, you have created your first modern web chart. But it looks like something from the 90’s, 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!