5 Replies Latest reply on Sep 20, 2016 7:23 AM by ravi.gutta

    Tableau multi-tenant worksheet and datasources

    ravi.gutta

      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.

      1. 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.
      2. 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.
      3. 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.
      4. 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.
      5. 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)?

        • 1. Re: Tableau multi-tenant worksheet and datasources
          Glen Robinson

          Hi Ravi

          If on each site you create a published data source which connects to the Database.

          Name the published Data source on each site with the same name

          Build the workbook, using the published data source, and then when you publish the workbook to each site, they will all use the same Published Data source name, but be pointing to different schemas

          I think that this should work.

           

          All the best

          Glen

          • 2. Re: Tableau multi-tenant worksheet and datasources
            ravi.gutta

            Hi Glen,

             

            When you say that on each site create a published data source, should we create the datasource for each site with all the data table joins, custom calculations, datasource filters separately and manually (or) can we clone them from the original datasource?

            We've many number of clients and creating a datasource manually for each site would be tedious and error prone. If we clone/duplicate a datasource, then the underlying SQL from the table joins contains the schema prefix from the original datasource and wouldn't be multi-tenant even if the data connection in the new datasource points to a different schema.

             

            Why would Tableau hardcode the schema name prefix to the table names in the generated SQL(datasource created using drag and drop approach) even though it uses a datasource with the underlying data connection that points to a specific schema username?

             

            Thanks,

            Ravi

            • 3. Re: Tableau multi-tenant worksheet and datasources
              Glen Robinson

              Hi Ravi

              Im not sure why you are seeing the old schema being retain when you alter Schemas.

              Datasource files can be altered using the document API to point at the different schemas.

              GitHub - tableau/document-api-python

              These Data sources can then be published to the different Sites using TABCMD / REST API.

              This should limit the amount of manual steps.

              Once the Datasource has been published, the same dashboard can be published to each site to point at those data sources.

               

              All the best

              Glen

              • 4. Re: Tableau multi-tenant worksheet and datasources
                Russell Christopher

                Ravi -

                 

                If you can dependably reproduce what you're reporting in "step 3" (sounds like you can), please open a support case. This should not be happening.

                 

                What version/build of Desktop are you on, btw?

                • 5. Re: Tableau multi-tenant worksheet and datasources
                  ravi.gutta

                  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>