Règle d'automatisation Jira pour le merge d'une pull request
Posted by: Sheridan Gaenger
When you create a SQL dashboard, you get a snapshot into the health of your business. These snapshots serve as an integral part of business intelligence systems, pulling from real-time data to visualize trends and make insights easy to identify.
Ultimately, a good SQL dashboard will empower anyone who sees it to make data-informed decisions that have a direct impact on the bottom line. For instance, Toronto-based startup Platterz created a dashboard they named Order Tracker, which is a “live feed of order data that [their] Partner Success team uses daily.”
This live feed shows the status of the orders and important details and, in particular, highlights when an order is delayed or canceled. This allows the Partner Success team to be proactive and work to create more positive experiences, ensuring repeat business.
Creating an SQL dashboard is possible for any business (yes, yours too), and it’s not as difficult as you may think to get started.
What you need to create a real-time SQL dashboard
To create a real-time SQL dashboard, you need some groundwork in place, including a relational database, the ability to fetch data, and a way to visualize it.
A relational database is where you will pull your data from. “Relational” means that the data is related to each other, which means the values in one table also exist in another. In our SQL tutorial, we use the example of TrackID as one of the values that exists in multiple tables. The “Albums” table contains a column for TrackIDs and so does the “Track” track table. If you want to see the relationship between these two tables, you can use TrackID to do so.
Once you have your data in one place, you need the ability to fetch the data you need using SQL. A number of tools can help you do this, such as database management systems (DBMS) like MySQL or business intelligence (BI) tools like Atlassian Analytics. If you use Atlassian Analytics, you don’t need to know SQL to fetch data, just use our Visual SQL, which allows you to query data without code.
Next, you need a way to visualize your results. To create a real-time SQL dashboard, you need to use a BI tool that can fetch live data from your relational database. Alternatively, you can export your query result from MySQL as a CSV and plug it into Google Data Studio or Excel. This is an inexpensive, effective way of creating reports, but they won’t be real time. If you’re in the market for a BI tool, be sure to assess their strengths and weaknesses before investing in one.
Au-delà d'Agile
Before creating a SQL dashboard, consider where you will use it, who will need access, and when they will need access. These considerations will help frame your thinking to create a dashboard that not only solves for your immediate reporting needs but also helps with long-term, data-driven decision-making.
Where will you share it?
Depending on the tools you use, you can present your dashboard in many ways. If you share it as an always-on TV presentation, make your graphs and metrics large, so they’re easy to read from a distance. Or, if you’ll display it directly within a BI tool, dive deeper and show metrics that are much more complex. Make sure your BI tool has strong collaboration capabilities if you go this route.
Embedding, which involves plugging your dashboard into other tools or on the web, is also an option. In this case, you’ll want it to be short and to the point so it doesn’t take up too much space.
One final strategy for sharing to consider is exporting as a PDF or CSV. Be sure to use tried-and-true graphs and metrics that can translate well to many different screens and physical formats.
Who will see it?
Tailor your dashboard to your audience so they can quickly get the information they need and the message you’re trying to convey.
First, center your dashboard on a central thesis. If you have a dashboard on the status of customer support tickets, that thesis might be “Closing customer support tickets quickly and comprehensively is vital to the health of our business.”
Second, consider your audience. Is it for internal teams? External clients? The general public? With a customer support ticket dashboard, you may present it on a TV in the middle of the office so the whole team can see how they’re performing. Include only the data that helps your team be successful, such as number of support tickets in the queue, number completed, etc.
Finally, make your conclusion easy to understand. If the customer support team is doing well, they should be able to understand that at a glance. For example, use a doughnut plot to show the amount of satisfied, neutral, and dissatisfied customers.
How often will you update it?
Dashboards are dynamic, real-time reports, not a set-it-and-forget-it endeavor. To ensure they’re truly real time, you need to think through three things:
- How much maintenance will it need? It’s best to keep dashboards lean, focused, and easy to maintain. That said, they will all eventually need updates and tweaks to make sure they’re pulling and properly representing the right data. Set a schedule for checking in on their health. Whether that’s daily, weekly, or monthly, it depends on the complexity and importance of the dashboard.
- Are you the only one who will make changes? Take into account the needs of your collaborators. Make the reasons behind why you built your dashboard the way you did easy to understand by documenting your thoughts. Depending on the tool you use, you can do this right in the dashboard itself, like you can with Atlassian Analytics.
- Do you need to make changes on the fly? Sometimes things don’t turn out exactly right the first time. SQL dashboard tools that provide the ability to change variables quickly can help save a lot of time instead of requiring you to go back and edit your query.
Once you’ve gone through all of these considerations, you’re ready to create your SQL dashboard.
Au-delà d'Agile
There are some free ways to create a SQL dashboard, but free doesn’t necessarily mean easy — the effort and time you spend may outweigh the money you save. Nevertheless, if you’re strapped for cash, you have a few options.
If you don’t have a relational database set up, Postgres is a good, free, and open-source option. With your data centralized in Postgres, you can use a free relational database management system (RDBMS), like MySQL, to make queries.
From here, you have two options: create a report or a real-time dashboard.
To create a report, export the results of your query as a CSV file, and upload it into Excel, Google Sheets, or Google Data Studio to visualize it. The problem here is that the data you fetch and visualize is not real-time data. By the time it’s in the report, the data may have changed. Not to mention, anytime you want an updated report, you’ll need to go through this process again.
To create a dashboard, you’ll need a free, open-source business intelligence tool such as Metabase. First, integrate your database or RDBMS with your BI tool, which will provide the ability to make queries and create a real-time SQL dashboard. Each tool has its own way of doing this, so it’s important to compare their strengths and weaknesses.
While these options are free, they’re labor-intensive and not well suited for scale. If you’re putting this much effort into creating a SQL dashboard, you may as well invest in a tool that’ll make your life a little easier. A tool like, say, Atlassian Analytics.
Au-delà d'Agile
Atlassian Analytics is a BI tool that makes SQL data easy for everyone to use.
1. Connect your data
If you don’t want to use the demo data, Connect your data in the “Data” tab. Just click “Add Data Source” and follow the instructions.
Connect your data through a direct connection, using any one of the following:
- MySQL
- Oracle
- PostgreSQL
- MariaDB
- Amazon Redshift
- Microsoft SQL Server
- Google BigQuery
- Cloud application data integrations via ETL (extract, load, transform) tools, such as Stitch
- Upload of CSV or Google Sheets file
And many more
Or use an SSH tunnel connection for private and secure networks.
Once your data is hooked up, start creating by going to the “Dashboards” tab and clicking “New Dashboard.”
2. Use visual SQL to make queries and explore
Visual SQL allows anyone to fetch and explore data without code while speeding up the workflow of SQL veterans. Here’s how it works:
After you’ve made your queries, explored the data, and are happy with the results, just click “Save to Dashboard.”
3. Drag, drop, filter and customize
With your graphs and charts added to your dashboard, you can make it your own with a wide variety of customization tools.
4. Share it with the world!
Invite team members to collaborate on it,embed it, export it, present it, create a shareable link, create alerts, or schedule a regular email or Slack report. You have multiple options for getting your dashboard out into the world. Here’s an example of a Slack alert:
.png?cdnVersion=2445)
Au-delà d'Agile
Whether you use Atlassian Analytics, a free tool, or another business intelligence platform, real-time SQL dashboards will quickly become an integral part of your everyday business. Your decision-making will improve, all stakeholders will be on the same page, and everyone that needs it will gain an understanding of how your business is really functioning.