Microsoft Business Intelligence Update on SQL Server 2012

1. Microsoft Business IntelligenceUpdate on SQL Server 2012 Bernd Schneider Technical Solution Professional Business Intelligence Microsoft Switzerland

2. Agenda Microsoft and Enterprise Information Management Update on Microsoft Business Intelligence Capabilities

3. Evolution of BI Self-Service BI Traditional BI Provision Dashboards & Scorecards Analysis Reports Data Staging Analysis Cubes Data Warehouse ETL/Data Quality Specialized Tools End Users ? Spreadsheets Existing Data IT Pro LOB Applications Data Marts Files

4. Microsoft Business Intelligence Most Broadly adopted Productivity & Collaboration Tools SharePoint Search & Dashboards SharePoint Scorecards Excel Workbooks PowerPivot Applications Analysis Services Reporting Services Most widely deployed EIM & BI Platform Integration Services Master Data Services Odata Feeds LOB Apps

5. Phases of Enterprise Information Management • Project Barcelona* • Integration Services • Discover Origins & Relationships between artifacts • Market-leading ETL and data integration tool • Discover • Acquire • Easy-start solution for master and reference data management • Knowledge-based Data Cleansing & Matching • Govern • Augment • Data Quality Services • Master Data Services • * Will be shipped separately from SQL Server 2012 and is subject to change

6. Integrated Data Management Scenario Cleanse, match DQS Temp Table Data Sources Acquire SSIS Discover Barcelona Curate MDS Consumer MDS Reference Store Views Match, de-duplicate DQS Publish SSIS

7. Project BarcelonaMetadata Discovery http://projectbarcelona.cloudapp.net/ demo

8. Project Barcelona Platform Architecture DBA/IT Pro Experience Admin UI Information Worker Experience 3rd Party / Vertical Application Metadata Graph Query, Augmentation, and Annotation API Barcelona Index Server Crawler Harvested Data API SQL IS Share Point Excel Other Microsoft 3rd Party Crawler

9. Project Barcelona Key Features • Incremental Investment • As more of the enterprise is crawled, more and more dependencies are uncovered • Open & Extensible • New crawlers, UIs added over time • In-house customized solutions • Opportunity for partners • Crawlers shipped out of band • Minimal Investment • No up front planning, modeling, ongoing maintenance • Out of the box, just starts working

10. Why is Data Quality Important? Data quality problems cost U.S. businesses more than $600 billion a year. Data Warehousing Institute (TDWI) Costs associated with bad data include: • Excess inventory • Higher supply chain costs • higher direct marketing costs • Billing • And more…

11. Common Data Quality Issues

12. How to Manage Data Quality? Data quality management entails the establishment and deployment of: • Roles • Responsibilities • Policies • Procedures • Technology People Technology • Processes

13. Make Data Quality Approachable to Everyone • Improve your data quality with DQS • Cleanse the data and keep it clean • Build confidence in your enterprise data • Share the responsibility for data quality • Remove Barriers for Data Quality • Designed for ease of use • Empowering the business users • See data quality results in minutes rather than months

14. DQS Solution Concepts • Knowledge-Driven • Based on a Data Quality Knowledge Base (DQKB) that is reusable for a variety of data quality improvements • Semantics • Data is mapped into Data Domains, which capture its Semantics • Knowledge Discovery • Acquire additional knowledge through data samples and user feedback • Open and Extendible • Support use of user-generated knowledge and IP by 3rd party reference data providers • Easy to Use • Compelling user experience designed for increased productivity

15. DQS Architecture Overview DQS Cloud Services DQS Clients DQS Store - KB, Domains DataMarket - Categorized Reference Data DQS Client Knowledge Discovery and Management Administration Interactive DQ Projects DQS Server 3rd Party Reference Data Reference Data API (Browse, Set, Validate…) Reference Data API (Browse, Get, Update…) Reference Data Services DQS Engine Cleansing Knowledge Discovery Reference Data Data Profiling Exploration Matching Other DQS Clients SSIS DQS Cleansing Component DQ Projects Store Common Knowledge Store DQ Active Projects Published KBs Future Clients: Excel, SharePoint, MDS…

16. Create DQKB dqs demo flow • Create DQS Project • Knowledge Discovery to create a domain • Manage Domains

17. What is Master Data? • Objects that are the focus of organizational activity • Exists in different forms at each level of the organization • The requirements of a product model can vary between org levels • Different object types are more specific to certain org levels • Characteristics • Relatively slowly changing • Objects of the transactions rather than transactional data • Shared among contributors and consumers

18. Main Scenarios Data Warehouse / Data Marts Mgmt • Operational Data • Management Data Solutions • Enables business users to manage the dimensions and hierarchies of DW / Data Marts • BI scenarios • Provides storage and management of the objects and metadata used as the application knowledge • Object mappings • Reference Data / managed object lists • Metadata management / data dictionary Central data records mgmt and consumption sourced by other operational systems A company has adopted 6 “best of breed” systems from different vendors. They need to be able to propagate the correct customer information to each system in a consistent way. MDS provides a platform for central schema, integration points and validation for SI/ISV/Internal IT to develop a custom solution1 MDS focus Partners Value Add

