Transcript [email protected] http://www.linkedin.com/in/peterjsmyers [email protected] http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B208 Unapproved data sources Bad formulas and wrong results Insecure devices and storage locations Proliferation of unmanaged business-critical solutions Fast and dynamic access to reports and analysis where and when you need it. High.

[email protected]
http://www.linkedin.com/in/peterjsmyers
[email protected]
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B208
Unapproved
data sources
Bad formulas
and wrong
results
Insecure
devices and
storage
locations
Proliferation of
unmanaged
business-critical
solutions
Fast and dynamic
access to reports and
analysis where and
when you need it.
High adoption
rate across
organizational
functions and
business units.
Familiar tools,
productive
collaboration, and
open lines of
communication.
IT department in
the role of a
business enabler
rather than a
dependency.
Executive
Strategic
• Vision and Leadership
Management
BI Governance
Relies on a steering committee
for forming focused working groups,
which consist of subject matter experts from
different areas of the organization.
BI Governance
Tactical
• Principles, Policies, Standards,
and Procedures
(steering committees and working groups)
User Base
Operational
• Awareness and Execution
Tighter Control
Enterprise Data Warehouse
EDW
Controlled
• Tightly governed
• Push Content
• PerformancePoint
• Reporting Services
Short Lived
• Collaboration
• Report Builder
Ad Hoc
• Loosely governed
• Push/Pull content
• PowerPivot
• Power View
Business Unit
Dashboards and
balanced Scorecards
Team BI and Collaboration Sites
Project Sites
File Shares
My Sites - Personal BI
Desktop and Mobile Devices
Permanent
• Dashboards
• Business Intelligence
• Business Process Management
• Operational Reports
Permanent
• Knowledge Management
• Information Sharing
Permanent
• Personal Information
• Public/Private Views
Servers
• Analysis Services
• Reporting Services
• SharePoint Server
• Third-party systems
BI Pros
Power Users
Decision Makers
Clients
• Excel and Add-ins
• Internet Explorer
• Custom Apps
Permanent
• BISM
• SQL Server Data Tools
• Report Builder
• Dashboard Designer
More Specialized
Ad Hoc
• PowerPivot
• Power View
• Data Mining Add-Ins
Permanent
• Operational Reports
• Dashboards
• Key Performance Indicators
Business Analysts
Ad Hoc
• PowerPivot
• Power View
Information Workers
SSBI
Corporate Business Intelligence
• Addresses the guiding policies, principles,
decision-making processes and oversight procedures
to ensure the relevance and accuracy of
the organization’s BI solutions.
Tighter Scope
BI Governance
Data Governance
Information Technology
• Revolves around the guiding policies,
principles, decision-making processes and
accountability of IT in the best interest
of the organization.
Self-Service Business Intelligence
• Addresses the guiding policies, principles,
decision-making processes and oversight procedures
to help business users create, share, and use relevant
and accurate self-service BI BI solutions.
Data Handling
• Create policies for data quality,
data management, process management,
and risk management regarding the
handling of data in the organization.
IT Governance
Corporate Governance
Enterprise
• Defines the goals for which
the organization is governed.
SQL Server Native Client
Microsoft ODBC Provider for Hive
Third-Party Providers
Analysis Services OLE DB Provider
ADOMD.NET
Analysis Management Objects
SQL Server Native Client
OLE DB for ODBC
Third-Party Providers
ADO.NET
Data Feed Client
Microsoft.ACE.OLEDB
Linked Excel Tables
Big
Data
Multidimensional
Sources
Big and Cloud
• Parallel Data Warehouse, Teradata
• Windows Azure SQL Database
• Apache Hadoop/Hive
More Complex
BISM
• Multidimensional and tabular Analysis Services databases.
• PowerPivot data models uploaded to a BI-enabled farm.
Relational Database Management Systems
Web Content
Plain-text data files and desktop databases.
RDMS
• SQL Server
• Oracle, Sybase, Informix, IBM DB2
• Others
Web-based
• Windows Azure Marketplace DataMarket
• OData, REST and JSON
• SharePoint lists and document libraries
• Cut and paste from Web pages
Desktop
• Microsoft Access databases
• Excel spreadsheets
• Comma-separated values (CSV) files
Cloud
SkyDrive Pro and Office 365
More Distributed
• Sync workbooks and other files across devices.
• SkyDrive Pro is part of both Office 365 and SharePoint Server.
• Office 365 enables browser interactivity for
workbooks and Power View sheets.
Email
Consumerization of IT
With the blurring of the lines between
personal and work devices, applications,
and services, the risk of losing track of
what type of data goes where
can lead to serious security issues.
SharePoint Sites
Network Shares
Files on local devices
Exchange Server 2013
• Configure Search to crawl all discoverable content in Exchange.
• Create an eDiscovery Center in SharePoint
SharePoint Server 2013
• SharePoint eDiscovery Center
• PowerPivot Management Reporting
• Approval workflows
• ACM Integration
Audit and Control Management Server
• Non-intrusive auditing
• Discover, assess and categorize
spreadsheets and databases based on
relevance, materiality and business impact
Office Professional Plus 2013
• Analyze and document spreadsheets
• Identify and categorize
spreadsheet differences.
Client Tier
Presentation Tier
SharePoint Server
Data Tier
Information Tier
SharePoint
Insights
Search
Power View
PowerPivot
Excel
Reports
Third-Party
PowerPivot Add-In
PerformancePoint Services
Visio Services
Excel Services
Reporting Services
Power View
Audit and Control Management Server
Self-Service BI Governance
Corporate BI and Data Governance
SQL Server
Analysis Services
SQL Server
Integration Services
Deployed
BI Semantic
Models
SQL Server
ReportingServices
SQL Server
Master Data Services
SQL Server
Data Warehouse
Relational
Databases
Business
Applications
Files
OData Feeds
Cloud Services
Native ECS
Features
Core BI
Features
Power View
1
Deploy your SharePoint 2013 farms and
enable shared services, such as Excel Services and
PerformancePoint Services.
2
Install Analysis Services in SharePoint mode and
register the server in Excel Services
to enable the core PowerPivot functionality.
3
Install Reporting Services add-in and
Reporting Services in SharePoint-integrated mode
to unlock stunning browser-based data exploration,
visualization, and presentation experiences.
Security in a Self-Service BI environment
Self-Service BI relies on the authentication
subsystem and authorization model provided by
SharePoint sites and libraries.
SQL Server Analysis Services
Reporting Services Power View
PerformancePoint Services
Excel Services
Visio Services
SharePoint 2013 Enterprise
SQL Server Database Engine
SharePoint
Role
Description
Visitor
Has Read Privileges
Member
Has Read & Contribute Privileges
Approver
Has Approver Privileges
Owner
Has Administrator Privileges
Code
Description
P
Publish
A
Approve/Reject
C
Create
R
Read
U
Update
D
Delete
X
E
Access Denied
Execute
Author a
dashborard or
report
Power
Power Users
Users
Unit Test
Deployment to
PreProd
UAT
Publish to prod
SharePoint List
as Major Version
Start approval
process
Modify an
existing
dashborard or
report
Adding a new
workbook to a
document library
Update an
existing
workbook in a
document library
Delete an
existing
workbook in a
document library
Send an email
Reassign
Approver
Approver
Send approval
for document
set
Complete
Reporting Services Power View
PerformancePoint Services
Excel Services
Visio Services
SharePoint 2013 Enterprise
Approve
Approve/
Reject/
Reassign
Reject
Deleted from
the SharePoint
list
Placed in the Recycle bin of the
SharePoint site which can be
accessed by Approvers,
Members, Owners
Document published and
available for all
authenticated users
Stop workflow. Document
rejected and can be viewed only
by the owner of the document
and the approvers
All PowerPivot
Features
4
Deploy the PowerPivot for SharePoint 2013 Add-In
to provide additional capabilities, such as full access to
workbooks as a data source, scheduled data refresh,
PowerPivot Gallery, and IT Management Dashboard.
SQL Server Analysis Services
PowerPivot for SharePoint 2013
Reporting Services Power View
PerformancePoint Services
Excel Services
Visio Services
SharePoint 2013 Enterprise
IT Management Dashboard
• Track the usage of Excel workbooks with data models.
• Discover mission-critical Excel applications.
• Visualize system performance and usage.
• See data sources being used.
• Extend analysis with custom solutions
based on the IT Management Dashboard data.
SQL Server Database Engine
Usage data collection must be enabled for
SharePoint components to log usage data.
Client Libraries
PowerPivot Web Service
PowerPivot
Management
Dashboard
SharePoint
Usage
Database
Usage
Logs
Server Health and
Workbook Activity
Workbooks
PowerPivot
Service Application
PowerPivot
Management Data
Workbook
Excel Calculation Services
PowerPivot Health Statistics
Collector
Usage Data
Import
Usage Data
Processing
Mgmt Dashboard
Processing
SharePoint Timer
Service
PowerPivot Application Database
(Customizing the PowerPivot Management Dashboard: http://msdn.microsoft.com/en-us/library/ff718155(v=SQL.105).aspx)
Spreadsheet Compare
• Compare spreadsheets side-by-side to identify and categorize their differences.
Spreadsheet Inquire
• Analyze, document, understand and diagnose spreadsheet.
Discovery and Risk Assessment
• Assess and categorize spreadsheets and
databases based on relevance,
materiality and business impact.
Audit and Control Management Server
• Non-intrusive auditing of changes made to
Excel spreadsheets and Access databases.
Security
Features
Import from Analysis
Services or PowerPivot
Visual Studio development with integrated
TFS Source Control
Partitioning Features
Personal BI
PowerPivot for Excel
Team BI
PowerPivot for SharePoint
Corporate BI
Analysis Services
One Model for All End User Experiences
Windows
Azure
mva
http://channel9.msdn.com/Events/TechEd
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn