Tableau is very flexible when it comes to connecting to data. Using the full paid for product it’s possible to connect to any data source accessible by an ODBC connection, cubes (both SSAS and Orcale Essbase), text files, spreadsheets and a number of different databases.
It’s possible to connect to the live data source directly or to import the data into Tableau as an extract. Importing the data opens up the full array of Tableau inbuilt functionality; for example the quick table calculations, the Count Distinct (COUNTD) function and a variety of others; where these options are often not available depending on the data source.
I haven’t used Tableau with all of the possible data sources but I have used it extensively with SQL Server, csv/excel files and SSAS cubes. Connecting to SQL Server using custom SQL is very simple but I believe it’s better to write a view on the server to connect to, for some reason the Tableau engine works faster using a view than the custom SQL. I would also recommend doing as much of the data processing as possible in SQL – it’s faster than doing this in Tableau.
When connecting to an Analysis Services cube the Tableau is more of a front end than anything else, most of the Tableau functions aren’t available – it’s best to write any required calculations, i.e. year on year growth, in the actual cube and not to rely on Tableau. It is possible to write MDX statements in Tableau but I would recommend doing this in the cube itself as opposed to Tableau if the situation permits.
In my experience Tableau actually works better with a relational backend (or any tabular data) than with cubes, which is worth thinking about if you’re considering Tableau as a reporting solution.