PostgreSQL dashboard overview

PostgreSQL Dashboard is a data visualization tool, which fetches data from PostgreSQL database and create dashboards in a minute with PostgreSQL dashboards tool.

PostgreSQL Database

PostgreSQL dashboards are pure dynamic and based on D3.js which is the most widely used JavaScript library for producing dynamic and interactive data visualizations in web browsers. Postgres dashboard uses the widely implemented SVG, HTML5, and CSS standards which can be created either static or real-time data coming from a variety of data sources.

 

Download PostgreSQL Dashboard


PostgreSQL dashboards examples

PostgreSQL dashboard allows a non-technical user with the ability to connect with PostgreSQL database using PDO, the most commonly and widely using database connection to fetch data in open source platform such as Windows, Linux and Ubuntu or any other platforms support Apache, Nginx etc., and summarizes all the metrics data fetches from PostgreSQL database. Postgres dashboard uses advanced D3.js framework which gives you instant visibility of dynamically maintain information about the connected component in a graphic layout.

PostgreSQL dashboard enables to customize dashboard layout with the most powerful drag-and-drop features to place each chart exactly where you want for optimal representation. This drag-and-drop layout is fully responsive and compatible with PC, mobile phone and tablets using the most powerful front-end open source dashboard framework such as D3.js and Bootstrap framework .

The advantages of PostgreSQL dashboard are:

  • Better Decision Making
  • Gain Competitive Advantage
  • Enhance Collaboration
  • Spotting potential problems
  • Merge with data from various data source.
  • Live SQL against database
  • No need for Data-warehouse or ETL
  • Leverage the speed and stability of your powerful database.


Charts type for PostgreSQL dashboard

PostgreSQL dashboards supports multiple charts in a single dashboard layout. Dashboard Postgres comes in a variety of chart types including 3D, line, bar, pie, donuts, bubble, stack, area, heatmap, histogram, sankey diagrams, sunburst and choropleth map. You can customize how chart is grouped, summarized, and displayed for each layout very easy. All charts built in PostgreSQL dashboard are in Scalable Vector Graphics (SVG) format, which is scalable, minimal file size and affects your site's download speed. The user can also download these dynamic charts in PNG image format.


Introduction

This how-to will show you how to connect with PostgreSQL database, fetch data and create dashboard using PostgreSQL dashboards step-by-step.


Postgresql data visualization reporting tool for Postgres

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and technical standards compliance. PostgreSQL database is designed to handle a huge range of workloads, from single machines to data warehouses or Web services with many concurrent users. The PostgreSQL database runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, macOS, Solaris, Tru64), and Windows.

Tip: PostgreSQL is also known as Postgres


Prerequisites

PosgreSQL Dashboard can be installed any platforms like Windows, Linux and Ubuntu or any other platforms support Apache, Nginx etc.

Requirements

  • PHP Version 5.6 or later
  • Apache 2 or later
  • PostgreSQL database
  • PDO and pdo_pgsql
  • Windows 7 or later /Linux 3 or later
  • Firefox 52, Chrome 57, IE
  • PostgreSQL Dashboard is available for downloading

Installation

There is a Free version of PostgreSQL dashboard available for trail bases,

  • You can download PostgreSQL dashboard code fromDownload PostgreSQL Dashboard
  • Place the files in a directory on the web server. e.g. …/www/yoursite/dashbboardbuilder-v3-FREE/
  • Unzip dashboard in zip filethe file dashboard.zip using Extract Here option to the root folder of "dashboardbuilder"


Steps to build PostgreSQL dashboard

STEP-1: Connect with PostgreSQL Database


  • Run the web folder in your browser. e.g. http://localhost/dashboardbuilder following welcome screen will appear.
  • PostgreSQL Dashboard welcome screen

  • The screen shows in the above picture is the main panel of the PostgreSQL Dashboard.
  • Now Click the Database icon
  • Following screen will appear.In order to connect with PostgreSQL and fetch data. Now, Select Database from data source tab. Select PosgreSQL from the drop down list of Database, Enter your "database-name" in the DB name field and Save changes as mentioned in the screen.
  • PostgreSQL Dashboard - Select your Database

  • When you click the save changes, a green tick mark with Database will appear that shows that your database is successfully connected.

PostgreSQL Dashboard - Preference screen

Tip: To retrive data from PostgreSQL database, PDO driver 'pdo_pgsql' must be installed. For more details please have a look at our Documentation


STEP-2: Chart settings


  • After successfully connect to your PostgreSQL database, we will retrieve data from your database in our next step.
  • Select the gear icon.

PostgreSQL Dashboard - Database Connection

  • A pop-up screen will appear and a list of the tables from your PostgreSQL database will appear on the table list as shown in the picture below.


PostgreSQL Dashboard - Chart Preference


