Thursday 28 November 2013

SQL Server Analysis Services (SSAS) business intelligence (BI) semantic modelling options

SQL Server Analysis Services (SSAS) provides three different options to create business intelligence semantic model. Namely those 3 options are tabular, multidimensional and PowerPivot.

  • Tabular model uses relational database modelling objects such as tables, views and relationships for modeling data, and the xVelocity in-memory analytics engine for storing and calculating data.
  • Multidimensional model uses OLAP modelling constructs such as cubes and dimensions and uses MOLAP, ROLAP, or HOLAP storage.
  • PowerPivot model is a self-service BI solution that lets business analysts build an analytical data model inside an Excel workbook using the PowerPivot for Excel add-in. PowerPivot also uses xVelocity in-memory analytics engine within Excel and on SharePoint.


Table below summarizes feature available for each modelling option.


 MultidimensionalTabularPowerPivot
ActionsYesNoNo
AggregationsYesNoNo
Calculated MeasuresYesYesYes
Custom AssembliesYesNoNo
Custom RollupsYesNoNo
Distinct CountYesYes (via DAX)Yes (via DAX)
DrillthroughYesYesYes (detail opens in separate worksheet)
HierarchiesYesYesYes
KPIsYesYesYes
Linked objectsYesNoYes (linked tables)
Many-to-many relationshipsYesNoNo
Parent-child HierarchiesYesYes (via DAX)Yes (via DAX)
PartitionsYesYesNo
PerspectivesYesYesYes
Semi-additive MeasuresYesYesYes
TranslationsYesNoNo
User-defined HierarchiesYesYesYes
WritebackYesNoNo
 
See below links to learn about each modelling option.


No comments:

Post a Comment