SQL Analytics

This will help you connect to your ion analytics

Requirements

If you are an Enterprise customer, contract us to securely acquire your access information and credentials. You will receive:

  • User name

  • Password

  • Your results output location

You will need a SQL client that support JDBC drivers. Our favorite is DataGrip because it supports a wide range of databases out of the box. But any client that succeeds with this setup guide will work!

Setup

Download the Athena JDBC driver

Download the Athena JDBC driver here: https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html‚Äč

Setup the driver and source

Follow these instructions from JetBrains. If you are using DataGrip, this will be even easier for you. If you are not, you should be able to map their instructions to your SQL client.

Setup notes / gotchas

  • Set the exact driver mentioned (com.simba.athena.jdbc42.Driver)

  • In the advanced set up, set S3OutputLocationas the one given in your credentials. It should be in the format s3://somename-with-your-company

Queries

We are working on changing the naming requirements to make it less redundant for your queries.

Analytics are provided by AWS Athena, which is a database based on PrestoDB. You can find usage and syntax documentation online. It has a SQL interface, so if you know SQL, much of it should familiar. If you plugging data into a business intelligence / data analysis tool, it should work fine too.

Table structure and Example Queries

Tables:

Every table name must be globally unique, so each table name is prepended with the organization. For example the runs table, will be firstresonance_io_runs for the firstresonance organization. Also in order to optimize queries the tables have flattened relationships so that users do not have to run time consuming joins to calculate needed properties. Below is a list of the currently available analytics tables.

  • abom_items

  • issues

  • parts

  • parts_inventory

  • procedures

  • redlines

  • run_steps

  • run_steps_fields

  • runs

  • steps

  • steps_fields

Example Queries:

Count of runs grouped by status:

SELECT status,
count(id)
FROM firstresonance_io.firstresonance_io_runs
GROUP BY status;

Time statistics on the execution of a particular step in runs.

select
avg(date_diff('second', start_time, end_time)) as avg_seconds,
stddev(date_diff('second', start_time, end_time)) as std_dev_seconds,
updated_by_email
from firstresonance_io.firstresonance_io_run_steps
where end_time is not null
and started_by_id = updated_by_id
and origin_step_id = 1
group by started_by_id, updated_by_email
order by avg_seconds

Cumulative issue count and percentage grouped by part number:

select
cumulative_sum / max(cumulative_sum)
over (order by count) * 100 as percentage,
count, part_number, cumulative_sum
from (select
cast(count(i.id) as double) as count,
cast(sum(count(i.id))
over (order by count(i.id) desc, r.part_part_number asc)
as double) as cumulative_sum,
r.part_part_number as part_number
from firstresonance_io.firstresonance_io_issues i
join firstresonance_io.firstresonance_io_runs r
on r.id = i.run_step_run_id
where r.part_part_number is not null
group by r.part_part_number)
order by count desc

In progress. Have more queries? Share them with us. We'd love to showcase your work.