19. MDS Capabilities ValidationAuthoring business rules to ensure data correctness Modeling Entities, Attributes, Hierarchies • MDS Data Matching Web UI Excel Add-In Role-based Security and Transaction Annotation • Master Data Stewardship Versioning Enabling Integration & Sharing External (CRM, ..) DWH Excel Loading batched data through Staging Tables Registering to changes through APIs Workflow / Notifications Consuming data through Views

20. SQL Server 2012 Focus for MDS • Empowering IWs through Excel Add-in and improved Web UI • Enhanced performance and scalability • Improved quality (usability, robustness, security) Focus on Foundational Platform • V1 product

21. Empowering IW: MDS Excel Add-in • Load filtered set of data from your MDS database, update leveraging the full power of Excel and publish the data back to the database • Create new entities and attributes • Define attribute constrains, validate and correct entries • Create relations between entities with domain based attributes • Create shortcut of the MDS connection and share it easily with others for easy collaboration

22. Create MDS Model • Create subscription views • Create Entity's in XLS mds demo flow • Explore/Update data • Publish data updates in XLS

23. Whats new in SQL Server 2012 Analysis Services and PowerPivot Reporting Services and Power View Master Data Services and Data Quality Services

24. Analysis Services: Tomorrow 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

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

26. How Should I Build my Model? • Depends on the application needs for each layer • Data model • Business logic • Data access & storage • Two Visual Studio (BIDS) project types in SQL Server 2012 • Multidimensional project – with MDX and MOLAP/ROLAP • Tabular project – with DAX and VertiPaq/DirectQuery

27. SSAS: Multidimensional Model Improvements • Multidimensional projects received over 300 improvements across the board for performance, supportability, reliability and functionality • Almost 100 were reported directly by customers • Major new features include: • Visual Studio 2010 designers • Removal of 4GB string store limit for attributes • New events for monitoring lock usage and contentions • New messages for tracking resources used per command • New PowerShell support

28. What’s New in PowerPivot for Excel Add-in • Includes the same designer and the same features available to the IT Pro in the Tabular Project in BIDS, except: • Table partitions • Security roles • Configuring Direct Query mode • Note it is possible to restore a PowerPivot workbook on a tabular instance of Analysis Services and then create and manage table partitions

29. What’s New in PowerPivot for Excel Add-in • Perspectives and the reporting properties are hidden by default, otherwise they are available on the PowerPivot Window’s Advanced ribbon tab • The specialized functionality on the Advanced ribbon tab (which is hidden by default) includes: • Perspectives • Summarize By • Reporting properties

30. What’s New in PowerPivot for Excel Add-in • Excel’s PowerPivot ribbon tab includes the ability to create, edit and delete KPIs • The PowerPivot Field List has been updated to: • Allow perspective selection • Display hierarchies and KPIs • Create a KPI based on a measure • The Measure Settings window, used to create and edit measures, supports the configuration of formatting options

31. What’s New in PowerPivot for Excel Add-in • SharePoint Server 2010 SP1 is a prerequisite • The add-in has update to include: • New administrative capabilities • New setup experience • Power View authoring from the PowerPivot Galley

32. BI Semantic Model demo

33. Whats new in Reporting Services

34. New SSRS SharePoint Integration Benefits • SSRS SharePoint 2010 Shared Service • Hosted in SharePoint Shared Service App pool • SSRS catalog DBs are SharePoint Service App DBs • WCF and Claims based communication • Central Admin UI for all RS administration • ULS Logging integration • Built-in scale-out and load balancer • Report Performance Improvements • Parity with SQL Server 2012 Native mode performance • Top SharePoint mode pain point • Small reports used to be 2-3 times slower: Fixed • Faster than 2008 R2 SharePoint mode ~ 30-60% • SQL Setup option for SSRS SharePoint service

35. Data Alert Workflow Create Alert Email Delivery Alert Management

36. Office Open XML Rendering Extensions • Support for new file formats introduced in Office 2007 • Word rendering to *.docx format • Excel rendering to *.xlsxformat • Feature parity with existing Word and Excel renderers • ZIP-compressed files • Larger worksheets in Excel • 65k -> 1M rows • 256 -> 16k columns • More colors in Excel • 56-color palette -> 24-bit color (16M colors)

37. Power View is an interactive data exploration and visual presentation experience. What is Power View

38. SQL Server 2012 Power View 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 • 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

39. Power View Architecture SharePoint Farm SQL Server BIDS BISM Model App Server Web Front End RS Shared Service SSRS Addin for SharePoint Power View client AS ServerTabular PowerPivot System Service PowerPivot Web Service Analysis Services SP Integrated Excel PowerPivot Model Data sources

40. Power View demo

41. © 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.