PostgreSQL Dashboard an overview
PostgreSQL Dashboard is a powerful drag-and-drop utility for creating interactive and dynamic dashboards for PostgreSQL database. PostgreSQL Dashboard fetches data from the PostgreSQL database and creates dashboards without writing SQL queries.
What is a PostgreSQL dashboard?
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.
Create High Quality professional dashboards with PostreSQL database
PostgreSQL dashboard allows a non-technical user with the ability to connect with PostgreSQL database using PDO, the most common and widely used 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 advance 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 sources dashboard framework such as D3.js and Bootstrap framework.
Why PostgreSQL Dashboard is important?
PostgreSQL Dashboard has the most powerful SQL query generator, it is the quickest and has a strong reputation of fatching data from PostgreSQL and generate SQL query for your data visualization.
- 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.
- Auto geneated PHP source code for your dashboard.
Building attractive charts with Postgres database
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.
How to create PostgreSQL dashboard?
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 7.2 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
the file dashboard.zip using Extract Here option to the root folder of "dashboardbuilder"
the 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.
"database-name" in the DB name field and click Save changes button.
Tip: If your database is not in the public schema, then you can use your schema name in this manner 127.0.0.1:5432/Schema in the host field.
- When you click the save changes, a green tick mark with Database will appear that shows that your database is successfully connected.
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.
- 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 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
- 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
- Charts will be appeared on the screen as shown below.
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
- 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>
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 Professional and Premium licenses include 100% source code for you to enhance the functionality or incorporate the Postgres Dashboard into your web application, 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 provide 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 on any platform. The data will be added or retrieved from the PostgreSQL database using the API. You may find more information about the Online Dashboard.