The following provides a summary comparison of the characteristics of multidimensional and tabular models. Feature Decision Multidimensional/ Multidimensional Tabular group criteria Tabular modeling modeling / Time to solution Longer time to solution. Shorter time to solution. Learning curve Dimensional modeling and MDX Relational modeling and Excel-like DAX language create a steeper learning curve language create a less steep learning curve but / but natively provide more complex complex capabilities may require sophisticated capabilities. DAX expressions. Data Data One-to-many. One-to-many. model relationships Many-to-many. Many-to-many requires DAX expressions. / Reference relationships must be explicitly Modeling table relationships creates reference modeled. relationships. Data Hierarchies Native support for standard, ragged, and Native support for standard hierarchies. / model parent-child hierarchies hierarchies require DAX expressions. Data Additional data Perspectives, translations, actions, Perspectives and drillthrough. / model modeling drillthrough, stored procedures, and features write-back. Business Calculation MDX DAX / logic language Business Calculations Native support for common and complex Native support for common and many complex / logic calculations. calculations. Business Aggregation Sum, Count, Min, Max, Distinct Count, Sum, Count, Min, Max, Average, DistinctCount, functions None, ByAccount, AverageOfChildren, and various time intelligence functions like / FirstChild, LastChild, FirstNonEmpty, and FirstDate, LastDate, OpeningBalanceMonth, and LastNonEmpty. ClosingBalanceMonth. Business Hierarchy logic Functions to navigate standard and DAX functions to navigate parent-child logic parent-child hierarchies. hierarchies, DAX expressions to implement logic / in standard dimensions. Hierarchy logic generally more difficult using DAX. Business KPIs Actual, goal, status, and trend with Actual, goal, and status with graphical indicators. / logic graphical indicators
The following provides a summary comparison of the characteristics of multidimensional and tabular models. Feature group Decision criteria Multidimensional/ Tabular Multidimensional modeling Tabular modeling Time to solution / Longer time to solution. Shorter time to solution. Learning curve / Dimensional modeling and MDX language create a steeper learning curve but natively provide more complex capabilities. Relational modeling and Excel-like DAX language create a less steep learning curve but complex capabilities may require sophisticated DAX expressions. Data model Data relationships / One-to-many. Many-to-many. Reference relationships must be explicitly modeled. One-to-many. Many-to-many requires DAX expressions. Modeling table relationships creates reference relationships. Data model Hierarchies / Native support for standard, ragged, and parent-child hierarchies Native support for standard hierarchies. hierarchies require DAX expressions. Data model Additional data modeling features / Perspectives, translations, actions, drillthrough, stored procedures, and write-back. Perspectives and drillthrough. Business logic Calculation language / MDX DAX Business logic Calculations / Native support for common and complex calculations. Native support for common and many complex calculations. Business Aggregation functions / Sum, Count, Min, Max, Distinct Count, None, ByAccount, AverageOfChildren, FirstChild, LastChild, FirstNonEmpty, and LastNonEmpty. Sum, Count, Min, Max, Average, DistinctCount, and various time intelligence functions like FirstDate, LastDate, OpeningBalanceMonth, and ClosingBalanceMonth. Business logic Hierarchy logic / Functions to navigate standard and parent-child hierarchies. DAX functions to navigate parent-child hierarchies, DAX expressions to implement logic in standard dimensions. Hierarchy logic generally more difficult using DAX. Business logic KPIs / Actual, goal, status, and trend with graphical indicators Actual, goal, and status with graphical indicators.
SQL2012AS%20Multidimensional%20Modeling.docx