What's New with BI in SQL Server Denali (SQL11)
0 likes2,139 views
Project "Crescent" is a new browser-based Silverlight reporting tool that will replace Report Builder 1.0. It will provide a highly interactive experience for exploring and visualizing data in Tabular BI Semantic Models created with PowerPivot for SharePoint and Analysis Services Tabular projects. Other enhancements include improvements to Integration Services, Master Data Services, new features like Data Quality Services and columnstore indexes, and changes to make Reporting Services integrate better with SharePoint.
More Related Content
What's hot (18)
Similar to What's New with BI in SQL Server Denali (SQL11) (20)
More from Dan English (12)
What's New with BI in SQL Server Denali (SQL11)
- 1. • − • − −
- 2. • − − − − • − − − − − − −
- 3. RAPID DATA CREDIBLE, SCALABLE ANALYTICS EXPLORATION & CONSISTENT DATA & DW SOLUTIONS VISUALIZATION Familiar data exploration and Consistent view of your data Engine tuning for high-scale visualization with Project through a single analytics in “Crescent” BI Semantic Model Analysis Services Inspire innovation with Ease of integration and Enhanced capabilities for high- powerful data mash-ups in management with scale data warehousing with PowerPivot Integration Services and Parallel Data Warehouse Master Data Services Greater IT oversight and Comprehensive offerings for management through Quickly manage, analyze, and Data Warehouses of all sizes SharePoint cleanse data across sources in Data Quality Services
- 4. Bringing Together… Richness Simplicity Scalability PowerPivot + Analysis Services
- 5. • • •
- 6. Build on the strengths Embrace the relational and success of Analysis data model – well Services and expand its understood by reach to a much developers and IT Pros broader user base Bring together the relational and Provide flexibility in the multidimensional platform to suit the models under a single diverse needs of BI unified BI platform – applications best of both worlds!
- 7. Seamless Transition Empowered Aligned My Context Our Context The Org’s Context BI solution created BI Solution created by BI Solution created by by user. Context is power user. Context IT, Established only for user & exists is for a small team & corporate context & is as document. it’s managed on a reusable, scalable and server. backed up. PowerPivot For Excel PowerPivot For SharePoint Analysis Services
- 8. Existing Existing New applications applications applications Based on Unified Every UDM becomes a BI New technology options Dimensional Model Semantic Model “Denali”
- 9. PowerPivot for Excel BI Development Studio Feels like Visual Feels like Studio Optimized One file, Excel for BI Pros Save to It’s a project Rapid SharePoint (business case, response to budget, dates) Larger data business volumes problems Teams building BI Solutions live solutions Deployment for weeks or Optimized for scripts, months Excel power user Source Control, versions TFS Personal Team Corporate
- 11. Third-party Reporting SharePoint applications Services Excel PowerPivot Insights Databases LOB Applications Files OData Feeds Cloud Services
- 12. Personal BI Team BI Organizational BI PowerPivot for Excel PowerPivot for Analysis Services SharePoint Flexibility Richness Scalability
- 13. Internet Explorer SharePoint BI Development Studio Project Juneau Excel Services Reporting Services PowerPiv xlsx ot for Excel Analysis Services PowerPivot for SharePoint (Analysis Services) Excel BI Semantic Model xlsx Third Party Apps Personal BI Team BI Organizational BI
- 14. information Master Data Services Data Quality Services Data quality Profiling Familiar tools Cleansing Performance Matching Integration Services Impact Analysis Easy data loading Lineage tracking with data sources
- 15. 15 • • 11001010 01010010 10011101 • 01100101 001 • • •
- 16. Row store: … C1 C2 C3 C4 C5 C6 Column store: Pages Uses VertiPaq compression
- 17. • SELECT region, sum (sales) … C2 C3 • C1 C4 C5 C6 • •
- 19. Improved Deployment, Configuration and Enhanced Management Usability UI Improvements Improved deployment, Helping new users ramp up configuration and Increasing ETL developers management of SSIS projects productivity SSIS Server Shared connection managers Support for parameters Ability to use SSMS for Key Customer Requests troubleshooting of common SSIS Undo issues Flexible order of authoring Easy to diff package format
- 20. Enhanced Usability • Visual Studio 2010 Shell • Design surface changes – rounded corners! • Improved annotations • Undo/Redo!!!! • SSIS Toolbox • New icons • Expression notations on SSIS items • New – Expression Task • Variable Scope Change (and ability to move) • Copy & Paste improvements • Zoom In/Out feature • Auto save and recovery • Package and Project Level Parameters! • Project Level Connection Managers! • DFT provides grouping functionality • New – DQS Cleansing Transform • New – Resolve References window (DFT) from PDW • DFT – Source and Destination Assistants • And even more!
- 21. Improved Deployment, Configuration and Management • SSIS Server, not just a service (legacy) • SSIS Catalog DB – SSISDB • Project Deployment Model (.ispac) • Easier Deployment • Easier Configurations • Provides Logging & Reporting • Can reference and execute SSIS from T-SQL! • Utilize Environments with projects • Built-in Reporting
- 22. Data Quality Services (DQS)
- 23. • • •
- 24. Data Quality Issue Example Standardization Is the data consistent between System 1: Female, Male, systems? Unknown System 2: F, M, U System 3: 0, 1, 2 Formatting Is there typos, abbreviations, or Street, St., ST punctuation issues? Avenue, Ave., Ave Apt No, Apt #, Apt Num Valid values Are values within acceptable Dates between 1/1/2000 and ranges (sanity checks)? 12/31/2012 Sales between $1 and $100,000 Completeness Is all of the data available that is Blanks, NULLs, incomplete needed? addresses, invalid email addresses, values of all 9’s Duplicates Does data appear more than once? Dan English, Daniel English, Mr. English, D English, Danny E.
- 25. Knowledge Management Build Discover / Explore Data / Connect Integrated Knowledge Profiling Base Use DQ Projects
- 26. SSIS Data Flow Reference Data Services DQS Server SSIS Package Source + DQS Cleansing Values/Rules Mapping Component Destination Reference Data Definition Microsoft Confidential—Preliminary Information Subject to Change
- 28. Empower users Increase Productivity Increase efficiency Proactive Intelligence • Project “Crescent” • End User Alerting • Enabled as SharePoint Shared Service • Highly visual design • Defined from within experience operational or • Built-in scale-out for RS ad-hoc reports Service Apps • Rich metadata-driven interactivity • Intuitive Alert rules • SharePoint Cross-farm reporting • Presentation-ready • Alerts self-managed at all times through SharePoint • Integrated backup & recovery, ULS logging, • XLS/Word 2007/2010 PowerShell etc. Managed Self Service BI – Corporate BI
- 29. • − − − − − − − • − − − − − •
- 31. Crescent is an interactive data exploration and visual presentation experience.
- 32. Highly Visual Design Experience • Interactive, web-based authoring, and sharing of information • Familiar Microsoft Office design patterns • Powerful data layout with banding, callout, and small multiples visualizations Rich metadata-driven interactivity • Fully integrated with PowerPivot and Tabular SSAS • Drive greater insight through smart and powerful querying • Zero configuration highlighting and filtering • Animated trending and comparisons Presentation-ready at all times • Interactive Presentation turns pervasive information into persuasive information • Deliver and collaborate through SharePoint • Full screen presentation mode for interactive boardroom session
- 35. 10. Integrations Services (SSIS) Enhancements – lots of usability enhancements have been made such as ability to undo and redo, a new SSIS Toolbox, grouping capabilities in the data flow, simplified data viewer configurations, new data flow source and destination assistants (wizards), and improved data flow designer experience with mapping and resolving errors. Other modifications were made so it is easier to compare and merge packages, and you have the ability to view object dependencies in SQL Server Management Studio. 9. Master Data Services (MDS) Enhancements – improvements in the user interface (UI), performance improvements with the UI, ability to incorporate data quality services into the workflow process, and a new Excel add-in client that will provide the ability to edit Master Data and publish it back to MDS and even create a model in Excel. 8. New Feature: Data Quality Services (DQS) – this is a new product offering that has been added that allows you to create a knowledge base repository of data related items that can then be used to incorporate and use for data cleansing. This will allow you to profile, improve the quality, and monitor the on- going health of your data. This new feature can be added into your Integration Services and Master Data Services processes. 7. New Feature: Database Columnstore Indexes – the VertiPaq engine first introduced with PowerPivot has now been brought to the database engine and is available to use to create indexes on columns in the relational store. This will provide a wonderful opportunity for users leveraging ROLAP technology to great improve the performance on the query processing. This index groups and stores the data for each column versus the row. This can alleviate having to use pre-aggregated data and summary tables. 6. New Feature: Reporting Services (SSRS) Shared SharePoint Service – the SSRS integration with SharePoint will now be setup as a Shared SharePoint service just like Excel Services and PerformancePoint Services. Everything will be managed completely through SharePoint and there will no longer be a separate SSRS Windows Service running. 5. New Feature: Reporting Services (SSRS) Data Alerts – new self-service data-driven alerting capabilities have been added to allow end-users the ability to setup and define alerts using existing Reporting Services reports. These alerts can be setup based on defined criteria and scheduled as needed to provide a mechanism to notify the user without them having to manually execute the report on their own. This new feature is only provided in the SharePoint Integrated mode of Reporting Services. 4. PowerPivot Enhancements – with version 2 of PowerPivot there have been some major additions which include features like a new diagram view of the model, hierarchy and KPI support, ability to sort a column based on another column, multiple relationships (like role playing dimensions kind of – can access through DAX), image support, add values to rows and columns (very cool – flexibility provided in the PivotTables), reporting properties (will be leveraged by ‘Crescent’), ability to format measures, field list sorted alphabetically (instead of how they were added to the model), and most importantly…show details (drillthrough support – this will be fabulous when using with PerformancePoint). 3. DAX Enhancements – new functions have been added to help simplify and provide additional capabilities like DISTINCTCOUNT, Rank, Standard Deviation, TOPN, table functions, LOOKUPVALUE, parent-child relationship functions, SWITCH, and more. 2. New Feature: Analysis Services Tabular – the PowerPivot experience in Excel has also been added into the Business Intelligence Development Studio (BIDS) introducing a new Tabular project option. This will allow you to take the PowerPivot model and convert it to a Tabular project and deploy the solution at the server level for a corporate/enterprise level solution that will scale and handle large volumes of data. With the Tabular models you will have the same options as in PowerPivot along with additional capabilities like partitioning, incremental updates, and role based security. 1. New Feature: Project ’Crescent’ – this is a brand new browser based Silverlight report authoring tool. This new tool will replace the Report Builder 1.0 application that leveraged Report Models and will be able to leverage the Tabular BI Semantic Models (BISM) that you will be able to create with PowerPivot for SharePoint and Analysis Services Tabular projects. This will be a highly interactive reporting experience that will allow you to access, explore, and visualize your data and have fun doing so in the process. This tool will enhance the existing Microsoft BI toolset and provide a very friendly self-service reporting solution for end-users to leverage. The new feature is only provided in the SharePoint Integrated mode of Reporting Services.
- 36. High Scalability & Security & Beyond Web & Business EIM Availability Performance Manageability Relational Breadth Intelligence SQL Server Up to 15K User-Defined Microsoft PHP Driver Unified Semantic SSIS Server AlwaysOn Partitions/Table Server Roles Project Model “Juneau” Reliable, Column Store Contained Win32 Access Local DB Crescent Data Quality Integrated Index, “Apollo” Database to Database files Runtime Failover Detection Authentication Application Database FileTable UTF-16 In-memory BI for Enhanced MDS Fast Centric Failover Replay corporate FILESTEAM Multiple Fast Full-Text Audit Semantic Paging for Alerting MDS add-in for Secondaries Search Enhancements Search Platform result sets Excel Readable Fast Spatial Management Full Globe JDBC 4.0 driver Sysprep for AS Secondaries Performance Pack for High Spatial Support Availability Online DBC & OLTP Backup DAC Support for PowerPivot Operations Appliances/RAs Secondaries Enhancements ARM Enhancements processors HA for Default Schema ODBC for Linux Reporting as StreamInsight for Windows SharePoint Group Shared Service Windows Server Active Directory Hybrid Core Support w/ SharePoint Applications for SSRS with SQL Azure
- 37. Questions?