hitchhiker’s guide to sharepoint bi
DESCRIPTION
TRANSCRIPT
Hitchhiker’s Guide to SharePoint BI(and Big Data too)
◦ Andrew J. Brust, Founder and CEO, Blue Badge Insights
2
Meet Andrew
CEO and Founder, Blue Badge Insights
Big Data blogger for ZDNetMicrosoft Regional Director, MVPCo-chair VSLive! and 17 years as a speakerFounder, Microsoft BI User Group of NYC• http://www.msbinyc.comCo-moderator, NYC .NET Developers Group• http://www.nycdotnetdev.com“Redmond Review” columnist for Visual Studio Magazine and Redmond Developer Newsbrustblog.com, Twitter: @andrewbrust
Read all about it!
The 2010 Business Intelligence Stack, Overall
Business User Experience
Data Infrastructure and BI PlatformAnalysis ServicesReporting ServicesIntegration ServicesMaster Data ServicesData MiningData Warehousing
Business Collaboration PlatformDashboards & ScorecardsExcel ServicesWeb based forms & workflowCollaborationSearchContent ManagementLOB data integration
Familiar User Experience Self-Service access & insightData exploration & analysisPredictive analysisData visualizationContextual visualization
Business Collaboration Platform
Information Platform
Today’s Stack
Data Warehousing
Analytical Repository
Enterprise Info. Mgmt. (EIM)
Analysis Tools
Big Data
SQL Server Enterprise, Fast Track, PDW
SQL ServerAnalysis Services
Excel, Excel ServicesPower View
Integration Services, Master Data Services, Data Quality Services
HDInsight (Hadoop)
The 2010 Presentation FlowPerformancePoint Services
Cube
Excel Services
Excel SQL Server
Tera- Data
Oracle DB2
Reporting Services
8
Agenda
Excel as an analysis clientPowerPivot, Excel ServicesPower ViewReporting Services, PerformancePointHDInsight/Hadoop (Big Data)
EXCEL AS AN ANALYSIS CLIENT
PivotStuffPivotTable, and linked charts (sometimes referred to as PivotCharts) work extremely well with OLAP cubesHow to create a PivotTable:• Ribbon’s Data tab (From Other Sources
button/From Analysis Services option or Existing Connections button)
• Insert tab (PivotTable “split button”)
How to insert a chart• PivotChart button on PivotTable Tools/Options tab• Several others
Formula Language CUBE Functions
CUBEMEMBER and CUBEVALUE• Also CUBEKPIMEMBER, CUBEMEMBERPROPERTY,
CUBERANKEDMEMBER, CUBESET and CUBESETCOUNT
IntelliSense style support• In a cell, type “=CU” and all CUBE formulas will
display• Select one with arrow keys and hit Tab• When prompted for connection, type " and view
pop-up list• Other pop-ups activate on " or "."
At Your Service
“Range Drag” and relative formula support on CUBEVALUECUBEVALUE and Data Bars go great togetherAbility to convert PivotTables to formulas
Excel as an Analysis Client
POWERPIVOT, EXCEL SERVICES
Self-Service BI with PowerPivot
Excel + Analysis Services + SharePointEnables the working in Excel but mitigates the “spreadmart” pitfalls:• Use Analysis Services (AS) as a hidden engine
•Instead of no engine
• Share via SharePoint, accessible by all AS clients•Instead of “deploying” via email
• Formal data refresh on server•So data doesn’t get stale, and users don’t have to make effort at updating
• Allow IT to monitor•So it’s not all rogue
• Provide path to more rigorous implementations•Can be upsized to Analysis Services
How Do You Get PowerPivot?
For Excel 2010:• Download the add-in from www.powerpivot.com• Get 32-bit or 64-bit version, depending on your version of Office
(not Windows)• Run the installer
For Excel 2013:• Requires Excel ProPlus retail product under volume license, or an
Office 365 ProPlus, Enterprise E3 or E4 subscription• It’s still in add-in, installed but not activated by default
• Same goes for Excel version of Power View
PowerPivot Guidebook
Table tabs
View data in Excel
DAX formula bar
Relationship indicator
Import data fromalmost anywhere
Sort and filter
Calculatedcolumnentry
Modeling the DataKPIs
Calculations
Data and Diagram views
Calculationarea
Calculationformula
Sort one column by another
Diagram ViewDefault Aggregations Special Advanced Mode
Reporting propertiesHierarchies
Hide specific columns andtables
Createrelationshipsvisually
KPIs
Perspectives
Calculations
PowerPivot Client
Excel Services
A component of SharePoint Server 2007+; requires Enterprise CALAllows export of workbook, worksheet, or individual items to SharePoint report library• Works great for PivotTables and Charts!• Also for sheets with CUBExxx formulas or
conditional formatting-driven “scorecards”
Content can be viewed in browser• Excel client not required• Drilldown interactivity maintained• Rendered in pure HTML and JavaScript• Parameterization supported
PowerPivot Server
Publish to Excel ServicesViewing and interactingData RefreshTreating as SSAS cube• 2008 R2 version: URL to .xlsx as server name• 2012 version: use POWERPIVOT named instance
and treat just like SSAS• DB name is GUID-based; best to discover it
Use Excel, Reporting Services as clients• And now Power View too…more later
The IT Dashboard
Increase IT efficiency:Familiar Technologies for Authoring, Sharing, Security, and ComplianceCustomizable IT DashboardVisualize usage with animated charts
Simplify management of SSBI content using
IT Operations Dashboard for SharePoint
24
SQL Server Analysis Services
Two Modes:• Multidimensional (was the only mode until SQL Server 2012)• Tabular (the SSAS version of PowerPivot)Both modes query-able by various clients:• Excel• Excel Services• Reporting Services• PerformancePoint• Even PowerPivotOnly Tabular mode is query-able by Power View• Power View wedded to BISM, so PowerPivot on SharePoint works too• Let’s talk about Power View now…
POWER VIEW
What is Power View?
Ad hoc reporting. Really!Analysis, data explorationData VisualizationIn Silverlight, in the browser, in SharePoint• And now in Excel 2013, tooIs actually based on SSRS• Power View makes a special RDL file
Power View Data Sources
Power View works only against BISMDirectQuery mode supported, howeverSupport for SSAS Multidimensional cubes comingGist is this: Power View emits DAX expressions• PowerPivot and SSAS Tabular support DAX (Data Analysis
eXpressions).• SSAS Multidimensional will support DAX soon
Create a Power View Report
In Excel 2013, click Power View button on Insert tab of ribbon• Make sure add-in is activated
In SharePoint, click “Create Power View Report” button or option on workbook in PowerPivot GalleryFor SSAS tabular model, create BISM data source, then click its “Create Power View Report” button or option• BISM data sources can point to PowerPivot workbooks too, if you
want.
Can export to PowerPoint and view there
Power View
SSRS, PERFORMANCEPOINT, HDINSIGHT
SSRS and Report BuilderReport Builder 3.0
• Familiar Microsoft Office Interface• Powerful Wizards• SharePoint list as data source
• Powerful Query Designer• Flexible Layout Options w/
rich visualizations
• PowerPivot as data source
Rich Visualizations
Report Parts
Skilled SSRS designers can publish report parts• From Report Builder 3.0 or VS report projects
End users can pick them from a gallery• A task pane, in Report Builder 3.0, with search capability• Cannot select from VS report designer
What can be published?:• Tablixes (i.e. tables, matrices)• Rectangles• Images, Charts, Gauges, Maps• Parameters and Lists
All aided by new ability to share Datasets and original ability to share Data Sources
Easy to Publish; Easy to Select
PerformancePoint Services (PPS)
Scorecards
Cascading scorecards with interactive charts and data from multiple sources
Analytics
Multi-dimensional slice and dice for advanced analytics including decomposition tree, performance map, and perspective view
PPS Capabilities
Analytic Grids & Charts• PPS’ own data visualizations• AJAX-based interactive analytics capabilities
Scorecards• Key Performance Indicators• Objectives• Dimensions
Dashboards• Bird’s eye view of business activities• Display Analytic Grids and Charts; Scorecards• Integrate SSRS, Excel Services (including PowerPivot)
content• Add Filters
SSRS and PerformancePoint
Microsoft HDInsight
Developed with Hortonworks and incorporates Hortonworks Data Platform (HDP) for WindowsWindows Azure HDInsight and Microsoft HDInsight (for Windows Server)Single node preview runs on Windows client
Includes ODBC Driver for HiveAnd Excel Add-In that uses it
JavaScript MapReduce frameworkContribute it all back to open source Apache Project
The New Presentation Flow
RelationalData
Hadoop/HDFS
UnstructuredData
SSAS Tabular
Power View
Excel
Thank you
[email protected]@andrewbrust on twitterWant to get on Blue Badge Insights’ list?”• Text “bluebadge” to 22828
Blog: http://www.bobhunt.net/
Twitter: @bobhms
Bob Hunt “The” Microsoft IT Pro Evangelist
Blog: http://powertoe.wordpress.com/
Twitter: @toenuff
Tome Tanasovski PowerShell MVP
Blog: www.ngenioussolutions.com/blog
Twitter: @nilaish
Nilesh Mehta SharePoint Architect
Blog: Too cool for one
Twitter: Too cool for one
Edwin Woo Windows Extremist
Blog: http://ehlotech.blogspot.com
Twitter:@bserebin
Ben Serebin Exchange Junkie
Thanks to our Organizers!
Blog: http://www.bobhunt.net/
Twitter: @bobhms
Bob Hunt “The” Microsoft IT Pro Evangelist
Website: http://www.nyewin.org
Twitter: @NYeWin
Eric Fellen
Website: http://www.nyewin.org
Ken Reid
Thanks to our Organizers!
Jason Gallicchio SharePoint Enthusiast
Twitter: @jasongall
Techstravaganza Communities
• Meetings: First Wednesday of the month, 5:30 to 7:30 PM, Microsoft NYC Office
• Web: http://www.sharepointusergroup.org/NewYork/Default.aspx
NYC SharePoint User Group
Princeton Area SharePoint User Group
NYC PowerShell User Group
• Meetings: Fourth Wednesday of the month, 6:00 to 8:00 PM, Infragistics Building Cranberry NJ
• Web: www.princetonsug.com
• Meetings: Second Monday of the month, 6:00PM, Microsoft NYC Office
• Web: http://powershellgroup.org/nyc
• Meetings: Second Tuesday of the month, 5:45PM to 9PM, Microsoft NYC Office
• Web: www.nyexug.com
New York Exchange User Group
• Meetings: First Thursday of the month, 6:00PM, Microsoft NYC Office
• Web: http://www.nyewin.org/
NY Enterprise Windows User Group
Thanks to our Sponsors!
Event Sponsors
Thanks to our User Groups!
Event User Groups