Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot

SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):

1. Tabular

a. Uses relational modelling constructs like such as tables and relationships

b. Uses xVelocity in-memory analytics engine for sorting and data calculations

c. Needs to use SSDT (SQL Server Data Tools) to implement

d. Can import data from relational data sources using OLE DB native and managed providers

e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.

f. support DAX calculations, DAX queries, and MDX queries

g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)

h. It might be not a good choice for the systems that are going to load terabytes of data

2. Multidimensional (cubes) and data mining

a. Uses OLAP modelling constructs such as cubes, dimensions and facts

b. Uses MOLAP, ROLAP or HOLAP storage

c. Needs to use SSDT (SQL Server Data Tools) to implement

d. Can import data from relational data sources, data feeds, and some document formats and also ODBC providers

e. Uses MDX calculations and MDX queries as well as ASSL

f. Data mining models support DMX and ASSL

g. Multidimensional model databases can use dimension and cell-level security, using role-based permissions in Analysis Services

h. The greatest amount of loading data are using multidimensional model

3. Power Pivot

a. Is a self-service BI solution

b. Uses xVelocity in-memory analytics engine

b.i. MS Excel

b.ii. SharePoint: As PowerPivot solutions use Excel for both data  modelling and rendering, deploying a workbook on a server for centralized and controlled data access requires:

        b.i.1. SharePoint

        b.i.2. Excel services

c. Can import data from relational data sources, data feeds, and some document formats and also ODBC providers

d. There is no developer support for PowerPivot workbooks. So, the built-in client and server applications must be used as part of the solution. Excel programming and SharePoint programming are the only options.

e. Uses DAX for calculations and queries

f. PowerPivot workbooks are secured at the file level, using SharePoint permissions

g. PowerPivot workbooks can be restored to a tabular mode server. Once the file is restored, it is decoupled from SharePoint, allowing using almost all of the tabular modelling features, including row-level security. The one tabular modelling feature that you cannot use on a restored workbook is linked tables

Notes:

1. Tabular does not supersede multidimensional, and the multidimensional and tabular formats are not interchangeable.

2. Tabular model is faster to design, test, and deploy and it will work better with the latest self-service BI applications.

3. It is not possible to deploy data mining objects to an instance that hosts tabular models or PowerPivot data.

4. Multidimensional storage size is about one third of actual data storage size in the DW.

5. Tabular storage size is about one tenth of actual data storage size in the DW.

Model Features:

References: Comparing Tabular and Multidimensional Solutions (SSAS), Data Mining Solutions