business intelligence in sharepoint 2013
DESCRIPTION
Starting in May 2010, with SQL Server 2008 R2, Microsoft began talking more and more about "Personal BI". The focus of this discussion was a paradigm shift moving business intelligence from being something a few BI professionals do with a data warehouse to a practice done by Information Workers every day in familiar tools like Microsoft Excel and SharePoint. Over the course of this session we will show you the improvements that Microsoft has made in the 2013 stack to take this new focus from being a “nice idea” to a truly powerful reality. We will explore the improvements made to Excel, PowerPivot & Power View, & Reporting Services. We will explain the underlying technology that makes the new features possible and walk through demos of some of the shinier toys. At the end of the session you will walk away with a better understanding of what is new in 2013 for business intelligence and an extreme desire to build reusable data model that will undoubtedly bring real value to your business.TRANSCRIPT
![Page 1: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/1.jpg)
Business Intelligence in SharePoint 2013
Empowering users to change their world
Jason HimmelsteinSenior Technical Director, SharePoint
@sharepointlhornhttp://blog.sharepointlonghorn.com
![Page 2: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/2.jpg)
2009 Atrion Networking Corporation
Jason’s contact & vitals
• Senior Technical Director, SharePoint at Atrion• Microsoft vTSP
– virtual Technology Solutions Professional
• SharePoint Foundation Logger – http://spflogger.codeplex.com
• Blog: www.sharepointlonghorn.com • Twitter: @sharepointlhorn • LinkedIn: www.linkedin.com/in/jasonhimmelstein• SlideShare: http://www.slideshare.net/jasonhimmelstein• Email: [email protected]
• Author of Developing Business Intelligence Apps for SharePoint– http://bit.ly/SharePointBI
![Page 3: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/3.jpg)
Takeaway from today’s session• Empowering users & driving adoption
of the new BI tools will get dynamic & powerful results
• Why implementing SharePoint 2013 for your business should happen sooner than you think
• Microsoft’s investment in solutions for Business Intelligence workloads will make you want to stand up & dance
![Page 4: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/4.jpg)
• Overview & What’s new• Excel Services• PerformancePoint Services• Reporting Services• PowerPivot• Power View
• Understanding the Microsoft Story• Demos, demos and more demos• PowerBI – a Primer
Agenda for this session
![Page 5: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/5.jpg)
Excel Services
![Page 6: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/6.jpg)
Key New Features• “Gallery” Mode• 3D Charts• Interactive
PivotCharts• PivotTable Field
Well• Timeline Slicers• Quick Explore• PowerView
![Page 7: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/7.jpg)
Mobile devices support for BI – including excelDelivering great touch-based data exploration and visualization capabilities in a browser on iOS, Android and Microsoft platformsExcel Services 2013PerformancePoint 2013Reporting Service 2012
![Page 8: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/8.jpg)
• Generates Excel table and chart views on-the-fly from an HTML table hosted on a web page• Export to Excel Web App or Excel file for further analyses
• Associated to any html table• Not only in SharePoint, available cross platform
Excel Interactive View
![Page 9: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/9.jpg)
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
![Page 10: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/10.jpg)
Analyze trends and timelines
Quickly navigate through your data
![Page 11: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/11.jpg)
PerformancePoint
![Page 12: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/12.jpg)
• Architecture and general model has remained the same as in previous version
• Key changes and new capabilities• Themes• BICenter (Dashboard Designer in the ribbon)• Filter Enhancements• Filter Search• Support for Analysis Services Effective User removing Kerberos dependency• Custom Target Applications• Easy Server-side Migration Dashboard• Dashboard Designer Authentication• Improved PerformancePoint Support on iPad
Changes in SharePoint 2013
![Page 13: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/13.jpg)
Theme support for dashboards
![Page 14: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/14.jpg)
Reporting Services
![Page 15: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/15.jpg)
SSRS for SharePoint 2013 needs SQL Server 2012 SP1• View and interact with reports on iOS
devices• Enables Power View 2013• SharePoint mode report server
• Leverage SharePoint scale-out functionality for Reporting Services Service Applications.• The new Reporting Services shared service is hosted in SharePoint Shared Service
Application pool.• The new Reporting Services service applications support Claims based authentication.• SharePoint cross-farm support for viewing reports.• Support for SharePoint backup and recovery and end-to-end SharePoint ULS logging.
• Data Alerts• Define and save data alert definitions• Run data alert definitions• Deliver data alert messages to recipients
• Excel Renderer & Word Renderer
![Page 16: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/16.jpg)
Get suggestions on chart types based on your data
Preview your graphs, formatting and KPIs
![Page 17: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/17.jpg)
Power View
![Page 18: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/18.jpg)
Power View - Original
• Server(s):• Minimum: SharePoint Server 2010 SP1 Enterprise Edition• SQL Server 2012 Reporting Services Add-in for SharePoint
• Client:• Supported browsers:
• Windows Vista: IE7 32-bit, FireFox 4• Windows 7: IE8 32-bit, IE9 32-bit, FireFox 4, Safari• Note the InPrivate browsing feature of IE is not supported
• Silverlight 5
![Page 19: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/19.jpg)
Power View
• A Power View report must be based on a deployed tabular BI Semantic Model:• Published PowerPivot workbook in a SharePoint library• Tabular database
• DAX Query is used to query the model• Ordinarily, the model needs to be optimized
for the Power View experience
![Page 20: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/20.jpg)
Power View• Users create a new Power View report (.rdlx) from:
• A BISM Connection File (.bism)• A PowerPivot workbook (.xlsx) in the PowerPivot Gallery (in Gallery view)• An SSRS shared data source (.rsds) based on a tabular BI Semantic Model
• Reports can consist of multiple views and each view can be filtered
• Reports may be:• Printed• Saved to SharePoint libraries• Exported to PowerPoint
• Clicking the report will open it in Preview mode• If the user has permission, they can switch to Edit
mode20
![Page 21: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/21.jpg)
Power View• The design experience consists of:
• Ribbon• Canvas• Filter area• Field List• Layout selection
• The report may be viewed in Preview or Full Screen mode
• Visualizations can be added to the canvas and then configured using the Layout Selection
![Page 22: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/22.jpg)
Power View
• Visualizations include:• Matrices• Charts• Cards• Tiles• Scatter and bubble charts• Maps
22
![Page 23: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/23.jpg)
Power View - Feature ComparisonPower 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, anonymousYes, anonymous
Yes No
Workbook or Model Size Max Workbook Size
AS Limits AS Limits5 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
![Page 24: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/24.jpg)
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)
![Page 25: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/25.jpg)
Fine tune your reports with chart and view filters
Visualize your insights with interactive charts
![Page 26: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/26.jpg)
What's new In BI with Office 2013 and SQL Server 2012 SP1• Excel 2013: Complete and Powerful self-service BI Tool
• Navigate models, preview and shape up your data (Quick Explore, Quick Analysis and Flash Fill)
• Access and mash-up data from any source (PowerPivot)• Stunning visualizations and data discovery (Power View)• Work with hundreds of millions of rows of data (powered by xVelocity)
• Improve governance, compliance and track business critical Excel assets:• Discover and assess user-created spreadsheets with SharePoint 2013• Compare spreadsheets, track lineage, conduct interactive diagnostics and create
spreadsheet analysis reports with Inquire in Excel 2013
• Browser-based sharing and collaboration for self-service BI via SharePoint 2013 and SQL Server 2012 SP1 Excel Services, Excel Web App, Power View in SharePoint
![Page 27: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/27.jpg)
Publish to SharePoint
• PowerPivot
• Flash Fill• Data
Model
• Power View• Quick Analysis• Quick Explore
Excel 2013: Complete and Powerful Self-Service BI Tool
Access
Clean
Mash-up
Explore
Visualize
Share
![Page 28: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/28.jpg)
Trend Charts, Flash Fill, Quick Explore and AnalysisGain insights quickly with guided data exploration and formatting
Easily show changeover time
Navigate models with one click
Instantly previewcharts and pivot tables with your data
Clean and shape up data
![Page 29: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/29.jpg)
PowerPivot in ExcelAccess and mashup data from virtually any source to createpowerful reports
Mashup largevolumes of data from various sources
Performdata analysis with extreme performance
Rapidly create compelling analytical applications
![Page 30: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/30.jpg)
Perform rapid visual data exploration
Easily create interactive visualizations
Effortlessly present and share reports
Power View in ExcelVisually discover and share insights across all levels of the organization
![Page 31: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/31.jpg)
The Microsoft Story
![Page 32: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/32.jpg)
SharePoint 2013
ManageBuild
DiscoverOrganize
SHAREShare
32
![Page 33: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/33.jpg)
Self-Service BI Reference ArchitectureTechnology View
RelationalDatabases
BusinessApplications
Files OData Feeds Cloud ServicesDeployedBI Semantic
Models
Third-PartyReportsExcel PowerPivotPower ViewSharePointInsights
SQL ServerData Warehouse
SQL ServerIntegration Services
SQL ServerAnalysis Services
SQL ServerReportingServices
SQL ServerMaster Data Services
SharePoint Server
Excel Services
Search
Visio Services
PerformancePoint ServicesPowerPivot Add-In
Reporting Services Power
View
Audit and Control Management Server
Dat
a Ti
er
Self-Service BI GovernanceCorporate BI and Data Governance
Info
rmati
on T
ier
Pres
enta
tion
Tier
Clie
nt T
ier
![Page 34: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/34.jpg)
Reporting Services Power View
PerformancePoint ServicesExcel ServicesVisio Services
SharePoint 2013 Enterprise
PowerPivot for SharePoint 2013 Reporting Services
Power ViewPerformancePoint Services
Excel ServicesVisio Services
SharePoint 2013 Enterprise
The Self-Service BI Light-up story
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.N
ati
ve E
CS
Featu
res
1
Install Analysis Services in SharePoint mode andregister the server in Excel Services
to enable the core PowerPivot functionality.Core
BI
Featu
res
2
Install Reporting Services add-in and Reporting Services in SharePoint-integrated
modeto unlock stunning browser-based data
exploration, visualization, and presentation experiences.
3
Pow
er
Vie
w
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
Pow
erP
ivot
Featu
res
4
![Page 35: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/35.jpg)
Office BI Strategy
The stack:Data Analysis with SQL
Analysis ServicesSQL ServerIntegration Services
The Tool:End user exploration
ExcelPower ViewPowerPivot
The Tool:Sharing
Excel ServicesVisio ServicesPerformancePoint
![Page 36: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/36.jpg)
Jason Himmelstein
Demo time!!!!
![Page 37: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/37.jpg)
PowerBI – a Primer
![Page 38: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/38.jpg)
Microsoft Power BI for Office 365Self-service BI with the familiarity of Office and the power of the cloud
Collaborate in Office 3651 in 4 enterprise customers on Office 365
Insights in Excel1 Billion Office Users
Analyze Visualize Share Find
Q&A
MobileDiscover
Scalable | Manageable | Trusted
![Page 39: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/39.jpg)
Powerful Self-Service BI with Excel 2013 Search and access internal &
external data
Clean, transform, and shape data
Merge and combine data from multiple sourcesLightning fast analytics with in-memory technology
Model relationships, custom measures, hierarchies, and KPI’s
Bring your data to life with interactive visualization
Explore data in new ways to discover hidden insights
![Page 40: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/40.jpg)
Powerful Self-Service BI with Excel 2013 Search and find internal &
external data
Clean, transform, and shape data
Merge and combine data from multiple sourcesLightning fast analytics with in-memory technology
Model relationships, custom measures, hierarchies, and KPI’s
Bring your data to life with interactive visualization
Explore data in new ways to discover hidden insights
![Page 41: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/41.jpg)
Powerful Self-Service BI with Excel 2013 Search and find internal &
external data
Clean, transform, and shape data
Merge and combine data from multiple sourcesLightning fast analytics with xVelocity in-memory technology
Model relationships, custom measures, hierarchies, and KPI’s
Bring your data to life with interactive visualization
Explore data in new ways to uncover hidden insights
![Page 42: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/42.jpg)
Powerful Self-Service BI with Excel 2013 Search and find internal &
external data
Clean, transform, and shape data
Merge and combine data from multiple sourcesLightning fast analytics with xVelocity in-memory technology
Model relationships, custom measures, hierarchies, and KPI’s
Bring your data to life with interactive visualization
Explore data in new ways to discover hidden insights
![Page 43: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/43.jpg)
Collaborate and Stay Connected with Office 365Easily setup powerful BI sites
Share live reports with data refresh from on-prem sources
Manage and share data sets and monitor usageAsk questions in the natural language speech bubble
Get back answers as the system generates data visualizations
View Power View reports on any device with HTML5 support
Discover and explore all your favorite reports with the mobile BI app
Q&A
![Page 44: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/44.jpg)
Collaborate and Stay Connected with Office 365Easily setup powerful BI sites
Share live reports with data refresh from on-prem sources
Manage and share data sets and monitor usageAsk questions in the natural language speech bubble
Get back answers as the system generates data visualizations
View Power View reports on any device with HTML5 support
Discover and explore all your favorite reports with the mobile BI app
Q&A
![Page 45: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/45.jpg)
Collaborate and Stay Connected with Office 365Easily setup powerful BI sites
Share live reports with data refresh from on-prem sources
Manage and share data sets and monitor usageAsk questions in the natural language speech bubble
Get back answers as the system generates data visualizations
View Power View reports on any device with HTML5 support
Discover and explore all your favorite reports with the mobile BI app
Q&A
![Page 46: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/46.jpg)
Extend with Hybrid Cloud Solutions
Enable ease of discovery with data search across on premise data
Monitor data usage across your organization to optimize system investments
Enable manual or scheduled data refresh for reports in Office 365 to on-premise sources
![Page 47: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/47.jpg)
Extend with Hybrid Cloud Solutions
Enable ease of discovery with data search across on premise data
Monitor data usage across your organization to optimize system investments
Enable manual or scheduled data refresh for reports in Office 365 to on-premise sources
![Page 48: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/48.jpg)
Extend with Hybrid Cloud Solutions
Enable ease of discovery with data search across on premise data
Monitor data usage across your organization to optimize system investments
Enable manual or scheduled data refresh for reports in Office 365 to on-premise sources
![Page 49: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/49.jpg)
Microsoft Power BI for Office 365Collaborate in Office 365
Insights in Excel
Analyze
Visualize
Share
Find
Q&A
Mobile
Discover Search, access, and transform public and internal data sources with Power Query
Share data views and workbooks refreshable from on-premises and cloud based data sources, with Power BI Sites
Easy data modeling and lightning fast in-memory analytics with Power Pivot
Bold new interactive data visualizations with Power View and Power Map
Ask questions and get immediate answers with natural language query
Mobile access through HTML5 and touch optimized apps for Windows 8, RT and iOS tablets
Scalable | Manageable | Trusted
![Page 50: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/50.jpg)
Designed for IW’s and IT
Easy to use - Self-Service BI capabilities delivered through the familiar Excel environment.
Easy to get started – turn-key solution delivered through a trusted service in Office 365.
Faster time to solution - reduces deployment complexity and project time.
Extend existing systems – cloud based services that extend your existing on-premises systems.
![Page 51: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/51.jpg)
Power BI Offer Details
O365 E3 Power BIExcel 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 ●
![Page 52: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/52.jpg)
Business Intelligence InvestmentsSQL Server 2012 SP1
& Office 2013
• Compatibility for self-service BI in Excel & SharePoint 2013
• PowerPivot, Power View & other BI
• Discovery and assessment of user-created spreadsheets
• Interactive diagnostics and analysis reports of spreadsheets (Prodiance)
Excel Add-Ins, updates, & BI Apps
• Data Explorer” - enhanced data discovery and info. mgmt. previewed at Strata
• “Geo Flow” - geo and temporal data visualization and analysis
• Native apps for IOS & Win8, works with excel files in SharePoint Online
BI in Office 365 & SQL Server
• Power View for multi-dimensional BISM
• SharePoint Online supports PowerPivot and Power View (Silverlight & HTML5)
• Connectivity to on-premises data sources
• Enhanced data discovery and management service
• Enhanced Mobile BI apps support
![Page 53: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/53.jpg)
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
Mobile BI
Touch optimized Mobile BI app connects you to all your favorite reports
Power View HTML5 for Office 365
Mobile BI Application & Power View HTML5
![Page 54: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/54.jpg)
Wrap-up
![Page 55: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/55.jpg)
• Introducing the Data Model & Relationships in Excel 2013• http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx
• Intro to Power View for Excel 2013• http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx
• What’s new in Reporting Services (SQL 2012 SP1)• http://msdn.microsoft.com/library/ms170438(SQL.110).aspx
• Introducing PerformancePoint Services 2013 • http://blogs.msdn.com/b/performancepoint/archive/2012/08/03/what-s-new-in-performancepoint-services-2013.aspx
• What’s new with Visio 2013• http://visio.microsoft.com/en-us/preview/visio-benefits.aspx
• Introducing the BI Light-Up Story for SharePoint 2013• http://blogs.msdn.com/b/analysisservices/archive/2012/07/27/introducing-the-bi-light-up-story-for-sharepoint-2013.aspx
• Going All In with Excel 2013• http://blogs.msdn.com/b/analysisservices/archive/2012/07/26/going-all-in-with-excel-2013.aspx
• Great new book on Developing Business Intelligence Apps for SharePoint• http://bit.ly/SharePointBI
Helpful Links
![Page 56: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/56.jpg)
2009 Atrion Networking Corporation
Jason’s contact & vitals
• Senior Technical Director, SharePoint at Atrion• Microsoft vTSP
– virtual Technology Solutions Professional
• SharePoint Foundation Logger – http://spflogger.codeplex.com
• Blog: www.sharepointlonghorn.com • Twitter: @sharepointlhorn • LinkedIn: www.linkedin.com/in/jasonhimmelstein• SlideShare: http://www.slideshare.net/jasonhimmelstein• Email: [email protected]
• Author of Developing Business Intelligence Apps for SharePoint– http://bit.ly/SharePointBI
![Page 57: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/57.jpg)
Backup Materials
![Page 58: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/58.jpg)
BI Semantic Model
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
Analysis Services VisionBI Semantic
Model
![Page 59: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/59.jpg)
Delivering on the promise of “BI for Everyone”
Self-Service Enterprise
Analysis ServicesPower Pivot
Empowered
FormalizedExcel, Reports, &
Dashboards
Business Intelligence Semantic Model (BISM)
![Page 60: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/60.jpg)
BI Semantic ModelOne Model for all End User Experiences
Personal BIPower Pivot for Excel
Organizational BI
BISM Analysis Services
Team BIPower Pivot for
SharePoint
Client Tools Analytics, Reports,
Scorecards, Dashboards, Custom Apps
BI Semantic Model
Data model
Business logic and queries
Data access
Data SourcesDatabases, LOB Applications,
OData Feeds, Spreadsheets, Text Files
Flexibility ScalabilityRichness
![Page 61: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/61.jpg)
Power View
BI Semantic Model
DatabasesLOB
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-partyApplications
Excel Reporting Services
PerformancePoint
![Page 62: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/62.jpg)
BI Semantic Model - Change
DatabasesLOB
Applications Files OData Feeds Cloud Services
BI Semantic Model
Data model
Business logic
Queries
Data access
Third-partyApplications
Power View Excel Reporting Services
PerformancePoint
Multidimensional
MDX
MDX/DAX
MOLAP ROLAP
Tabular
DAX
MDX/DAX
In-Memory Direct Query
![Page 63: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/63.jpg)
SQL Server Data Tools
SQL Server Data Tools
Power View
SharePoint 2010 or 2013
xlsx
Architecture - Change
Excel Services
Reporting Services
Analysis Services
BI Semantic Model Tabular
Analysis Services
BI Semantic ModelMultidimensional
PerformancePoint
Excel
Power Pivot for Excel
Reporting Services
PowerPivot for SharePoint
(Analysis Services)
xlsx
![Page 64: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/64.jpg)
BI Semantic Model - Deliverables
64
• 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 ofenterprise servers
Flexibility Richness Scalability
![Page 65: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/65.jpg)
Data Model
• 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
![Page 66: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/66.jpg)
Business Logic
• 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
![Page 67: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/67.jpg)
Data Access and Storage
• 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
![Page 68: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/68.jpg)
BI Semantic Model
Data model
Business logic
Data access
BI Semantic Model: Architecture
ROLAP MOLAP VertiPaqDirectQuery
MDX DAX
Multidimensional
Tabular
Third-partyapplications
Reporting Services
(Power View)Excel PowerPivot
Databases LOB Applications Files OData Feeds Cloud Services
SharePointInsights
Queries MDX MDX/DAX
![Page 69: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/69.jpg)
Multidimensional Tabular MappingBISM-MD Object Tabular Object
Cube Model
Cube Dimension Table
Attributes (Key(s), Name) Columns
Measure Group Table
Measure Measure
Measure without MeasureGroup Within Table called “Measures”
MeasuregroupCube Dimension relationship
Relationship
Perspective Perspective
KPI KPI
User/Parent-Child Hierarchies Hierarchies
![Page 70: Business Intelligence in SharePoint 2013](https://reader035.vdocuments.us/reader035/viewer/2022062703/55510c47b4c9052d0e8b460a/html5/thumbnails/70.jpg)
Calculated Members
Types Additional constraints
Children of all with a single real member
Calculated members on user hierarchies
Attribute may have an optional unknown member
Attribute cannot be key unless it’s the only attribute
Not a parent-child attribute