v0.39.0.1 / Administration Guide / Oracle
Working with Oracle in Metabase
- The Metabase Store. Login to manage your Metabase Cloud account or other products. Looking to get started with Metabase or Metabase Cloud?
- Please email us at customerservice@thesoftwarereport.com or call us at 646-598-3815 and we will get back to you right away. We look forward to hearing from you.
- Empleos.io (PLEO) Sale Starts November 15 Sale Ends December 5 / A new technological platform that brings Blockchain, Machine Learning, and a Rewards system into the online recruiting market Javvy (JVY) Sale Starts November 15 Sale Ends May 14, 2019 / The full integration of a universal wallet connected to numerous exchanges presently available.
- A short video explaining how you can run the open source data visualization software Metabase on your local machine.
Starting in v0.20.0, Metabase provides a driver for connecting to Oracle databases. Under the hood, Metabase uses Oracle’s JDBC driver; due to licensing restrictions, we can’tinclude it as part of Metabase. Luckily, downloading it yourself and making it available to Metabase is straightforward and only takes a few minutes.
MaaxMarket mabl Macabacus MacCaption MacheteSoft Team MachineShop macOS MacroPoint MacroView MacStadium MadCap Software Made in Office Madison Logic MadKudu Madyourself Maestro Health MaestroQA MageMail Magento MagicLinks Magicpay MagicSoft Recovery Magisto MAGIX Magmito Magnet Magnetic Software MagNews Magnifinance Magnolia Magstar MailBakery.
Downloading the Oracle JDBC Driver JAR
Metabase Postgresql Json
You can download the JDBC driver from Oracle’s JDBC driver downloads page.Head to this page, accept the license agreement, and download ojdbc8.jar:
Before downloading this JAR you may need to sign up for a free account with Oracle. We have had success with the latest version at the time of this writing, 19.3 (even with older Oracle 12c databases), but any version should work.
Adding the Oracle JDBC Driver JAR to the Metabase Plugins Directory
Metabase will automatically make the Oracle driver available if it finds the Oracle JDBC driver JAR in the Metabase plugins directory when it starts up.All you need to do is create the directory, move the JAR you just downloaded into it, and restart Metabase.
Connecting with SSL
To connect to Oracle via SSL and enable encryption, check the Use a secure connection (SSL)? option on the connectionsetup page. You can add other SSL features (including client and/or server authentication) as explained below. You canuse both client and server authentication (known as mutual authentication).
Server authentication
To configure the client (Metabase) to authenticate the identity of the server (the Oracle server), you may need toconfigure a truststore file that includes the server’s root CA, so that the JVM running Metabase trusts itscertificate chain. Refer to theOracle documentation on using keytool tomanage key and truststore files, importing certificates, etc. Once you have a truststore file ready, add thefollowing JVM options for Metabase:
With this done, the SSL connection to Oracle will authenticate the server.
For more information on setting up a truststore for AWS RDS Oracle instances, see theinstructions provided by Amazon.Note that if you require connecting to other databases using SSL, instead of creating a new truststore, as shown inthose examples, you’ll probably want to add the RDS CA to your existing truststore file (likely called cacerts),
Client authentication
To configure the server (the Oracle server) to authenticate the identity of the client (Metabase), you need toconfigure a keystore file that includes the client’s private key. The steps are almost identical to thoseabove, except that you will import the client’s private key into the keystore rather than a root CA into a truststorefile.
With this done, the Oracle server will authenticate Metabase using the private key when Metabase tries to connect overSSL.
When running from a JAR
By default, the plugins directory is called plugins, and lives in the same directory as the Metabase JAR.
For example, if you’re running Metabase from a directory called /app/, you should move the Oracle JDBC driver JAR to /app/plugins/:
When running the Mac App
If you’re running Metabase from the Mac App, the plugins directory defaults to ~/Library/Application Support/Metabase/Plugins/:
Finally, you can choose a custom plugins directory if the default doesn’t suit your needs by setting the environment variable MB_PLUGINS_DIR.
When running from Docker
The process for adding plugins when running via Docker is similar, but you’ll need to mount the plugins directory. Refer to instructions here for more details.
Metabase is a good, popular open-source BI tool that anyone can quickly install on your local environment to get a simple BI system up and running.
Yet when your analytics needs grow, you might face some of these pain points when using Metabase:
- Have your business users ever found Metabase’s “Ask a question” too limiting for complex queries, and end up coming back to the analytics team to ask for custom reports?
- Metabase only works well with a single SQL data source. If you have data from multiple sources, Metabase likely won't work well for you since it doesn't allow joining of data.
- With Metabase, you can only work directly with your database tables because everything in Metabase is designed for simplicity.
In this post, we'll share with you a few alternative options to that, and tell you which pain points of Metabase each of these alternative addresses. So depending on your needs you might be able to pick the right option to replace, or use alongside with Metabase.
The below list of options are only tools that eventually offer a drag-and-drop interface to end users. At the end of the post we also include a list of tools that is only designed for technical users, SQL-to-chart translation.
Holistics is a nice BI alternative to Metabase. It works similar to Metabase in a way that it allows you to map your database tables into models and relationships, and expose this to the end business users to 'self-service explore'.
Similars/Difference to Metabase:
- Both are built on top of SQL querying engine and both offers a drag-and-drop experience for non-technical users.
- Metabase fits only at at the visualization layer, Holistics offer additional ELT capabilities (data preparation).
Pros (compared to Metabase):
- A stronger data modeling layer that allows handling sophisticated raw data.
- Business users can ask more sophisticated questions using their Explore interface.
- Works well with non-SQL data sources as they offer a mini-ETL experience with common sources (MongoDB, Google Analytics, etc)
- Have an in-built DAG-like transformation layer so that you can transform raw data into aggregated datasets before exploration
- Cloud-based so it doesn't take time to setup.
Cons (compared to Metabase):
- Might not look as visually appealing as Metabase
Pricing:
- Free, paid plans start from $50-$500 per month.
Tableau is considered the best tool when it comes to visualization (prettiness) as it's their primary focus. Tableau is also recently acquired by Salesforce.
Similars or differences to Metabase:
- While Metabase translates everything to SQL, by default Tableau uses their in-memory datastore, making it more difficult to debug when things go wrong (you can't look at the SQL query to troubleshoot).
Pros (compared to Metabase):
- Pretty visualization (best in their class)
- Friendly for business users to build your own chartings
- Work with a wide range of data sources
Cons (compared to Metabase):
- To design charts effectively you need to use their Desktop version
Pricing:
- Based on user roles (Creator, Explorer, Viewer) with mimimum commitment required.
- They have a Free desktop version if you're publishing the reports publicly.
Coming out of Microsoft and with strong history of Excel and PowerPivot, PowerBI is a fine choice to replace Metabase. They also have ability to load custom visualization.
Metabase Percentile
Similar/Difference from Metabase:
- While Metabase translates everything to SQL, by default PowerBI uses their in-memory datastore and their proprietary language DAX, making it more difficult to debug when things go wrong (you can't look at the SQL query to troubleshoot).
Pros (compared to Metabase):
- Their explorer interface is comprehensive for end-business users to work with.
- They offer from loading data from multiple sources, to drag-and-drop transform UI, to visualization.
Cons (compared to Metabase):
- We suspect if you're more inclined towards SQL-backed data reporting like Metabase, you might not like Microsoft-style, Excel-like, proprietary approach of PowerBI.
- PowerBI Editor can only run on Desktop running Windows. (that's why we wrote a post on how to use PowerBI on Mac devices)
- Their best practice require you to host your data into PowerBI servers, i.e duplicating your data into their server.
Pricing:
- Free for single user (desktop)
- $10 per user for small-scale shared resource deployment
- Starts to get fairly expensive for medium-to-large scale deployment (starts at $5K USD a month - listed price on website).
Looker (now part of Google) is quite a good BI tool to replace Metabase, but only if you're a big organization with large budget.
Pros (compared to Metabase):
- Have a custom-built DSL layer (called LookML) to perform mapping between database tables and business logic, thus it's more flexible and customizable.
- Have an in-built transformation layer so that you can transform raw data into aggregated datasets before exploration
- Cloud-based so it doesn't take time to setup (as compared to Metabase)
Cons (compared to Metabase):
- Since they use their own DSL language to model data, it takes quite a learning curve to get started.
- It's also expensive and meant for large-scale deployment
Pricing:
Metabase
- Quite expensive, starting from $3000/month.
Metabase Port
If you don't need self-service capabilities that Metabase offers for business users, you can also check out these tools:
Metabase Plugin
- Redash (open-source SQL to chart tools)
- Cluvio (SQL to chart tool, paid offering with a free plan)
- Superset (opensource, SQL to chart tool, coming out of Airbnb)
- Mode Analytics (SQL to chart tool with paid offering)