1 / 22

SQL Server Analysis Services

SQL Server Analysis Services . Introduction to Tabular Mode and BISM. Josh Fennessy. BI Architect BlueGranite, Inc ( http://www.blue-granite.com ). Agenda . Analysis Services – before today The BI Semantic Model Tabular Mode Architecture Demonstration Review / questions / comments .

dulcea
Télécharger la présentation

SQL Server Analysis Services

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. SQL Server Analysis Services Introduction to Tabular Mode and BISM

  2. Josh Fennessy • BI Architect • BlueGranite, Inc (http://www.blue-granite.com)

  3. Agenda • Analysis Services – before today • The BI Semantic Model • Tabular Mode Architecture • Demonstration • Review / questions / comments

  4. A brief history SQL Server Analysis Services

  5. SSAS’ story • Based on OLAP technology purchased by MSFT from Panorama Software in ’96 • Officially released in ’98 as ‘OLAP Services’ in SQL 7.0 • Renamed in SQL 2000 to SSAS • Many new features delivered in SSAS 2005 • Data mining • UDM

  6. Broad adoption “Customers in the Magic Quadrant survey report that their Microsoft average deployment sizes are now larger than any other vendor in the survey in terms of users.” “Use of OLAP functionality by Microsoft customers is more than double that for the rest of the survey respondents.” Source: Gartner Magic Quadrant for BI Platforms, 2011 Large ecosystem "Wide availability of skills is among the top reasons customers select Microsoft over competing vendors.” Source: Gartner Magic Quadrant for BI Platforms, 2011 Analysis Services Today Highest rated infrastructure and development tools “Microsoft customers rate its BI platform infrastructure and development tools among the highest compared to other vendors, and a higher percentage of customers use them extensively.” Source: Gartner Magic Quadrant for BI Platforms, 2011

  7. Embrace the relational data model – well understood by developers and IT Pros Build on the strengths and success of Analysis Services and expand its reach to a much broader user base Analysis Services Vision BI Semantic Model Provide flexibility in the platform to suit the diverse needs of BI applications Bring together the relational and multidimensional models under a single unified BI platform – best of both worlds! Analysis Services Tomorrow

  8. Business Intelligence Semantic Model

  9. One Model for all End User Experiences BI Semantic Model Data model Client ToolsAnalytics, Reports, Scorecards, Dashboards, Custom Apps Business logic and queries Data access Team BI Personal BI Organizational BI Analysis Services PowerPivot for SharePoint PowerPivot for Excel BI Semantic Model Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files

  10. BI Semantic ModelWhat about existing Analysis Services applications? Existing applications Based on Unified Dimensional Model Existing applications Based on Unified Dimensional Model Existing applications Every UDM becomes a BI Semantic Model New applications New technology options After RTM “Denali”

  11. SharePoint Insights Third-party applications Reporting Services Excel PowerPivot BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX Direct Query Data access ROLAP VertiPaq MOLAP BISM Architecture Databases LOB Applications Files OData Feeds Cloud Services

  12. Richness Scalability Flexibility • VertiPaq for high performance, MOLAP for mission critical scale • DirectQuery and ROLAP for real-time access to data sources • State-of-the-art compression algorithms • Scales to largest enterprise servers • Rich data modeling capabilities • Sophisticated business logic using MDX and DAX • Fine-grained security – row/cell level • Enterprise capabilities – multi-language and perspectives • Multi-dimensional and tabular modeling experiences • MDX and DAX for business logic and queries • Cached and passthrough storage modes • Choice of end-user BI tools BISM Features

  13. End User Model Developer BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX Data access VertiPaq Scenario: Excel over Sales Model SQL Server Dynamics CRM

  14. Quiz time! Pick which one is a Tabular Model. What does BISM do for me?

  15. MOLAP • Disk based store… typical 3x compression • Disk scans with in-memory subcube caching… aggregation tuning required • Extensive paging support… data volumes can scale to multiple terabytes xVelocity • In-memory column store… typical 10x compression • Brute force memory scans… high performance by default… no tuning required • Basic paging support… data volume mostly limited to physical memory DirectQuery • Passes through DAX queries & calculations… fully exploits backend database capabilities • No support for MDX queries… no support for data sources other than SQL Server (in Denali) ROLAP • Passes through fact table requests… not recommended for large dimension tables • Supports most relational data sources… no support for aggregations except SQL Server indexed views SSAS Data Access & Storage

  16. MDX • Based on understanding of multidimensional concepts – higher initial learning curve • Complex solutions require steeper learning curve – CurrentMember, overwrite semantics, etc. • Ideally suited for apps that need the power of multidimensional calculations – scopes, assignments, calc members DAX • Based on Excel formulas and relational concepts – easy to get started • Complex solutions require steeper learning curve – row/filter context, Calculate, etc. • Calculated columns enable new scenarios, however no named sets or calc members Custom Calculations

  17. Two Visual Studio (BIDS) project types in Denali • Multidimensional project – with MDX and MOLAP/ROLAP • Tabular project – with DAX and VertiPaq/DirectQuery • Some Considerations Favors Tabular/DAX Favors Multi-dim/MDX • Cube write-back needed? • Parent/Child needed? • 4/4/5 Fiscal Calendars • Excessive Many to Many • Extreme data volumes • Large MD investment? • Large RAM footprint a negative? • Financial models (budgeting/forecasting) • Real-time (Direct Query) • Counting what’s not present • Excel-based Modeling Attractive? • Non-relational data sources? • In-memory performance benefit • Lower learning curve desirable? • Simpler models (Sales, OLTP transaction analysis, etc.) How should I build my SSAS solution?

  18. Multidimensional isn’t dead • DAX doesn’t address some common modeling requirements • Vertipaq has more limited storage (models must fit in RAM) • Many simple data modeling tasks are easier in DAX; many complex ones are easier in MDX • As DAX/BISM evolves it will close the gap, but not for a couple years • At RTM Power View is a Tabular-only technology • This will probably force a decision to tabular in some scenarios • Business Analytics is complex no matter what expression language is used • DAX isn’t a silver bullet, but it probably is easier to learn to implement basic/intermediate calculations than MDX for those new to OLAP • Should I port my Multidimensional cube to Tabular during migration? • If calculations aren’t complex and all necessary features are available in Tabular Mode/DAX, you should consider doing so to achieve better performance and Power view support • If the existing calculations and installed Other thoughts…

  19. Process for Multidimensional to Tabular migration • Evaluate features in the gap • Many-to-many (can be done in calculations however) • Parent/Child • Cube writeback • Calculated members • Etc. • How difficult to rewrite calculations in DAX? • Is the data too large for Tabular mode? (terabytes+) • Will the server have enough RAM? • Existing application impact? • Does Tabular/DAX solve unmet needs? • Multi-select issues in calculations • Counting what’s not there needs • Performance issues (ad-hoc w/o aggregation issues) Other Thoughts…

  20. Demo

  21. BISM is designed to make USER experience smoother • Complexity still exists in data modeling • Multi-dimensional is not gone • DAX is still complex REview

  22. Questions? Email me - jfennessy@blue-granite.com Thank you!

More Related