PostgreSQL dashboards have a powerful Query Builder, a smart SQL statement builder tool for quick and easy writing of complex SQL queries without manual code writing which is designed to simplify the retrieving of data from the database.

  • When you click on the table, a default SQL statement will appear on the SQL statement box, which you may modify as per your need.
  • You can add up to 10 SQL statements simultaneously to retrieve data from different tables.

STEP-3: pgSQL statements


  • In our example, we will be creating a simple PHP application for our sales management. This PHP dashboard will contain a chart where users can find the sales stats and maintain the data about the products.
  • Now, enter the pgSQL statement in text box in a following manner

SELECT strftime('%Y-%m',o.shippeddate) as xaxis, sum(d.quantity) as yaxis from `order details` d, orders o where o.orderid = d.orderid group by strftime('%Y-%m',o.orderdate) limit 50


  • Now, we want to execute our query and fetch the data from the tables. Click the Run Query button

PHP Dashboard - SQL Statement

  • Query Result will appear. Now, we have to assign x-axis data to the x-axis column and y-axis data to the y-axis column. Select your x-axis data from the drop down list of X and y-axis data from the drop down list of Y .
  • Click Save Changes

PHP Dashboard - SQL Query Result

  • Charts will be appeared on the screen as shown below.

PostgreSQL Dashboard - Chart Preference

STEP-4: Adjust the position


  • You may adjust the size and the position of the Chart Panel by dragging its corner. The size and position are fully responsive according to the Bootstrap framework


STEP-5: Generate PHP and Postgresql dashboard source code


  • So far we have successfully build our dashboard. In this step we are going to generate PHP dashboard source code we have just created. Click the Generate   button to auto-generate PostgreSQL dashboard source code for our PHP application

PHP Dashboard - Chart Preference

  • PostgreSQL dashboard source code for the chart will automatically generate, you may copy past this script of dashboard to your PHP application.

PHP Source Code for PHP and PostgreSQL dashboard


/**
 * DashboardBuilder
 *
 * @author Diginix Technologies www.diginixtech.com
 * Support  - http://www.dashboardbuilder.net
 * @copyright (C) 2018 Dashboardbuilder.net
 * @version 2.1.7
 * @license: license.txt
 */

include("inc/dashboard_dist.php");  // copy this file to inc folder 


// for chart #1
$data = new dashboardbuilder(); 
$data->type =  "line";

$data->source =  "Database"; 
$data->rdbms =  "pgsql"; 
$data->servername =  "your-servername";
$data->username =  "userid";
$data->password =  "password";
$data->dbname =  "your-db-name";
$data->xaxisSQL[0]=  "SELECT strftime(^%Y-%m^,o.shippeddate) as xaxis, sum(d.quantity) as yaxis from `order details` d, orders o  where o.orderid = d.orderid group by strftime(^%Y-%m^,o.orderdate) limit 50";
$data->xaxisCol[0]=  "xaxis";
$data->yaxisSQL[0]=  "SELECT strftime(^%Y-%m^,o.shippeddate) as xaxis, sum(d.quantity) as yaxis from `order details` d, orders o  where o.orderid = d.orderid group by strftime(^%Y-%m^,o.orderdate) limit 50";
$data->yaxisCol[0]=  "yaxis";
$data->name = "linechart";
$data->title = "Line Chart";
$data->orientation = "";
$data->xaxistitle = "x-axis title";
$data->yaxistitle = "y-axis title";
$result[0] = $data->result();

?>

<!DOCTYPE html>
<html> 
<head> 
	<script src="assets/js/dashboard.min.js"></script> <!-- copy this file to assets/js folder --> 
	<!--<link rel="stylesheet" href="assets/css/bootstrap.min.css"> Bootstrap CSS file, change the path accordingly --> 


</head>
<body> 

<div class="container">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading"></div>
    <div class="panel-body">
        <?php echo $result[0];?>
    </div>
</div>
</div>
</div>
</body>


 

 

The dash postgresql project also offers free and paid plans. The code for Dashboard Builder can be found on GitHub under this license


Conclusion

Now we have a PostgreSQL dashboard which can be used with any web application that was customized to fit the requirements of the web design. The project also offers free and paid plans. You can also download Free Dashboard PostgreSQL Dashboard version under this license.

Best of all, with our Standard and Enterprise licenses include 100% source code, for you to enhance the functionality or incorporate the Postgres Dashboard into your web application or any PHP software solutions/products, analytics/data science/data warehouse enterprise systems or Joomla, Wordpress or Drupal sites.

All editions of our PostgreSQL dashboard series are still fully functional, for sale and supported and provides increasing levels of support to address the queries of users and developers.

Postgres dashboard can also be implemented using an online API where you don’t have to mix with a PHP script or PHP source code. The online API uses universal HTML code which can be used in any platform. The data will be added or retrieved from PostgreSQL database using the API. You may find more information about the Online Dashboard.


Download PostgreSQL Dashboard