2 Replies Latest reply on Apr 20, 2016 8:43 AM by Diogo Braga

    Monthly Updated Dashboard with Data from Multiple Excel Files.

    Diogo Braga

      I am looking for guidance to set up data from multiple excel spreadsheets to work well in Tableau.

       

      I am creating a high level overview dashboard for a network of 50+ schools. The dashboard will include around 30 different metrics housed in 7 separate excel spreadsheets.

      All 7 separate excel spreadsheets are normalized, have the same unique identifiers, these files are kept separate because their formats are slightly different and most important they are owned by different people that update them when data is available, some updates are monthly, others 3 times or twice a year. As soon as data owners update the spreadsheet, the dashboard should be updated too.

       

      In my research, I learned about data blending and data joining. Data blending looked like a good option until I couldn't figure out how to control the dashboard with a global filter. Data joining has potential, except every time I experimented with this feature I noticed numbers changing in my dataset, specially in Levels of Details. Now I am reluctant, but tempted to append/merge my data in Excel Power Query before pulling into Tableau. I heard great things about Alteryx when it comes to data prepping and modeling before Tableau, but I am not ready to make an investment. I am hearing a great deal about the new features of Tableau 10 and its enhancements in data modeling.

       

      I appreciate if you can share insights on how to creatively address the challenges described above.

       

      Thank you!

        • 1. Re: Monthly Updated Dashboard with Data from Multiple Excel Files.
          Tharashasank Davuluru

          Hi,

          If you are  trying to import a number of Excel files .Each one of those files may  contain data about one table. Some of them are primary tables  but some of them are secondary/lookup tables containing category descriptions, school names, etc.. you told that you  15 such tables. Right now, in Tableau, If you  have to import them one by one and then use data blending to create the relationships between them. if you  want to do is to import all of these files under a single connection and list them as they are coming from the source . and then  create the relationships in advance of creating any visualization.

           

          One hack that I have found is to combine all the above Excelsheets into one Excelsheet and then import that as a datasource - in that case, I am able to see them as one source and define joins in advance. But this involves more manual work for each time the data is refreshed.

          1 of 1 people found this helpful
          • 2. Re: Monthly Updated Dashboard with Data from Multiple Excel Files.
            Diogo Braga

            Thanks for the reply Tharashasank Davuluru

             

            I am trying to stay away from more manual work, but this might be the best solution at this point. I didn't quite understand your explanation on the first paragraph, I appreciate if you can elaborate some more.

             

            Thank you!