Magento Business Intelligence Help Center

Auditing Metrics Using the SQL Report Builder

BROUGHT TO YOU BY:

Avatar

The SQL Report Builder is primarily used to build new reports and iterate on analyses, but it can also be used to effectively audit data and metrics. This article explains how to audit data and metrics using the SQL Report Builder so that you can compare the results with the data from your local database.

Querying a metric

To get started, open the SQL Report Builder by navigating to Report Builder > SQL Report Builder > Create Report. You can use the sidebar in the SQL editor to insert a metric directly into your query by hovering over the metric and clicking Insert. This will add the query definition of that metric to the editor. The definition will include the following components:

  • The metric operation being performed, indicated by SUM() in the example below.
  • The table on which the metric is built, indicated by the FROM clause.
  • Any filters (and filter sets) that have been added to the metric, indicated by the WHERE clause in the example below.
  • The component of the timestamp (year, month) on which the data is to be ordered, indicated by the ORDER BY clause in the example below.

If you’d like to have a clearer view of the query, you can reformat how it's displayed in the query field. Once you’re ready, run the query by selecting Run Query. The results will populate as a table in the report panel below the query.

 
Click for a closer look!

Restricting the query

If you’re trying to pinpoint a specific discrepancy or set of data, you should restrict the query to a specific sample to check against your local database. You can do this by editing the query to match your desired restrictions. In the example below, we’re restricting the query to only include revenue from January 1, 2013 or later. Once you update the query, select Run Query again to update the results.

 
Click for a closer look!

Saving and exporting

Once the report meets your needs, save it to a dashboard by giving the report a distinct name, clicking Save, and selecting the type of report you’d like to save and the dashboard to save it to. When auditing metrics, we recommend saving the report as a Table and saving it to a test dashboard.

Once the report is saved, navigate to that dashboard by selecting Go to Dashboard. From there, you can export the data by finding the report and selecting the Options gear > Full CSV Export or Full Excel Export.

 
Click for a closer look!

Custom queries

You can also write custom queries and export the results to compare against your local database. Following the guidelines for query optimization, write a query in the SQL editor. You can use the buttons at the top of the sidebar to toggle between lists of tables and metrics available for use in the SQL Report Builder and add them to your query. Once your custom query fits your needs, you can save the report and export that data from the dashboard.

Still Stumped?

If you find a discrepancy after auditing your data, take a look at our Contacting Support: Data Discrepancies article for more information on what to do next.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk