14 Replies Latest reply on Aug 21, 2015 12:45 PM by Toby Erkson

    SharePoint List as data source

    sinadashtipour

      Hi All,

       

      I have searched the forum but couldn’t find a clear answer to my query.

       

      I basically want to know if it's possible to connect Tableaeu to a SharePoint list as a data source? If yes, what are the steps?

       

      Many thanks

        • 1. Re: SharePoint List as data source
          Nathaniel Fitzgerald-Hood

          Hi, yes it's possible but you have to do it via MS Access.

           

          You create a linked table in Access (to the SP list) and then connect Tableau to Access. You don't need MS Access on the server, it uses the MDAC (data access components) to do the business - when you query MS Access it'll get the data from Sharepoint and pass it back to Tableau. You can create an extract from there (as often as you need) and then you can get away from continuously querying Jet. It works fine.

           

          - Nathaniel

          • 2. Re: SharePoint List as data source
            John Maki

            Just wanted to share my process for linking Tableau 6.0 Desktop to a SharePoint list in support of a "live" (or as-live-as-possible) connection. (Apologies if this is documented elsewhere. I couldn't find it.)

             

            Phase 1: Connect MS Access to SharePoint.

             

            1. Open MS Access (I use 2007).

            2. Click External Data > SharePoint List

            3. Specify the correct SharePoint site.

            4. Click the "Link to the data source by creating a linked table." (Note: do not select the "Import" option, or this process will fail.)

            5. Click the Next button.

            6. Check the Link(s) checkboxes for the List(s) you want to import.

            7. Click OK. The selected lists will appear in the Access navigation pane.

            8. Save the Access DB file (in the accdb file format).

             

            Phase 2: Publish (Copy) the MS Access file to a SharePoint library.

             

            A couple options here. You can use the built-in "Publish" wizard in Access to move a copy of the file to a SharePoint library (the library needs to be in the same site as the list). I just copy and paste the file into the library. Either works.

             

            Phase 3: Connect Tableau to the SharePoint-hosted Access DB file.

             

            - Assumes you are on the appropriate network to open your SharePoint site. My company is on SharePoint WSS 3.0.

             

            1. Invoke the usual Tableau connect-to-data process.

            2. Select Access as the datasource.

            3. Browse to the SharePoint library where the Access accdb file is hosted.

            4. Select the file.

            5. Continue as usual selecting lists, tables, or connecting via SQL.

            6. Save the TWBX file back to the SharePoint libary where the Access file resides.

             

            The result: From here you can create an extract, save a TWBX file, save a TDE file, update queries, etc...with normal functionality. On file open, the latest SharePoint data appears in both TWB (update query) and TWBX files.

             

            I have found some differences in the update behaviors in TWBX files based on where the extract resides. Best performance is if it is in the SP document library too. Perhaps a Tableau expert could comment further on where the TDE file should ideally live.

             

            There may be other processes that provide a similar outcome, but this has worked in my environment (large, fairly locked down SharePoint; ditto for local server shares).

            1 of 1 people found this helpful
            • 3. Re: SharePoint List as data source
              sinadashtipour

              Thanks, thats very useful!

              • 4. Re: SharePoint List as data source
                guest contributor

                I have also used SLAM to push SP list data into a SQL database.  This works very well especially when the SP list data is relational.  Lots of very cool capabilities here to use Actions to drive SP data onto web pages in a dashboard.

                • 5. Re: SharePoint List as data source
                  guest contributor

                  Sharepoint Help business processes to make a team work by providing a central access database. It maintains records and provides central platform for maintaining coordination in an organization.

                  • 6. Re: SharePoint List as data source
                    guest contributor

                    Thanks for this. I am able to complete the analysis after connecting to the access database which is connected live to the SharePoint List.

                     

                    I have issues when trying to publish the sheets I created based on this DataBase. I tried connecting to a normal access database and it's working fine.

                     

                    Can anyone help please.

                     

                    Thanks & Regards

                    Anoop

                    • 7. Re: SharePoint List as data source
                      sinadashtipour

                      Hi,

                       

                      We have followed John's instructions above and successfully connected to the SharePoint List through our Access database (MS Access 2007). We then connected to the database from Tableau and its bringing back the data as it should. There is just one problem, when we publish the workbook to the server, we don’t get the following error message:

                       

                      An error occurred on the server. The details of the error are:

                      Database error 0x80040154: Class not registered

                      Unable to connect to the Microsoft Access database "C:\Progress\PCT2\Analyst Server\data\tabsvc\temp\529D.tmp\Data\Shared Documents\Ops Helpdesk Live Connection.accdb". Check that the path is correct and that you have access privileges for the requested file.

                       

                      I'm sure we have installed the necessary drivers on the server, because we have tried to connect and publish to a MS Access Database without the SharePoint connection and it worked fine.

                       

                      Any ideas what we may have missed from the instructions?

                       

                      Many thanks

                      • 8. Re: SharePoint List as data source
                        John Maki

                        I'm not sure if I can help, but I'll try. It appears your Tableau file is looking for a database on a local drive, instead of the one published to the SharePoint library. Your Tableau file must point to the database that is in a SharePoint library (hence the connection string would look something like: https://xxxxxxx/sproot/spsite/splibrary/accesssource.accdb).

                         

                        Also these instructions are for the Desktop 6.0 version of Tableau, not for the server version.

                        • 9. Re: SharePoint List as data source
                          guest contributor

                          I am trying to connect Tableau to my SharePoint instance using the above method. However, I got the following error:

                          Database error: unrecognized database format

                          Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.

                           

                          Hope someone can point me in the right direction. Thanks!

                          • 10. Re: SharePoint List as data source
                            Alex Blakemore

                            Not sure if this is your problem, but when you publish something to server, you need to make sure that Tableau server has the drivers for your database installed. If you don't install MS Office on server machines (not uncommon), then you may need to install a driver to be able to read MS Access files on the server. See the Tableau drivers page.

                            • 12. Re: SharePoint List as data source
                              Mark Cook

                              John, thanks for your posts here, they've been very helpful.

                               

                              We have currently published two dashboards using SharePoint lists as their datasource. I am using an Access database linked to the list tables, but have not ever published them to the SharePoint site. The data refreshes manually, but hangs when the extract is scheduled in Tableau Server. The error message is a timeout error.

                               

                              I'm wondering if publishing the Access database to the SharePoint site would resolve this issue. We need regular refreshes, so OData is not an option.

                               

                              Would you contact me at Mark.Cook@Providence.org, or call me at (360) 493-5707 when you have an opportunity?

                               

                              Thank you, Merry Christmas, and happy New Year!

                              Mark

                              • 13. Re: SharePoint List as data source
                                Marc Faber

                                John,

                                 

                                I followed all your steps and can link from Tableau desktop to the Access database, but when I finally want to publish the database to Tableau server I get an error message:

                                 

                                ----------------------

                                An unexpected error occurred. If you continue to receive this error please contact your Tableau Server Administrator.

                                 

                                 

                                TableauException: Database error 0x80004005: The Microsoft Office Access database engine cannot read the data in Projects. The minimum required version to read the data is 14.0.0000.0000. The table "Projects" does not exist. Unable to connect to the Microsoft Access database "\\xxxxxxxx@SSL\xxxxProjects\SharedDocuments\APT.accdb". Check that the path is correct and that you have access priv...

                                2015-08-21 18:34:49.631 (VddvSQqFJ9QAAE1o294AAAJX,0,0)

                                 

                                ------------------------

                                 

                                I have replaced the true names with xxxx's.

                                 

                                Now, the table is there, as I was able to build the Tableau work book throught Tableau desktop, but Tableau server just doesn't like it. (We have several other Tableau Server to MSAccess connections that work normally) The only way to make it work is to create local  tables in the same access database, create a macro to append the data from the linked Sharepoints lists into the local access tables and then link with Tableau to these tables. Then it can be published.

                                But then we're not really "live" anymore. I'd have to set a scheduled task to refresh the local tables at set intervals.

                                 

                                Does anybody know how to make this work "live"?

                                Is it a matter of Sharepoint rights from the Tableau Sever to the Sharepoint Server?

                                The minimum required version error says something about MS Access issue, but we can't find anything wrong with that.

                                 

                                Thanks for anybody's help!

                                • 14. Re: SharePoint List as data source
                                  Toby Erkson

                                  Please see this:  Connecting to a SharePoint 2007/2010 object to use as a data source

                                   

                                  If it's SharePoint 2013 then that is a MS SQL database and I have no experience with that as we are not allowed to connect to it