Recently Power BI became available worldwide, and thanks God, I am able to play with it. Fortunately, I have couple BI projects coming at work and one is my favorite – usage of IT resources. One part of the project aims to provide a real time dashboard for any calls made in a company by utilizing Cisco Call Manager . Also, we have a network of Self Services kiosks with call functionality and it is impotent for us to highlight the usage of those kiosks in terms of call functionality.
Power BI REST API
You can find all necessary information about Power BI REST api and available samples from official dev site here. The api allows you to push data directly to the cloud and your dashboards will display it in a real time. It is really amazing and works really well even with big data sets. There are still some limitations, but so far it is enough for my project. From different samples and cases, I have got an idea that REST api could be really good for Stream Analytics. The api is still in preview and doesn’t allow you to do many things, apart from just pushing data and deleting rows from tables. Unfortunately, you are not even able to add tables to existing data sets, or list available tables from existing data sets etc. I think guys are working really hard on it and we hopefully will see those methods coming soon. For now, it will limit our flexibility a bit, but I am very happy with final end-to-end solution.
Let’s have a look at the overall design:
The interesting point here is Cisco Call Manager. It requires FTP server, to where it can push the data with some small interval (10-15 seconds). Files are usually small in size (around 2-15 kbs) and contains from 1 to 30 rows. I would say those files are perfect candidates to be moved to the cloud. Files provide a lot of information about made calls, but we will be interesting only in outgoing calls, as they made the major in cost. Once files dropped in the folder, we have a SSIS package with a custom build Power BI adapter (see below), to move data to the Power BI data sets.
SSIS adapter for Power BI (link)
I will leave couple words about the adapter I have built for that project.As you will read through the blog post, you will find that there are some limitations in the current version of the api. The most clean solution would be for now just to have one DataSet and one Table, as having multiple tables in the same DataSet will require additional manual steps to create a data set. In this example I will have only one DataSet called “Unified Communications” and one table called “Cisco”. Also, as we are not able to connect to the dataset from any tool (Excel, Power BI Designer), we are not able to specify built in filters like to show the data only for Today, or for this Week etc. I have added one small property to the adapter to clean the rows before inserting new ones. In the SSIS package it is possible to check the date of the records, so kind of simple to implement logic to clean the table with old records before processing with new.
Power BI Dashboard
The real time Dashboard will keep data only for one day and will be complemented with another report, built in Power BI Designer to accumulate date for the whole financial year.
You would be surpised, but the SSIS package still works fine since it was deployed 3 month ago.