SharePoint By Bugday

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
• Partitioning
• 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)