Business Intelligence in SharePoint 2013
Empowering users to change their world
Jason Himmelstein, MVP
Senior Technical Director, SharePoint
@sharepointlhorn
http://www.sharepointlonghorn.com
Gold Sponsor
Silver Sponsors
#CollabCon
Share your ideas and feedback on Twitter
$250 Future Shop gift card for most #CollabCon mentions
Session Evaluations
Go to Evaluations on the home page of www.collabcon.org
It is important to get your feedback
Please fill out evaluations for all the sessions you attend
Following the conference, a random drawing from all
completed evaluations will be selected
The winner will receive a $250 Future Shop gift card, valid
online or in store
Blog: www.sharepointlonghorn.com Twitter: @sharepointlhorn
LinkedIn: www.linkedin.com/in/jasonhimmelstein
SlideShare: http://www.slideshare.net/jasonhimmelstein Email: [email protected]
me
Takeaway from today’s session
Share your Excel workbooks in the web
Use slicer targets to optionally filter dashboard items
Interact with your workbook with all of the rich features of the Excel client in the browser
Analyze trends and timelines
Quickly navigate through your data
PerformancePoint
Get suggestions on chart types based on your data
Preview your graphs, formatting and KPIs
22
Power View - Feature Comparison
Power View in
Excel Power View
in Excel Services Power View
(SQL edition) Office 365 “O15”
All Visualizations Yes Yes Yes Yes
Power View Authoring Yes No Yes No
Cross Visualization Interactivity Yes Yes Yes Yes
External images Yes, anonymous Yes, anonymous Yes No
Workbook or Model Size Max Workbook
Size AS Limits AS Limits 5 MB CTP
10 MB RTM
Export to PowerPoint No No Yes No
Reorder Power View views No No Yes No
Connecting to UDM
(multidimensional) No No Yes No
Offline Mode Yes No No No
Multiple External Models Yes Yes No Yes
Report Zoom No Browser Zoom Browser Zoom Browser Zoom
Reading & Presentation Mode No No Yes No
Silverlight V5 required on client Yes Yes Yes Yes
Power View for Multidimensional Models
• Power View on Analysis Services via BISM
• Native support for DAX in Analysis Services
• Better flexibility: Choice of DAX on Tabular or Multidimensional (cubes)
Fine tune your reports with chart and view filters Visualize your
insights with interactive charts
The Microsoft Story
Share
Relational
Databases Business
Applications Files OData Feeds Cloud Services Deployed
BI Semantic Models
Third-Party Reports
Excel PowerPivot
Power View SharePoint
Insights
SQL Server Data Warehouse SQL Server
Integration Services
SQL Server
Analysis Services SQL Server ReportingServices
SQL Server Master Data Services SharePoint Server
Excel Services Search
Visio Services
PerformancePoint Services PowerPivot Add-In Reporting Services
Power View Audit and Control Management Server
Data Tier
Self-Service BI Governance Corporate BI and Data Governance
Information TierPresentation TierClient Tier
Reporting Services Power View PerformancePoint Services
Excel Services Visio Services SharePoint 2013 Enterprise PowerPivot for SharePoint 2013
Reporting Services Power View PerformancePoint Services
Excel Services Visio Services
SharePoint 2013 Enterprise SQL Server Database Engine SQL Server Analysis Services Deploy your SharePoint 2013 Enterprise farms and
enable shared services, such as Excel Services, Visio Services and PerformancePoint Services.
Native ECS Features
1
Install Analysis Services in SharePoint mode and register the server in Excel Services to enable the core PowerPivot functionality.
Core BI Features
2
Install Reporting Services add-in and Reporting Services in SharePoint-integrated mode to unlock stunning browser-based data exploration,
visualization, and presentation experiences.
3
Power View
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.
All PowerPivot Features
4
The stack:
Data Analysis with SQL
Analysis Services SQL Server
Integration Services
The Tool:
End user exploration
Excel
Power View PowerPivot
The Tool:
Sharing
Excel Services Visio Services PerformancePoint
1 in 4 enterprise customers on Office 365
1 Billion Office Users
Analyze Visualize Share Find
Q&A
Mobile Discover
Scalable | Manageable | Trusted
O365 E3 Power BI
Excel Self-Service BI
●
SharePoint Online
●
Excel Web Client Size
Limit
10MB* 250MB*
On Premise Data Refresh
●
Power BI Site
●
Natural Language Query
●
Mobile BI Client
●
Data Stewardship
●
Enterprise Data Search
●
View browser based reports on any mobile device with Power View in HTML5
Discover and explore all your favorite Excel and Power View reports from O365 through the mobile BI application Collaborate on insights with
others through the mobile BI app Touch optimized Mobile BI app connects you to all your favorite reports
Power View HTML5 for Office 365
Wrap-up
http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx
http://msdn.microsoft.com/library/ms170438(SQL.110).aspx
http://blogs.msdn.com/b/performancepoint/archive/2012/08/03/what-s-new-in-performancepoint-services-2013.aspx http://visio.microsoft.com/en-us/preview/visio-benefits.aspx
http://blogs.msdn.com/b/analysisservices/archive/2012/07/27/introducing-the-bi-light-up-story-for-sharepoint-2013.aspx http://blogs.msdn.com/b/analysisservices/archive/2012/07/26/going-all-in-with-excel-2013.aspx
http://bit.ly/SharePointBI
Session Evaluations
Go to Evaluations on the home page of www.collabcon.org
It is important to get your feedback
Please fill out evaluations for all the sessions you attend
Following the conference, a random drawing from all
completed evaluations will be selected
The winner will receive a $250 Future Shop gift card, valid
online or in store
Blog: www.sharepointlonghorn.com Twitter: @sharepointlhorn
LinkedIn: www.linkedin.com/in/jasonhimmelstein
SlideShare: http://www.slideshare.net/jasonhimmelstein Email: [email protected]
me
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
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
Self-Service Enterprise
Analysis Services
Power Pivot
Empowered Excel, Reports, & Dashboards Formalized
Business Intelligence Semantic Model (BISM)
One Model for all End User Experiences
Personal BI
Power Pivot for Excel Organizational BI
BISM Analysis Services
Team BI
Power Pivot for SharePoint
Client Tools
Analytics, Reports, Scorecards, Dashboards, Custom Apps
BI Semantic Model Data model Business logic and queries Data access
Data Sources
Databases, LOB Applications, OData Feeds, Spreadsheets, Text Files
Flexibility Richness Scalability
Power View
Databases LOB Applications Files OData Feeds Cloud Services
BI Semantic Model
Data model Business logic Queries
Data access
Multidimensional MDX MDX
MOLAP ROLAP
Tabular DAX MDX/DAX
In-Memory Direct Query Third-party
Applications Excel Reporting
Services PerformancePoint
Databases LOB Applications Files OData Feeds Cloud Services
BI Semantic Model
Data model Business logic Queries
Data access Third-party
Applications Power View Excel Reporting
Services PerformancePoint
Multidimensional MDX MDX/DAX
MOLAP ROLAP
Tabular DAX MDX/DAX
In-Memory Direct Query
SQL Server Data Tools
SQL Server Data Tools Power View
SharePoint 2010 or 2013
xlsx
Excel Services
Reporting Services
Analysis Services
BI Semantic Model Tabular Analysis Services
BI Semantic Model Multidimensional
PerformancePoint Excel Power Pivot
for Excel Reporting Services
PowerPivot for SharePoint (Analysis Services)
xlsx
•
Rich data modeling
capabilities
•
Sophisticated business
logic using DAX and MDX
•
Fine-grained security –
row and cell level
•
Enterprise capabilities –
multi-language and
perspectives
•
Tabular and
multidimensional
modeling experiences
•
DAX and MDX for
business logic and
queries
•
Cached and passthrough
storage modes
•
Choice of end-user BI
tools
•
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 of
enterprise servers
Flexibility Richness Scalability
50
• Familiar model, easier to build, faster time to solution
• Advanced concepts (parent- child, many-to-many) not available natively in the model… need calculations to simulate these
• Easy to wrap a model over a raw database or warehouse for reporting & analytics
Tabular
• Sophisticated model, higher learning curve
• Advanced concepts baked into the model and optimized (parent- child, many-to-many,
attribute relationships, key vs. name, etc.)
• Ideally suited for OLAP type apps (e.g. planning,
budgeting, forecasting) that need the power of the
multidimensional model
Multidimensional
• 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 calc
members
DAX
• Based on understanding of multidimensional concepts – higher initial learning
curve
• Complex solutions require steeper learning curve – CurrentMember, overwrite semantics, etc.
• Ideally suited for apps that need the power of
multidimensional
calculations – scopes,
assignments, calc members
MDX
• In-memory column store… typical 10x compression
• Brute force memory scans… high performance by default… no tuning required
• Basic paging support… data volume mostly limited to physical memory
xVelocity
• Disk based store… typical 3x compression
• Disk scans with in-memory subcube caching… aggregation tuning required
• Extensive paging support… data volumes can scale to multiple terabytes
MOLAP
• Passes through DAX queries &
calculations… fully exploits backend database capabilities
• No support for MDX queries… no
support for data sources other than SQL Server (in Denali)
Direct Query
• Passes through fact table requests… not recommended for large dimension tables
• Supports most relational data sources…
no support for aggregations except SQL Server indexed views
ROLAP
Third-party
applications Reporting Services
(Power View) Excel PowerPivot
Databases LOB Applications Files OData Feeds Cloud Services SharePoint
Insights
