microsoft business intelligence update on sql server 2012 l.
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft Business Intelligence Update on SQL Server 2012 PowerPoint Presentation
Download Presentation
Microsoft Business Intelligence Update on SQL Server 2012

Loading in 2 Seconds...

play fullscreen
1 / 41

Microsoft Business Intelligence Update on SQL Server 2012 - PowerPoint PPT Presentation

  • Uploaded on

Microsoft Business Intelligence Update on SQL Server 2012. Bernd Schneider Technical Solution Professional Business Intelligence Microsoft Switzerland. Agenda. Microsoft and Enterprise Information Management. Update on Microsoft Business Intelligence Capabilities. Evolution of BI.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Microsoft Business Intelligence Update on SQL Server 2012

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft business intelligence update on sql server 2012

Microsoft Business IntelligenceUpdate on SQL Server 2012

Bernd Schneider

Technical Solution Professional Business Intelligence

Microsoft Switzerland


Microsoft and Enterprise Information Management

Update on Microsoft Business Intelligence Capabilities

evolution of bi
Evolution of BI

Self-Service BI

Traditional BI


Dashboards & Scorecards







Data Warehouse

ETL/Data Quality

Specialized Tools

End Users



Existing Data

IT Pro

LOB Applications

Data Marts


microsoft business intelligence
Microsoft Business Intelligence

Most Broadly adopted Productivity & Collaboration Tools


Search & Dashboards





PowerPivot Applications





Most widely deployed EIM & BI Platform



Master Data




LOB Apps

phases of enterprise information management
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
integrated data management scenario
Integrated Data Management Scenario

Cleanse, match


Temp Table

Data Sources








MDS Reference Store


Match, de-duplicate




project barcelona metadata discovery

Project BarcelonaMetadata Discovery


project barcelona platform architecture
Project Barcelona Platform Architecture





Information Worker Experience

3rd Party / Vertical Application

Metadata Graph Query, Augmentation, and Annotation API

Barcelona Index Server

Crawler Harvested Data API






Other Microsoft

3rd Party Crawler

project barcelona key features
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
why is data quality important
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…
how to manage data quality
How to Manage Data Quality?

Data quality management entails the establishment and deployment of:

  • Roles
  • Responsibilities
  • Policies
  • Procedures
  • Technology



  • Processes
make data quality approachable to everyone
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
dqs solution concepts
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
dqs architecture overview
DQS Architecture Overview

DQS Cloud Services

DQS Clients

DQS Store - KB, Domains

DataMarket - Categorized Reference Data

DQS Client

Knowledge Discovery and Management


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


Knowledge Discovery

Reference Data

Data Profiling Exploration


Other DQS Clients

SSIS DQS Cleansing Component

DQ Projects Store

Common Knowledge Store

DQ Active Projects

Published KBs

Future Clients: Excel, SharePoint,



Create DQKB

dqs demo flow

  • Create DQS Project
  • Knowledge Discovery to create a domain
  • Manage Domains
what is master data
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

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

mds capabilities
MDS Capabilities

ValidationAuthoring business rules to ensure data correctness


Entities, Attributes, Hierarchies

  • MDS

Data Matching

Web UI

Excel Add-In

Role-based Security and Transaction Annotation

  • Master Data Stewardship


Enabling Integration & Sharing


(CRM, ..)



Loading batched data through Staging Tables

Registering to changes through APIs

Workflow / Notifications

Consuming data through Views


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
empowering iw mds excel add in
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

Create MDS Model

  • Create subscription views
  • Create Entity's in XLS

mds demo flow

  • Explore/Update data
  • Publish data updates in XLS
whats new in sql server 2012
Whats new in SQL Server 2012

Analysis Services and PowerPivot

Reporting Services and Power View

Master Data Services and Data Quality Services

analysis services tomorrow
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


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

bi semantic model architecture
BI Semantic Model: Architecture

Reporting Services & Power View







BI Semantic Model



Data model


Business logic

and queries





Data access





LOB Applications


OData Feeds

Cloud Services

how should i build my model
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
ssas multidimensional model improvements
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
what s new in powerpivot for excel add in
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
what s new in powerpivot for excel add in29
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
what s new in powerpivot for excel add in30
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
what s new in powerpivot for excel add in31
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
new ssrs sharepoint integration benefits
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
data alert workflow
Data Alert Workflow

Create Alert

Email Delivery

Alert Management

office open xml rendering extensions
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)
sql server 2012 power view
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
power view architecture
Power View Architecture

SharePoint Farm


BISM Model

App Server

Web Front End

RS Shared Service

SSRS Addin for SharePoint

Power View client

AS ServerTabular


System Service


Web Service

Analysis Services SP Integrated

Excel PowerPivot


Data sources


© 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.