The legacy connection is only available for the Excel connector. Even when the files are the same, the way OneDrive exposes the data is different than how the OS does, so the connections and capabilities are different.
In regards to your notes:
1. You can create calculated fields in Tableau that use IIF conditions. You can then use the calculated fields as filters.
2. I can see how hosting the file in a shared folder can be slow, but if you are planning on using an extract, this should only be slow when refreshing. Since you are planning on doing this in the server and assuming it can be done over night, end users should not be impacted by this. Would that work?
Hope this helps!
OK so this is about how we have it now.
In your experience is it better to use calculated fields to filter or custom SQL.?
ASking this as i have found that C Sql improves performance..
Considering you'll be using extracts, the main difference is at which point the filter happens.This article and the chart included there are helpful: Tableau's Order of Operations.
When you use a filter in custom SQL, you are essentially limiting the size of the data set and your extract. You can achieve the same using calculated fields if you then use those calculated fields as 'Extract Filters':
if we have a data source with multiple connections, some of those being live but one being extract will only the extract refresh?
Saying this as I get a failure from server when it tries to refresh.
I may be confusing terms, but in the same data source you can't have a mix of live and extract. Each data source connection has a type you choose:
You can however have multiple data sources in a workbook, each can be of different type. In that situation, you would only refresh the extracts, live connections would only be accessed when you open the workbook.
If you are hitting an error when refreshing the extracts you'll need to review that with your server administrator and potentially with Tableau Support. With files on shared drives, I often times see the error being on the path specified to connect to the file (which needs to be a path the Server needs to be able to connect to) or permissions (the account running Tableau Server needs to have permissions to read the file). I'd suggest reading: https://kb.tableau.com/articles/howto/automatically-updating-data-in-server-workbook-that-uses-live-connection-to-excel. This applies to both live and extracts.