Accessing Tableau Repository

In this post I will explain how to connect to the Tableau repository using Tableau Desktop and SQL Developer. Both which are handy for querying the repository data.

  1. Enable access to PostgreSQL repository DB– set a password for the username ‘tableau’:
    C:\Program Files\Tableau\Tableau Server\8.1\bin>tabadmin dbpass <password>
  2. Create a firewall rule to allow remote access of PostgreSQL:
    1. Windows Firewall > Advanced Settings
    2. Create New Rule
      1
    3. Configure a Port specific rule2
    4. Create a rule for TCP – 8060 (the default port used by Tableau for PostgreSQL)
      3
    5. Keep on default
      4
    6. Keep on default (allow access from all networks) or modify per your requirements
      5
    7. Give the rule a name and description
      6
  3. Create a Tableau report
    1. Open Tableau Desktop
    2. Connect to Data
      7
    3. Select PostgreSQL
      8
    4. Enter the connection details
      9
    5. Example of a Custom SQL query I created to retrieve audit data:
      select
       count(*),
       ET.name as event_name,
       U.name as user_name,
       W.name as workbook_name,
       VW.name as view_name,
       VW.repository_url
      from
       historical_events E
       left join historical_event_types ET
        on (E.historical_event_type_id = ET.type_id)
       left join hist_users U
        on (E.hist_actor_user_id = U.id)
       left join hist_workbooks W
        on (E.hist_workbook_id = W.id)
       left join hist_views VW
        on (E.hist_view_id = VW.id)
      where
        created_at >= '2015-12-01 00:00:00.000' and
        VW.name is not null
       group by
        ET.name,
        U.name,
        W.name,
        VW.name,
        VW.repository_url
    6. Example of pie chart showing user usage
      10
  4. Connect using SQL Developer
    1. Download JDBC Postgresql driver for SQL Developer:
      http://jdbc.postgresql.org/download.html#current
    2. Open recent version of SQL Developer (example uses version 4.0.3.16)
    3. Setup the JDBC driver in SQL Developer
      1. 11
      2. 12
      3. 13
      4. 14
    4. Create new connection
      1. Create a new connection
        16
        Note that by default SQL Developer is attempting to connect to a database name which is the same as the username (database: ‘tableau’) without an option to choose a database.
        The next steps illustrate a workaround to fix this issue
      2. Save the connection
        17
      3. Export the Connection:
        Connections > Export Connections
        18
      4. Select Connection
        19
      5. Save XML file
        20
      6. Open XML file in notepad and point the connection to the database: ‘workgroup’
        1. Modify the row:
          <Contents>jdbc:postgresql://<server name>:8060/</Contents>
        2. Add an indication of ‘workgroup’ database
          <Contents>jdbc:postgresql://<server name>:8060/workgroup</Contents>
        3. Save
      7. Import XML file
        21
      8. Open connection details
        22
        Note that now the connection is showing ‘workgroup’ as the chosen database.
        You can now add your password and connect to the Tableau Repository using SQL Developer.

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 )

Connecting to %s