Connecting Looker to analytics data

Connect Looker to Tallyfy data

Section titled “Connect Looker to Tallyfy data”

You can connect Looker to Tallyfy’s analytics data through Amazon Athena in about 15-20 minutes. Tallyfy exports your workflow data as Parquet files (with Snappy compression) to S3, and Athena lets Looker query that data directly - so you can build LookML models and dashboards from real process metrics.

Looker vs Looker Studio

This guide covers Looker (Google Cloud’s enterprise BI platform). For Looker Studio (the free visualization tool), see the Looker Studio connection guide instead.

Requirements

Section titled “Requirements”

Before you start, confirm you have:

• Tallyfy Analytics enabled for your organization (the analytics_enabled flag must be active)
• Looker access - either self-hosted or Looker Cloud
• Admin permissions in Looker to create database connections
• Admin access to your machine for driver installation (self-hosted only)
• AWS Access Key credentials from Tallyfy Support

Authentication overview

Section titled “Authentication overview”

Athena supports two authentication methods. For Looker, you’ll want Access Keys.

Method	Description	Best for
IAM Username/Password	Web console access credentials	AWS Console access only
Access Key/Secret	Token-based authentication	BI tool connections (recommended)

Two separate credential sets

Tallyfy provisions two credential sets when activating Analytics:

• AWS IAM login - for the AWS web console only
• Access Key + Secret Access Key - for connecting Looker and other BI tools

Use the Access Keys for Looker. Token-based auth avoids “security token invalid” errors that plague console-credential connections.

Setup process

Section titled “Setup process”

Step 1: Install the JDBC driver (self-hosted Looker only)

Section titled “Step 1: Install the JDBC driver (self-hosted Looker only)”

Using Looker Cloud? Skip ahead - Google pre-installs the Athena drivers. For self-hosted instances:

1. Download the Simba Athena JDBC Driver from the AWS Athena JDBC documentation page ↗.
2. Place the driver in your Looker JDBC directory (typically /usr/local/looker/looker/lib/custom/)
3. Restart Looker to load the new driver

Driver versions

AWS provides 2.x and 3.x JDBC drivers. The 3.x driver handles large result sets better. Use the latest 3.x unless you have compatibility constraints.

Step 2: Configure the Looker connection

Section titled “Step 2: Configure the Looker connection”

1. Log into Looker as an administrator
2. Go to Admin > Database > Connections
3. Click Add Connection
4. Select Amazon Athena as the dialect
5. Fill in the connection parameters:
   • Name: Something descriptive (e.g., “Tallyfy Analytics”)
   • Host: athena.us-east-1.amazonaws.com (Tallyfy’s default AWS region is us-east-1)
   • Port: 443
   • Database: Your assigned database name (typically your organization name)
   • Username: Your Access Key ID from Tallyfy
   • Password: Your Secret Access Key from Tallyfy
6. In Additional Params, add:

   s3_staging_dir=s3://your-staging-bucket/looker-results/

7. Click Test to verify the connection
8. If the test passes, click Connect

S3 staging directory

Athena stores query results in this S3 location. Tallyfy Support provides the path when provisioning your Analytics account. Your Access Key needs write permissions to this bucket.

Step 3: Tune performance settings

Section titled “Step 3: Tune performance settings”

1. In the connection settings, find Additional Params
2. Append these performance parameters:

   ;UseResultsetStreaming=1;MaxConcurrentQueries=20

3. UseResultsetStreaming=1 enables streaming for large result sets (requires the athena:GetQueryResultsStream IAM policy)
4. MaxConcurrentQueries=20 increases the connection pool for heavier usage
5. Save the connection settings

Connection pooling

Default is 5 concurrent connections. If Looker is your primary Tallyfy analytics tool, raise this to 10-20. For occasional exploration, 5 is fine.

Step 4: Set up temp database for PDTs

Section titled “Step 4: Set up temp database for PDTs”

