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
- Slides: 50