1010data Tableau Setup Guide

1010data Tableau Setup Guide Connect to 1010data from Tableau 20 Connect to 1010data from Tableau Once you have configured Tableau and the 1010data ODBC driver, you can connect to 1010data from Tableau and start analyzing data and creating visualizations. To connect to 1010data from Tableau: 1. Open Tableau. Note: The instructions and screen shots in this guide correspond to Tableau Desktop 9.0 on Windows. Earlier versions of Tableau Desktop may have a different appearance and workflow. Although there are versions of Tableau Desktop for Mac, they cannot connect directly to 1010data via ODBC. Tableau displays the start page. 2. In the Connect pane, under To a server, click Other Databases (ODBC).

1010data Tableau Setup Guide Connect to 1010data from Tableau 21 If Other Databases (ODBC) does not appear in the To a server list, click More Servers and select Other Databases (ODBC) from the full list.

1010data Tableau Setup Guide Connect to 1010data from Tableau 22 The Server Connection dialog appears.

1010data Tableau Setup Guide Connect to 1010data from Tableau 23 3. Under Connect Using, click the DSN drop-down arrow and select the 1010data ODBC driver you want to use.

1010data Tableau Setup Guide Connect to 1010data from Tableau 24 Note: This example uses the data source that was created in Configure the 1010data ODBC driver on page 10 (i.e., TableauOn1010data). 4. Click Connect. A Processing Request dialog appears while Tableau connects to the selected data driver and database. When the processing is complete, the fields under Connection Attributes are populated with the information from the selected data source.

1010data Tableau Setup Guide Connect to 1010data from Tableau 25 5. Click OK. The Tableau Data Source page appears.

1010data Tableau Setup Guide Connect to 1010data from Tableau 26 Once you are connected to 1010data from Tableau, you can begin to specify 1010data queries and create visualizations from the results of those queries. See Using Tableau: An Example on page 27 for a stepby-step tutorial that uses some of 1010data's publicly available data sets so that you can follow along and get started using Tableau with 1010data.

1010data Tableau Setup Guide Using Tableau: An Example 27 Using Tableau: An Example Tableau is a powerful software tool, and it is beyond the scope of this guide to go into all the details and nuances of it. However, there are some basic techniques when working with Tableau in conjunction with 1010data that will make getting started and seeing results that much easier. The following sections cover: An example of a 1010data query that will be used to create visualizations in Tableau How to specify a 1010data query in Tableau How to create a basic visualization of the results from your 1010data query How to parameterize a variable in your 1010data query so that you can easily change which measurements are displayed in the visualization 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: Sets the base table to the Sales Detail table

1010data Tableau Setup Guide Using Tableau: An Example 28 Links in the Customer Master table and uses the customer column in both tables as the linking column Links in the Store Master table and uses the store column in both tables as the linking column Selects rows in the resultant table that have "East" in the division description Performs a tabulation, grouping by the state and primary segment columns and sorting both columns in ascending order. The tabulation calculates the following metrics: Sum of extended sales Sum of quantity/weight Average extended sales, weighted by quantity/weight 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 :

1010data Tableau Setup Guide Using Tableau: An Example 29 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. 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 on page 20 for details.

1010data Tableau Setup Guide Using Tableau: An Example 30 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.

1010data Tableau Setup Guide Using Tableau: An Example 31 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: }

1010data Tableau Setup Guide Using Tableau: An Example 32 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.

1010data Tableau Setup Guide Using Tableau: An Example 33 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.

1010data Tableau Setup Guide Using Tableau: An Example 34 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 on page 34 for an example of creating a color-coded map that shows the total sales for each state in the specified division. Create a basic visualization in Tableau Tableau makes creating compelling visualizations easy. Creating a basic map, for instance, is as simple as dragging and dropping objects within the Tableau workspace. In this example, you will create a colorcoded map that shows the total sales for each state in the specified division. To create a basic visualization in Tableau: 1. Specify your 1010data query in Tableau. Note: This task uses the 1010data query example provided in this setup guide. If you haven't done so already, follow the steps in Specify a 1010data query in Tableau on page 29 to get started. After you've specified your 1010data query in Tableau, the current worksheet is displayed in the Tableau workspace:

