Introducing SQL Server 2012 Improvements

1. Introducing SQL Server 2012 Improvements

2. MODULE OUTLINE Introduction New Improvements and Features

3. INTRODUCTION

4. INTRODUCTION SQL SERVERS NEW STORAGE MODEL

6. BUSINESS intelligence highlights

7. The Vision IMPROVED PRODUCTIVITY FOR DEVELOPERS (CONTINUED)

8. Introducing SQL Server 2012 Integration Services Improvements SQL11UPD05-DECK-01

9. INTRODUCTION SQL SERVER ENTERPRISE INFORMATION MANAGEMENT Integration Services Master Data Services Data Quality Services • Primarily designed to implement ETL processes • Provides a robust, flexible, fast, scalable and extensible architecture • Knowledge-driven data cleansing • Corrects, de-duplicates and standardizes data • Integrates with Integration Services • Master data management • Manages reliable, centralized data • Broadens its reach with a new Excel Add-in that can leverage Data Quality Services Complete, Current, Consistent and Clean Data

10. New Improvements and Features IMPROVED USABILITY FOR NEW DEVELOPERS • “Getting Started” window with links to samples and videos • New SSIS Toolbox: • Exposes a description and links to samples for a selected component • Allows categorizing, including Favorites • Data flow connection assistants to facilitate the configuration of sources and destinations

11. New Improvements and Features IMPROVED PRODUCTIVITY FOR DEVELOPERS • Undo/Redo • Data flow Scriptcomponent debugging • SQL Server Data Tools (SSDT) designer • Updated look and feel • New VSTA scripting environment with support for .NET 4 • Auto-save and recovery • Improved performance when opening packages • Project connection managers • Icon marker to indicate when expressions are used

12. New Improvements and Features IMPROVED DEPLOYMENT, CONFIGURATION AND MANAGEMENT • SSIS catalog: • User database (SSISDB) hosted on a SQL relational instance with a collection of views and stored procedures that provide a T-SQL API • New project model for bundling project resources together to simplify deployment (.ispac) • New parameter model to simplify configuration management • Similar to parameters in programming functions • Read-only variable in a special namespace

13. New Improvements and Features IMPROVED DEPLOYMENT, CONFIGURATION AND MANAGEMENT (CONTINUED) • Available with the SSIS catalog: • Connection manager properties are automatically exposed on the server • Environments can be created to run packages with different settings • Automatic capture of package execution logs • Data tap functionality, to dynamically capture data as it flows through the data flow, and without modifying the package • Server can be managed using PowerShell

14. New Improvements and Features IMPROVED DEPLOYMENT, CONFIGURATION AND MANAGEMENT (CONTINUED) • Available with the SSIS catalog (continued): • Built-in reports: • Integration Services Dashboard – view all packages that have run on the server in the past 24 hours • Performance Reports – view a package’s performance over time, at package and component level • Error Message Report – details failed package executions and related error messages • Support for custom reports

15. DEVELOPER OPPORTUNITIES • Manage the SSIS catalog by using the public T-SQL API, or the .NET interface: • Develop management tools and solutions • Develop custom monitoring and dashboard reports • Embed functionality into solutions (e.g. package execution) • Automate project deployment as part of an installer package • Develop dynamic packages or SSIS extensions by using the SSIS object model: • Custom tasks • Custom data flow components (Transformations, Sources, Destinations) • Data source connectors • ForEach enumerators

16. Introducing SQL Server 2012 Master Data Services Improvements SQL11UPD05-DECK-02

17. INTRODUCTION SQL SERVER ENTERPRISE INFORMATION MANAGEMENT Integration Services Master Data Services Data Quality Services • Primarily designed to implement ETL processes • Provides a robust, flexible, fast, scalable and extensible architecture • Knowledge-driven data cleansing • Corrects, de-duplicates and standardizes data • Integrates with Integration Services • Master data management • Manages reliable, centralized data • Broadens its reach with a new Excel Add-in that can leverage Data Quality Services Complete, Current, Consistent and Clean Data

18. NEW IMPROVEMENTS AND FEATURES IMPROVED MASTER DATA MANAGER

19. NEW IMPROVEMENTS AND FEATURES IMPROVED MASTER DATA MANAGER (CONTINUED)

20. NEW IMPROVEMENTS AND FEATURES NEW MICROSOFT EXCEL ADD-IN (CONTINUED)

21. DEVELOPER OPPORTUNITIES • For the Systems Integrator: • Leverage MDS to connect disparate information systems that share common master data • For the ISV: • By integrating with MDS, customers who deploy your line-of-business solution can incorporate it into their own enterprise-wide master data management solution • This ensures that your solution is not an island and stays consistent and compliant with master data that is shared with other applications running in the customers environment • Anything that MDS can do can be embedded into, or automated by, your solutions by using the WCF API. For example: • Create, read, update, and delete metadata • Create, read, update, and delete entity members

22. Introducing SQL Server 2012 Data Quality Services SQL11UPD05-DECK-03

