You want embedded dashboards and customer-facing analytics for your clients, so you need to pick a database to run them.

But which one? It's a big choice for your business, and it's effectively irreversible. Once you commit, it's very difficult to change course.

So how do you make this high-stress, high-impact choice?

First off, look for a relatively easy-to-scale data sharing system — rather than one with manual, one-off reporting — which will result in better customer experiences.

There are lots of databases that allow you to do this. But before you commit, consider these features:

• Maturity curve — How advanced your data analysis is
• Speed — How much hardware do you need to run your analysis?
• Maintenance — Automation that improves your database
• Data amount — Defines how much space you need
• Data type support — Things like integers, characters, strings, floating-point numbers, and arrays
• Cost — Does it fit your budget?

There are hundreds of databases to choose from. Let's look at the features, pros, and cons of the five databases we recommend for embedded dashboards:

1. Snowflake

Snowflake's Data Cloud is a modern data warehouse. Snowflake's data storage, processing, and analytic solutions are far quicker, more straightforward, and more versatile than conventional services. 

What Does Snowflake Do?

Snowflake blends cutting-edge architecture created explicitly for the cloud with a brand-new SQL query engine. It offers users all of the features and capabilities of an enterprise analytic database along with a host of other features, like:

• Excellent control over data layout and indexing
• Advanced data-sharing features
• Separate storage and computing payments
• Robust semi-structured/JSON data support
• Automated maintenance and scalability
• Tons of new developer features

Why We Like Snowflake

Snowflake is a great choice if you're looking for a database with reporting as an analytics data cloud rather than a transactional database. It's also good if you're looking to share and govern data across organizational boundaries.

Check out these Snowflake reviews to see how it might fit your needs. Here's a summary for you:

• Solves traditional hardware-based data warehouse concerns, including:
• Restricted scalability
• Data transformation problems
• Delays or failures brought on by high query rates
• Scales up virtual warehouses to take advantage of additional computing resources
• Helpful if you need to load data more quickly or execute a large number of queries
• Facilitates data sharing and governance across Snowflake tenants

Explo's Perspective on Snowflake for Embedded Dashboards

At Explo, we recently worked with a company in the food-tech space to revamp and improve analytics for the restaurants they serve.

They wanted to showcase application data, but their existing data model and application database weren't designed to surface valuable insights for their customers. Snowflake allowed them to pull all their data into a centralized warehouse and create materialized views for analytics-specific tables.

Because of this optimization, they reduced dashboard load times to just a few seconds while scaling to hundreds of customers. Rather than spending months writing code to fit the unique needs of customer-facing analytics, organizations can deploy production-ready data sharing capabilities in a matter of days

2. Rockset

Rockset is a real-time analytics warehouse that offers operations-light searches on vast amounts of semi-structured data. 

It automates configuring, deploying, and denormalizing clusters — along with shard and index management.

Rockset can ingest data and begin running queries in around 15 minutes, depending on the amount of data collected. 

What Does Rockset Do?

Real-time integration.

Rockset creates a schema for your data automatically, which enables SQL queries for data sources without native SQL capabilities.

Rockset's other capabilities include:

• Column store
• All columns are indexed by default
• No need for an additional ETL tool, and no lag, so it’s real-time
• Fast query speeds are great for complex queries. Better for smaller data scale but more complex, real-time queries
• Easy management with simple cluster sizes to choose from
• A Flexible data model supported

Why We Like Rockset

• Rockset can take in large data streams
• Indexes the data so it's queried within two seconds
• Allows a high number of concurrent SQL queries
• Even for complicated queries, Rockset provides quicker results than traditional databases due to Converged Index.

Explo's Perspective on Rockset for Embedded Dashboards

A retail consulting company chose to implement Rockset to speed up their customer reporting. Prior to Rockset, they were streaming data through Kafka and used MongoDB as their primary database.

Rockset allowed them to centralize all their data into a high-performant database without an additional ETL tool. In addition, Rockset automatically reads in and supports semi-structured data and indexes all the fields, so once the data was loaded in, it was ready to query.

All this took less than a day to set up and was ready to plug into an embedded analytics solution to share insights with their retailers.

3. ClickHouse

ClickHouse is an open-source database that has a cloud-hosted version available. Performance-wise, it beats every other column-oriented database management system.