1010data Tableau Setup Guide Using Tableau: An Example 35 The Data tab on the left (outlined in green) contains the Dimensions and Measures areas. In general, the items in the Dimensions area are the data columns being aggregated, and the items in the Measures area are the totals of those aggregations. The area on the right (outlined in red) contains cards and shelves where you can create and control Tableau visualizations. 2. Drag the state dimension from the Dimensions section to the shelf labeled Drop field here (lower-right quadrant).

1010data Tableau Setup Guide Using Tableau: An Example 36 Tableau creates a map based on the data in the state dimension. Although the map indicates all the states where sales occurred, there is no sense of magnitude. To add a splash of color as well as display sales information, you can apply one of the items listed under Measures.

1010data Tableau Setup Guide Using Tableau: An Example 37 3. Drag sumofextendedsales from the Measures area to the Color button on the Marks shelf. Each state in the map is filled with a color from a gradient that gives a visual indication of the total sales in that state.

1010data Tableau Setup Guide Using Tableau: An Example 38 As you can see, it's very simple to create a basic visualization in Tableau. However, with just a little more effort, you can add even more flexibility to your visualizations. For instance, in the 1010data query example, you can parameterize the variable for the division selection, which will allow you to dynamically change the results in the map without creating a new query or worksheet for each division. See Parameterize a variable in Tableau on page 38 for instructions on how to do just that. Parameterize a variable in Tableau Parameterizing variables in your query give your visualizations more flexibility by allowing you to make dynamic selections that are reflected immediately in the visualization. Though there are several ways to do this, the method presented in this topic is one technique that works well. The following example assumes that you have specified the 1010data query example in Tableau (see Specify a 1010data query in Tableau on page 29) and have created the color-coded map (see Create a basic visualization in Tableau on page 34). Although this example modifies a previously saved query to parameterize variables within it, the parameterizations could have been done when you originally specified the 1010data query in Tableau. In this example, you will create a parameter for the division specified in the 1010data query so that you can change the division from within the Tableau workspace without the need to create a separate worksheet or write a separate query for each division. To parameterize a variable in Tableau: 1. From the Tableau workspace, click Data New Data Source. The Connect pane is displayed.

1010data Tableau Setup Guide Using Tableau: An Example 39 2. Follow steps 2 on page 20 through 5 on page 25 in Connect to 1010data from Tableau on page 20. The Tableau Data Source page appears.

1010data Tableau Setup Guide Using Tableau: An Example 40 3. From the Tableau Data Source page, double-click New Custom SQL. The Edit Custom SQL dialog opens.

1010data Tableau Setup Guide Using Tableau: An Example 41 4. In the Edit Custom SQL dialog, enter the following Macro Language code. pub.demo.retail.cookbook.store:{ tabu label "Tabulation on Store Master" breaks "divisiondesc" tcol source "divisiondesc" fun "cnt" name "count" label "Count"/ /tabu } When this query is run, it will connect to the Store Master table and tabulate all the possible values in the column containing the division descriptions (divisiondesc). 5. 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.

1010data Tableau Setup Guide Using Tableau: An Example 42 6. Click the title of the data source and enter "Division Codes" as the new title. This will help you to easily identify the data source in the following steps.

1010data Tableau Setup Guide Using Tableau: An Example 43 7. Click the arrow to the left of the title and select Custom SQL Query (1010sql). The original data source is displayed.

1010data Tableau Setup Guide Using Tableau: An Example 44 8. Hover your mouse over Custom SQL Query and click the Edit icon ( The Edit Custom SQL dialog appears. ).

1010data Tableau Setup Guide Using Tableau: An Example 45 9. In order to parameterize the variable associated with the division description, highlight the value of the variable in the 1010data query. sel value "(divisiondesc 'East')"/ 10.Click Insert Parameter Create a new parameter

1010data Tableau Setup Guide Using Tableau: An Example 46 The Create Parameter dialog appears. 11.Modify the values in the Create Parameter dialog. a) In the Name field, enter "Division". b) Select String from the Data Type drop-down list. c) Click the List radio button. The List of values section appears.

1010data Tableau Setup Guide Using Tableau: An Example 47 d) Click

To configure the 1010data ODBC driver to work with Tableau: 1. Navigate to the folder containing the 1010data ODBC configuration programs. a) Click the Start button. b) Click All Programs. c) Click 1010data. d) Click 1010odbc. 2. Open the configuration program corresponding to the versions of Tableau and Windows you are using.