How to use R, a Tableau connection to R and Calculated Fields to store and load the underlying data of a defined Tableau View in a Database or Text File (part 2 of 2)
Michael is the Managing Partner at Business Information Arts, Inc, Tableau Partner, Tableau Certified Consultant, Tableau Instructor and leader of the Toronto Tableau User Group. Last, but not least, Michael is the only person I know who is using Tableau Software since version 1.0.
NOTE: You may need to zoom into some of the screen shots below for legibility reasons. Also, the text below contains references to the "Level of Detail Shelf". This is synonymous with the Detail Pill in the Tableau Marks Card within the Tableau 9.x and 10.x user interface. As mentioned in the introduction to part one of this post, including a '$' character as part of an RODBC object name (for example crs$odbc, crs$odbc.tables, crs$dataset) could cause an error on your system, and the affected calculation will fail. If this happens, replace instances of $ within an RODBC object name with another character within the affected calculation, such as an underscore - i.e. _.
Supporting files and a README file are available for download at the end of this part of the post. Please look at the README file first.
The author would like to thank Leonid Koyfman for his suggestions.
The first part of this post walked you through the process of
- downloading and installing R and RStudio
- installing the required R packages,
- setting up the ODBC database connection
- writing the Calculated Fields in Tableau to read the underlying data of a Tableau view from a database
In the first part, we have only run SELECT queries, but we could also run INSERT, DELETE, UPDATE or INSERT INTO (Append data) queries – all we need to be is pass a valid SQL statement to a Calculated Field that establishes an ODBC connection to the Database and run the SQL statement.
Today’s second part of the post will
- show how to insert data of a Tableau view into a database
- demonstrate the necessity of aligning the orientation of the view and the Table Calculation
- show how to perform data definition queries
- provide the general caveats of the technique
- demonstrate an analysis technique that leverages multiple exports of a Tableau View based on the Superstore Dataset to SQL Server
- give some insights on performance and upper limits
- provide some useful links regarding the integration of Tableau and R