Specify a 1010data query in Tableau

Once you have established a connection to your 1010data data source, you can specify a 1010data Macro Language query using a custom SQL query in Tableau.

You must be connected to your data source in Tableau before you can create a custom SQL query. See Connect to 1010data from Tableau for details.

1010data recommends that you use a custom SQL query to limit the amount of data used by Tableau to create visualizations. The custom SQL query should contain a 1010data Macro Language query that reduces the size of the table via a summarization such as a tabulation. This is particularly important when working with very large tables. This will ensure that your experience using Tableau will remain smooth and responsive.

To create a custom SQL query in Tableau:

  1. From the Tableau Data Source page, double-click New Custom SQL.

    The Edit Custom SQL dialog opens.

  2. In the Edit Custom SQL dialog, enter the Macro Language code for your 1010data query.

    This example uses the following 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>

  3. Wrap the 1010data query inside a SQL SELECT statement.

    In order for Tableau to process the 1010data Macro Language query, you must submit it as a SQL query with the form:

    SELECT * from [1010data_base_table]:{
         [1010data_query]
    }
    where:
    • [1010data_base_table] is the name of the base table for the query
    • [1010data_query] is the 1010data Macro Language code for the query

    For this example, insert the following line of code as the first line in the Edit Custom SQL dialog:

    SELECT * FROM pub.demo.retail.cookbook.salesdetail:{

    Add the following line of code as the last line in the Edit Custom SQL dialog:

    }

    This results in the following SQL query:

    SELECT * FROM pub.demo.retail.cookbook.salesdetail:{
    <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>
    }

    Note: Make sure the SQL wrapper does not contain any semicolons.
  4. Click OK.

    A Processing Request dialog appears while Tableau is loading the metadata.

    When the processing is complete, the Tableau Data Source page is updated to show the Custom_SQL_Query.

  5. Click Sheet 1.

    Tableau displays a warning dialog identifying limitations for the ODBC data source.

  6. Click OK.
    Tableau creates a data extract and displays the worksheet associated with the custom SQL query.

Now that you are connected to the 1010data ODBC data source using the custom SQL query that contains your Macro Language code, you can use Tableau to create visualizations. See Create a basic visualization in Tableau for an example of creating a color-coded map that shows the total sales for each state in the specified division.