23. INTRODUCTION SQL SERVER ENTERPRISE INFORMATION MANAGEMENT Integration Services Master Data Services Data Quality Services • Primarily designed to implement ETL processes • Provides a robust, flexible, fast, scalable and extensible architecture • Knowledge-driven data cleansing • Corrects, de-duplicates and standardizes data • Integrates with Integration Services • Master data management • Manages reliable, centralized data • Broadens its reach with a new Excel Add-in that can leverage Data Quality Services Complete, Current, Consistent and Clean Data

24. INTRODUCTION BUSINESS SCENARIOS

25. INTRODUCTION DQS PROCESS Cloud Services Knowledge Management Build Discover / Explore Data / Connect Knowledge Manage Reference Data Enterprise Data Integrated Profiling Status Progress Notifications Use Correct & Standardize Match & De-duplicate Data Quality Projects

26. DQS COMPONENTS DATA QUALITY CLIENT

27. Knowledge BaseS Rationale: To cleanse data you need knowledge about it The Knowledge Base is a data repository of knowledge that enables professionals to understand their data and maintain its integrity Knowledge in a Knowledge Base is maintained in Domains, each of which is specific to a data field

28. KNOWLEDGE BASES (CONTINUED) • Domains capture the semantics of the data • Domains can use online reference data • Online DataMarketReference Data Service • Direct Online 3rd Party Reference Data Services • Processes include: • Domain Management – to define domains • Knowledge Discovery – to learn domain values • Matching Policy – to identify potential duplicates and non-matches

29. KNOWLEDGE BASES (CONTINUED) • Composite Domains • Values • Knowledge Base • Matching Policy • Domains • Represent the data type • Domains • Rules & Relations Reference Data

30. DATA QUALITY PROJECT • A Data Quality Project is a means of using a Knowledge Base to improve the quality of source data by performing data cleansing and data matching activities • Created and managed in the Data Quality Client • Results can be exported to a SQL Server table or CSV file • Two types: • Cleansing Activity – processed data is categorized as new, invalid, corrected, and correct • Matching Activity – used to prevent data duplication by identifying exact and approximate matches

31. DQS CLEANSING TRANSFORM • Implements data cleansing in an SSIS 2012 data flow • Configuration involves: • Defining a connection to a Data Quality Server • Selecting a knowledge base • Mapping input columns to domains • Selecting advanced statistical columns • The output includes the original data and corrected data, together with status

32. Introducing SQL Server 2012 Analysis Services Improvements SQL11UPD06-DECK-01

33. introduction Tabular model development is explored in SQL11UPD06-DECK-02 • 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

34. 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 One Model for All End User Experiences Analysis Services PowerPivot for SharePoint PowerPivot for Excel Data Sources Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files

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

36. BI SEMANTIC MODEL DELIVERABLES Richness Scalability Flexibility • Tabular and multidimensional modeling experiences • DAX and MDX for business logic and queries • Cached and passthrough storage modes • Choice of end-user BI tools • Rich data modeling capabilities • Sophisticated business logic using DAX and MDX • Fine-grained security – row and cell level • Enterprise capabilities – multi-language and perspectives • In-Memory for high performance, MOLAP for mission critical scale • DirectQuery and ROLAP for passthrough access to data sources • State-of-the-art compression algorithms • Scales to the largest ofenterprise servers

37. CHOOSING THE RIGHT DEVELOPMENT APPROACH The model developer needs to choose the right development approach

38. CHOOSING THE RIGHT DEVELOPMENT APPROACH DATA MODEL 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 Tabular Multidimensional

39. CHOOSING THE RIGHT DEVELOPMENT APPROACH BUSINESS LOGIC 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) DAX MDX

40. CHOOSING THE RIGHT DEVELOPMENT APPROACH DATA ACCESS AND STORAGE

41. Exploring SQL Server 2012 Power View SQL11UPD06-DECK-04

42. INTRODUCTION • Power View is an interactive data exploration, visualization, and presentation experience • Highly visual design experience • Rich meta-driven interactivity • Presentation-ready at all times • Provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers • Ordinarily, a Power View report needs to be based on a tabular BI Semantic Model that has been optimized for the report authoring tool

43. POWER VIEW REPORTING AUDIENCE AND AUTHORING TOOLS

44. POWER VIEW EXAMPLE REPORT

45. Tabular BI Semantic Model Optimization Ordinarily, the tabular BI Semantic Model needs to be optimized for the Power View experience This is required to exploit the unique capabilities of the report authoring tool by supplying hints and directives Note: Optimizing a model for Power View may de-optimize it for OLAP clients

46. Tabular BI Semantic Model Optimization (CONTINUED) • The following model resources are not available in the Power View Field List: • Hidden tables, columns and measures • Hierarchies • Implicit measures (defined in the PowerPivot Field List) • Key Performance Indicators (KPIs) • Only the default perspective can be used

47. Tabular BI Semantic Model Optimization (CONTINUED) • The model can be optimized by: • Providing friendly names for tables, columns and measures • Hiding unnecessary tables, columns and measures • Setting appropriate formats for columns and measures • Providing descriptions for tables, columns and measures • These are surfaced as tooltips in the Field List • Adding columns that contain images (binary data) • Images can also be referenced by their URL • There may not be the need to define measures