Using the TableauDatasourceGenerator class of the tableau_tools package

As part of the tableau_tools release, I’ve implemented a class called TableauDatasourceGenerator which allows for the easy programmatic generation of Tableau Datasources, both TDS and TDSX. If you’ve read Programmatically Creating Datasources but really just want to start doing it, this class implements all of the findings in that post so far. There is a datasource_generation_example.py included in the /examples directory of the tableau_tools package that goes over most of these use cases was well. Note: You need to have the Tableau SDK installed to generate datasources programmatically, owing to how extracts work.

The TableauDatasourceGenerator class allows for the programmatic creation of a TDS file or a TDSX file based on the programmatically generated TDS file.

Getting Started

With the new tableau_tools package, you can just do the following two imports to start your script:

<pre>from tableau_tools import *
from tableau_tools.tableau_documents import *</pre>

The main package is tableau_datasource_generator, which mostly includes the following
TableauDatasourceGenerator(ds_type, ds_name, server, dbname, logger_obj, authentication=u’username-password’, initial_sql=None)

You start by adding the first table, which is the equivalent of the FROM clause in a SQL SELECT statement:

TableauDatasourceGenerator.add_first_table(db_table_name, table_alias)
TableauDatasourceGenerator.add_first_custom_sql(custom_sql, table_alias)

Then JOIN clauses can be defined to expand out the relations. You must define the ON clauses first, then pass the ON clauses as a list to the join_table method:

TableauDatasourceGenerator.define_join_on_clause(left_table_alias, left_field, operator, right_table_alias, right_field)
TableauDatasourceGenerator.join_table(join_type, db_table_name, table_alias, join_on_clauses, custom_sql=None)

Finally you can save the file.
TableauDatasourceGenerator.save_file(filename_no_extension, save_to_directory)

Example:

ds = TableauDatasourceGenerator(u'postgres', u'My DS', u'localhost', u'demo', logger)
ds.add_first_table(u'agency_sales', u'Super Store')
join_on = ds2.define_join_on_clause(u'Super Store', u'region', u'=', u'Entitled People', u'region')
ds.join_table(u'Inner', u'superstore_entitlements', u'Entitled People', [join_on, ])

Adding Data Source Filters

You can add Data Source Filters to your TDS file programmatically as well.

TableauDatasourceGenerator.add_dimension_datasource_filter(column_name, values, include_or_exclude=u’include’, custom_value_list=False)

TableauDatasourceGenerator.add_continuous_datasource_filter(column_name, min_value=None, max_value=None, date=False)

TableauDatasourceGenerator.add_relative_date_datasource_filter(column_name, period_type, number_of_periods=None, previous_next_current=u’previous’, to_date=False)

There is an equivalent method for each of these for adding filters to extracts

Defining calculations

TableauDatasourceGenerator.add_calculation(calculation, calculation_name, dimension_or_measure, discrete_or_continuous, datatype)

This method returns the internally defined name for the calculation, which is necessary if you want to define a Data Source filter against it. This is particularly useful for creating Row Level Security calculations programmatically. The following is an example:

# Add a calculation (this one does row level security
calc_id = ds3.add_calculation(u'IIF([salesperson_user_id]=USERNAME(),1,0) ', u'Row Level Security',
u'dimension', u'discrete', u'integer')
# Create a data source filter that references the calculation
ds3.add_dimension_datasource_filter(calc_id, [1, ], custom_value_list=True)

Making the Data Source an Extract

Any data source can be turned into a TDSX with a defined extract using

TableauDatasourceGenerator.add_extract(tde_filename, incremental_refresh_field=None)

The TDE filename is just the name that will be used inside the TDSX file. This method actually calls out to the TDEFileGenerator method eventually, which uses the Tableau SDK to generate an extract with the minimum defined to work correctly. The extract neesd to be refreshed, either in Desktop or Server, to generate and have data.

Extracts can have filters set as well:

TableauDatasourceGenerator.add_dimension_extract_filter(column_name, values, include_or_exclude=u’include’, custom_value_list=False)

TableauDatasourceGenerator.add_continuous_extract_filter(column_name, min_value=None, max_value=None, date=False)

TableauDatasourceGenerator.add_relative_date_extract_filter(column_name, period_type, number_of_periods=None, previous_next_current=u’previous’, to_date=False)

Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s