Performing analyses without SQL

Follow

When working with your database, there’s a lot going on behind the scenes. Every time you select a metric, attribute, or work with a filter, it is translated as a SQL query to the database, and the results are returned back as a beautiful visualization.

This support tip will demonstrate how you can easily create the same analysis as you can with your SQL queries.

For example an online retail store sells phones, tables, and accessories throughout the world and would like to perform an analyses of the revenue from the United States stores. This example uses Amazon Redshift, but can communicate with any database in the same way.

In a SQL editor, the query to view the revenue from each state would resemble the image below.

Note: If you would like to replicate this query, you might need to download the relevant JDBS driver. For more information on the Redshift driver, see Amazon Redshift's article Configure a JDBC Connection .

This query does return the correct results, but it is difficult to see at a glance how the revenue from the different states compare. To view the comparison would require additional queries to see which product categories are driving revenue.

It is easy to explore and ask questions of your data in a visual way. The same analyses as above can be achieved in a total of six clicks in the Query Builder.

Note: Before building queries, you must connect to your data source. For information on establishing new connections, see Adding connections .
To analyze revenue by state
  1. Select the Revenue metric from Metrics.
  2. Add the State and Country attributes to Columns.
  3. Click on the Country attribute and select the value USA. This will filter results to the United States.

This query will resemble the image below.

You can easily compare states to evaluate where the highest revenue is located. For the example above, you can see that California is driving up revenue results.

If you wanted to dig deeper into this data, you can add the Category attribute to Rows to see which products are generating the most revenue in these locations. In one glance, you can see that the tablet sales create the most revenue across the USA.

In the SQL editor, you could achieve the same results by adding category to your select clause, but it could make things a little messy. If you wanted to analyze additional criteria, such as how the tablet sales grew in California during 2015, you would have to write a new query.

The new query would resemble the image below.

You can achieve the same results by using decompose and filtering your date range. For more information on decomposing your results, see Interacting with queries .

To view the growth of tablet sales in California during 2015

  1. In your query select the Tablets category from the Row Selector.
  2. Click on the data point for California, then select Decompose .
  3. In the Decompose menu, select the Year(Date) attribute and the Month(Date) attributes as the axis of analysis.
  4. Select On columns , and make sure Keep original element on axis is not checked.
  5. Click Ok .
  6. Click on the Year(Date) attribute, then select Edit date ranges (see Adding and editing dates ).
  7. From the Simple menu select Last year .
  8. Click Apply . Your results will now show the growth of tablet sales in 2015.

    From those results, you can see the months that achieved the highest revenue in 2015. If you wanted to analyze what products led to this rise in revenue in August, you would need to write another query.

    The SQL query would resemble the image below.

    You can achieve the same results by decomposing again during the month of August.

To view the products driving revenue in August

  1. Click the August data point, then select Decompose .
  2. Select the sub_category attribute as the axis of analysis.
  3. Select On columns and make sure Keep original elements on axis is unchecked.
  4. Click Ok .

    The results will resemble the image below.

    As you can see the sales of iPads in August did very well.

    If you were interested in understanding the trend of iPad sales and wanted to see the growth, it could be beneficial to apply a running total to the query. In SQL, you would need to write a new query resembling the image below.

    There is a great function built-in in the Result manipulation menu that lets you add running totals within a few clicks. By decomposing, then using a result path calculation, you can easily view the running total of iPad sales (see Creating result path calculations ).

To view the running total of iPad sales

  1. Click on the iPad data point, then select Decompose .
  2. Select Day(Date) as the axis of analysis.
  3. Select On columns and make sure Keep original elements on axis is unchecked.
  4. Click Ok .
  5. In the Result manipulation menu, select Result path calculation .
  6. Select Running total as the pattern, On columns as the path, and SUM as the Aggregation.

  7. Click Apply .

    Your results will resemble the image below.

What's great about the pre-built result manipulation feature is that it takes only a few click to achieve the same result as SQL queries. The auto-chart mode intelligently visualizes the data for you in the most easy to view way.

If you're ever interested in the seeing queries that are sent to your database, you can look in Result manipulation > SQL options .

Note: This SQL options feature is only available for database connections.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk