Metabase was primarily built to support traditional databases like MySQL, PostgreSQL, SQL Server, in addition to cloud offerings like Amazon Athena, Google BigQuery, Snowflake, etc. As its data source. However, there are extensions by the community that provide access to other types of data sources. Connect google bigquery connect using sqirrel how to retrive image from sql to picturebox usinf image location Msg 8164, Level 16, State 1, Procedure getSalaryMonth, Line 31 Batch Start Line 13 An INSERT EXEC statement cannot be nested. Describe the bug Hi. I'm brazilian and when we create tables or fields, we gave portuguese names to be more familiar to ours contrubutors. Recently we upgraded Metabase from 0.36 to 0.38, and a lot of questions stoped to work. Log in to your Metabase account. Click Settings (gear icon) in the top right corner of the screen, and then, Admin. Click Databases in the Admin menu, and then, Add database. Power BI: Tableau: Power BI is the business data analytics tool to analyze the business and derive insights from it. Tableau is the business intelligence and data analytics tool for generating reports and data visualization with high flexibility.
Over the years, the PostgreSQL community has come up with many high-qualityopen-source GUI tools for manipulating, visualizing and sharing the data livingin Postgres servers. Here is a round up of various types of useful GUI toolsthat can help you and your team manage and share your data.
pgAdmin
- Home Page: https://www.pgadmin.org
- Type: Browser-based desktop GUI
pgAdmin is the venerable quasi-official feature-rich GUI for PostgreSQL. It letsyou run queries as well as explore and examine every bit of your server anddatabases.
OmniDB
- Home Page: https://omnidb.org/index.php/en/
- Type: Browser-based desktop GUI
OmniDB also lets you run queries and explore and edit database objects. It alsosupports servers other than Postgres, including MySQL and Oracle. Supportsdebugging of user-defined functions (stored functions) written in PL/pgSQL.
DBeaver
- Home Page: https://dbeaver.io/
- Type: Java/Eclipse-based Desktop GUI
DBeaver is similar to pgAdmin and OmniDB, with support for more RDBMS servers.Can generate ER diagrams from the schema automatically.
pgweb
- Home Page: http://sosedoff.github.io/pgweb/
- Type: Single-binary Go-based web server
pgweb is a simple, easy-to-deploy tool that runs as a web server. You can viewdata, run queries and examine tables and indexes.
Metabase
- Home Page: https://www.metabase.com/
- Type: BI tool, server install
Metabase is a business intelligence tool, that can help your team setup, runand visualize queries. Knowledge of SQL is optional. Supports RDBMS (and datasources) other than PostgreSQL also.
Redash
- Home Page: https://redash.io/
- Type: BI tool, server install
Redash is also a business intelligence tool for teams, with a more hands-onapproach. It lets you write SQL queries directly and setup visualizationsand dashboard with more options and customizations.
Blazer
- Home Page: https://github.com/ankane/blazer
- Type: Basic BI queries, dashboards; server install
Blazer is a Ruby-on-Rails gem that let’s users define queries, visualizationsand dashboards, and share them with their team. You’ll need to know your wayaround RoR to make use of this.
Apache Superset
- Home Page: https://superset.incubator.apache.org/
- Type: BI tool, server install, Python-based
Apache Superset is an incubating (pre-release) BI tool of the Apache SoftwareFoundation. It has a rich set of visualizationsand support for many data sources.
Apache Zeppelin
- Home Page: https://zeppelin.apache.org/
- Type: Notebook, server install, Java-based
Zeppelin is a web-based notebook (like Jupyter for Python) from the ApacheSoftware Foundation. It lets you interactive explore the data from many sources(including PostgreSQL of course) with visualizations.
franchise
- Home Page: https://franchise.cloud/
- Type: Notebook, Online
Franchise is a web-based notebook that can connect to your local database usinga bridge tool, thus requiring no server installation. Supports data sources likeMySQL and BigQuery also.
More..
You can find even more links on the PostgreSQL Wiki,including commercial software, tools for ER diagrams, IDEs and more.
About pgDash
pgDash is a modern, in-depth monitoring solution designedspecifically for PostgreSQL deployments. pgDash shows you information andmetrics about every aspect of your PostgreSQL database server, collected usingthe open-source tool pgmetrics.pgDash provides core reporting and visualizationfunctionality, including collecting and displaying PostgreSQL information andproviding time-series graphs, detailed reports, alerting, teams and more.
The Diagnostics feature in pgDash examines your PostgreSQL server and databasesscanning for potential issues that can impact the health and performance of thedeployment. No additional setup is required for Diagnostics - you just need tobe actively sending in data to pgDash. Learn morehere or signup todayfor a free trial.
How do you begin combining data from cloud applications with your internal databases to gain insight into your business? Maybe your organization has already standardized on Metabase as your analytics tool, but you're still learning about using it with multiple data sources.
Metabase Bigquery
To analyze data from diverse sources, you need a data warehouse that consolidates all of your data in a single location. Most businesses take advantage of cloud data warehouses such as Amazon Redshift, Google BigQuery, or Snowflake.
You can extract data that you have stored in SaaS applications and databases and load it into the data warehouse using an ETL (extract, transform, load) tool. Once the data is available, your analysts can use it to create reports. In this post, we'll look at how to start from scratch and create a report using Metabase, an open source business intelligence (BI) tool that's free to download and use.
Metabase Bigquery Convert
Metabase's visual query builder lets you generate simple charts and dashboards, or you can use SQL to create more complex visualizations, as we'll do here. Each query starts by clicking a button to 'ask a question.' Metabase is simpler than tools like Tableau and Power BI, offering fewer features, but it's correspondingly simpler to learn.
Reporting, data warehouses, and ETL
Metabase can run in the environment of your choice via a Docker image, on AWS Elastic Beanstalk, or on Heroku, or you can run it as a native application on macOS or as a Java jar file. I used macOS version 0.31.1.
Per its FAQ, 'Metabase is primarily meant to work with actual databases.' If you want to analyze data in SaaS platforms, the developers 'suggest that you use other tools to build a data warehouse with the data you need.'
That's what we'll do with Stitch, a simple, powerful ETL service for businesses of all sizes, up to and including the enterprise. It can move data from dozens of data sources. Sign up and you can be moving data to a data warehouse in five minutes. Choose at least one of Stitch's integrations as a data source. Most businesses get more value out of their data as they integrate more data sources; for our example, we'll look at data from two separate sources.
Setting up a data warehouse
I used some of Stitch's real data to build a data visualization for this post. Specifically, I was curious to see how many support conversations came from each of Stitch's pricing tiers. To find out, I'd need to use billing data stored in Salesforce and join it with support conversation data from Intercom to create a visualization of support conversations.
My first step was to set up a BigQuery data warehouse to store the data from the two SaaS platforms. Following our documentation, I added to my Google user the permissions that Stitch would need to load data to BigQuery.
Using Stitch makes extracting data from a source and loading it into a data warehouse easy. I logged in to Stitch and added new integrations for Salesforce and Intercom, following the instructions in our documentation. From Salesforce I selected the Account table to replicate. Stitch's Intercom integration automatically extracts data from all available tables. From both, I chose the period from which to replicate data.
Once I'd set up my integrations I added BigQuery as my destination and specified a BigQuery project name. Within a few minutes, Stitch extracted the data I specified and loaded it into BigQuery.
Building a sample report
Next I turned to Metabase.
To start, I clicked on Metabase's settings icon, then Admin. I chose Add a Database and entered the names of my BigQuery project ID and other information, which I obtained from the BigQuery dashboard. As part of that process I had to generate an OAuth 2.0 Client ID and Client Secret — a simple process that involved entering some simple information on a screen connected to a Click here link. Similarly, I generated an Auth Code by providing my Google authentication information.
Once you have a database to work with, you can 'ask a question' in one of three ways:
If you choose Metrics, Metabase directs you to create segments and metrics from its admin panel. You can choose Custom for slightly more complex queries on a single table. I wanted to join data from two tables, but a Metabase blog post says:
Our goal with Metabase has always been to provide a way for non-technical users to answer their own questions in a self-serve manner. While joins are a great tool that a skilled analyst or programmer might reach for, we will be trying to add features that expose a highly specific, easily understood operation that someone who isn't SQL fluent would understand.
Fortunately, we're SQL-fluent around here. I chose Native Query, which lets you paste in SQL code.
A look at the Intercom schema showed me that to associate the number of conversations with the Stitch customer ID, I'd need to join the Intercom Users and Conversations tables. The Users table contains a field called companies that contains information about the companies a given user is associated with. That field is a list that could contain multiple values – a nested data structure, in other words. Many data warehouses don't support nested data structures, but BigQuery does. It stores each list in an array.
Retrieving data from a nested data structure is tricky if you're used to working with fully normalized data. My colleague Erin, Stitch's senior technical documentation manager, set me on the right path with this query:
The SQL UNNEST operator takes an array and returns a table, with one row for each element in the array.
To get the plan tier information from our Salesforce Account table, I created another query:
I used those those queries, along with data from Intercom's Conversations table, to retrieve the data I wanted, joining Intercom and Salesforce data. I used a case clause to consolidate data from monthly and annual billing plan into a single bar for each pricing tier:
Running this query in the BigData console gave me the data I wanted. Pasting it into Metabase gave me a table of data. To turn it into a visualization, I selected Bar from the Visualization drop-down and chose tier as the X axis. The result was a useful visualization.
Metabase lets you customize bar colors and axis labels. When you're satisfied with your visualization, you can save it in a collection and publish it to a dashboard that other users can share.
So there you have it – a quick walk through the process of using an ETL tool like Stitch to move data from multiple sources into a data warehouse, then report on it using Metabase. Sign up for a free trial of Stitch and start creating your own.