DBI 319 BISM Multidimensional vs Tabular Marco Russo

DBI 319 BISM: Multidimensional vs. Tabular Marco Russo Senior Consultant SQLBI

marco@sqlbi. com http: //sqlblog. com/blo gs/marco_russo Marco Russo @marcorus

Third-party applications Reporting Services Power View Excel Power. Pivot Share. Point Insights BI Semantic Model Databases LOB Applications Files OData Feeds Cloud Services

Customers Table ID Name Address City State Bal Due 1 Bob … … … 3, 000 2 Sue … … … 500 3 Ann … … … 1, 700 4 Jim … … … 1, 500 5 Liz … … … 0 4 Jim … … … 1, 500 6 Dave … … … 9, 000 5 Liz … … … 0 7 Sue … … … 1, 010 6 Dave … … … 9, 000 8 Bob … … … 50 9 Jim … … … 1, 300 7 Sue … … … 1, 010 8 Bob … … … 50 9 Jim … … … 1, 300 Nothing special here. This is the standard way database systems have been laying out tables on disk since the mid 1970 s. Technically, it is called a “row store”

Customers Table ID Name Address City State Bal Due 1 Bob … … … 3, 000 2 Sue … … … 3 Ann … … 4 Jim … 5 Liz 6 ID Name Address City State 500 1 Bob … … … 3, 000 … 1, 700 2 Sue … … … 500 … … 1, 500 3 Ann … … … 1, 700 … … … 0 4 Jim … … … 1, 500 Dave … … … 9, 000 5 Liz … … … 0 7 Sue … … … 1, 010 6 Dave … … … 9, 000 8 Bob … … … 50 7 Sue … … … 1, 010 9 Jim … … … 1, 300 8 Bob … … … 50 9 Jim … … … 1, 300 Tables are stored “column-wise” with all values from a single column stored in a single block Bal Due

Quarter Start Count 100 Q 1 1 310 100 1 120 Q 2 311 290 120 Q 1 1 315 … … … 315 Q 1 1 100 Q 1 1 315 Prod. ID Start Count Q 1 2 198 1 1 5 … 2 450 2 6 3 Q 2 2 320 … … … Q 2 … 320 1 51 5 Q 2 1 150 2 56 3 Q 2 1 256 Q 2 1 450 Q 2 1 192 Q 2 1 184 Q 2 2 310 Q 2 2 251 … 2 266 Quarter Prod. ID Price Q 1 1 Q 1 RLE Compression applied only when size of compressed data is smaller than original Price 100 315 198 450 320 150 192 184 310

Only 4 values. 2 bits are enough to represent it Q. ID Quarter 1 0 Q 1 1 1 Q 2 Q 1 1 2 Q 3 Q 1 1 3 Q 4 Q 2 2 Q. ID Start Count … … 1 1 4 2 5 10 3 11 4 4 15 15 Quarter Q. ID Q 1 Q 2 Q 3 DISTINCT 2 3 Q 3 3 Q 4 4 … … R. L. E. x. Velocity Store

Split String Column Split a 10 -character length string into two 5 -character strings SELECT LEFT( Transaction. ID, 5 ) AS Transaction. High. ID, SUBSTRING( Transaction. ID, 6, LEN( Transaction. ID ) - 5 ) AS Transaction. Low. ID, Quantity, Price FROM Fact

Split Integer Column Split 100 million range in two 10. 000 ranges Beware of possible materialization later on SELECT Transaction. ID / 10000 AS Transaction. High. ID, Transaction. ID % 10000 AS Transaction. Low. ID, Quantity, Price FROM Fact

Number of Columns Process Time Cores Used Disk Size 1 (original) 02: 48 1 2 03: 21 up to 8 191 MB 3 03: 49 up to 8 129 MB 4 04: 01 up to 8 97 MB 8 05: 32 up to 8 105 MB 2, 811 MB

Feature Multidimensional Tabular RAM Some (16/32 Gb) A lot (64/128 Gb) RAM Speed Important Crucial Number of cores 4/8/16 Core speed Less Important Crucial Disk speed Very Important Useless SSD Disk Usage Strongly recommended Useless Network speed Important Concurrency Pretty good Not enough experience… Don’t use the same server for both

SQL Engine appliance Power. Pivot appliance

Classical Weighted Avg Solution Mean. Price : = SUM( [Price. Multiplied. By. Quantity] ) / SUM( [Order. Quantity] )

Weighted Aggregation in DAX Mean. Price = SUMX( Fact. Reseller. Sales, [Order. Quantity] * [Unit. Price] ) / SUM( [Order. Quantity] )

Data Model Complexity Visual Studio SSAS Multidimensional User Requirements

Data Visual Model. Studio Complexity SSAS Tabular Power. Pivot for Share. Point EXCEL Power. Pivot for Excel User Requirements

DBI 305 Developing and Managing a BI Semantic Model in Analysis Services DBI 413 Many-to-Many Relationships in BISM Tabular DBI 62 -HOL Optimizing a MS SQL Server 2012 Tabular BI Semantic Model

Learning Connect. Share. Discuss. Microsoft Certification & Training Resources http: //northamerica. msteched. com www. microsoft. com/learning Tech. Net Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn

mva

Complete an evaluation on Comm. Net and enter to win!

Scan the Tag to evaluate this session now on my. Tech. Ed Mobile

www. sqlbi. com