Share Point Roundtable Self Service Business Intelligence with

Share. Point Roundtable Self Service Business Intelligence with Share. Point 2013 Presented by: Jason Ditzel Rich Deken

BI Landscape Self Service Analysis Business Reporting Performance Monitoring & Scorecards Parameterized & Operational Reporting Big Data Non-technical users creating reports Moderate IT involvement Authored by IT only Cloud Integrated data from disparate sources Fixed Format Reports Summarized views with KPIs measured and scored against targets Fixed format reports suitable for printing Massive Computation Self Service

Attributes of Self-Service BI o Empower end users o Interactive data visualization o Increased collaboration o IT manages and protects data without hampering creativity Self Service Analysis Business Reporting Performance Monitoring & Scorecards

The Software o Excel’s Power. Pivot – Author the Data Model o Excel Services – Publish to Share. Point - Interactive Reporting o Power View – Additional Visualization Options o SQL Server 2012 o Power. Pivot for Share. Point 2013 o Analysis Services o Reporting Services Self Service Analysis Business Reporting

Excel 2013 Power. Pivot o Build data models in Excel o Use as the basis of Pivot Tables, Pivot Charts, and Power View reports o Built into Excel 2013 o Import millions of row of data from multiple sources o Create relationships between different data sources o Create calculated fields on the data Self Service Analysis Business Reporting

Excel Services 2013 o Share. Point Enterprise Feature o End users can view interactive workbooks via the browser o Eliminate the emailing of workbooks o Interact with the Pivot Tables and slicers created from the Power Pivot data model Self Service Analysis Business Reporting

Demo 1: Self Service Analysis Excel 2013 Power. Pivot Business Reporting Excel Services

Note: Excel Web App vs. Excel Services o They are two different products o The Excel Web App enables creating or editing a workbook in a browser o Excel Services enables interaction with Pivot Tables and Pivot Charts that use the Power Pivot data model – Excel Web App does not o Excel Services supports most kinds of external data connections – Excel Web App supports some external data connections o Excel Services supports calculated fields and measures in the Power Pivot data model Link to the official comparison table

Power View o Think of it as additional charting options created from the Power Pivot data model o Animated Bubble Chart o Card View o Maps Self Service Analysis Business Reporting

Demo 2: Excel 2013 Power. Pivot Self Service Analysis Business Reporting Excel Services Power View

The Software (Part 2) o Report Builder – Create powerful operational reports, reusable report parts, and shared datasets o SQL Server 2012 o Reporting Services in Share. Point Mode o Reporting Services Add In for Share. Point o o Power View Site Collection Feature Report Server Integration Feature o Performance Point – Build Dashboards o Dashboard Designer Business Reporting Performance Monitoring & Scorecards

Report Builder o Use when parameterized reports are required o WSYWIG print capability o Reporting Services skillset is commoditized o Can be created from Power Pivot data model Business Reporting

Demo 3: Excel 2013 Power. Pivot Business Reporting Report Builder Share. Point

Performance Point o Dashboard feature o Easily expose the presentation of KPIs from the Power Pivot data model Performance Monitoring & Scorecards

Demo 4: Performance Monitoring & Scorecards Excel 2013 Power. Pivot Performance Point Dashboard

BI Semantic Model

Promoting the Data Model from Power Pivot to the Enterprise o Use SQL Server 2012 tools to import the data model created in Power Pivot to SQL Server Analysis Services o SSAS - “Tabular Model” o Secure data in the model by applying filters and assigning permissions to the query result set

A Note on Installation o SQL Server 2012 Enterprise or Business Intelligence Edition required o Share. Point Server 2013 Enterprise required o The documentation on the Microsoft site is comprehensive, but is split between Share. Point and SQL Server topics o ‘Medium’ level installation task (compared to other features of Share. Point) o Give the servers running Analysis Services and Excel Services plenty of RAM

Data Visualization Product Landscape o Products o Excel + Share. Point o Qlik. View o Tableau o Spotfire (TIBCO) o Business Objects (SAP) o Others. . o Leverage your investment in Share. Point and Office

Demo 5 Excel “Power” Tools Qlik. View Tableau

Power Query o Ability to perform ETL prior to loading data into Power View o Example: crosstabs and pivoted data can be “unpivoted” o Additional data sources beyond Power Pivot, including: o Hadoop (HDFS) o Azure HDInsight o Active Directory o Reads multiple data formats: XML, JSON, OData, etc o Powerful language for complex transformations

Power Query

Power Map o Provides a way to visualize data plotted by geography o Views organized by “Tours” and “Layers” o Data can be plotted and displayed over time

Power Map

Excel Tools compared to SQL Server Excel SQL Server Extract/Transform/Load (ETL) Power Query Integration Services Data Modeling, Merging, + Mash-ups Power Pivot Analysis Services Visualization and Reporting Services, Power View, Report Builder Power View, Power Map

Demo 6 Powe r Query Powe r Pivot Powe r Map

“Power BI for Office 365” o “Power BI” = “Power Query” + “Power Map” + “Power Pivot” + “Power View” o Available “later this summer” o “Power BI” app available now on Windows 8 Marketplace

Demo 7 Power BI

Review Tool Purpose Power Query Extract/Transform/Load (ETL) Power Pivot Data Modeling, Merging, + Mash-ups Power View, Power Map, Report Builder Visualization and Reporting

Summary: Empowering the User with Self-Service BI Excel Power Pivot Analysis Services Power Query Data Reuse Share. Point Discovery Power Map Power View Excel Services Business Insight

Questions? Self Service Business Intelligence with Share. Point 2013 Presented by: Jason Ditzel Rich Deken