ÃÛ¶¹ÊÓƵ

Using SQL Report Builder

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 writing

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

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:

Also, any JOIN type is supported, but ÃÛ¶¹ÊÓƵ recommends only using INNER JOIN as it is the least expensive of the JOIN types.

Running the query and viewing results runquery

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

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 createviz

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

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 save

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 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.

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 cohortrb

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.

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.

Wrapping up wrapup

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.