Tabular data models are in-memory databases that uses the xVelocity storage and processing engine.
Can be used by Excel and Power View
Tabular data models exposes data in a relational format(tables, columns and relationships) instead of dimensions and measures in a cube.
Tabular data models are quicker to build and not so complex, although they lack some of the advanced features that multidimensional models have.
The best things with tabular models are:
- Very easy to learn and implement if you know relational databases
- Much simpler in design than multidimensional
Tabular models use Data Analysis Expressions(DAX) to create measures and calculated members and is very similiar to Excel formulas.
There’s two options where you can create a tabular model:
- In Excel using PowerPivot(stored in the workbook) and can be published to SharePoint PowerPivot Service
- Using SQL Server Data Tools(old BIDS in VS) by creating a SQL Server 2012 Analysis Server instance installed in Tabular Mode
You’ll get this additional features if you use SSAS Tabular mode:
- DAX-based row-level security
- DirectQuery mode
- Deployment options
Dax is the language use create measures, queries and calculated members.
Here’s a really good quick learn guide:
QuickStart: Learn DAX Basics in 30 Minutes
Tabular Model in Analysis Services
SQL Server Analysis Services 2012 can be installed in three different modes:
SSAS(Multidimensional mode) – old traditional multidimensional mode(Cubes and Dimensions)
SSAS(Tabular mode) – New mode, where we use reletional databases with tables and columns(instead of cubes and dimensions)
SSAS(PowerPivot) – The SharePoint Service Application used to host the PowerPivot Service in SharePoint
You create tabular models using Visual Studio 2010:
You could also create a new tabular database in SSAS(Tabular mode) using SSMS by right clicking on the databases folder and choose “Restore from PowerPivot)