Using Tableau server 10, I'm evaluating how we can achieve multi-tenancy based on our database structure. We've logically partitioned our clients data into separate Oracle schemas(for ex: schema1, schema2 etc) on the same database. The table structure across multiple db schemas will be identical.
- I've developed a worksheet using a datasource1(using schema1 username/pwd) and by using tables drag and drop mechanism. The worksheet works fine according to the data in datasource1.
- In the same workbook, I've created another datasource2(using schema2 username/pwd) using Desktop's duplicate data source feature from datasource1, and edited the data connection on datasource2 to use schema2 username/pwd.
- In the worksheet, using Desktop's replace data source feature, I've updated the worksheet to use datasource2. Now when I refresh the worksheet, it still pulls the data from datasource1 instead of datasource2.
- When I checked the SQL that it is running, the table names are prefixed/hardcoded with schema1 so the data is always retrieved from it no matter what the underlying datasource is.
- I've tried with using custom SQL in the datasource without prefixing db schema names to the tables and it works fine when used with multiple datasources that uses their own db schemas. When the SQL is generated by Tableau using table drag and drop approach, Tableau is hardcoding schema name prefix to the table names and it wouldn't work for multi-tenancy when a worksheet is published to multiple tableau sites, with each site using its own datasource.
What am I doing wrong?
How can we re-use a worksheet/dashboard that is developed using tables drag and drop mechanism(instead of custom SQL), and which is deployed across multiple tableau sites to support multi-tenancy? How can we achieve multi-tenancy for dashboards that are deployed to multiple tableau sites, with each site using their own datasource(db schema)?
Yes I can reproduce the issue. I'm using Desktop 9.3.6(9300.16.0811.1521) 64-bit for MAC. I've opened a support case #02366779. Thanks!
As shown below, the <datasource> elements in the .twb workbook file contains the same schema name prefixed to the tables, although the 2 datasources use different Oracle schema usernames. The datasource2 is cloned from datasource1 using Desktop's "Duplicate data source" feature instead of re-creating datasource2 from scratch.
<datasource caption='SCHEMA1' inline='true' name='SCHEMA1' version='9.3'>
<connection authentication='RDBMS' class='oracle' one-time-sql='' port='1521' schema='SCHEMA1' server='XYZ' server-oauth='' service='ABC' username='SCHEMA1' workgroup-auth-mode='prompt'>
<relation join='inner' type='join'>
<clause type='join'>
<expression op='='>
<expression op='[EMPLOYEE_HIST].[EMPID]' />
<expression op='[EMPLOYEE].[EMPID]' />
</expression>
</clause>
<relation name='EMPLOYEE_HIST' table='[SCHEMA1].[EMPLOYEE_HIST]' type='table' />
<relation name='EMPLOYEE' table='[SCHEMA1].[EMPLOYEE]' type='table' />
</relation>
....
....
</connection>
</datasource>
<datasource caption='SCHEMA2' inline='true' name='SCHEMA2' version='9.3'>
<connection authentication='RDBMS' class='oracle' one-time-sql='' port='1521' schema='SCHEMA2' server='XYZ' server-oauth='' service='ABC' username='SCHEMA2' workgroup-auth-mode='prompt'>
<relation join='inner' type='join'>
<clause type='join'>
<expression op='='>
<expression op='[EMPLOYEE_HIST].[EMPID]' />
<expression op='[EMPLOYEE].[EMPID]' />
</expression>
</clause>
<relation name='EMPLOYEE_HIST' table='[SCHEMA1].[EMPLOYEE_HIST]' type='table' />
<relation name='EMPLOYEE' table='[SCHEMA1].[EMPLOYEE]' type='table' />
</relation>
....
....
</connection>
</datasource>
