microsoft ssas: should i use tabular or multidimensional?€¦ · microsoft ssas: should i use...
Post on 19-May-2018
222 Views
Preview:
TRANSCRIPT
1
Helping Companies Learn From the Past, Manage the Present and Shape the Future
www.senturus.com
Microsoft SSAS: Should I use Tabular or Multidimensional?
2
GoToWebinar Control Panel
Submit questions here
Click arrow to restore full control panel
3
Download a Copy of this Slide Deck http://www.senturus.com/recorded-webinars.php
4
Agenda
• Introductions
• Company Overview
• Overview and History of SSAS Multidimensional and Tabular
• Technical comparison of the two versions
• Summary Recommendations
• Further Resources
• Q & A
5
Presenters Today
Greg Herrera
Co-Founder
CEO
David Shroyer
• Over 12 years experience implementing BI solutions with the Microsoft stack
• Worked for ProClarity, which was later purchased by Microsoft
• Started “cube building career” with Arbor Essbase.
• MBA
6
Who is Senturus ?
• Consulting firm specializing in Corporate Performance Management
– Business Intelligence
– Tools of the Office of Finance
• Enterprise planning & budgeting
• Consolidate, close, report and file (CCRF)
– San Francisco Business Times Hall of Fame -- Four consecutive years in Fast 100 list of fastest-growing private companies in the Bay Area
• Experience
– 13-year focus on performance management
– More than 1,200 projects for 650+ clients
• People
– Business depth combined with technical expertise. Former CFOs, CIOs, Controllers, Directors...
– DBAs with MBAs
www.Senturus.com 888.601.6010 info@senturus.com
7
A few of our 650+ Clients
8
SSAS Multidimensional
• MSOLAP debut with the release of SQL Server 7.0
• The technology was purchased from an Israeli company Panorama
• It quickly became one of the most used OLAP engines, primarily because it was included in the SQL Server suite and there was no additional cost
9
SSAS Multidimensional
• SSAS was completely overhauled with the release of SQL Server 2005
• This new version allowed for “sub cubes” with the Scope statement. This radically increased the functionality of the cubes
• SSAS 2008R2 and 2012 have primarily been concerned with query performance and scalability
10
PowerPivot
• With the release of Excel 2010 came an add-in called PowerPivot
• PowerPivot uses a local instance of Analysis Services with the new xVelocity engine
• The xVelocity in-memory engine can greatly increase query performance
11
SSAS vs PowerPivot
• PowerPivot is a client based tool. All data is stored in the Excel workbooks.
• PowerPivot does not have any security, other than securing a workbook on the file system
• Microsoft position PowerPivot as “Self-Service BI” whereas SSAS Multidimensional is “Corporate BI”
12
SSAS Tabular
• With the release of SQL Server 2012 came the “server version” of PowerPivot which is a “tabular” instance of SSAS on the server
• Models can be created directly on the server or PowerPivot models can be imported
13
Languages
• Multidimensional cubes use MDX for calculations, security, and queries
• Tabular uses DAX for calculations and queries. It is also MDX compliant, which means that MDX based tools, like Excel, can query Tabular cubes
14
Multidimensional Modeling
• Based on a traditional star schema methodology. This includes dimension tables and fact tables. (Kimball methodology)
• Multidimensional models can include advanced calculations and sophisticated business logic.
15
Tabular Modeling
• Organizes data into related tables
• Because tables do not need to be designated as “dimensions” or “facts” the development time is less with tabular because all related tables serve both roles
16
Data Model Considerations
• One criteria to use when making the choice between versions is to look at your data warehouse data model. The relationships and characteristics of the data can help guide you to the proper product
17
Data Considerations
• One to Many Relationships
– Both models handle these out of the box. Although with tabular you can only join on ONE field
• Many to Many Relationships
– Multidimensional handles this out of the box. Tabular does not allow for M2M relationships but you can “work around it” by using DAX calculations
18
Hierarchies
• Standard Hierarchies
– Multidimensional needs to have hierarchy attribute relationships to be defined. The cube then uses those hierarchies to pre-calculated “totals” at those levels
– Tabular does not need any defined relationships. Because it is “in-memory” there is no need for pre-calculated aggregations
19
Hierarchies
• Ragged Hierarchies
– Multidimensional provides support for ragged hierarchies by hiding any missing values in the hierarchy
– Tabular does not support ragged hierarchies
20
Hierarchies
• Parent/Child Hierarchies – Multidimensional allows for parent/child
hierarchies out of the box. These are useful for supervisor/employee dimensions as well a chart of accounts dimension
– Tabular does not support the definition of parent-child hierarchies; however, the DAX language provides a set of functions that allows users to explore parent-child hierarchies and to use these hierarchies in formulas.
21
Financial Hierarchies
• Unary Operators
– Multidimensional offers out of the box support for unary operators. This is very important for chart of account hierarchies
– Tabular does not support unary operators
• Semi-additive Measures
– Multidimensional has built in aggregation functions
– Tabular handles these measures but you need to use separate DAX functions for each level such as ClosingBalanceMonth
22
Additional Features
• Perspectives – available in both models
• Translations – only available with multidimensional
• Actions – only available in multidimensional
• Drillthrough – available in both models
• Write-back – only available in multidimensional
• Role Playing Dimensions – not available in Tabular
23
Additional Features
• Formatting Measures – Tabular does not allow for custom formatting
• Display Folders – Tabular does not allow for display folders
• Naming the All Level – Tabular does not allow for the naming of the All Level
24
Calculations
• Both models have a wide variety of calculation possibilities
• Multidimensional allows for extra calculation properties such as font color and background color based on logic
• Multidimensional allows for calculations that are not located on the measures dimension
25
Shell Dimensions
• In multidimensional you can create “shell dimensions” which can store “global” calculations
• This is often used for Date Calculations such as year over year variances and YTD
• These can also be used for unit of measure conversions and other global calculations
26
Date Calculations
Adding a date calculations dimension to a cube can make
creating dashboard type reports very easy
27
Date Calculations
• With tabular you have two choices
– You build your date calculations into the measures dimension – Qty, Qty LY, Qty LY Var, Qty LY Var %, Sales, Sales LY, etc
– Or you create a faux shell dimension and include massive nested IIF statements to trap for every possible intersection. (which is just not reasonable) This also causes problem in that you can only format a measure so shell dimensions are just not suited for Tabular
28
SSAS Advanced Course
• In case you are interested in learning how to use shell dimensions and create a date calculations dimension, in multidimensional, we have an upcoming class which teaches these concepts
29
MDX vs DAX
• MDX (Multi-Dimensional Expressions) is used in Multidimensional and it is a language of hierarchies and dimensions
• DAX (Data Analysis Expressions) is used in Tabular and it is more columnar based
• One of the reasons why Microsoft invested heavily into the xVelocity technology was because they felt that MDX was too difficult for the average DBA
30
MDX vs DAX
• MDX can be hard to conceptualize but it has the advantage of being able to navigate hierarchies
• Percent of Parent calculation in MDX
[Measures].[Sales Amount] / ([Product].[Product Categories].CurrentMember.Parent, [Measures].[Sales Amount])
31
MDX vs DAX
• Here is the Percent of Parent calculation in DAX
IF(
ISFILTERED(Product[Product])
,[Sales]/CALCULATE([Sales],ALL(Product[Product]))
,IF(
ISFILTERED(Product[Subcategory])
,[Sales]/CALCULATE([Sales],ALL(Product[Subcategory]))
,1
)
)
32
Named Sets
• In multidimensional named sets allow you to retrieve common sets of members that can be used in reporting, such as “current day”, “current month”, or “last 12 months”
• Named sets are not available in Tabular but you can duplicate some of their functionality using attributes and SQL date functions
33
Named Sets
• Named sets are critical for automating date selection in dashboards
• Named sets will also be covered in our SSAS Advanced Topics course
34
Data Storage
• Multidimensional stores data in data partitions and then it creates aggregations within the partitions
– Compression is normally 3X to 4X
• Tabular loads data into memory so there is no need for aggregations
– Compression is normally 10X
35
Scalability
• Multidimensional can scale to billions of records. This is accomplished through the use of data partitioning
• Tabular is limited by the amount of memory on the server
– The lower the cardinality of the data the more records can be fit into memory
36
Processing Performance
• Multidimensional has dependencies which can make cube processing somewhat tricky
– All dimensions must be processed before the fact tables
– Updating of dimensions drops all aggregations and therefore they need to be recalculated
– Full process of dimensions drops the data
37
Processing Performance
• Tabular is just a “collection of tables” so there are no processing dependencies
• Therefore it is much more flexible when it comes to processing
• You can partition the data within the tabular model so that you don’t have to process the full data set each day but the partitions cannot be processed in parallel
38
Query Performance
• Multidimensional query performance is dependent upon the partition design and the aggregation design
• Tabular query performance does not need to be tuned
• Generally tabular will provide for better query performance especially if the users are pulling large amounts of detail data
39
Security
• Multidimensional bases security on members within a dimension
• Tabular bases security on rows in a table
• Both versions support dynamic security
• Multidimensional also allows for visual totals and allows users to use drillthrough
40
Microsoft Client Tools
• Microsoft Excel can connect to both models.
– Although the query performance benefits of Tabular are lost when you query with MDX
• PerformancePoint can only connect to Tabular with the use of a custom data source
• PowerView can only connect to Tabular
• Most third party products can connect to both models because Tabular is MDX compliant
41
Demonstration
• How do these models look to the user in Excel?
42
Summary Recommendations for Tabular
• You should consider Tabular if:
– You have a very short development time span
– Your data model is fairly simple
– Your users are pulling large amounts of detail data and query performance is important
– Cube has a lot of Distinct Counts
– You want to use PowerView in SharePoint
43
Things to Consider
• Once you choose a path you cannot migrate to the other version without starting over
• You will not be able to “merge” data between Tabular and Multidimensional cubes
• Tabular can be risky if requirements change mid-way through the project
44
Further Resources: New Training Course
Advanced Topics in Microsoft SSAS
June 11 (7am-3pm PDT)
• Learn how to
– create global date calculations,
– create named sets, which can help automate dashboards and reports
– use ‘many to many’ relationships
• $695
• For more information and to register: http://www.senturus.com/Microsoft_SSAS.php
45
GoToWebinar Control Panel
Submit questions here
Click arrow to restore full control panel
46
Helping Companies Learn From the Past, Manage the Present and Shape the Future
www.senturus.com
888-601-6010
info@senturus.com
Thank You
Copyright 2013 by Senturus, Inc. This entire presentation is copyrighted
and may not be reused or distributed without the written consent of Senturus, Inc.
top related