Tableau is very flexible when it comes to connecting to data. Connect to any data source accessible by ODBC, cubes (both SSAS and Orcale Essbase), text files, spreadsheets and a number of different databases.
It’s possible to connect directly to the live data source or to import the data into Tableau and create an extract. Importing and extracting 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. These options are often not available when connecting live to a data source. Each data type has different constraints which are often dependent on the limitations of the underlying data source.
I have used Tableau extensively with SQL Server, Oracle, csv/excel/text files and SSAS cubes. Connecting to SQL Server using custom SQL is very simple however I believe it’s better to create a view on the SQL server where possible. 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, Tableau is more of a front end than anything else. Consequently most of the Tableau functions aren’t available. Writing calculations, i.e. year on year growth, is better in the actual cube rather than Tableau. However it is still possible to write MDX in Tableau, so you can write calculations against a cube data source.
In my experience Tableau actually works better with a relational database (or any tabular data) than with cubes. Consider this point if researching Tableau as a reporting solution.