8 Replies Latest reply on Aug 8, 2018 2:28 AM by mu zh

    How to build scorecard based on multiple data sources?

    mu zh

      Hi all, would like to consult how to build scorecard based on multiple data sources.

      For example, we have three metrics to monitor and each of them are calculated from three different data sources.

      I am able to calculated them seperately in different worksheet. Now wondering whether there is a way to put them together and show in one scorecard.

      Thanks in advance!

        • 1. Re: How to build scorecard based on multiple data sources?
          meenu choudhary

          Hi Mu,

           

          You can create a dashboard and drag the individual worksheet having KPI from different data sources on it.

          • 2. Re: How to build scorecard based on multiple data sources?
            mu zh

            Hi Meenu, thanks for the response! Then would that be three different tables in the dashboard? I am hoping to show them in one jointed table. Is that possible?

             

            Something like..

            Metrics Target Performance Datasource

            KPI1     10      9                    source1    

            KPI2     100     98                 source2

            • 3. Re: How to build scorecard based on multiple data sources?
              Monica Van Loon

              Have you read this

              Connecting to Multiple Data Sources Without Joining or Blending | Tableau Software

              It is pretty self explanatory.  Note, when you try to bring in fields from another data source on one worksheet it will tell you a relationship needs to be created.  But if you put all 3 sheets on a dashboard next to each other it will "appear" they are one source. Especially if you get rid of left and right padding under Layout and don't have separate scroll bars.  Use Fit Width and a dashboard title as opposed to a worksheet title.

              The below has data from College Smokers , Sample Superstore and Legislation (Bills) scroll bars will mess this up and it would look better if they were all the same number of rows.

               

              • 4. Re: How to build scorecard based on multiple data sources?
                mu zh

                Hi Monica, No, I didnt read that article before but I am doing the similar way by using different data source in each worksheet. What I did is to create one metric in one worksheet using single data source. Now my problem is how to bring them together in the final dashboard.

                As you listed above, puting them side by side is one way. Just that they may look like seperate tables. Wondering whehter can put them in one table as a traditional way.. Thanks again.

                • 5. Re: How to build scorecard based on multiple data sources?
                  Monica Van Loon

                  You can go to format and get rid of all column banding, lines , borders etc on each worksheet and do a top filter so they all have the same number of rows and it looks like one dashboard to me.  Otherwise maybe you could  create a sequential number in each data source and use that as a fake key just to join.

                  • 6. Re: How to build scorecard based on multiple data sources?
                    mu zh

                    Thanks. I guess it is ok to put side by side without borders.

                    Just to be sure, it is not possible to join calculated tables in worksheets, right? Not to join the data source, but to join the calculated ones in the worksheet to reuse.

                    "Otherwise maybe you could  create a sequential number in each data source and use that as a fake key just to join."

                    • 7. Re: How to build scorecard based on multiple data sources?
                      Monica Van Loon

                      Mu,

                      I meant you could do the creation of a sequence number or key in the actual Data source (add a new column to your Excel or to your database). Create the new key columns and bring that in to Tableau use them for the join.  I don't have time to test now but maybe investigate Tableau Prep for this.

                      You could create the sequence number

                      Calculation to generate a series of numbers

                      You could probably use this calc above in Tableau prep  join the 3 sources and generate one file.

                      For more information on Tableau Prep and what it does tool see the Tableau Website or I wrote a blog on it.

                      https://www.senturus.com/blog/10-features-tableau-data-prep-maestro/

                       

                      I have to say for best practice it is normally not a good idea to put totally unrelated columns together on one worksheet using fake keys. I am a database person this is usually a bad idea.  But maybe you have a good use case for this. You probably need to think talk to someone who is a  data person and fully explain  what you are trying to and if your approach is right.

                      Monica

                      • 8. Re: How to build scorecard based on multiple data sources?
                        mu zh

                        Thanks Monica, I guess I didnt make myself clear perviously.. I totally understand that it is not wise to join different data together in one table. What I want to consult is whether it is possible to join the calculated tables in worksheet.

                         

                        For example, in worksheet1, I have a calculated table using data source 1,

                        Company     Profit

                        A                    10

                        B                     100

                         

                        in worksheet2, I have a calculated table using data source 2,

                        Company     Cost

                        A                    5

                        B                     95

                         

                        Wondering whether it is possible to join these 2 small tables somewhere without doinging anything in data source . Understand that we can show them in dashboard side by side without borders. Hope I get my questions clear this time... Thank you sincerely.