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.
tableau extracts provide additional calculation options
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.
Connecting live to data sources limits Tableau to the constraints within that data source type
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.
SSAS Cubes are more limited to use 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.
Tableau works better with relational sources or files than with cubes
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.