As a widely used database type, SQL or MySQL is accessible to all ranges of businesses, from small enterprises to international corporations. I bet you use SQL too, maybe even without knowing it.
SQL databases can be used directly within your company for sales data, financial data, and logistics… but also by third-party applications, built on SQL too. HR tools, CRM platforms, and emarketing SaaS products might be using SQL databases.
With so many different facets of your business data being spread out across SQL databases, a unique place to combine them all and to produce dashboards to report and monitor your business productivity seems like an obviousness.
How to set up an SQL dashboard?
Could you use Excel? Sure, you can, but consider spending quite some time on it, if not ending up in hiring a dedicated resource for this task.
If you want to avoid time-consuming copy-pasting, data connection set-up, or managing multiple Excel sheets that threaten to crash your PC every time you open one, then choose a BI tool that will avoid just that and help you build beautiful SQL dashboards based on your database’s data. Choose a platform that will enable you to manage all your data in one place, and that will automate work processes each time your data is refreshed.
Let’s see how to build a dashboard with ClicData and a Retail SQL database.
1. Connect your data sources
Start by setting up a connection to your SQL database. ClicData offers a range of native connectors for databases using SQL queries, such as:
- MySQL
- Oracle
- PostgreSQL
- MariaDB
- Percona
- Access
- Amazon Aurora
- Amazon Redshift
- Google BigQuery
You can simply choose to use the SQL server connector as well:
Install the DataLoader if you want to get data from an on-premise SQL database. This application, developed by ClicData, will ensure the security of data located on a local server as it will allow opening a port only when data needs to be sent to ClicData and keep the connection shut the rest of the time.
Advantages of using Data Loader :
- Maximum security for your data
- No need to whitelist ClicData’s IP addresses
- Available for Linux, Windows, and MacOS: https://app.clicdata.com/help/link/dataloader
You can also connect directly to online databases like in the following example, in which case you won’t need the DataLoader.
2. Pull data
Now your SQL connection is all set up, and you will be able to pull datasets from your SQL database with it, and this is where the magic begins!
Select the connection you’ve just created, called here ‘SQL Server’.
With the Query Editor, you can choose to pull in all the data from your tables…
… or to write a more complex query to combine different tables and create the perfect dataset that will contain only the data you need. It’s pure SQL: just write your query as you would in your favorite editor.
3. Macros
Now you can even go a step further and integrate ClicData based macros to your query.
In this example, you will only retrieve rows for Orders that are not in the ClicData dataset called ‘MyDataset’ yet.
You can set up macros based on any dataset in your ClicData account, no matter which source it is built from.
You can use any of the ClicData formulae in your SQL query, hence combining the power of both systems: your database and your ClicData datasets, that you might have enhanced and empowered with the integrated ClicData ETL.
Learn more about working with Formulas here.
Automated refreshing of SQL data
Now there’s one last step to take before enjoying the end of your old manual data pulling processes!
Let’s automate your SQL query to update and get fresh data every hour from your database.
Create a schedule:
And fine-tune the options to get fresh data only when you need it.
If you know that no one is going to consume reporting dashboards on a Sunday afternoon or in the early hours of a work day, you can define the schedule to run only on work days, between 7 AM and midnight, based on your specific time zone.
This is a way to optimize general performances by avoiding unnecessary calls to the database.
Choose your preferred Refresh method.
Based on our query, we decided to pull only Order ids that are not listed yet, so Appending new data is the most efficient way to refresh this dataset.
Add as many other tasks to this schedule as necessary, also based on other SQL queries and designed for different dashboards. They will all be run at the same cadence.
Your datasets are now ready to be used to create beautiful and fully automated dashboards. With each refresh of the SQL datasets (hourly, in our example), dashboards, calculations, and formulae will automatically follow and keep the users of the dashboards up to date.
Here’s an example of a retail dashboard that you could build on SQL data. If you like this example and would like to use it or parts of it for your own dashboards, you can download it directly from the Welcome Guide in your account.
Click here to open the live dashboard in full screen
So what are you waiting for?
Start your ClicData 14-day free trial and start building captivating SQL dashboards for your reporting needs!