hitchhiker’s guide to sharepoint bi

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

Andrew’s New Blog (bit.ly/bigondata)

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

andrew.brust@bluebadgeinsights.com@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