Build a Slack Dashboard (Part 2): Loading Into Postgres & Creating Basic Charts

Srini Kadamati

Welcome! This is the second of a three part blog series on building a basic Slack dashboard.

In part 1 of this blog series, we learned:

Part 1 Recap

In this post, we'll walk through:

  • how to load data from Meltano into a database
  • create the following charts that require no ELT

Slack Dashboard

Loading Slack Data Into PostgreSQL

Superset can create charts and dashboards using data from pretty much any SQL-speaking datastore.

  1. Let's start by installing the PostgreSQL loader, named target-postgres, to our Meltano project.
meltano add loader target-postgres

Target Postgres

  1. If you don't have an existing PostgreSQL database for loading data, follow this tutorial on the Meltano documentation to get an instance setup locally. Note that if you're using Superset via docker-compose, a PostgreSQL database comes packaged as one of the services.

  2. Once you have a PostgreSQL database setup, we need to configure the connection details in our Meltano project. The recommended way is to use a .env file that contains the necessary environment variables for Meltano to access and use for connecting.

    Start by creating a .env file in the same folder and then use vim, emacs, or your favorite text editor to add the following variables:

    export PG_ADDRESS=""
    export PG_USERNAME=""
    export PG_PORT=""
    export PG_PASSWORD=""
    export PG_DATABASE=""
    export PG_SCHEMA=""

    If you want Meltano to load data in the PostgreSQL instance that's included in the Superset docker-compose setup, use the following settings:

    export PG_ADDRESS="localhost"
    export PG_USERNAME="superset"
    export PG_PORT="5432"
    export PG_PASSWORD="suprerset"
    export PG_DATABASE="postgres"
    export PG_SCHEMA="tap_slack"
  3. To test our pipeline from tap-slack to target-postgres, run the following command:

    meltano elt tap-slack target-postgres --job_id=daily_update

    You should see a flurry of command line output, ending with the following if everything worked as expected:

    target-postgres | INFO UPDATE 97
    target-postgres | INFO INSERT 0 0
    meltano         | Incremental state has been updated at 2020-09-29     17:35:23.985219.
    meltano         | Extract & load complete!
    meltano         | Transformation skipped.

Registering a Postgres Database in Superset

Now that our raw data is neatly tucked away in our PostgreSQL database, it's time to bake the metaphorical raw data and create some delightful charts that we can present in a dashboard.

  1. First, let's register the PostgreSQL we loaded Slack data to with Superset. Fire up Superset, navigate to Data > Databases, then click the + button in the top right corner.

Add New Database

Add the database connection credentials along with the following settings.

Edit Database Panel

You can read more about connection strings for PostgreSQL [in our docs](https://docs.preset.io/docs/en/postgres-connection).
  1. Now that we've added the PostgreSQL database, we can see the tables containing the data Meltano synced from the Slack API listed in the Data > Datasets page.

List Datasets

  1. We encourage you to explore the data in SQL Lab to get familiar with it. SQL Lab is an excellent way to quickly learn the schema of the different tables, preview thousands of rows of data to get some ideas for charts, and start spotting any data cleaning tasks that are needed to massage the data into a more useful format.

SQL Lab Exploration

Creating Basic Charts in Superset

Now that we have our data available to Superset, we're ready to start creating visualizations. We'll start with some simple charts and progressively scale up to more complex ones. If you need extra help, we recommend using our docs alongside this blog post.

  1. Start by opening the Explore view in Superset. From the Data > Datasets tab, click tap_slack.users (or your equivalently named table).

Explore View

  1. Tracking the number of users in the community is a good place to start. Create a Big Number chart that showcases the number of users in the community. Change the Visualization Type to Big Number, remove the Time Range filter, and add a Subheader.

Big Number

Simple but elegant! Save this chart by selecting **+ Save**, giving the chart a **Chart Name**, and creating a new dashboard named **Slack Dashboard**.

Saving Chart

  1. Next, let's add Big Number with Trendline charts for Weekly Threads, Weekly Messages, and Members per Month.

Big Number With Trendline Charts

If you navigate to Dashboards > Slack Dashboard, your dashboard should match the following (keep in mind the numbers and charts themselves will differ based on your data!).

  1. Now let's create a Table visualization that showcases the top timezones that the members of the Slack community live in. Save the chart to your dashboard when you're done.

Timezone table

  1. Finally, let's add the Slack logo to the dashboard. This requires adding a custom Markdown component to your dashboard. First, open your dashboard by navigating to Dashboards > Slack Dashboard (or whatever you named your dashboard). Then, switch to Edit mode and drag the Markdown component into your dashboard.

Adding IFrame Component

Finally, edit the Markdown so it contains an IFrame link to the Slack logo.

IFrame Markdown

Your dashboard should now resemble the following screenshot:

Dashboard Screenshot

Rearrange Charts to Craft a Cleaner Dashboard

  1. Starting from the left side of the dashboard, we need to stack the Slack IFrame above the total number of members chart. To stack 2 charts above each other, you need to first drag in a Column component.

Add Column

  1. Then, drag in the Big Number chart first into this column, then add the IFrame chart.

Stack Charts

  1. Finally, end by re-arranging and resizing the rest of the charts to match the following image:

Final Dashboard

In this post, we learned how to configure Meltano to sync Slack data to a database, how to configure Superset to query that database, and then use the Explore view in Superset to create charts without any coding.

In the next post, we'll cover how to perform ELT, create more advanced visualizations, and customize the dashboard further to your needs.

Follow Preset on Twitter and LinkedIn to get updates on our future content. If you have feedback on this post, please send it to srini at preset.io

Subscribe to our blog updates

Receive a weekly digest of new blog posts