Using SQL Report Builder

Last update: July 25, 2023

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User
NOTE
Requires Admin permissions to create and edit SQL charts. Standard users can rearrange these charts on dashboards, and Read-only users have the same experience they do with traditional charts. In addition, Read-only users do not have access to the text of the query.

See the training video to learn more.

SQL, or Structured Query Language, is a programming language used to communicate with databases. In Commerce Intelligence, SQL is used to query, or retrieve, data from your Data Warehouse. Look at the reports on your dashboard - behind the scenes, each one is powered by a SQL query.

You can use the SQL Report Builder to directly query your Data Warehouse, view the results, and transform them into a chart. You can start creating a report with the SQL Report Builder by clicking Report Builder > SQL Report Builder.

See the training video to learn more.

The SQL Report Builder allows you to directly query your Data Warehouse, view the results, and quickly transform them into a chart. The best part about using SQL to build reports is that you do not need to wait on update cycles to iterate on columns you create. If the results do not look right, you can quickly edit and rerun the query until things match your expectations.

This topic walks you through using the SQL Report Builder. After you know your way around, check out the SQL for visualizations tutorial or try optimizing some of the queries you have written.

Covered in this article:

SQL Report Builder Integrations

Google Analytics is the only integration unavailable for use with the SQL Report Builder. This functionality is in development.

To get started creating a SQL report, click Report Builder or Add Report at the top of any dashboard. In the Report Picker screen, click SQL Report Builder to open the SQL editor.

Get Started

To edit a report, click the gear ( ) icon in the top-right corner of a SQL-based chart and click Edit.

Writing a query

NOTE
SQL Report Builder queries are case-sensitive. Make sure you are using the correct case when writing queries or you could wind up with unexpected results or errors.

Following the guidelines for query optimization, write a query in the SQL editor.

IMPORTANT
Metrics in SQL reports - When you insert a metric into a SQL report, the current definition of the metric is used.

If the metric is updated in the future, the SQL report does not reflect the changes. You must manually edit the report to have the changes take effect.

Using the buttons at the top of the sidebar, you can toggle between lists of tables and metrics available for use in the SQL Report Builder. If you do not see what you are looking for in the list, try searching for it using the search bar at the top of the sidebar.

You can also use the sidebar in the SQL editor to insert metrics, tables, and columns directly into your queries by hovering over them and clicking Insert:

Inserting a table into the SQL editor.

NOTE
Any SELECT function, or any function that does not mutate data, that is supported by PostgreSQL is supported in the SQL Report Builder. This includes, but is not limited to, AVG, COUNT, COUNT DISTINCT, MIN/MAX, and SUM.

Also, any JOIN type is supported, but Adobe recommends only using INNER JOIN as it is the least expensive of the JOIN types.

Running the query and viewing results

When you are done writing your query, click Run Query. The results display in a table below the SQL editor:

Running the query and viewing results.

If something looks amiss in the results, you can edit the query and rerun it until you are satisfied.

You might sometimes see messages below the editor with EXPLAIN in them. If you see one of these, that means that your query has not run and needs a bit of fine-tuning.

After you are done editing your query, you can move onto either creating a visualization or saving your work to a dashboard.

Creating a visualization

To create a visualization with your query results, click the Chart tab in the Results pane. In this tab, you select:

  • The Series, or the column you want to measure, such as Items sold.
  • The Category, or the column you want to use to segment your data, such as acquisition source.
  • The Labels, or X-axis values.

Here is a quick look at what the visualization process looks like:

For a detailed walk-through of how to create a visualization, refer to the Creating visualizations from SQL queries tutorial.

Saving the report

Before you can save your work, you must give the report a name. Remember to follow the best practice guidelines for naming and choose something that clearly conveys what the report is!

Click Save at the upper-right corner of the SQL editor and select the report Type (Chart or Table). To wrap things up, select the dashboard to save the report to and click Save to Dashboard.