Build a Focus and Talent report with Visual SQL

Where the data comes from

Watch this from 1:10

Every chart in Atlassian Analytics reads from a data source. For Atlassian apps, that source is the Atlassian Data Lake: one central database of your organization's app data, already modeled into tables and columns. There are no exports and no pipelines to build. You connect to it and you query it. If your data team wants the same model in its own platform, data shares let them extract it.

The Data Lake covers eight apps: Jira, Jira Service Management, Jira Product Discovery, Confluence, Focus, Talent, Goals and Atlassian Projects. Because they share one model, one chart can blend them:

• service requests beside Jira work
• goals beside the projects behind them
• Focus beside Talent, which is this lesson's example

In this example, Focus brings the focus areas, Talent brings the positions, and a mapping table records which positions are allocated to which focus area.

Watch out. The Data Lake is not real time. Changes arrive with its refresh: on the demo site, Talent changes appeared after the overnight refresh.

What a connection can see

Watch this from 2:09

A connection decides what Analytics can see. Only an organization admin can create or edit one. To create a connection:

1. Pick the apps and sites it reads, and include or exclude specific spaces.
2. Pick the scope of data.
3. Acknowledge the permission difference, explained in the next section.
4. Name the connection.
5. Add the people who can use it.

An organization can have up to ten connections, so sensitive data can sit in a connection of its own with a smaller audience. Access is then set per connection, at two levels:

Watch out. A connection that selects specific spaces does not pick up new spaces on its own. They stay out until someone edits the connection.

Permissions do not carry over

Watch this from 3:08

Permissions set in Jira, Confluence or Focus do not carry over to Analytics. In practice:

• There is no row-level security. A permission that hides a work item in Jira does not hide its row in Analytics.
• Access means the whole connection. Anyone who can query a connection, or view a dashboard built on it, sees everything that connection includes.
• Email visibility settings do not apply.

The connection is the permission boundary. Scope each connection on purpose, and share dashboards the same way: a dashboard's audience sees the whole connection underneath it.

Inside the chart editor

Watch this from 3:56

To open the editor, start on any dashboard, add a chart, and pick your Atlassian Data Lake connection. The editor has three parts:

Visual mode writes the SQL for you. Switch to SQL mode whenever you would rather write it yourself.

Add column opens the schema, where tables are grouped by app. Focus tables and Talent tables sit in the same schema, so one chart can use both. Hover any table to preview its rows before you pick a column. Each column you add shows the table it comes from. For this report, the first columns are focus area name and focus area health.

Group the labels, aggregate the numbers

Watch this from 5:00

The dropdown next to each column is the most important choice in visual mode. It decides whether the column becomes a label or a number:

The two counts differ when a join repeats a row. The video's sample has five rows, two focus areas, and one position that appears twice:

Group on focus area and you get two rows. Count of unique counts each position once: three and one. Count of all counts every row, so the repeat counts twice: four and one.

Rule of thumb. Group the columns you want as labels. Aggregate the columns you want as numbers. For positions behind each focus area: group name and health, and use count of unique on position ID.

Query filters

Watch this from 6:08

A query filter runs in the Data Lake, before anything is grouped or counted. Use it to shrink the data early. To add one:

1. Pick the column, here lifecycle status.
2. Pick an operator, here is one of.
3. Pick the values. Analytics offers the ones it finds in your data; this report keeps active.

The operators come in four families:

A query filter cannot see anything you calculate later. For that, see filter steps.

The join path and the generated query

Watch this from 6:53

Under the columns you set the sort order, see the join path, and cap the row limit. Run the query and you get the focus areas, each with its positions counted.

Two things are worth opening on any chart, including one you inherit:

• Generated query shows the SQL visual mode wrote. It is the quickest way to check exactly what a chart does.
• Join path shows how the tables were connected. Nobody told Analytics how Focus and Talent connect: visual mode found the route on its own, from focus area, through the Talent position mapping table, to position.

Visual mode writes both joins for you; you only pick columns. When there is more than one way to connect two tables, the join path is where you pick the right one.

Every chart is a pipeline

Watch this from 7:55

Everything below the query is a Visual SQL step. A chart runs in this order:

1. The queries run first, against the Data Lake.
2. Each step runs in order, on the results.
3. The last result becomes the chart.

Each step appears in the pipeline on the left, so a chart is a recipe you can edit or remove one step at a time. Change one step and everything after it updates. This lesson's finished pipeline is: query one, a copy of query one, join, formula column, apply formula, filter, sort rows, then the chart.

Joining two queries

Watch this from 8:27

Total positions is only half the story; the report also needs the open ones. To add them:

1. Choose Add query. Start fresh, or copy a query you already have.
2. Copy query one, and add one filter: position status is unfilled.
3. Visual SQL adds a join step to merge the two results. Columns from the second query get :1 on the end of their names, so you can tell them apart.
4. Open the join step. It matches on the first column, here focus area name, and the join type decides which rows survive.

With query one returning A, B and C, and query two returning A, B and D:

This report uses left: the first query leads, so every focus area stays on the list, even one with no open roles at all.

Formulas, three ways

Watch this from 10:14

Visual SQL calculates in three ways:

The open role rate takes two of them:

1. A guided column ratio: open positions, the column ending :1, as the numerator, and total positions as the denominator.
2. Apply formula on the new column, to round it to three decimal places.

The guided formulas fall into four families:

Watch out. The across-rows formulas depend on row order. Sort the rows before you add one.

The same hover menu hides a column or sorts it, and clicking a header renames it, so a leader can read the chart without a legend. Hidden columns still feed your formulas; they only stay out of the chart.

Filter steps

Watch this from 12:10

A filter step runs on the results, after the counts and the formulas. This report uses one to keep only the focus areas whose health is at risk, then a sort rows step to put the highest open role rate first.

In the demo data the answer is plain: Quality Management is at risk, with twelve of its thirty-four positions still open, and four at-risk focus areas have more than a quarter of their seats open. That is the first conversation in the next portfolio review.

Picking the chart

Watch this from 13:17

Analytics has more than a dozen chart types. The right one depends on your question and on the shape of your result table. For most charts, the first column is the label or the x-axis, and the columns after it are the numbers. A grayed-out type does not fit your data yet: pick it anyway and Analytics shows the format it needs, or choose Auto and let it pick.

Watch out. Bar charts do not sort dates or fill in missing ones. Add a sort rows or zero fill step first.

This report compares categories, so it is a bar chart. To finish it:

1. Hide the helper columns. Hidden columns still feed the formulas.
2. Switch from Auto to a bar chart: open role rate for each at-risk focus area.
3. Give it a title and save it to the dashboard.

Every step is saved with the chart. When Talent picks up a new hire, the numbers follow on the next data refresh without anyone rebuilding the report.

Same steps, other questions

Watch this from 15:43

The same handful of steps answers the questions that usually come next:

And because every app shares the Data Lake, the same editor works across the rest of your stack: request volume by team from Jira Service Management, pages created by space in Confluence, or goals beside the projects behind them.