Multidimensional vs tabular picking your modeling style CATHY

Multidimensional vs tabular – picking your modeling style CATHY DUMAS, BUSINESS INTELLIGENCE CONSULTANT HTTP: //CATHYDUMAS. COM AKSHAI MIRCHANDANI, PRINCIPAL SOFTWARE DEVELOPMENT ENGINEER, MICROSOFT

Transition across BI spectrum PERSONAL BI Power. Pivot for Excel TEAM BI CORPORATE BI Power. Pivot for Sharepoint Analysis Services BI Semantic Model 2

Why Multidimensional vs Tabular is important PAGE 3 12/16/2021

Demo § Quick demo of a Tabular model PAGE 4 12/16/2021

Comparing modeling styles PAGE 5 12/16/2021

Easy choices § Multidimensional – incremental update to existing projects § Tabular – using Power View in SQL Server 2012 RTM PAGE 6 12/16/2021

Multidimensional modeling features • Role playing dimensions • Many-to-many dimensions • Intrinsic parent/child dimensions • Translations • Key vs name, composite keys • Advanced dimension and cell security PAGE 7 12/16/2021 • Display folders • Mixed real-time support (e. g. ROLAP and MOLAP partitions) • Linked measure groups/dimensions • Writeback • Proactive Caching

Tabular modeling features § Lower barrier to entry, conceptually simpler § Data sources such as OData, Text, Excel, Access, pasted data § Easy to mashup data § Upgrade from Power. Pivot § Direct. Query § Optimized real-time SQL queries, using a single query whenever possible § Can push down more calculations to the relational engine than ROLAP PAGE 8 12/16/2021

Multidimensional tools support • Date dimension generation • Creating custom actions (including drillthrough) • Setting Hide. Member. If property for graceful display of ragged hierarchies PAGE 9 12/16/2021 • Editing collation and language settings • Best practice analysis and notification • Multiple build configurations • Calculation debugging • Online mode

Tabular tools § Data in development environment for previewing § Simplified UI in SSDT, fewer clicks to building a solution § Analyze in Excel for rapid testing during development § msbuild support § Power View reporting properties PAGE 10 12/16/2021

Multidimensional calculation capabilities § Scope assignments § Custom rollups § Unary operators § Recursive calculations § Calculated members § User defined functions PAGE 11 12/16/2021

Tabular calculation capabilities § Simple measures are easier to express in DAX § Calculated columns § Support for multiple selection in DAX calculations § DAX functions for expanding parent/child hierarchies PAGE 12 12/16/2021

Multidimensional scale and performance § Parallel processing of partitions § Data need not fit in memory, data can be fetched from disk § Only a consideration with large data volumes § With 1 TB RAM, Verti. Paq can hold a 0. 5 TB database (which corresponds to 5 TB source data with 10 x compression) § Aggregations – improves performance for queries on big data sets § More caching of query results § Attribute hierarchies can be turned off to speed processing § Development environment works offline, so no up front data import PAGE 13 12/16/2021

Tabular scale and performance § “Engine of the devil” – good performance out of the box without aggregation design § Distinct count is faster § Certain modeling scenarios (eg Pareto (80/20) analysis) are faster § Dimension processing can be faster, since there are no aggregations or indexes to rebuild § DAX queries for detail level reporting are faster than MDX queries PAGE 14 12/16/2021

Where multidimensional and tabular are similar § Hardware requirements § Concurrent users § Processing windows § Replication and scale out § Backup/restore § Security (Kerberos) § Modeling features such as measures, time intelligence, KPIs, hierarchies, perspectives, … PAGE 15 12/16/2021

Futures Multidimensional • Mature technology • Continued incremental investment over time • Power View support over multidimensional PAGE 16 12/16/2021 Tabular • Young technology • Room to grow and change drastically to handle more capabilities in classic BI scenarios 100% feature parity is a non-goal

Scenarios § Budgeting – multidimensional § This scenario typically requires writeback and complex assignments § Sales – multidimensional or tabular § Web analytics – multidimensional or tabular § Large data volumes (>5 TB source data) require multidimensional § Scorecarding and forecasting – multidimensional, tabular, or Power. Pivot § Complex calculations require multidimensional PAGE 17 12/16/2021

Resources § Marco Russo § Why to use tabular in SQL Server 2012 http: //sqlblog. com/blogs/marco_russo/archive/2012/03/01/why-to-use-tabular-inanalysis-services-2012 -ssas. aspx § Verti. Paq vs OLAP – change your modeling approach http: //www. sqlbits. com/Sessions/Event 9/Vertipaq_vs_OLAP_Change_Your_Data_Mo deling_Approach § Cathy Dumas § The BI Semantic Model, MDX, DAX, and you http: //blogs. msdn. com/b/cathyk/archive/2011/10/17/the-bi-semantic-model-mdxdax-and-you. aspx PAGE 18 12/16/2021