Collecting data has been an issue over the last centuries. But over the last decade, with the growth on computational power and connectivity, it has increased exponentially. We can say that over the past 3 years the emphasis has gradually changed into the importance of understanding data.
Every company stores and collects data nowadays. But of all those companies only a small percentage actually knows what they’re collecting. Most of the data gets stored and never looked back to. There are several tools on the market and Power BI by Microsoft is a great tool to take this stored data and transform it into readable reports, dashboards and visualizations.
Power BI is a tool for understanding your data. In Power BI you can create dashboards and reports with visualizations that everyone can understand. From simple tables and histograms to intricate charts and graphs. It is also a great tool for analyzing your data. In the picture, you can see data coming to life. Not only visual, but also interactable. You can add filters and interactions with every object in the report.
Picture taken from the power BI Microsoft site.
During the past few months I have been working with IVC on a project which makes it possible to convert all of a company’s data into a dashboard which is easy to understand for everyone. Here is a summary of the points to be considered:
Tools used: Power BI, SQL Server Management Studio and Visual Studio. Programs made available for everyone by Microsoft.
At IVC, we are experts in the use of several CRMs. This makes it easy for us to store and find our data. Unfortunately, not all CRMs are the same and not all of them are easy to use. One of our first challenges was to build a general platform. We developed an interface to combine all available CRMs.
All pieces of information found on the CRMs are converted into a SQL Server Database with the use of Visual Studio. The program is written in C# and a .NET application. It is possible to access the database and the program from anywhere using an internet application.
Next, the database is imported into Power BI. The main problem we faced here was understanding and converting the different API’s of each CRM. Once this was done, we could easily imported every piece of data into the right database, which is easily accessible from Power BI.
A problem we ran into converting the API’s for each database, was understanding which data could be retrieved from the CRM and which type was. If you try to retrieve data that is not in the right place, the program would crash, and we’d have to debug it. Also, every different data object has a different type; e.g. an ID object is of type int, a date object is of type DateTime and so on.
It is critical to understand what the data types are in order to successfully build a converter for the data. It is also important to set the maximum lengths of each type. An ID object will generally not be that large thus using a length 10 will be sufficient, but a note object (notes written by users, like what was discussed in a phone call) will be much larger and therefore needs a varchar type(variable in size) of maximum length.
When you import your database in Power BI, the first thing you need to do is edit the database. This can be done using power queries. The good thing about Power BI is that you have easy access and easy to use power queries, which most of us are already familiar with because of excel. Now it is possible to transform certain columns into readable data by changing their types, doing calculations and creating new calculated columns.
In this phase we had a very interesting challenge. The data that we had was imported as text type but to make nice reports based on date and time you need to convert those to DateTime types. If you want to do this you need to make sure that all the dates are in the same format (dd/mm/yyyy). It could happen that some CRMs return different datetime formats than others, meaning that some CRMs return UK format dates and others return American format dates. Of course, a filter or a calculated column could solve this, but this is quite a difficult task.
The most important factor of creating a Power BI dashboard is understanding what you want to show your users. Which data is relevant? Which visualizations are needed for better understanding this data? And which analyses is needed to improve performances? Once you have a clear vision on this you can start “painting” and building your reports.
With “painting” I mean designing the reports. You have to think about what you want in the reports, how you want it to look like and how people can use it. It is the creation of a blueprint for the report. This blueprint makes it easier for yourself and others to actually implement the reports. With the blueprint you know where to work on.
After creating your reports in Power BI you can share them with all your colleagues and coworkers and they can view and interact with the models. You can give to users access to everything or only to certain aspects of the reports per group of users.
We finished the project on time, with the required dashboards and a happy customer. One of the benefits of working on IT related projects is the rewarding feeling you get when something works. I believe that we have built a very powerful tool for IVC´s customers. I would love to know some other experiences from your side.
At IVC would be happy to talk to you about the potential of this analytical & reporting tool.