We do have Join and Pivot tables in Tableau and from tableau 2018.1 version, you have more flexibility in creating on fly Data Model for reporting.
For your reference,
Tableau Prep: A new data preparation solution -- This is the new way of creating data model on fly using tableau Data Prep Tool from Now.
Zubair- This whitepaper on Tableau's Metadata Model should give you additional insight (Tableau whitepapers are best resources to understand the product). There are so many ways you could get data into Tableau either by connections to different types of databases/Excel/Text files etc., The other approach is by creating your own "Tableau Data Source" and develop reports thereof. You could do this either using Tableau Desktop or Tableau Prep like Venkat mentioned. Tableau Prep is a data preparation tool newly launched by Tableau.
As you work more with Tableau you'd realize it is much more than anything that you know now.
Thank you Venkat and Shiva for your respnses, I will read up on the links that you provided. As I see it, it seems that you have to build a data source every time you create a dashboard or report and if you want the drill down functionality, you have to bring in tables and columns even if you don't want to, simply because you feel that the user "might" want to drill down. That means full table scans, and unnecessary query hits on the db.
If we have a DW, we can't simply just create a meta data layer once and that's it?
That is where tableau Data Prep Come in. For now, Your right we need to apply and do data preparation for each time while creating a report.
From now, I mean tableau 2018.1 Data Prep tool, it behave like Universe(Meta layer) from tableau Desktop to Tableau Prep.