WIXLIB

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

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

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

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

1010data Tableau Setup Guide Connect to 1010data from Tableau 26Once you are connected to 1010data from Tableau, you can begin to specify 1010data queries and createvisualizations 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 andget started using Tableau with 1010data.

1010data Tableau Setup Guide Using Tableau: An Example 27Using Tableau: An ExampleTableau is a powerful software tool, and it is beyond the scope of this guide to go into all the details andnuances of it. However, there are some basic techniques when working with Tableau in conjunction with1010data 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 TableauHow to specify a 1010data query in TableauHow to create a basic visualization of the results from your 1010data queryHow to parameterize a variable in your 1010data query so that you can easily change whichmeasurements are displayed in the visualization1010data query exampleThe 1010data query used in the examples in this setup guide links a number of tables containing sampleretail data, selects rows that have a specific division description, and performs a tabulation that calculatesvarious 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 byCustomer" 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 chainof stores. This is a very common type of table utilized by retailers and typically contains detailedinformation 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 linkingcolumnLinks in the Store Master table and uses the store column in both tables as the linking columnSelects rows in the resultant table that have "East" in the division descriptionPerforms a tabulation, grouping by the state and primary segment columns and sorting bothcolumns in ascending order. The tabulation calculates the following metrics: Sum of extended salesSum of quantity/weightAverage extended sales, weighted by quantity/weightThe 1010data query used in the examples in this setup guide is fairly simple and straightforward, howeveryour own queries can be as complex as the analysis demands. In addition, you can use 1010data blocksand libraries to allow for parameterized variables and code reusability. Furthermore, you can controlaccess to your query by granting access to certain users or groups via explicit permissioning, which will berespected when run in Tableau.The sample Sales Detail table contains over four million rows of data. (It is not uncommon for real-worldtables of this type to contain billions of rows.) The tabulation in the 1010data query reduces this table to 25rows. Below, you can see the results of running this query in 1010data's Trillion-Row Spreadsheet :

1010data Tableau Setup Guide Using Tableau: An Example 29For every combination of state and primary segment in the East division, the tabulation calculates thesum 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, youcan create a basic visualization that displays a color-coded map showing the extended sales for eachstate. In addition, Tableau provides a way to parameterize variables in your query so that you can changethe division you are interested in and see the results immediately reflected in the map.Specify a 1010data query in TableauOnce you have established a connection to your 1010data data source, you can specify a 1010data MacroLanguage 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. SeeConnect to 1010data from Tableau on page 20 for details.

1010data Tableau Setup Guide Using Tableau: An Example 301010data recommends that you use a custom SQL query to limit the amount of data used by Tableauto create visualizations. The custom SQL query should contain a 1010data Macro Language query thatreduces the size of the table via a summarization such as a tabulation. This is particularly important whenworking with very large tables. This will ensure that your experience using Tableau will remain smooth andresponsive.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 31This 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 byCustomer" 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 SQLquery 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 queryFor 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 32This 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 byCustomer" 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 theCustom SQL Query.

1010data Tableau Setup Guide Using Tableau: An Example 335. 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 34Now that you are connected to the 1010data ODBC data source using the custom SQL query that containsyour Macro Language code, you can use Tableau to create visualizations. See Create a basic visualizationin Tableau on page 34 for an example of creating a color-coded map that shows the total sales for eachstate in the specified division.Create a basic visualization in TableauTableau makes creating compelling visualizations easy. Creating a basic map, for instance, is as simpleas 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 29to get started.After you've specified your 1010data query in Tableau, the current worksheet is displayed in theTableau workspace:

1010data Tableau Setup Guide Using Tableau: An Example 35The 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 theMeasures area are the totals of those aggregations. The area on the right (outlined in red) containscards 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-rightquadrant).

1010data Tableau Setup Guide Using Tableau: An Example 36Tableau 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. Toadd a splash of color as well as display sales information, you can apply one of the items listed underMeasures.

1010data Tableau Setup Guide Using Tableau: An Example 373. 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 salesin that state.

1010data Tableau Setup Guide Using Tableau: An Example 38As you can see, it's very simple to create a basic visualization in Tableau. However, with just a littlemore effort, you can add even more flexibility to your visualizations. For instance, in the 1010dataquery example, you can parameterize the variable for the division selection, which will allow you todynamically 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 TableauParameterizing variables in your query give your visualizations more flexibility by allowing you to makedynamic selections that are reflected immediately in the visualization. Though there are several ways to dothis, 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 (seeSpecify a 1010data query in Tableau on page 29) and have created the color-coded map (see Create abasic visualization in Tableau on page 34). Although this example modifies a previously saved query toparameterize variables within it, the parameterizations could have been done when you originally specifiedthe 1010data query in Tableau.In this example, you will create a parameter for the division specified in the 1010data query so that you canchange the division from within the Tableau workspace without the need to create a separate worksheet orwrite 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 392. 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 403. 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 414. 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 inthe 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 theCustom SQL Query.

1010data Tableau Setup Guide Using Tableau: An Example 426. 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 437. 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 448. 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 459. In order to parameterize the variable associated with the division description, highlight the value of thevariable in the 1010data query. sel value "(divisiondesc 'East')"/ 10.Click Insert Parameter Create a new parameter

1010data Tableau Setup Guide Using Tableau: An Example 46The 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 47d) Click Add from Field Division Codes divisiondesc.This populates the List of values with the values returned from the tabulation in the Division Codesdata source.

1010data Tableau Setup Guide Using Tableau: An Example 48e) Click OK in the Create Parameter dialog.The value of divisiondesc is replaced with the Parameters.Division variable.12.Click OK in the Edit Custom SQL dialog.13.From the Tableau workspace, click Sheet 1 to go back to the or

1010data Tableau Setup Guide Configure Tableau 4 Configure Tableau In order for Tableau to connect to 1010data's ODBC driver, you must first copy one of the Tableau datasource connection files provided by 1010data into your Tableau Repository. If you have not already done so, you must download and inst