What Does ClickHouse Do?

Each ClickHouse server is capable of:  

• Processing tens of gigabytes of data in billions of rows per second 
• Using column store optimized for clickstream analytics
• Completing analysis jobs that traditional databases can't, such as:
• Running fast query speeds
• Traditional databases are often too expensive, or their data volume is too big to evaluate quickly with queries
• Supporting multiple concurrent queries
• This is great for customer-facing analytics
• Responding with low latency

ClickHouse performs on substandard hardware better than traditional databases.

Why We Like Clickhouse

ClickHouse is great if you're looking for aggregation over a specific column in large volumes of data.

• High performance
• Multiple engine options for adapting user cases
• Easy configuration of data replication

Explo's Perspective on Clickhouse for Embedded Dashboards

A cloud communications platform leveraged ClickHouse for its embedded analytics. Speed and scalability were their highest priorities as they wanted to showcase near real-time data for thousands of clients.

With Clickhouse's new cloud offering, they no longer need to spin up and manage their own database, which saved their developers days to ramp up and even more on ongoing maintenance.

Their new dashboard provides crucial metrics on API usage with load times at 3x faster than their previous solution.

4. Postgres

Postgres is an open source object-relational database system that supports:

• Developers — in creating applications
• Administrators — in safeguarding data integrity and creating fault-tolerant systems
• You — in managing your data regardless of dataset size

What Does Postgres Do?

Postgres lets you create new functions, specify your data types, and even write code in several programming languages without recompiling your database. 

Postgres complies with SQL and supports most of the SQL standard's key capabilities.

Postgres also has:

• A robust access-control system
• Column and row-level security
• Multiple cloud-hosted vendor options

Why We Like Postgres

• Postgres is easy to set up, popular, and compatible with about any tool
• Startups and smaller companies can create a read-replica of their Postgres database without worrying about additional data infrastructure

Explo's Perspective on Postgres for Embedded Dashboards

A retail platform startup leverages its existing Postgres database to surface sales and inventory analytics to its customers. By spinning up a read replica of their current application database, they spun up analytics for their customers in minutes.

As a startup, they can also iterate quickly on their data structure and run simple queries fast to show their retailers as they build their platform.

5. BigQuery

BigQuery is a big data warehouse. It offers built-in technologies — machine learning, geospatial analysis, and business intelligence — to collect and analyze your data. 

What Does BigQuery Do?

With no infrastructure administration required, BigQuery's serverless architecture lets you perform SQL queries.

BigQuery's scalable, distributed analytical engine allows you to query terabytes of data in seconds and petabytes in minutes.

BigQuery uses a columnar structure for data storage that is ideal for analytical queries. It supports database transaction semantics and displays data in tables, rows, and columns. BigQuery storage is automatically mirrored across several locations to maximize availability.

Why We Like BigQuery

• BigQuery works well with Google Suite
• highly scalable with large amounts of data
• Fully managed, so it's easy to set up and doesn’t need much tuning

Explo's Perspective on BigQuery for Embedded Dashboards

A retail analytics company created dashboards for their direct-to-consumer clients using BigQuery. They showcase challenging-to-calculate but mission-critical metrics clearly and concisely.

They designed a simple UI that is fully managed and scalable. Initially, they didn’t know all their data sources and what their schema would be. So, they wanted a solution that allowed them to rewrite tables and update schemas easily.

With Explo, they connected directly to their existing databases and warehouses without replicating data or creating new data models. They copied a few lines of code and utilized Explo's API to embed our interactive dashboards and reports.

Final Thoughts

Embedded dashboards are a vital component of customer experience. 

Before implementing an embedded dashboard, choose a database that can power an embedded analytics application.

Evaluate your infrastructure, current technologies, and resources.  

• These options can help you get started. But, before you create your implementation plan, consider the additional demand on your tool and downstream databases, the cost, and the type and amount of data you have.

What’s next? Check out our brief demo.

‍

Other Dashboard Resources:

• DevOps Metric Dashboard
• Embedded Dashboards
• your FIRST Dashboard
• HR Dashboard
• Marketplace Dashboard‍
• React Dashboard‍
• Sales Dashboard‍
• Self Service Dashboard‍
• SQL Dashboard‍
• User Dashboard Design