4 Replies Latest reply on Jan 10, 2017 6:49 AM by sherry.shaik

    Datasource connection

    sherry.shaik

      Hello everyone,

      I am building a dashboard by extracting data from 2 different datasources(Hadoop and Oracle) using custom SQL in Tableau. I have written two queries, one for each datasource. Instead of two different queries can we make a single query that extracts data from two datasources with different server names?

      Thanks.

        • 1. Re: Datasource connection
          Bill Lyons

          If the connections were to the same type of data source, it might be possible depending on the data source. For example, with SQL Server, using linked servers, you can build cross-server queries. However, they are slow, and may not be possible with many data sources.

           

          But with completely different data source types, they would use different drivers - the underlying software that connects to the database. So, connecting directly to two different data source types in the same query would be impossible.

           

          However, that is what is so exciting about Tableau v10: you can build cross database joins! So, while you would still have separate connections with the custom SQL code, you can join them together in Tableau. There is much online information about this, but here is just a couple that you might find helpful to get you started:

           

          Tableau 10 | Tableau Software

          Integrate your data with cross-database joins in Tableau 10 | Tableau Software

           

          I hope that helps.

          • 2. Re: Datasource connection
            sherry.shaik

            What is the best way to join them in Tableau9.2?
            The actual issue is that it's automatically taking all the non-aggregated values as aggregated when you try to edit relationship between them.

            Let's say there are two different Databases

            Hadoop:(Primary DataSource)

            W,X,Y and Z which are dimensions.

            Oracle:(Secondary)

            A, B, C, D and E (All are dimensions)

            When Customizing relationship by saying E=Z

            then the dimensions in secondary datasource like A,B,C and D are automatically considered as aggregated fields which is creating havoc for my requirement.

            I need to create a Nested If calculation field which include both data sources and when tried to execute the calculation field it's showing error that aggregated and non aggregated fields cannot be used together.

            • 3. Re: Datasource connection
              Bill Lyons

              I think you have already answered your own question. In pre-version 10 Tableau, you cannot do true cross-database joins, only "blending." With blending, the secondary data source must be aggregated. When "aggregating" a dimension, Tableau uses the ATTR() function. For blend link value, the ATTR() function tests the MIN() value with the MAX() value; if they are the same, the value is returned; if they are different, an asterisk (*) is returned.

               

              Possible solutions include:

              • Reverse the blend - swap the primary and secondary data source. The most granular source should be the primary.
              • Upgrade your version of Tableau to v10+ so you can use a true join.
              • Do the join externally to Tableau, with a tool like Alteryx
              1 of 1 people found this helpful
              • 4. Re: Datasource connection
                sherry.shaik

                I'm aware of the first two solutions but the third one, i've to look into Alteryx.

                Thanks