Exercise - Combine table results by using the lookup operator

Completed

In the preceding exercise, you used the join operator to find the total sales per product. In this exercise, you use the lookup operator to find the total sales per country/region.

Use the lookup operator

Your sales team wants to know the company's total sales per country/region. You could use a join operator to get the customer and product information. However, this kind of query performs best when you use the lookup operator to get this information.

Recall that the lookup operator enriches a fact table with data from a dimension table. It's a bit like reading a book (fact table) and looking up unknown words in a dictionary (dimension table).

For this query, you start with the SalesFact table and use the lookup operator to get Customer data and add it to the resultant table.

In the following procedure, you build the query in stages to give yourself a better understanding of the result of using the lookup operator.

1. Run the following query to get 10 matching arbitrary rows from the SalesFact table and the Customers table.

   Run the query

   SalesFact
   | lookup Customers on CustomerKey
   | take 10
   

   Take a look at the resulting list. Notice that the resulting table contains columns from the SalesFact table followed by matching columns from the Customers table.

2. Run the following query to get the total sales per country/region.

   Run the query

   SalesFact
   | lookup Customers on CustomerKey
   | summarize TotalSales = count() by RegionCountryName
   | order by TotalSales desc
   

   Your results should look like those in the following image:

   

3. Take a look at the resulting list. Notice that the top sales are in the United States. Try modifying the query to show the total sales in the United States by state.

In the preceding unit, you used a rightouter join to get the total sales by product category. That query took 0.834 seconds to run. Now, write a query to get the same result by using the lookup operator and then compare the execution time.

1. Run the following query.

   Run the query

   SalesFact
   | lookup Products on ProductKey
   | summarize TotalSales = count() by ProductCategoryName
   | order by TotalSales desc
   

   Your results should look like those in the following image:

   

   Notice that you get the same results but the execution time is 0.398 seconds. The faster execution time is because the lookup operator is optimized for this type of query.