Connect Power BI

Connecting Power BI to Tallyfy Analytics

Section titled “Connecting Power BI to Tallyfy Analytics”

Connect Power BI to Tallyfy Analytics through Amazon Athena¹ and build dashboards from your workflow data. The setup takes about 15 minutes.

With this connection, you can build interactive dashboards showing bottlenecks and team performance, create metrics tailored to your KPIs, schedule automated reports, and merge Tallyfy data with other business systems like your CRM or ERP.

Connection architecture

Section titled “Connection architecture”

Power BI connects to your Tallyfy data through Amazon Athena via an ODBC driver.

What to notice:

• Authenticate with your Access Key and Secret Key, never your AWS console sign-in
• All connections must use the us-west-2 region

Authentication methods

Section titled “Authentication methods”

When Tallyfy activates your Analytics subscription, you receive two credential sets:

Authentication Method	Description	Use for
IAM username and password	Signing in to the AWS website	The AWS console only, never the driver
Access Key and Secret Key	A typed key pair the driver sends with each query	Power BI, and every other BI tool

The dropdown named “IAM Credentials” is the right one

This is the one genuinely confusing part of the setup. The Athena ODBC driver has an Authentication Option called IAM Credentials, and that is the option you want, because it is the one that accepts a typed Access Key and Secret Key. It has nothing to do with the username and password you use to sign in to the AWS console, which the driver cannot use at all.

Do not pick IAM Profile. That option reads credentials from an instance profile or a local AWS config file, so there is nowhere to type your key and the connection will fail.

If you see “The security token included in the request is invalid”

Check your Workgroup setting first. Left at the default primary, the driver produces exactly this message even though your key is correct. Set it to your organization ID and try again. If the message persists, confirm you pasted the Access Key and Secret Key rather than your console username and password.

Connection setup

Section titled “Connection setup”

Requirements

Section titled “Requirements”

• Power BI Desktop installed
• Tallyfy Analytics subscription activated
• Amazon Athena ODBC driver version 2 installed (64-bit)
• Access Key credentials provided by Tallyfy
• Outbound port 444 open on your firewall - Athena uses it to stream query results back to the driver

Step 1: Install the Amazon Athena ODBC driver

Section titled “Step 1: Install the Amazon Athena ODBC driver”

1. Download the Amazon Athena ODBC driver version 2 from the AWS ODBC 2.x driver page ↗. Version 1 is the older Simba-branded driver; version 2 is the current one
2. Run the installer and follow the on-screen instructions
3. Complete the installation before proceeding

Step 2: Configure the ODBC connection

Section titled “Step 2: Configure the ODBC connection”

1. On Windows, open the ODBC Data Source Administrator (64-bit)
2. Go to the System DSN tab
3. Click Add to create a new data source
4. Select the Amazon Athena ODBC Driver and click Finish
5. Configure the connection using the settings below
6. Click Test to verify, then OK to save

Setting	Value
Data Source Name	Tallyfy Analytics
Description	Tallyfy workflow data
AWS Region	us-west-2
S3 Output Location	s3://aws-athena-query-results-570841547480-us-west-2/<your-orgID>/
Authentication Option	IAM Credentials
Access Key	Your Tallyfy-provided Access Key
Secret Key	Your Tallyfy-provided Secret Access Key
Workgroup	Your organization ID (as provided by Tallyfy)
Catalog	AwsDataCatalog

Step 3: Connect Power BI to Athena

Section titled “Step 3: Connect Power BI to Athena”

1. Launch Power BI Desktop
2. Click Get Data in the ribbon menu
3. Select More… to view all connectors
4. Choose ODBC from the Database section and click Connect
5. From the DSN dropdown, select the Tallyfy Analytics connection you created
6. Click OK to establish the connection
7. In the Navigator window, expand AwsDataCatalog
8. Select your database (typically your organization name)
9. Choose the tables you want to analyze
10. Click Load to import the data into Power BI

Working with Tallyfy data

Section titled “Working with Tallyfy data”

You’re connected. Your Tallyfy Analytics data is organized as:

• Per-process Parquet files - Each process run is exported as a single flat table. Rows are differentiated by the type column: kick_off_task (kick-off form data), process_task (task-level data), process_task_data (form field answers within tasks), and process_task_comment (comments). Columns include process metadata (blueprint_name, process_status, process_owner), task details (process_task_name, completed_on, due_by, user_assigned), and form field data (form_field_type, question_in_form_field, answer_in_form_field).
• Members table - User data including email, full_name, job_title, is_active, timezone, and login activity timestamps.

Time metrics show elapsed time

Completion times show elapsed calendar time, not actual working time. A “3-day” task might be 30 minutes of work interrupted by a weekend. For accurate effort metrics, implement time tracking fields as described in how to track time spent on tasks.

Calculating accurate process completion times with NaN handling

The process_last_modified field updates whenever a process is edited, archived, or modified - not just when it’s completed. For accurate completion time calculations, use the maximum completed_on timestamp from all tasks in the process instead.

Important: Date fields may contain the string “NaN” when the original database value was NULL. Always filter these out to prevent errors.

DAX Formula (Calculated Column) - Process Completion Timestamp:

Process_Actual_Completion =
CALCULATE(
    MAXX(
        FILTER(
            'Query1',
            'Query1'[process_id] = EARLIER('Query1'[process_id])
            && 'Query1'[type] = "process_task"
            && NOT(ISBLANK('Query1'[completed_on]))
            && 'Query1'[completed_on] <> "nan"
        ),
        DATEVALUE('Query1'[completed_on]) + TIMEVALUE('Query1'[completed_on])
    )
)

DAX Formula (Measure) - Average Process Completion Time:

Average_Process_Completion_Time =
VAR ProcessCompletions =
ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            'Query1',
            'Query1'[process_status] = "complete"
            && 'Query1'[type] = "process_task"
        ),
        'Query1'[process_id]
    ),
    "First_Assigned_Date",
    CALCULATE (
        MINX (
            FILTER (
                'Query1',
                NOT(ISBLANK('Query1'[assigned_or_shown_on]))
                && 'Query1'[assigned_or_shown_on] <> "nan"
            ),
            DATEVALUE('Query1'[assigned_or_shown_on]) + TIMEVALUE('Query1'[assigned_or_shown_on])
        )
    ),
    "Last_Task_Completed_Date",
    CALCULATE (
        MAXX (
            FILTER (
                'Query1',
                NOT(ISBLANK('Query1'[completed_on]))
                && 'Query1'[completed_on] <> "nan"
            ),
            DATEVALUE('Query1'[completed_on]) + TIMEVALUE('Query1'[completed_on])
        )
    )
)
RETURN
AVERAGEX (
    FILTER (
        ProcessCompletions,
        NOT(ISBLANK([First_Assigned_Date]))
        && NOT(ISBLANK([Last_Task_Completed_Date]))
    ),
    DATEDIFF([First_Assigned_Date], [Last_Task_Completed_Date], DAY)
)

Key Points:

• Timestamp Precision: Using DATEVALUE() + TIMEVALUE() preserves exact completion times down to the second. Using DATEVALUE() alone would only give day-level precision, losing hours, minutes, and seconds - resulting in fractional days for sub-day durations (e.g., 2.3 days instead of just 2 days)
• Groups by process_id ONLY (not by assigned_or_shown_on) to avoid duplicate rows
• Filters out both ISBLANK and “nan” string values
• Calculates when the last task was completed, representing the true process completion timestamp
• Works correctly even if processes are archived/unarchived later

Why NaN Appears: NULL database values → Pandas conversion → Parquet export → string “NaN”

Configuration notes

Section titled “Configuration notes”

• You must connect to the us-west-2 region - other regions won’t work
• Your workgroup must match your organization ID exactly
• The catalog name is always AwsDataCatalog (exact capitalization matters)
• Use Direct Query mode² for datasets over 1GB
• Set up refresh scheduling if you need reports updated automatically

Scheduled refresh and the on-premises data gateway

Section titled “Scheduled refresh and the on-premises data gateway”

Power BI Desktop connects to Athena with nothing more than the ODBC driver and your DSN. Publishing is where it changes.

Once you publish a report to the Power BI Service and want it to refresh on a schedule, you need an on-premises data gateway. The Service has no Athena driver of its own, so the gateway machine does the querying on its behalf. Download it from https://powerbi.microsoft.com/en-us/gateway/ ↗

Install the same Amazon Athena ODBC driver on the gateway machine, and create a DSN there with exactly the same name as the one on your desktop.

Use a System DSN, not a User DSN

On the gateway machine the DSN must be created on the System DSN tab. A User DSN produces the error “Data source name not found and no default driver specified” when the gateway tries to connect.

The reason is that User DSNs live in the HKEY_CURRENT_USER registry hive, and the gateway runs as a Windows service under an account that never loads that hive. This is a Windows limitation rather than anything specific to Athena.

Personal mode is fine for testing. Use standard mode for anything a team depends on.

Import or DirectQuery, and what it costs

Section titled “Import or DirectQuery, and what it costs”

Athena bills per terabyte of data scanned, and that includes queries that fail or get cancelled. Which connectivity mode you pick therefore has a direct cost consequence.

Import with scheduled refresh scans your data once per refresh. DirectQuery fires a fresh Athena query every time somebody interacts with a visual, so an actively used dashboard can scan the same data hundreds of times a day.

Prefer Import unless your dataset is genuinely too large to hold in memory. Either way, open Transform Data and remove the columns you do not need before loading. Tallyfy stores your data as Parquet, which only reads the columns a query touches, so dropping unused columns cuts both refresh time and cost. Selecting the table in the Navigator and clicking Load pulls all 49 columns and gives that advantage away.

Video tutorial

Section titled “Video tutorial”

Troubleshooting

Section titled “Troubleshooting”

• Verify you’re using the Access Key and Secret Access Key, with the authentication type set to IAM Credentials. The IAM user name and console password are for the AWS web console only and will not work here
• If you get “The security token included in the request is invalid”, check the workgroup first. Left on primary it throws this error even when your keys are correct
• The ODBC driver must be the 64-bit version (32-bit won’t work with Power BI Desktop)
• Confirm the region is us-west-2
• Check that your Tallyfy Analytics subscription is active
• Restart Power BI Desktop after configuring a new ODBC data source

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

Related articles

Section titled “Related articles”

Analytics > Power BI

Tallyfy’s Power BI integration connects your workflow data through Amazon Athena so you can…

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…

Analytics > How Tallyfy Analytics works

Tallyfy Analytics processes your workflow data through a five-step pipeline, converting events…

Footnotes

Section titled “Footnotes”

1. A serverless SQL query service that analyzes data in S3 using standard SQL syntax ↩

2. Queries data directly from source instead of importing, ideal for large datasets or real-time needs ↩

Was this helpful?

YesNo