Persistent Derived Tables (PDTs)¹ cache complex calculations so dashboards load faster:

1. In your connection settings, find Temp Database
2. Enter the S3 directory path: tallyfy-athena-results/looker-pdts/
3. Looker stores pre-calculated tables here for reuse
4. Save the settings

Working with Tallyfy data in Looker

Section titled “Working with Tallyfy data in Looker”

Available data

Section titled “Available data”

Tallyfy Analytics exports two categories of data to S3 as Parquet files:

• Run analytics - per-process data including process metadata (blueprint name, status, owner), task details (assignments, completion dates, due dates), form field values (questions and answers), comments, and issue tracking
• Members - user activity and profile data (email, name, role, login history, status)

These are organized per-organization in S3, and Athena tables may present this data across views for processes, steps, form fields, and users.

Building a LookML model

Section titled “Building a LookML model”

Here’s a basic starting point:

1. In Looker, go to Develop > LookML Projects
2. Create a new project or pick an existing one
3. Create a model file with your connection:

   connection: "tallyfy_analytics"
   
   explore: processes {
     join: steps {
       sql_on: ${processes.process_id} = ${steps.process_id} ;;
       relationship: one_to_many
     }
   }

4. Create view files for each data table
5. Define dimensions and measures based on your business needs

Dashboard ideas

Section titled “Dashboard ideas”

Some practical dashboards you can build from Tallyfy data:

1. Process duration analysis - find where workflows get stuck (durations show elapsed time, not effort)
2. Team performance scorecards - completion rates and average handling times per user
3. Form data analytics - spot trends in customer requests or quality issues
4. Active process monitors - real-time view of running processes and their current status
5. SLA compliance tracking - measure whether tasks complete within target timeframes

Interpreting time metrics

Duration metrics show elapsed calendar time between task assignment and completion, including weekends, holidays, and waiting periods. For actual effort tracking, see how to track time spent on tasks.

Advanced features

Section titled “Advanced features”

Embedding Looker dashboards

Section titled “Embedding Looker dashboards”

The Looker API lets you share insights outside Looker itself:

• Embed dashboards in internal portals or wikis
• Share via Slack using Looker’s Slack integration
• Schedule email delivery of reports
• Create public dashboards with controlled access

Query optimization

Section titled “Query optimization”

A few ways to keep queries fast:

• Aggregate awareness - pre-calculate common metrics in PDTs
• Incremental PDTs - only recalculate changed data
• Push filters to Athena - filter at the query level rather than post-processing in Looker
• Monitor query performance - use Looker’s Query History to find slow queries

Troubleshooting

Section titled “Troubleshooting”

Connection not working? Check these in order:

• Double-check your Access Key and Secret Access Key - one wrong character breaks the connection
• Verify the S3 staging directory path is correct and your credentials have write access
• Confirm the JDBC driver is installed properly (self-hosted only)
• Test that Looker can reach athena.us-east-1.amazonaws.com on port 443
• Verify your Tallyfy Analytics subscription is active
• Enable debug logging by adding ;LogLevel=DEBUG;LogPath=/tmp/athena_debug.log to Additional Params, then check /tmp/athena_debug.log for details

Still stuck? Contact Tallyfy Support with the exact error message.

Related articles

Section titled “Related articles”

Analytics > Looker

Looker and Looker Studio both connect to Tallyfy Analytics through Amazon Athena so teams can…

Looker > Connecting Looker Studio to analytics data

Looker Studio connects to Tallyfy Analytics data through a CData or OWOX connector in about…

Tableau > Connecting Tableau to analytics data

Tallyfy Analytics connects to Tableau Desktop through Amazon Athena in about 15 minutes. Install…

Integrations > Tallyfy Analytics

Tallyfy Analytics is an add-on that replicates your workflow data into a private Amazon Athena…

Footnotes

Section titled “Footnotes”

1. Looker’s caching layer that pre-computes query results and stores them in S3 for faster repeat access ↩

Was this helpful?

YesNo