1 / 37

Welcome

Welcome. Überblick SQL Server 2012 BI Semantic Model, PowerPivot und neue Analysis Services Features November 2012 Meinrad Weiss. Introduction. SQL Server 2012 Analysis Services (SSAS) is the fifth release of the product New features and enhancements in this release are based on:

noura
Download Presentation

Welcome

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. Welcome ÜberblickSQL Server 2012 BI Semantic Model, PowerPivotund neueAnalysis Services Features November 2012Meinrad Weiss Highlights SQL Server 2012: BI Semantic Model

  2. Introduction • SQL Server 2012 Analysis Services (SSAS) is the fifth release of the product • New features and enhancements in this release are based on: • A vision to expand the reach to a broader user base • Embracing the tabular data model • Bringing together tabular and multidimensional models under a single unified platform – the BI Semantic Model • Note: There are no improvements to the data mining component Highlights SQL Server 2012: BI Semantic Model

  3. What Were Microsoft’s Goals? Build on the strengths and success of Analysis Services and expand its reach to a much broader user base Embrace the relational data model – well understood by developers and IT Pros Analysis Services Vision BI Semantic Model Bring together the relational and multidimensional models under a single unified BI platform—best of both worlds! Provide flexibility in the platform to suit the diverse needs of BI applications Highlights SQL Server 2012: BI Semantic Model

  4. One Model for All User Experiences (Vision) Your Apps Reporting Services & Power View SharePoint Insights Excel PowerPivot Any client BI Semantic Model Multi- dimensional Data Model Tabular Business Logic/Queries MDX DAX Direct Query Data Access ROLAP xVelocity MOLAP Any data source Highlights SQL Server 2012: BI Semantic Model

  5. BI Semantic Model BI Semantic Model Data Model Client Tools Analytics, Reports, Scorecards, Dashboards, Custom Apps Business Logic and Queries DataAccess Team BI Personal BI Corporate BI Analysis Services PowerPivot for SharePoint PowerPivot for Excel One Model for All End User Experiences Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files Highlights SQL Server 2012: BI Semantic Model

  6. Excel 2010 Power Pivot Excel Workbook (with PowerPivot) “Traditional” Excel In Process “AnalysisServices” Pivot Table Services BISM(DAX) Data Sources Raw Data Store UDM(MDX) Reporting Services xVelocitystore Highlights SQL Server 2012: BI Semantic Model

  7. PowerPivot for Excel Add-in, Advanced Features • Perspectives and the reporting properties are hidden by default, otherwise they are available on the PowerPivot Window’s Advanced ribbon • The specialized functionality includes: • Perspectives • Show Implicit Measures • Summarize By • Reporting Properties Highlights SQL Server 2012: BI Semantic Model

  8. BI Semantic Model BI Semantic Model Data Model Client Tools Analytics, Reports, Scorecards, Dashboards, Custom Apps Business Logic and Queries DataAccess Team BI Personal BI Corporate BI Analysis Services PowerPivot for SharePoint PowerPivot for Excel One Model for All End User Experiences Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files Highlights SQL Server 2012: BI Semantic Model

  9. Excel 2010 PowerPivot PowerPivot and SharePoint Excel 2010 PowerPivot Get Copy SharePoint Server View in Browser Save/Publish Access Data Highlights SQL Server 2012: BI Semantic Model

  10. Excel 2010 PowerPivot PowerPivot and SharePoint Excel 2010 PowerPivot Get Copy SharePoint Server View in Browser Save Access Data Data connection • Refresh Engine for PowerPivot Data • File Level Secutity Highlights SQL Server 2012: BI Semantic Model

  11. PowerPivot for Sharepoint Add-in • The PowerPivot Gallery (in Gallery view) now includes these commands: • Open New Excel Workbook via a data connection • Create Power View Report • Manage Data refresh Highlights SQL Server 2012: BI Semantic Model

  12. BI Semantic Model BI Semantic Model Data Model Client Tools Analytics, Reports, Scorecards, Dashboards, Custom Apps Business Logic and Queries DataAccess Team BI Personal BI Corporate BI Analysis Services PowerPivot for SharePoint PowerPivot for Excel One Model for All End User Experiences Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files Highlights SQL Server 2012: BI Semantic Model

  13. New BI Project in SQL Server Data Tools New BI Project Excel 2010 PowerPivot Extract Model Highlights SQL Server 2012: BI Semantic Model

  14. Analysis Services 2012 SSAS BISM • In addition to Sharepoint Analysis Services provides: • Data Security • Partition Support Highlights SQL Server 2012: BI Semantic Model

  15. Conclusion PowerPivot and SSAS Tabular Model • PowerPivot is an Excel Add-In • Modern column store in memory technology • Simple to use • High performance • SharePoint acts as central storage place • Scalability • Automatic Data Refresh • File level Security • Analysis Services • Partition Support • Dimension (Data)Security Highlights SQL Server 2012: BI Semantic Model

  16. All together and “the reality” PowerView Reporting Services SharePoint Insights Third-Party Applications Excel PowerPivot DAX Query MDX Query BI Semantic Model Tabular Multidimensional Tabular Design Type Project Type PowerPivot Workbook Multidimensional Project Tabular Project Excel 2010 SQL Server Data Tools SQL Server Data Tools Development Tool MDX DAX DAX Business Logic In-MemoryN/A MOLAPROLAP In-MemoryDirectQuery Data Access – CachePassthrough SharePoint Library / Analysis ServicesPowerPivot Analysis ServicesMultidimensional Analysis ServicesTabular Deployment A PowerPivot workbook can be restored to a Tabular instance, or imported to create a Tabular Project RelationalDatabases LOBApplications ODataFeeds DeployedBI Semantic Model Cloud Services Files Highlights SQL Server 2012: BI Semantic Model

  17. Multidimensional Model Improvements • Multidimensional models received over 300 improvements across the board for performance, supportability, reliability and functionality • Almost 100 were reported directly by customers • Major new features include: • Designers upgraded for SQL Server Data Tools (Visual Studio 2010) • Removal of 4GB string store limit for attributes • New events for monitoring lock usage and contentions • Support for XEvents for low overhead eventing support • PowerShell support Highlights SQL Server 2012: BI Semantic Model

  18. Choosing the right development approach Tabular Multidimensional • Sophisticated model involving a higher learning curve • Advanced concepts are baked in to the model and optimized (parent-child, many-to-many, attribute relationships, key vs. name, etc.) • Ideally suited for OLAP type applications (e.g. planning, budgeting, forecasting) that need the power of the multidimensional model • Familiar model, easier to build, faster time to solution • Some advanced concepts are not available natively in the model and may need calculations to simulate these • Easy to wrap a model over a raw database or data warehouse for analytics and reporting Highlights SQL Server 2012: BI Semantic Model

  19. CHOOSING THE RIGHT DEVELOPMENT APPROACH DAX MDX • Based on an understanding of multidimensional concepts – involves a higher initial learning curve • Complex solutions require steeper learning curve • Ideally suited for applications that need the power of multidimensional calculations involving scopes, assignments, and calculated members • 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 calculated members (other than measures) Highlights SQL Server 2012: BI Semantic Model

  20. What‘sMissing in theTabular Engine • DataMiningcapabilities • Translations • Writeback • Dimension Security on Measures • RaggedHierarchies • Role-playingdimensions • Scopedassignmentsandunaryoperators • Parent/childhierarchysupport (partiallysupported) • Many-to-manyrelationships (partiallysupported) • Drillthrough (partiallysupported) http://msdn.microsoft.com/en-us/library/ms143506 Highlights SQL Server 2012: BI Semantic Model

  21. Where’s the money going SQL Server 2012 SQL Server vNext SQL Server 2008 R2 MultidimensionalModel It’s not dead, it’s done! $$$ TabularModel $$$ Highlights SQL Server 2012: BI Semantic Model

  22. DAX Enhancements • DAX is fully supported in both tabular BI Semantic Model project types: • PowerPivotWorkbook • Tabular Project • New functions have been added to extend DAX’s usefulness • DAX Query allows querying a tabular BI Semantic Model Highlights SQL Server 2012: BI Semantic Model

  23. New DAX Functions • ALLSELECTED • Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters • FILTERS • Returns the values that are directly applied as filters to a column • HASONEFILTER • Returns TRUE when the number of directly filtered values for a column is one; otherwise returns FALSE • HASONEVALUE • Returns TRUE when the context for a column has been filtered down to one distinct value only; otherwise returns FALSE Highlights SQL Server 2012: BI Semantic Model

  24. New DAX Functions (2) • ISCROSSFILTERED • Returns TRUE when a column or another column in the same or related table is being filtered • ISFILTERED • Returns TRUE when a column is being filtered directly • USERELATIONSHIP • Specifies the relationship to be used in a specific calculation as the one that exists between two columns • Can only be used in functions that take a filter as an argument, for example CALCULATE • Allows producing a calculation based on an inactive relationship Highlights SQL Server 2012: BI Semantic Model

  25. New DAX Functions (3) • CONTAINS • Returns TRUE if values for all referred columns exist, or are contained, in those columns; otherwise, the function returns FALSE • CUSTOMDATA • Returns the content of the CustomData property in the connection string • LOOKUPVALUE • Returns the value in specified column for the row that meets all criteria specified by a search column and value • USERNAME • Returns the authenticated username (DOMAIN\USER) Highlights SQL Server 2012: BI Semantic Model

  26. New DAX Functions (4) • The Parent-Child functions are useful for converting a self-referencing relationship into its natural equivalent for the purpose of transforming a parent-child hierarchy into a fixed-level hierarchy • PATH • Returns a vertical bar delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until the current • PATHITEM • Returns the item at the specified position from a string resulting from evaluation of a PATH function • Positions are counted from left to right Highlights SQL Server 2012: BI Semantic Model

  27. New DAX Functions (5) • PATHITEMREVERSE • Same as PATHITEMbut positions are counted backwards from right to left • PATHLENGTH • Returns the number of parents to the specified item in a given PATH result, including self • PATHCONTAINS • Returns TRUE if the specified item exists within the specified path Highlights SQL Server 2012: BI Semantic Model

  28. New DAX Functions (6) • SWITCH • Evaluates an expression against a list of values and returns one of multiple possible result expressions = SWITCH([Month], 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December ", "Unknown month number") Highlights SQL Server 2012: BI Semantic Model

  29. New DAX Functions (7) • ADDCOLUMNS • Adds calculated columns to the given table or table expression • CROSSJOIN • Returns a table that contains the Cartesian product of all rows from all tables in the arguments • The columns in the new table are all the columns in all the argument tables • DISTINCTCOUNT • Counts the number of different cells in a column of numbers • Useful when creating a measure based on a column Highlights SQL Server 2012: BI Semantic Model

  30. New DAX Functions (8) • GENERATE and GENERATEALL • Returns a table with the Cartesian product between each row in Table1 and the table that results from evaluating Table2 in the context of the current row from Table1 • RANK.EQ • Returns the ranking of a number in a list of numbers • RANKX • Returns the ranking of a number in a list of numbers for each row in the table argument • ROW • Returns a table with a single row containing values that result from the expressions given to each column Highlights SQL Server 2012: BI Semantic Model

  31. New DAX Functions (9) • STDEV.S, STDEV.P, STDEVX.S and STDEVX.P • Returns the standard deviation of a sample, or entire, population • SUMMARIZE • Returns a summary table for the requested totals over a set of groups • TOPN • Returns the top N rows of a specified table • VAR.S, VAR.P, VARX.S and VARVX.P • Returns the variance of a sample, or entire, population Highlights SQL Server 2012: BI Semantic Model

  32. Enhanced DAX Function • SEARCH • Enhanced to include an optional string parameter that is returned when a substring is not found Highlights SQL Server 2012: BI Semantic Model

  33. DAX Query • DAX supports a new query syntax to return table data from a query using XMLA • Though tabular, the syntax is different from SQL • Essentially, a DAX table expression is evaluated • Queries can define helper objects such as measures, variables or views as part of the final table expression • Queries can be parameterized • Power View uses DAX Query Power View DAX Query Highlights SQL Server 2012: BI Semantic Model

  34. INSTALLATION AND MANAGEMENT • All Analysis Services instances are installed with the SQL Server Installer Highlights SQL Server 2012: BI Semantic Model

  35. Installation and Management • Object Explorer in SQL Server Management Studio (SSMS) supports managing all three modes Highlights SQL Server 2012: BI Semantic Model

  36. Feature Comparisons Highlights SQL Server 2012: BI Semantic Model

  37. Wettbewerb Let‘sgo. Highlights SQL Server 2012: BI Semantic Model

More Related