1 / 48

BISM: Multidimensional vs. Tabular

BISM: Multidimensional vs. Tabular. Marco Russo Senior Consultant SQLBI. http://www.sqlbi.com. marco@sqlbi.com. @ marcorus. Marco Russo. What we’ll discuss. History recap & roadmap xVelocity Patterns comparison Considerations about porting/migration Who wins?. UDM was born in 2005.

orestes
Download Presentation

BISM: Multidimensional vs. Tabular

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BISM:Multidimensional vs. Tabular Marco Russo Senior Consultant SQLBI

  2. http://www.sqlbi.com marco@sqlbi.com @marcorus Marco Russo

  3. What we’ll discuss • History recap & roadmap • xVelocity • Patterns comparison • Considerations about porting/migration • Who wins?

  4. UDM wasborn in 2005 • Once upon a time, there was UDM • Unified Dimensional Model • Only one BI data modeling tool

  5. 2012: BI Semantic Model • Therewas UDM, nowthereis BISM • Business Intelligence Semantic Model • Onlyone BI data modelingtool • Divided in twoflavors • Tabular • Multidimensional • Nowthere are twomodelingtechniques • Under a single technology

  6. BI Semantic Model: Vision Reporting Services Third-party applications SharePoint Insights Power View Excel PowerPivot BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX xVelocityVertiPaq Direct Query Data access ROLAP MOLAP Databases LOB Applications Files OData Feeds Cloud Services

  7. Tabular: the new kid in town • Part of the BISM vision • Super-duper fast xVelocity in-memoryengine • Amazing compression ratio of tables • Based on the relational data model • Programmed with the new DAX language • Whencompared with OLAP • Quitealwaysfaster. Muchfaster • Easier to learn and use

  8. IsThis The Right Approach? • I amxVelocity, your new engine • Thoushalthave no otherenginebefore me

  9. Or isitBetter to Investigate? • I havetwoenginesnow • What are theirstrenghts? • Whenshould I chooseoneagainst the other?

  10. Analysis Services Server Mode • Choose server mode of Analysis ServicesDeploymentMode setting in msmdsrv.ini • 0 – Multidimensional and Data Mining (default) • 1 – SharePoint • 2 – Tabular • Different icons in Object Explorer • Same code, different behavior • Choice at the INSTANCE level • Two SSAS instances needed to use both

  11. What is xVelocity in-memory? • In-memory database • Based on the relational methodology • Column oriented database • Data is stored in a compressed format

  12. Row Storage Layout Customers Table Nothing special here. This is the standard way database systems have been laying out tables on disk since the mid 1970s. Technically, it is called a “row store”

  13. Column Storage Layout Customers Table Tables are stored “column-wise” with all values from a single column stored in a single block

  14. Column vs Row Storage • Column Storage • Quick access to a single column • Time needed to materialize rows • Trade CPU vs I/O • Row Storage • Quick access to a single row • No materialization needed • Trade I/O vs CPU

  15. Run Length Encoding (RLE) RLE Compressionappliedonlywhensize of compressed dataissmallerthanoriginal

  16. Dictionary Encoding Only 4 values.2 bits are enough torepresentit xVelocity Store R.L.E. DISTINCT

  17. xVelocity works only in-memory • Compressed data • Must fit in memory • Otherwise, it simply does not work • On the hardware side this means • Very fast CPU • Very fast memory • Disks are not important at all

  18. Which Server for SSAS? Ideally, don’t use the sameserver for both

  19. MainLimitations of Tabular • Database must fit in memory • MissingFeatures • No support for translations • No concept of «Set» • MDX Script notavailable • Onlyplainvanilla 1:M relationships

  20. DistinctCount in Multidimensional • In Multidimensionalthey are a pain • Dedicatedmeasuregroup • Partitioningneeded for performance • Based on the number of cores • Designed for the hardware • Poor processing performance • ORDER BY during processing • On the facttable…

  21. DistinctCount in Tabular • Simply… easy asyoumightwant • DistinctCount on anycolumn • Dictionary encodinghelps a lot • In-memorycalculations • No special processing for DistinctCounts • No partitioningneeded • No special measuregroup

  22. Whataboutaggregations? • Hard to design • Ifyoudon’trely on the wizard • Whichyoushouldnot!  • Improvement on a subset of queries • Need to checkthemperiodically • UsageBasedOptimizationshelps • Butitis hard to manage • In Tabular? They are gone. No aggregation!

  23. Leaf-Level Calculations • Severalscenarios • Weightedaggregations • Currencyconversion • Easy to compute in DAX • Muchfasterthan MDX • Don’trequirechanges in the data model

  24. SnapshotBasedPatterns • Snapshot-basedanalysispatterns: • Warehouse • Balance Account • Portfolio • Insurance Policy

  25. SnapshotTable

  26. Warehouse Data Model

  27. MDX Query WITH MEMBER MEASURES.Stock AS SUM ( NULL : [Date Order].[Calendar].CURRENTMEMBER, [Measures].[Quantity] ) SELECT Stock ON 0, NON EMPTY [Product].[Product].[Product].MEMBERS * [Date Order].[Calendar].[Month].MEMBERS ON 1 FROM [Movements]

  28. DAX Query Stock := CALCULATE( SUM (FactMovements[OrderQuantity]), FILTER( ALL (DimTime), DimTime[TimeKey] <= MAX(DimTime[TimeKey]) ) )

  29. SnapshotBasedPatterns in VertiPaq • Snapshotisalmostalwaysrequired in Multidimensional • Typical Pattern in DimensionalModeling • Snapshot can be avoided in Tabular • Dynamiccalculationat a point-in-time • Smallermemoryfootprint, better performance and flexibility

  30. Many-to-ManyRelationships • Not so bad in Multidimensional • Critical issue: size of bridge table • Hard to optimize • Badusage of aggregations • Notavailable in Tabular as part of the data model • Butgreatsupport in DAX and VertiPaq! • Amazingly fast performance

  31. ToolDimensions • Common SSAS technique • Onetooldimensionsmodifiescalculations • Frequentlyused for the time dimension • Techniquebased on • [Measures].CurrentMember • SCOPE statement • Featuresnotavailable in DAX… • Complex DAX code needed to solve it

  32. Parent / Child Hierarchies • Multidimensionalsupports P/C hierarchies • Butthey are slow • Thus, younaturalizethem • Tabular supportsonlynaturalizedhierarchies • They are standard hierarchies • And they are fast • HideMemberIfpropertynotavailable • Can be createdusing DAX • Not an easy task, butitworks just fine

  33. UnaryOperators • Multidimensionalsupportsunaryoperators • Frequentlyused on P/C hierarchies • Pretty slow calculations • Used on financialcubes • On pre-aggregated data • Provide a veryniceuserexperience • No support in Tabular for unaryoperators • Use DAX to implementthem • Formulastend to be verycomplex

  34. Custom Rollups • Unaryoperators on steroids • Data drivencalculations • Pretty slow, butthey work • Again, on small financialcubes • Or to implementcurrencyconversion • No support in Tabular • Data-drivencannot be implemented in DAX • Butcurrencyconversion can runfaster

  35. MDX Script isnotAvailable • Usages of MDX Script • Simple calculations • More complex SCOPES • Entire business logic • Shouldyoustore business logic in MDX? • No, really • Sometimesitisnecessary • And DAX isnot the best option • Anyway, try to move business logic in ETL

  36. HierarchyUsage • Hierarchies in Multidimensional • One of the modelingpillars • E.g. «Ratio To Parent» • Hierarchies in Tabular • Collection of column • No function to handlethem in DAX • Ratio to parentispretty hard • Not a big issue, afterall • Userswilladapt to the limitation

  37. Should I Migrate My Solutions? • Notnecessarily • Differentmodelingoptions • Calculatedcolumns • ComplexRelationships in DAX • Differentcalculationtechniques • Evaluation Contexts • Migratingmeans building a new project

  38. And the winneris…. • Magic consultant’sanswer: itdepends • Tabular isnot the new UDM • Itisone of the flavors of BISM • Side by side with Multidimensional • DAX iseasier to learn and to use • Differentmodelingtechniques

  39. FAQ and OtherTips • Should I migrate from Multidimensional to Tabular? • No • I alreadyhaveMultidimensionalcubes and I know MDX • Continue usingMultidimensional by default • Consider Tabular for specific data marts / reports / calculations • I am new to Analysis Services • Start with Tabular, it’seasier to learn, butcheckrequirements first

  40. What’s the Best ChoiceBased on Requirements? • ChooseMultidimensional: • Support Translationswithin the data model • Calculationsstronglydepend on hierarchies • Consider Tabular: • SeveralDistinctCountmeasures on large tables • Complexmany-to-manycalculations with large bridge tables • Partition a dimension • NOTE: youhave to buyenough RAM

  41. Call to Action for Tabular • Learn DAX • Seriously, learnit! • DAX isnot easy, butitissimple! • Learndifferentmodelingtechniques • Spend time to getacquainted with this new kid • Build some prototypes • Don’tmake a porting, build a new project • Learn to think in DAX

  42. Related Content • DBI305 Developing and Managing a BI Semantic Model in Analysis Services • DBI413 Many-to-Many Relationships in BISM Tabular DBI62-HOL Optimizing a MS SQL Server 2012 Tabular BI Semantic Model

  43. Track Resources @sqlserver @TechEd_europe #msTechEd SQL Server 2012 Eval Copy Hands-On Labs Get Certified! mva Microsoft Virtual Academy

  44. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://europe.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

  45. Evaluations Submit your evals online http://europe.msteched.com/sessions

  46. © 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

  47. Who’sSpeaking? • BI Expert and Consultant • Founder of www.sqlbi.com • Problem Solving • Complex Project Assistance • DataWarehouseAssesments and Development • Courses, Trainings and Workshops • Book Writer • Microsoft Business Intelligence Partner • SSAS Maestro – MVP – MCP

More Related