Introduction
Data visualization is essential for any type of business, be it large or small. Understanding the data and extracting valuable information in real-time, facilitates decision-makers when making important decisions. In an increasingly competitive market, where time is money, it is no longer enough just to look at the data of the past, and it is necessary to visualize in real-time what is happening so that it is possible to make efficient assertive decisions.
Therefore, real-time data visualization is crucial for stakeholders to achieve the best results.
Objective
The purpose of this article is to present a general approach to how a real-time dashboard can be published on a website. The steps in this process will include the necessaries interfaces for the dashboard to work in real-time considering the database updates. We do not aim to detail the characteristics of each tool, but rather the interface between them, so it is important that the user has a basic knowledge of SQL languages, data visualization techniques and HTML language.
Technology
We will present here some of the main technologies in the market that facilitate to develop and deploy a real-time dashboard project, they are;
· PowerBI
· Microsoft SQL Server
· Webpage WIX
Creating a database
First, we need to create a database, where PowerBI will collect the data and where the original data can be updated at the source.
For this propose, we will use the Microsoft SQL Server Studio installed locally to reproduce this laboratory.
We will use public data for security and privacy policy reasons. The primary data is made available by Microsoft in a Machine Learning project that can be found at the link below.
Fig1- Accessing the Database
The first step is to access the database. For a database used locally, we use the Server name as the name of the computer as a server. In cases of the real scenarios, the database manager generally provides access with a specific authentication profile for accessing the data with restriction policies to preserve data security.
In our specific case, we will have access to the database through this created user, using login and password.
As soon as we access the database, then we have access and permission to create a new database according to the steps in Fig.2. and Fig.3. below.
Fig.2. Creating the Database
Fig.3. Database created
As it is not the purpose of our article to create an SQL tutorial, we will be importing the tables from the public dataset into the database as shown in Fig.4 below by clicking on “import Flag File”:
Fig.4. Importing the tables
Next, we have the imported tables within the database we created, as shown in FIG.5 below.
Fig.5. Tables imported into the database
Then we can create the database diagram to check the relationships between the tables so that it is possible to relate the tables during the creation of the dashboard.
In the Fig.6 below we can check the relationship between the tables that have the respective Primary Keys and Foreign Keys.
Fig.6. Database Diagram
If it is necessary to make any changes to the tables within the database when you have permission for this type of manipulation, we can perform the edition through command lines using SQL language or through the visualization panels.
To perform manipulations, you can create a new query and then enter the desired commands as shown in Fig.7 below.
Fig.7 Using command lines to allow manipulation of tables in the database
Another option would be to use the database panels as illustrated in Fig8 and Fig.9 below, where we can have a more comprehensive view of the imported data.
Fig.8. Editing the tables
Fig.9. Editing the tables
As we already have our database created with the 3 tables that we will use to create a dashboard model, we will start the process of publishing the dashboard.
Data Visualization
Why PowerBI
As we can see in Fig.10 below, Gartner recognizes Microsoft as a Leader for the thirteenth consecutive year in the Gartner 2020 Magic Quadrant for Analytics and Business Intelligence Platforms.
Fig.10. Gartner 2020 Magic Quadrant do Analytics and Business
PowerBI allows the use of the FREE version, where it is possible to develop dashboards normally, and only the PRO version allows the publication of reports on the clouds in a private authentication.
Another attractive factor of the PowerBI is the cost, with only an investment of U$ 9.90 per month it is possible to have a PRO version with all the necessary utilities to carry out the applications of an implementation pipeline.
Importing database data into PowerBI
The first step is to import the data that is in the database, clicking the icon where we can easily identify where to get data.
As we can see in Fig.11 below, we started the import process by connecting directly to the desired database.
Fig.11 Accessing the database
Then we enter the information to access the database, as shown in Fig.12 below.
Fig.12 Accessing the database from PowerBI
After connecting to the database, it is possible to view the tables we wish to work on. Then we select the tables to start to build the dashboards as shown in Fig.12 below.
Fig.13 Selecting database tables from PowerBI
After reading the tables, we can check the relationship between the tables to verify that the tables can be related. We then see in Fig.13 below the same relationship pattern shown in Fig. 6 that represents the tables relationships within the database.
Fig.14 Relationship between the tables
As our goal is to present a project for publishing a dashboard report on a webpage, we will not spend time here on the details of the construction of the graphics. We will present a simple dashboard model in Fig.14 below, for publication testing purposes only.
Fig.15 Dashboard design
Then we must click on publish icon on the top and the graph will automatically be stored in our PRO account on the cloud and the dashboards will be available to be accessed from anywhere through a connection with the internet.
After clicking on the publish icon, just access the PRO account where the dashboard will be stored in our workspace, as we can see in Fig.15 below.
Fig.16 Dashboard in the workspace
An important note to make is that PowerBI allows to create graphics using Python and R.
Since the dashboards are confidential information of any business, for a greater guarantee of the privacy and security of access of the PowerBI Workspace, the access can be restricted by a verification code sent to the user's mobile number, thus guaranteeing security and avoiding intruders entry in your workspace in the cloud.
Then we click on the “share” icon to generate the access key that we will use to publish the dashboard within the website.
It is important to emphasize that access key is a secure code, where only authenticated users who have a PowerBI PRO account have access as we can see in the Fig 17 below.
Fig 17 Verification code to access the PowerBI workspace cloud.
Another security tool is to restrict access to users by e-mail, so only PRO users with their e-mails will have access to the report, as we can see in the figure 18 below.
Fig.18 Share Report authentication
There is an option to generate a public link for unrestricted access, but it is not recommended to use it, because we are dealing with confidential company data information.
In Fig.19 below you can see the generation of the Secure Embed code.
Fig.19 Generation of the access key for web publishing with security access restricted to accredited user.
Another interesting option is the generation of the QR code that can be read through the cell phone and the user is automatically redirected to the application, where the dashboard is redirected to your workspace, allowing the visualization on the cell phone according to the sequences of the figures 20 to 25.
Fig.20 Generate a QR code
Fig.21 QR code
Fig.22 Saving the QR code as jpg format
Fig.23 QR code jpg format
Fig. 24 cell phone PowerBI App
Fig.25 PowerBI Workstation using the cell phone.
After generating the publication code, we then create the necessary link between the website, the PowerBI and the database.
Publishing the Dashboard to the Website
The final step is to publish the dashboard created within the webpage. It is worth mentioning that, in real scenarios, it is recommended to create a private page inside of the webpage to have access to dashboards with restricted access, where only authenticated users have access to the dashboards, following the security and privacy policies.
We will use the popular WIX website template where we will present the options for sharing HTML codes.
In Fig.26 below, within the Website admin page, we selected the Custom Embed option.
Fig.26. Enabling HTML viewing of the dashboard on a corporate website
After we paste the Secured embed code from PowerBI to the webpage, the dashboard is displayed on the screen automatically, as shown in Fig.27 below.
Fig.27 - Copy and Past the PowerBI HTML code to the website company
Then we performed the final test to check if the dashboard can actually be viewed on our official webpage as we can see in Fig.26 below.
Fig.28 Final display of the Dashboard's publication within the webpage
The access to the page of dashboards within the webpage is restricted to authenticated users.
For each database updates, all graphics are updated automatically in real-time.
For testing purposes, we made a small change within the database to check if the dashboards are being updated with the recent changes.
As we can see the figures BEFORE and AFTER in Fig.29 to Fig 32 below. Each database update generated the same update on the dashboard in real time
BEFORE
Fig.29 Database
Fig.30 Dashboard
AFTER
Fig.31 Database
Fig.32 Dashboard
Conclusion
Therefore, we verify in this article all the simulation steps of the process of publishing a dashboard report on a corporate webpage. We verified the stage of access to the database and data collection using PowerBI and the generation of the secured embed code and the final publication for viewing the dashboard accessing the webpage.