Using SQL Report Builder

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.

Analyze Your Data

SQL Report Builder

SQL Report Builder gives you the power to directly query your Data Warehouse, view the results, and quickly transform them into a report. Using SQL also allows you to use SQL functions that are not available in the Visual or Cohort Report Builders, thus giving you greater control over your data.

Calculated columns created using SQL are not dependent on update cycles, meaning you can iterate on them as you please and immediately see results.

NOTE
This only applies to the structure of the column, not the freshness of the data. Fresh data is still dependent on successfully completed update cycles.
This is perfect for…
This is not so great for…
Intermediate/advanced analysts
Beginners - you need to know SQL.
The SQL savvy
Simple analyses - writing a query can be more work than simply using the Visual Report Builder.
Building one-time-use calculated columns
Sharing with others - consider your audience: do they understand SQL? If not, they may be confused by how the report is built.
Data with one-to-many relationships
Testing a new column or analysis

Database vs SQL Editor Results

Most the time, differences in results can be attributed to update cycles. If Commerce Intelligence is in the process of replicating data from your database to your Data Warehouse, you might see different results even when using the same query.

Connection issues can also result in discrepancies. Navigate to the Connections page by clicking Manage Data > Connections to check it out - is there an error for the database integration in question? If so, you may need to reauthenticate the integration to get things running again.

If all your integrations are connected successfully and you are not in the middle of an update cycle, something else may be amiss.

Does deleting a SQL report also delete the underlying columns from my Data Warehouse?

No, you do not lose any columns from your Data Warehouse, regardless of how you built them.

Columns created using the Data Warehouse Manager are not affected if you delete a report or query that uses them.

Columns created using the SQL Report Builder are not saved to your Data Warehouse.

Report Builder versus SQL Report Builder

The SQL Report Builder gives you more flexibility when creating and structuring your charts - you can, for example, select what values should show on the X and Y axes. For more information on creating charts in the SQL Report Builder, check out the Creating visualizations from SQL queries tutorial.

Cohort Report Builder

Unlike the Visual Report Builder, the Cohort Report Builder is meant for a single purpose - analyzing and identifying behavioral trends of similar user groups over time. Using the Cohort Report Builder does not require any SQL savvy, so you can dive right in without hesitation if you are just starting out.

This is perfect for…
This is not so great for…
Intermediate/advanced analysts
Beginners - you need practice-defining cohorts.
Identifying behavioral trends over time
Qualitative analysis - it can be done, but requires Adobe assistance.

Rebuilding Queries after the Update Cycle

You do not have to rebuild your queries. Reports created using the SQL Report Builder are saved like those created in the traditional Report Builder. The update process for SQL charts is the same - after your data is updated, the values in your charts will be recalculated and redisplayed.

NOTE
When deleting a SQL report/query, it does not delete the underlying columns from your Data Warehouse. You do not lose any columns, regardless of how you built them.
  • Columns created using the Data Warehouse Manager are not affected if you delete a report or query that uses them.

  • Columns created using the SQL Report Builder are not saved to your Data Warehouse.

Wrapping up

If you want to try something a bit more challenging, why not try writing a query that is optimized for visualization? Check out the Creating visualizations from SQL queries tutorial to get started.

e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc