1010data query example

The 1010data query used in the examples in this setup guide links a number of tables containing sample retail data, selects rows that have a specific division description, and performs a tabulation that calculates various metrics.

The following is the Macro Language code for the example 1010data query:

<note type="base">Applied to table: pub.demo.retail.cookbook.salesdetail</note>
<base table="pub.demo.retail.cookbook.salesdetail"/>
<link table2="pub.demo.retail.cookbook.customer" col="customer" 
      col2="customer" label="Customer"/>
<link table2="pub.demo.retail.cookbook.store" col="store" 
      col2="store" label="Store"/>
<sel value="(divisiondesc='East')"/>
<tabu label="Tabulation on Sales Detail by Customer" breaks="state,primary_segment">
  <break col="state" sort="up"/>
  <break col="primary_segment" sort="up"/>
  <tcol source="xsales" fun="sum" name="sumofextendedsales" 
        label="Sum of `Extended `Sales"/>
  <tcol source="qty" fun="sum" name="sumofqtybywgt" 
        label="Sum of `Qty/Wgt"/>
  <tcol source="xsales" fun="wavg" name="avgextendedsalesweighted" 
        label="Average`Extended Sales`Weighted by`Qty/Wgt"/>
</tabu>
This query operates on the following tables, which can be found in the Published Data > Demo > Retail > CookBook folder on 1010data:
Sales Detail (pub.demo.retail.cookbook.salesdetail)
A sample table that contains a record of every item purchased at every location of a store or chain of stores. This is a very common type of table utilized by retailers and typically contains detailed information about every transaction (e.g., transaction ID, SKU, sales price, cost, quantity).
Customer Master (pub.demo.retail.cookbook.customer)
A sample table that contains detailed customer information (e.g., name, email, date of birth)
Store Master (pub.demo.retail.cookbook.store)
A sample table that contains detailed store information (e.g., city, state, division)
The query performs the following actions:

The 1010data query used in the examples in this setup guide is fairly simple and straightforward, however your own queries can be as complex as the analysis demands. In addition, you can use 1010data blocks and libraries to allow for parameterized variables and code reusability. Furthermore, you can control access to your query by granting access to certain users or groups via explicit permissioning, which will be respected when run in Tableau.

The sample Sales Detail table contains over four million rows of data. (It is not uncommon for real-world tables of this type to contain billions of rows.) The tabulation in the 1010data query reduces this table to 25 rows. Below, you can see the results of running this query in 1010data's Trillion-Row Spreadsheet®:

For every combination of state and primary segment in the East division, the tabulation calculates the sum of extended sales, the sum of quantity/weight, and the average extended sales weighted by quantity/weight.

Tableau makes it extremely easy to visualize these results. For example, in just a few simple steps, you can create a basic visualization that displays a color-coded map showing the extended sales for each state. In addition, Tableau provides a way to parameterize variables in your query so that you can change the division you are interested in and see the results immediately reflected in the map.