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:
- 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.
- 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.
- 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.
- 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.