6 Replies Latest reply on Oct 8, 2018 8:37 AM by Toby Erkson

    Excel-on-SharePoint as a Tableau Data Source

    Mark Winieski

      I am posting this scenario to get the Community Forum sharing any known solutions to a growing convention in Tableau Analytics.  Presently many Tableau users with personal or organizational data are keeping and joining their data with larger data sets.  This data is maintained by many users, dependent upon regular and frequent input and edit. So, it needs to be readily available and with a user data interface that is intuitive.  Many have chosen an Excel workbook as stored on Microsoft SharePoint to accommodate.

       

      Sure, Tableau can connect to just about any data source ever imagined, however, there are some inherent risks in this Excel-on-SharePoint data design:

      1. Excel is not a database.  Shared Excel “database” files are at risk of “corruption” in the hands of many users.  Any contributor can delete, add or change the name of a column or worksheet which can render the file structure unreadable by Tableau.
      2. While SharePoint makes files easily accessible to multiple users, likewise it is not a database.  In fact, we have seen cases where SharePoint is less than reliable to connect to from Tableau, leaving dashboards unreliable and sporadic in their connectivity and performance.
      3. Live connections to a SharePoint file do not always result in instantaneous data changes on your Tableau Dashboard. SharePoint file system uses a Check-out, Check-in, synchronization method which can cause lags in real-time results.
      4. Did you know SharePoint has a file size limitation?  There are users splitting their data across duplicate workbooks and again merging them in Tableau merely to accommodate this very limitation.

       

      Naturally, a best practice would be to consider a true database to contain structured data for real-time results.  However, procuring database space on enterprise databases (Oracle, Hadoop, SQL Server) can be a time-challenge - you need to work through the IT department.  Enterprise File Servers are on their way out in favor of SharePoint so sharing your file-based database (i.e. MS Access) on a File Server is not a viable option.  Perhaps the most daunting hurdle with these databases is the lack of a widely available and intuitive, tabular data entry/edit interface.

       

      It stands to reason why so many turn to the familiar Excel “database” on SharePoint for familiarity, a spreadsheet-style data interface, ease of use, implementation time, and wide user accessibility.  Unfortunately for this reason many users are convinced it is their only implementation option and unwittingly suffer the inherent drawbacks and disappointment.

       

      I would ask anyone to share any successful alternatives to Excel-on-SharePoint which accommodate the velocity, intuitive interface, and accessibility while offering the stability, capacity and durability of a true database back-end. Any and all suggestions, welcome.

        • 1. Re: Excel-on-SharePoint as a Tableau Data Source
          Ciara Brennan

          Thanks for the inputs Mark, this is an interesting topic.

           

          There are definitely some areas to bear in mind when connecting to SharePoint-based Excel files (such as ensuring the Excel is hosted on an on-prem SP site and that it's stored on the local file system). Personally I don't have experience with using successful alternatives, but I will ask around to see if other folks have comments to add....

           

          Ciara 

           

          Just FYI, some links on this topic

          Connecting to SharePoint-Based Excel File | Tableau Software

          Data Not Refreshing When Using SharePoint-Based Excel Data Sources | Tableau Software

          • 2. Re: Excel-on-SharePoint as a Tableau Data Source
            Toby Erkson

            Mark Winieski wrote:

             

            ...

             

            It stands to reason why so many turn to the familiar Excel “database” on SharePoint for familiarity, a spreadsheet-style data interface, ease of use, implementation time, and wide user accessibility. Unfortunately for this reason many users are convinced it is their only implementation option and unwittingly suffer the inherent drawbacks and disappointment.

             

            I would ask anyone to share any successful alternatives to Excel-on-SharePoint which accommodate the velocity, intuitive interface, and accessibility while offering the stability, capacity and durability of a true database back-end. Any and all suggestions, welcome.

             

            User indolence*.

             

            That is the simplest way I can describe pretty much every user and is why there is such a huge amount of push-back when trying to do the right thing by moving away from Excel as a data source.

             

            I would challenge anyone to meet your criteria; ease of use as described with a true db back-end, however, with my own criteria to include ease-of-use and low cost.

             

            • I don't see network drives going away -- they are a valid, fast, and simple way to store & share a plethora of files & file types.  However, I would like to see mapped-drives go away i.e. mapping a drive to a limited set of single alphabet characters.  UNC format only.
            • SharePoint isn't as ubiquitous as Excel and is not the ideal file sharing system.  Being the SharePoint admin for my team I find enough faults to not really like it nor find it as necessary.  I will disclose that our SharePoint environment is a bit over-controlled by our parent company so that affects my experience in some areas.

             

            The issue as I see it is, simply, Excel.  Where ever it may be stored is less consequential. It's brilliant software as it's easy to learn and use, a fancy visual calculator that can be as simple or -- thanks to VBA -- as complex as one wants (I used to be an Excel/Access programmer).  Given how users will use time as an excuse to get things done now instead of doing it right (which seems to be the antithesis of time) I believe finding any alternative method will be difficult, if not impossible.  Users must have the desire to do things "right", even if it costs a little more time & money.  That's what it all boils down to.  In my opinion

             

             

             

            *Click me for the definition.

            • 3. Re: Excel-on-SharePoint as a Tableau Data Source
              Mark Winieski

              Watch out!!  Indeed it is true... at our company, the Server Infrastructure custodians and SharePoint Admin organization are conspiring to eliminate File Servers (and the network shared drives hosted on them) in favor of SharePoint. It comes down to a server management reduction and cost savings initiative.

              • 4. Re: Excel-on-SharePoint as a Tableau Data Source
                Toby Erkson

                Oh wow...that is...I'm trying to wrap my head around that   I foresee nothing but end user unhappiness and I find it difficult to understand how the over-used "cost savings" excuse is even a consideration.  I must be missing something.  Goooooood luck with that initiative!  Wow.  I feel for ya   If some [IT illiterate?] person piped-up with that as a way to reduce costs (no file shares, everything onto SharePoint) I'd fight them tooth-and-nail to explain how that isn't an option (well, at least where I work), there's just so many things wrong with that.

                • 5. Re: Excel-on-SharePoint as a Tableau Data Source
                  alex.apolloni.0

                  We have the same thing going on..... (Network files shares being EOLed  as part of a push to Office365)

                   

                  I'd like to see Tableau support accessing Onedrive sync'ed Sharepoint sites from Tableau server just like they currently support Onedrive.

                   

                  Can I ask you vote here?:  link

                  • 6. Re: Excel-on-SharePoint as a Tableau Data Source
                    Toby Erkson

                    The magic word:  Office365

                    Two people going through this is a bad sign.  Thanks for the Idea, I up-voted it!