Hi Jiang Fung,
Tableau will not automatically update its cache of your OData feed. The OData connection is tied to an Extract because OData is not a rich enough API for querying data to allow Tableau to operate on it as a live connection. We are interested in making OData more flexible in the future, but I don't know when this might happen.
We were also trying to get live sharepoint list data (2010) to tableau using OData. Does this work? By reading your comments I think it is not possible to get live data out of sharepoint (when the list is updated we need to get the tableau dashboard get updated as well) using OData connection from Tableau. Can you please clarify? If not is there any way I can get live data from sharepoint and use it in tableau? It is kind of urgent. Thanks for the help
You can force a refresh of the data in Tableau Desktop for an OData connection, which will reload all data from the entire OData feed. In Tableau Server you can schedule the refresh of a connection to automatically occur on a regular basis. Note however that this approach may not be great for live data since Tableau OData connections will pull all of the records, not just the latest; additionally Tableau connections do not have a trigger mechanism to automatically detect changes in the remote data source that warrant a data refresh.
Thanks for the reply. So if we get the data from sharepoint lists using OData and create a dashboard using tableau desktop and publish this (I guess along with the OData datasource) to the tableau server and then if we show this in the sharepoint (using say iframe) can we get live data at the latest schedule(if we schedule the extract to run in a particualr time) when ever the user opens the dashboard in sharepoint?
Yes, you should be able to get the behavior you desire. You may also need to configure the Tableau Server query caching policy to ensure that the latest data is used. See the Server admin guide for more details.
I tried to do the above, publish report with an odata connection and then refresh the report from server, but the report doesn't update with new data. It works on desktop when I force a refresh, but if I publish it, it doesn't change. I tried publishing the connection and building the dashboard using that, but it doesn't seem to make it work either. Is there something that needs to be done to the server to make OData connections refresh based on a schedule?
I have the same problem... I have the oData connection to Share Point working in Tableau Desktop... but the scheduled Server refresh never works... any help out there?
I also have this issue. In looking at the logs on the server it seems to be a permission issue but I have granted access to the server account and tried to embed my credentials with no luck..if anyone has an idea I am definately open to it.
I also have this issue, would be great to see if anyone has a solution?
My workaround at the moment:
- Export the Sharepoint list to an Excel File
- Save it to a network folder, ensure that Tableau Admin has access rights
- Save the Excel Data Connection File in the same location
- Write a Macro that opens the file, runs the 'Refresh All' command and then saves & closes the file.
This is far from the ideal solution, however it is the closest I get to the original idea of having a scheduled refresh to OData connection.
Any other improvement / workaround ideas?
Quick question, do you need to manually update the excel with the macro or is tableau able to do that?
Had a similar issues and ended up using a little app called "Simego Data Sync Studio" to schedule the updating of the SharePoint lists to corresponding SQL Server tables. Set up the sync package and just ran it as a scheduled job from a batch file on the Sharepoint server. Far from elegant, but, hey - it works without manual intervention! : )
Tableau is only able to read the data currently in the excel file, but not to refresh the actual data.
the macro opens the file and runs the 'Refresh All' command before save/closing it again.
This 'Refresh All' command, reads the current data on the Sharepoint List and update the data in the Excel file.
I have the macro sitting on our internal server which is running 24/7 so it will be able to run even if I am out of the office.
I am trying to get away though from the macro and have requested our internal IT department to create a Sharepoint Workflow which will export the Sharepoint List to the Excel file, overwriting it, upon every change made to the Sharepoint List.
Apparently this is possible with the use of Sharepoint Designer, but I am not given the permissions to use Sharepoint Designer, hence had to request IT to create this workflow for me.
Which means the excel file would then always contain the latest data and basically gives a 'live view' of what is in the Sharepoint List. Whereas the macro runs on intervals and hence has a delay.
Once the Sharepoint Workflow is created and Tableau is connected to the Excel File (either live or with scheduled refresh), this is as close as it gets to have the initially desired functionality.
Hope this helps,
I was looking at some of the solutions similar to the one you described and while this would work - it would involve investing some amount of money, which unfortunately is not an option for us as IT/Management would not allow purchasing such tools for a low-impact-project due to lack of Return Of Investment. (at least for my purpose)
For the ones sitting in the same boat - I'm afraid you need to get creative
Just to clarify, I was unaware of any way to export a list to excel using SharePoint designer. Do you have any type of documentation or other information on this? I have worked with SPD workflows extensively but have not seen this option anywhere.
I am sure it could be done using a VS workflow but that is a whole different deployment process.