Transcript Self-Service Business Intelligence with Power BI

Self-Service
Business Intelligence
with Power BI
Theresa Eller | @SharePointMadam | [email protected]
sharepointmadam.com | slideshare.net/SharePointMadam
Theresa Eller
• Business Systems Analyst at MD Anderson Cancer Center
• Microsoft Office Specialist certified in SharePoint 2013
• President of Houston SharePoint User Group (HSPUG)
• Frequent speaker at SharePoint events
• Dallas, Paris, Houston, New York City, Baton Rouge, San Antonio, Raleigh
• Mom to 2 furry, four-legged children (Shay & Cookie)
Thank You Sponsors!
Visit the Sponsor tables to
enter their end of day
raffles.
Turn in your completed Event
Evaluation form at the end of the
day in the Registration area to be
entered in additional drawings.
Want more free training? Check
out the Houston Area SQL
Server User Group which
meets on the 2nd Tuesday of
each month. Details at
http://houston.sqlpass.org
6/13/2015
Agenda
• Power BI Designer
• Power BI Dashboard
• Power Query
• Power View
• PowerPivot
Demos
• Download, install, and enable Power BI Designer, Power Query, Power
View
• Import data from SQL Server
• Load data to the data model
• Expand or remove columns of data
• Create reports & charts in Power BI Designer and Power View
• Share Power BI dashboards with others
• Expose Power View reports & charts in SharePoint
Power BI Designer Preview
Getting Started
• From the Power BI site
• powerbi.microsoft.com
• Sign up for a free account
• Download Power BI Designer Preview
• Open Power BI Designer
• Have your data source(s) ready
Power BI Designer Preview is subject to change without notice.
Power BI Designer Welcome Page
Power BI Designer
Get Data
Switch between
Report and
Query view
Get Data
Get Data from SQL Server
Credentials
Select Tables to Load
Load to Data Model
Manage Relationships
Select Fields from the Tables
• Chart or graph automatically starts building upon selecting first field
Multiple Charts on Same Page
Change Chart Type
Power BI Dashboard
Power BI Preview
app.powerbi.com
Mobile App
• Browser-based
• Create dashboards
• Create reports
• Upload reports
• Available for iOS and Windows
tablets
Welcome Screen – app.powerbi.com
Welcome Screen – Mobile App
Create Dashboard
Get Data (1 of 3)
• Imports reports to selected dashboard
Get Data (2 of 3)
Azure SQL Database
SQL Server Analyses Server
Get Data (3 of 3)
Report Added to Dashboard
• Delete the report (optional)
Reports and Data Set Added to Power BI
Click the Report to Open It
Reporting View
Editing View
Add Pages to the Report
Too Much of a Good Thing
Filters
Save New Report
Pin Report to Dashboard
Resize the Map/Chart on the Dashboard
Share the Dashboard
app.powerbi.com
Mobile App
Where Did You Go?
Power Query
Power Query
• An Excel add-in that enhances the
self-service Business Intelligence
experience in Excel by simplifying
data discovery, access and
collaboration
• A new add-in that provides a
seamless experience for data
discovery, data transformation and
enrichment for Information
Workers, BI professionals and other
Excel users
• Identify the data you care about from
the sources you work with (e.g.
relational databases, Excel, text and
XML files, OData feeds, web pages,
Hadoop HDFS, etc.).
• Combine data from multiple,
disparate data sources and shape it in
order to prepare the data for further
analysis in tools like Excel and Power
Pivot, or visualization in tools like
Power View and Power Map.
Power Query Requirements
• Operating System
• Windows 7, 8, 8.1
• Windows Server 2008 R2
• Windows Server 2012
• Internet Explorer 9 (or higher)
• Microsoft Office 2013:
• Power Query Premium: All Power
Query features available for:
Professional Plus, Office 365 ProPlus
or Excel 2013 Standalone
• Microsoft Office 2013:
• Power Query Public: Available for all
other Office 2013 Desktop SKUs.
Includes all Power Query features,
except the following ones: Corporate
Power BI Data Catalog, Azure-based
data sources, Active Directory, HDFS,
SharePoint Lists, Oracle, DB2, MySQL,
PostgreSQL, Sybase, Teradata,
Exchange, Dynamics CRM, SAP
BusinessObjects, Salesforce.
• Microsoft Office 2010 Professional
Plus with Software Assurance
Enable Power Query Add-In
• File > Options > Add-Ins > COM Add-Ins
Power Query Tab & Ribbon
Get External Data
Get Data from Microsoft SQL Database
Select Tables
Load To Data Model
• Handles hundreds of millions of rows of data
Workbook Queries
Launch Editor
Flatten Columns
Flattened (Expanded) Column
Query Editor Ribbon
Close & Load
• Updates the workbook queries with applied changes
Power View
Power View Requirements
• Microsoft Excel 2013
• Office Professional Plus 2013
• Office 365 Professional Plus
• Standalone edition of Excel 2013
• SharePoint Server Enterprise
Edition (2010 or 2013)
• SQL Server 2012 SP1 Reporting
Services Add-In
Insert – Power View
Power View Sheet
Select Fields
Switch Visualization
Geocode Data Through Bing
Change Layout
Charts on Same Page are Connected
Slicers Filter All Charts on the Page
Data Refresh
• Refresh – current connection
• Refresh All – all connections
PowerPivot
PowerPivot
• Powerful data analysis
• Import millions of rows from multiple sources
• Excel COM add-in
• Download for 2010
• Built-in for 2013
• Enable add-in if you don’t see the PowerPivot tab
PowerPivot Ribbon
Get Data
• Power Query
• PowerPivot
Insert – Pivot Table
PowerPivot Sheet
Select Fields
PowerPivot Report
Slicer
Questions?
Online Resources
• Download SQL Server 2012 Express
• https://www.microsoft.com/en-us/download/details.aspx?id=29062
• Download Adventure Works Database
• http://msftdbprodsamples.codeplex.com/
• Power BI Blog
• https://blogs.office.com/sku/power-bi/
• The White Pages
• http://whitepages.unlimitedviz.com/
People Who Speak/Write About Power BI
John White
Jason Himmelstein
@diverdown1964
@sharepointlhorn
whitepages.unlimitedviz.com
sharepointlonghorn.com
Chris McNulty
Dave Feldman
@cmcnulty2000
@bostonmusicdave
chrismcnulty.net
about.me/bostonmusicdave
Thank You Sponsors!
Visit the Sponsor tables to
enter their end of day
raffles.
Turn in your completed Event
Evaluation form at the end of the
day in the Registration area to be
entered in additional drawings.
Want more free training? Check
out the Houston Area SQL
Server User Group which
meets on the 2nd Tuesday of
each month. Details at
http://houston.sqlpass.org
6/13/2015
Theresa Eller
• @SharePointMadam
• [email protected]
• sharepointmadam.com
• slideshare.net/SharePointMadam