1 / 90

SSAS 2012 Tabular Mode Best Practices

Learn about SSAS 2012 Tabular Mode, BISM, DAX vs MDX, visualization, and PoC lessons. Explore BI solution architecture and technology requirements for efficient implementation.

heidir
Download Presentation

SSAS 2012 Tabular Mode Best Practices

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. SSAS 2012 Tabular ModeBest Practices Itay Braun CTO & BI Architect, Twingo Manager of the Microsoft BI User Group in Israel itay@twingo.co.il

  2. Target • Understanding what is exactly SSAS 2012 Tabular Mode and when to use it • Understanding the “small letters “ Exact Limitations

  3. PowerView • Great Visualization functionality • Simple and intuitive • BTW, • Part of SharePoint 2010 Enterprise Edition • Can only query SSAS 2012 Tabular Model

  4. PowerView • Great Visualization functionality • Simple and intuitive BTW, • Part SharePoint 2010 Enterprise Edition • Can only query SSAS 2012 Tabular Model

  5. Agenda • General BI Solution Architecture • What is SSAS 2012 Tabular Mode • BISM and other Main Features • Direct Query • DAX vs. MDX • Processing • Security • Clients • PoC – Lessons Learned

  6. BI Solution Architecture

  7. BI Solution Requirements • Business Requirements: • single version of the truth • unified data • KPI and CPM • Dashboards • Reports… • Technology is just an Enabler • Main Technological Requirements: • Easy to change and maintain • Security • Good performances • Self Service BI • Good visualization layer

  8. Using a New Technology • What does it do? • When should I use it? • And when shouldn’t I use it? • What’s it place in the technological road map? • Two examples • Similar Technologies • Who are the biggest competitors? • Which old technology replaced by the new one?

  9. SSAS 2012 Tabular Mode • Customers ask: • Does it replace good old MOLAP or is it just a better PowerPivot? • From now on, should I develop in Tabular Mode only? • We understand that in-memory column database is faster. How fast is Tabular compared to MOLAP? • We understand that development at Tabular Mode is simpler. We are SSAS 2008 R2 experts and know nothing about DAX and Tabular. Would you still say Tabular is simpler option for us?

  10. Is SSAS 2012 Tabular Mode Enterprise Ready?

  11. What is SSAS 2012 Tabular Mode?

  12. What is SSAS 2012 Tabular Mode • New DB Engine • Tabular Mode • Column Store • In-Memory • Compression (about 1:3; sometimes much better) • All data copied (processed) to the Tabular model • Direct SQL • Queries translated to SQL, running directly against the relational SQL Server. • Tabular Mode’s version of ROLAP

  13. BISM – BI Semantic Model • BISM is a concept, not a technical term • Users can easily query the intuitive data model • No need to understand complex data source structure • The BISM provides: • Data Model • Business Logic and Queries • Data Access • The implementation of the BISM are these three technologies: • MOLAP • SSAS Tabular Mode • PowerPivot

  14. Tabular vs. Multi Dimensional • Tabular • Quicker development • From detailed (granular, images) data to aggregate data • Good performances • Some features are missing • Multi-Dimensional • Rich Multi-dimensional functionality • Known and well documented engine • Suitable for very large cubes, beyond server’s memory size

  15. Schema (SQL Server Data Tools)

  16. Management Studio

  17. Tabular Mode - Main Features • Understanding the main features of a technology is the key to understand when and how to use it • Feature by Server Mode or Solution Type

  18. Developing a Model • Use the VisualStudio SQL Server Data Toolsto build a BISM • Open an existing PowerPivotModel using SSDT or Management Studio • Deployment • Immediate changes implementation at the SDDT • Use small DB for development

  19. Workspace Database • Created during model authoring using SSDT. • Disappeared automatically when closing the project • The workspace database resides in-memory. Workspace DB

  20. Data Sources • The model can use multiple data sources

  21. Tabular Mode Schema vs. Data Source Views • Much simpler than a Data Source View • Building the AdventureWorks DB using Tabular Mode is about x4times faster.

  22. Tabular Mode Schema • Dimension = One Table • No Dimension Wizard to create Hierarchies

  23. Tabular Mode Schema • Measures and KPI are defined here. No cube Wizard.

  24. Tabular Mode Schema • Edit Table Properties

  25. Tabular Mode Schema- Adding a column • Adding new calculated columns in the schema is possible. • Either add calculated columns to the source DB or to the BISM

  26. Adding a new columns • Add new columns using DAX • At the OLAP Data Source View you write an expression in the source language, TSQL, PLSQL…

  27. Schema - Limitations • Self Joins are not supported • Two tables can have only one active relationships • No role playing dimension • Many to Many relationships allowed using DAX

  28. Dimensions • Active / non active relationships • Only ONE relationship can be active • No Role Playing Dimensions • The Date Dimension can be connected by either OrderDate or ShipDate or DueDate You’ll have to build many date dimensions

  29. Dimensions • Dimensions • Multiple Hierarchies

  30. Snowflake Dimensions • A dimension is based on one table / view only. • A hierarchy can be based on one Table. • Create a view on the data source which joins the tables. • . Category name from Dim Category with a measure works as expected

  31. Dimensions - Limitations • No member properties • No Parent-Child • No linked dimensions • No need for Attribute Relationships • The dimension is a table • A dimension can be based on one and only one table • Dimension names taken directly from the • Large dimensions (million members ) performed better

  32. Measures • Sum, min, max, count, distinct count…or complex DAX Expressions • A table can contain both attributes and measures.

  33. Multi Grain Measure Groups • Support many “Measure Groups” with different dimensionality • For ex. Internet Sales (product level), Sales Quota (Category Level)

  34. KPI • Key performance Indicators are the heart of any BI solution • Replaces existing Measures

  35. KPI • Easy to createusing a wizard • Value – existing measure • Target – measure or a number • Status – using a GUI

  36. Perspectives • Same as in SSAS Multidimensional • Allows users to see a Database in a simpler way • Can hide: • Tables (dimensions) • Columns (Attributes) • Hierarchies • Measures • KPI

  37. Partitions • Same logic as in SSAS Multidimensional. • Easy to create • Can be processed alone (like in Multidimensional)

  38. What’s missing? • Translations • Sync Two Databases • Actions • Custom Assemblies (SSAS Procedures) • Self Join (Parent Child) • Role Playing Dimensions

  39. More Limitations • Named Set • Scopes • Write Back

  40. Direct Query

  41. DirectQueryMode • DirectQuery mode uses data that is stored in a SQL Server database. • Used for Real Time analysis • No processing • Changes to the underlying source data reflected immediately. • no extra management overhead of having to maintain a separate copy of the data.

  42. Direct Query • Performances • No clear answer, depends on the source DB • Still checking the efficiency of the generated SQL Queries. • Security - Any security enforced by the back-end database is guaranteed to be enforced, using row-level security • Unlike Multi Dimensional, you Can’t mix Direct Query and Tabular Partitions

  43. Direct Query Limitations • Can only use one SQL Server DB as a source • The entire model is either is either using DirectQuery or not. • Limited use of DAX functions • Client restrictions: Can only be queried by using DAX • Excel can’t be used because it uses MDX

  44. DAX vs. MDX

  45. DAX vs. MDX • MDX (Multi Dimensional Cube) vs. DAX • MDX (Tabular) vs. DAX (Tabular)

  46. Querying a Tabular Model using MDX • Quick start – just use MDX to query the model • Youdon’thaveto learn DAX • In general speaking, MDW performs well • Fine tuning DAX queries

  47. What is DAX Set of Excel-like formulas Enables advanced data modeling inside the PowerPivot for Excel or Tabular Mode Two types of calculations calculated columns measures

  48. Learn DAX • QuickStart: Learn DAX Basics in 30 Minutes • http://www.sqlbi.com/ • Chris Webb’s blog. 6 lessons about DAX start here • Converting MDX to DAX – First Steps

  49. DAX vs. MDX • DAX and MDX coexists peacefully • Tabular Mode can be queried using both DAX and MDX • Use MDX for data analysis (group by, hierarchies) • Use DAX for detailed reports • MDX uses Crossjoin to join many columns. - Excel uses MDX, PowerView uses DAX, SSRS can use both

More Related