Saturday, March 10, 2012

What is new in SQL Server 2012 Analysis Services?



Highlights:
  1. The new release is based on the vision to reach a broader user base
  2. SSAS brings together the existing  multi-dimensional model with the new tabular model pioneered by PowerPivot under a single unified platform – the BI Semantic Model
  3. Data Mining Component remains the same
  4. The BI Semantic Model promises one model for all end user experiences
    1. End Users/Personal BI – PowerPivot for Excel
    2. Team BI – PowerPivot for SharePoint
    3. Coporate BI – Analysis Services
  5. What is The BI Semantic Model? It is just another term for a Data Model hosted within Analysis Services
  6. There are three approaches in developing a BI Semantic Model
    1. PowerPivot Workbook
      1. Design Type: Tabular
      2. Tool: Excel 2010
      3. Biz Logic: DAX
      4. Data Access-Cache Passthrough: In Memory
      5. Deployment: SharePoint Library/Analysis Services/PowerPivot
    2. Tabular Project
      1. Think of this as PowerPivot for the IT Pro. End user hands off the PowerPivot workbook to IT department.
      2. Design Type: Tabular
      3. Tool: SQL Server Data Tool
      4. Biz Logic: DAX
      5. Data Access-Cache Passthrough: In Memory Direct Query
      6. Deployment: Analysis Services Tabular Mode
    3. Multidimensional Project
      1. Design Type: Multidimensional
      2. Deployment: SQL Server Data Tools
      3. Biz Logic: MDX
      4. Data Access-Cache Passthrough: MOLAP/ROLAP
      5. Deployment: Analysis Services Multidimenional
    4. Front End Clients
      1. PowerView (uses DAX to query the BI Semantic Model)
      2. 3rd party apps
      3. Reporting Services
      4. Excel
      5. PowerPivot
      6. SharePoint Insights (PerformancePoint Services)
    5. Note that Tabular Models understand both DAX and MDX
    6. DAX is new SQL Server 2012 and only works against tabular models
    7. The advantage of a tabular model is that we can now work with:
      1. Files
      2. OData Feeds
      3. Cloud Services
      4. Relational Databases etc
    8. BI Sematic Model supports Fine-grained security row and cell level
    9. The following templates are available through SQL Server Data tools:
      1. Analysis Services Multidimensional and Data Mining Project
      2. Import from Server (Multidimensional and Data Mining)
      3. Analysis Services Tabular Project
      4. Import from PowerPivot
      5. Import from Server (Tabular)
    10. Multidimensional Model New Features
      1. 300+ improvements
      2. New Developer tool: SQL Server Data tools (Visual Studio 2010)
      3. Removal of 4G string limit for attributes
      4. New events for monitoring locks and attributes
      5. Support for XEvents for low overhead eventing support
      6. PowerShell support
    11. Installation and Configuration
      1. All Analysis Services instances are installed with SQL Server Installer
        1. Server modes: Multidimensional and Tabular – both can be installed on the same server
      2. The PowerPivot Configuration tool is used to configure/repair a PowerPivot instance
    12. New PowerShell cmdlets for AMO for command-line connectivity, navigation and discovery of SSAS databases
    13. Existing apps – every  UDM  becomes (can be upgraded to) a BI Semantic Model
The Below Presentation is part of SQL Server 2012 Training Kit: SQL11UPD06-DECK-01