Transcript What is Power BI for Office 365?
September 17, 2014 13 Tips for Getting Started w/Power BI for Office 365
(Hosted by SQL PASS BI VC)
The
B
aker’s
D
ozen
B
usiness
I
ntelligence
13 SQL Server / Business Intelligence Productivity Tips
Kevin S. Goff Microsoft SQL Server MVP
Kevin S. Goff – Brief BIO
• Developer/architect since 1987 / Microsoft SQL Server MVP • Columnist for CoDe Magazine since 2004, “The Baker’s Dozen” Productivity Series” , 13 tips on a SQL/BI topic • Wrote a book, collaborated on a 2 nd book • Frequent speaker for SQL Server community events and SQL Live!360 Conferences • Email: [email protected]
• My site/blog: www.KevinSGoff.Net
(includes SQL/BI webcasts) • Releasing some SQL/BI video courseware in 2015 Intro to Power BI for Office 365 2
Today’s Topic
• 13 steps for getting started with Power BI for Office 365 • This is an Introductory Session • Two objectives: – To provide you with steps to set up a Power BI for Office 365 site – Give a general roadmap of the major components of Power BI, and a quick glance at what can be built
• Will provide some links at the end to study Power BI further, and a quick look at what’s coming in the future
Intro to Power BI for Office 365 3
Topics for today
What is Power BI and Power BI for Office 365?
Power BI for Office 365 vs SharePoint On-Premises
What can we build with Power BI for Office 365?
Getting Started: Creating a Power BI for Office 365 site/account
Creating Data Sources and Data Management Gateways
Deploying/Saving Content to Power BI for Office 365 sites
Setting up Data Refreshes back to On-Premise Sources
Intro to Power BI for Office 365 4
1: What is Power BI for Office 365?
• One way to look at it – there are 2 major parts • The first is a set of Excel 2013 add-ins – Power Pivot (a simple way to create analytic pivot tables) – Power View (a new data visualization tool for creating “image” reports and charts) – Power Map (formerly GeoFlow, creates 3-D geographic visualizations using Bing Maps under the hood) – Power Query (allows you to search and query data from public sources) • The second is based on Office 365 Cloud Services – Office 365 Power BI sites that offer a cloud-based version of SharePoint portals for sharing Excel content – Data Management Gateway so that IT can control data sources published on Power BI sites (as well as user access) – Mobile BI device support for Windows 8, Windows RT and iOS tables (iPads) to view visualizations using HTML5
Intro to Power BI for Office 365 5
2: Power BI for Office 365 vs SharePoint On-Premises
• In a traditional SharePoint 2010 or 2013 environment: • Works well, but requires SharePoint licenses and maintenance Users then publish the Excel content to a SharePoint site collection SharePoint Enterprise Server Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Larger set of users interact with the data in SharePoint
Intro to Power BI for Office 365 6
2: Power BI for Office 365 vs SharePoint On-Premises
• But what if….???
Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Users then publish the Excel content to a SharePoint site collection
What if this piece could exist “in the cloud”, in an external portal that could render all the Excel content , and even work with newer versions of Excel add-ins?
SharePoint Enterprise Server Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data
And what if mobile users could access this site with a Surface table or an iPad?
Larger set of users interact with the data in SharePoint
Intro to Power BI for Office 365 7
2: Power BI for Office 365 vs SharePoint On-Premises
• That’s the basis of Power BI for Office 365 Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Users then publish the Excel content to an Office 365 site
Office 365 site, managed by Microsoft, that contains stripped down version of SharePoint, with simpler management features
Office 365 Server (with SharePoint support) Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Larger set of corporate users interact with the data by accessing Office 365 site (either with desktop or laptop or mobile device
Intro to Power BI for Office 365 8
2: Power BI for Office 365 vs SharePoint On-Premises
• That’s the basis of Power BI for Office 365 Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Users then publish the Excel content to an Office 365 site
Max workbook upload is 250 MB Office 365 site does not contain ALL features of SharePoint Enterprise (such as PerformancePoint Services)
Office 365 Server (with SharePoint support) Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Larger set of corporate users interact with the data by accessing Office 365 site (either with desktop or laptop or mobile device
Intro to Power BI for Office 365 9
2: Power BI for Office 365 vs SharePoint On-Premises
But when the content in the database changes, do we need to rebuild and redeploy the Excel spreadsheet?
Step 4: Set up a Data Refresh , so that the deployed content can refresh from the on site, on-premises data source But it requires that we configure a Data Gateway and Data Source on the Power BI for Office 365 site Power BI for Office 365 site Deployed Excel sheet contains data (compressed using xVelocity) Step 2: Deploy (Save) Excel Power BI Content to Power BI for Office 365 site. Remember, it contains the actual static content from the original database Step 1: Created PowerPivot / Power View content in Excel against SQL Server database Step 3: Users can access Excel content on the site, in the browser or from a mobile device, even if they don’t have Excel
Intro to Power BI for Office 365 10
3: What can we build with Power BI for Office 365
DAX formulas for KPIs, Return %, and Ranking Intro to Power BI for Office 365 • • • • Excel Spreadsheet Built against SQL Database Deployed to Power BI site Can Navigate against it without Excel in browser or mobile device!
11
3: What can we build with Power BI for Office 365
DAX formulas for Moving Average Intro to Power BI for Office 365 12
4: Getting Started with Power BI for Office 365
• First, load up on the Excel Add-Ins for Excel 2013 • Get PowerPivot, Power View, and (maybe) Power Maps – – http://www.microsoft.com/en-us/bi/powerpivot.aspx
(Power Pivot) Install/Configure steps for PowerPivot – PowerView is an add-in that comes with Excel 2013 Pro – http://www.microsoft.com/en-us/download/details.aspx?id=38395 Maps) (Power – http://www.microsoft.com/en-us/download/details.aspx?id=39379 (Power Query) • “Getting started” information for Power Query & Power Maps – – http://blogs.msdn.com/b/powerbi/archive/2013/07/07/getting-started with-pq-and-pm.aspx
A great overview of the components of Power BI • These are KILLER add-ins that allow Microsoft to compete with other self-service BI tools like QlikView and Tableau
Intro to Power BI for Office 365 13
4: Getting Started with Power BI for Office 365
• • • • Main Power BI Site Power BI Blog Power BI Support/FAQ Pricing and Free Trial A free trial starts with basic credentials and a user ID
Intro to Power BI for Office 365 Main portal only has ADMIN enabled to start. Must do some provisioning 14
4: Getting Started with Power BI for Office 365
Admin dashboard – need to add any additional users and start services
Intro to Power BI for Office 365 15
4: Getting Started with Power BI for Office 365
Either use new domain (based on original ID) and then add any additional users
Intro to Power BI for Office 365 16
4: Getting Started with Power BI for Office 365
Intro to Power BI for Office 365 Final checklist Remember https://portal.office.com
17
4: Getting Started with Power BI for Office 365
The main SharePoint portal admin center. At this point we have a public SharePoint site Our next step is to configure and provision a Power BI
New public Office 365 SharePoint site Intro to Power BI for Office 365 18
4: Getting Started with Power BI for Office 365
Power BI Admin Center Our objective is to deploy some BI content, where refreshes will point “back” on on-site, on-premise data source Need to establish Data Source and Gateway
Intro to Power BI for Office 365 19
5: Creating a Data Source/Gateway
But when the content in the database changes, do we need to rebuild and redeploy the Excel spreadsheet?
Step 4: Set up a Data Refresh , so that the deployed content can refresh from the on site, on-premises data source But it requires that we configure a Data Gateway and Data Source on the Power BI for Office 365 site Power BI for Office 365 site Deployed Excel sheet contains data (compressed using xVelocity) Step 2: Deploy (Save) Excel Power BI Content to Power BI for Office 365 site. Remember, it contains the actual static content from the original database Step 1: Created PowerPivot / Power View content in Excel against SQL Server database Step 3: Users can access Excel content on the site, in the browser or from a mobile device, even if they don’t have Excel
Intro to Power BI for Office 365 20
5: Creating a Data Source/Gateway
Back in the Power BI Admin Center, if you try to define a data source, it will prompt to install a Gateway first
Intro to Power BI for Office 365 21
5: Creating a Data Source/Gateway
Must download Microsoft Gateway Management client
When gateway client loads, enter the Gateway key from above Intro to Power BI for Office 365 22
5: Creating a Data Source/Gateway
Now that we have a gateway established, we can define an on-site data source for the Gateway Provide credentials for when Gateway reads “back” to on site source Define Connection, on site database server, and database name
Intro to Power BI for Office 365 23
6-PowerPivot
Compressed Star Schema Model “in the basement” of the Excel Sheet 1. Users can point Excel to database content 2. Can create the equivalent of a “mini-cube”, compressed using xVelocity compression 3. The Power Pivot Data Model lives “inside” the Excel Sheet 4. Users can create many Pivot Tables or Pivot Charts off the Model 5. Users can also create Power View report visualizations off the data model Intro to Power BI for Office 365 24
6-PowerPivot
Model can come from physical relational tables or database views Must create relationships if source was views This is somewhat like building SSAS OLAP cubes, except it doesn’t support advanced fact/dimension relationships
Intro to Power BI for Office 365
Can implement dimensional hierarchies
25
6-PowerPivot
Garrett’s sales as % of Quota was 80.18%. That’s “OK”, so status is yellow. But his sales one year ago was 85.13% of quota – so his % of quota is trending down, and that’s not good The % of Quota last year represents a DAX formula to express the % of quota for “same time period last year”
Intro to Power BI for Office 365
KPI scorecards in Excel, similar to other SharePoint dashboarding tools User can look at sales and quotas by Employee for a year or a quarter
26
6-PowerPivot
Geography Hierarchy, and evaluating sales in 2 different sale channels (customer internet and reseller/vendor Visual Slicers
Intro to Power BI for Office 365
Excel Generic Feature, % of Row Parent
27
6-PowerPivot
Monthly Sales + 12 month moving average, plotted as a line chart (Requires a set of DAX formulas)
Intro to Power BI for Office 365 28
6-PowerPivot
Sparkline (compressed graphic, a line chart with markers for high and low month) More Visual Slicers
Intro to Power BI for Office 365 29
7-DAX Formulas
• • • •
For formulas and Expressions, PowerPivot provides a language called DAX Very Loosely speaking, DAX is to PowerPivot as MDX is to OLAP Cubes Some websites state that DAX is easier than MDX
• That “depends” • For basic formulas, basically true • But for more complicated expressions, DAX can be just as involved as MDX When comparing PowerPivot to SSAS OLAP, worth noting that PowerPivot does not directly support advanced fact/dimension relationships (like role-playing dimensions and many-to-many bridge tables). So DAX is necessary to manually set relationships through code.
Intro to Power BI for Office 365 30
7-DAX Formulas
Intro to Power BI for Office 365
DAX formula to express % of Quota in terms of one year ago
31
7-DAX Formulas
More complicated DAX code to calculate a Moving Average Must determine, for any one month, the 12 month range (start month and end month of range) Must average the internet sales over the span of that range DAX is sometimes advertised as “easier” than the MDX language used in SSAS/OLAP applications, but sometimes DAX can be just as involved
Intro to Power BI for Office 365 32
8: Power View
• • • • Report Visualization Tool for Power Users Great for storyboard-type reporting, “face style” reporting where a page or subset of a page tells a story Not intended for full blown detail reports Not as much developer functionality as Reporting Services
Intro to Power BI for Office 365 33
8: Power View
Scatter chart plotting city observations of Sales revenue and # of orders Power View visualization against the Power Pivot Data Model Can use year as “Play axis” to show that while Beaverton is top city in Oregon across all years, it wasn’t top city in 2007
Intro to Power BI for Office 365
User can filter on Country – State Province
34
8: Power View
While this has nice interactive features, advanced users might want to show a linear regression line, and also the correlation coefficient (impact of order count on sales) Here is where tools like SSRS or even Excel Pivot Charts are a better option – Power View does not have these features
We can even select a single city and plot the progression of annual sales for a city over time
Intro to Power BI for Office 365 35
8: Power View
Intro to Power BI for Office 365
Cross filtering – I can click on the pie slice for Australia, and the bar chart above shades the monthly sales just for Australia
36
9: Power Maps
• As name implies, ability to create map visualizations based on geography definitions • Geography Definitions can come from country / state / city names, or from latitude/longitude points • For those who used maps in SQL Server Reporting Services, these maps are more interactive • Power BI Blog has links to examples that showcase some very fancy maps!
Intro to Power BI for Office 365 37
9: Power Maps
Can map based on geography labels or latitude / longitude points Intro to Power BI for Office 365 38
9: Power Maps
Intro to Power BI for Office 365 Can use nav controls to zoom in/out 39
10: Power Query
• Essentially “ETL” against many data sources using a query language inside Excel • Can query from many public websites (Facebook, Wikipedia) with nothing but a URL • Can be used to shape and format data into an Excel Pivot Table • Strong book recommendation, by Chris Webb
Intro to Power BI for Office 365 40
10: Power Query
We can point to a site, and then a table within the site, and utilize Power Query syntax
Intro to Power BI for Office 365 41
10: Power Query
Regardless of whether opponents were scoring 15 points or 30 points, Chiefs generally surrendered same number of yards Intro to Power BI for Office 365 42
11: Deploying
Uploading is very easy – in the Power BI site, just take the option to Add/Upload a file, and then select an XLSX file saved locally
Intro to Power BI for Office 365 43
12: Setting up Data Refreshes back to On-Premise Sources
In the Power BI for Office 365 site, click the ellipsis button and take the option to schedule a data refresh History page to show history of successful (or failed) refreshes
Verify the connection (there could be more than 1), and set frequently of schedule Intro to Power BI for Office 365 Notifications 44
13: Power BI Future Enhancements/Reading
• Power BI News from WPC: – – – http://www.jenunderwood.com/2014/07/14/microsoft-power-bi-news-from-wpc 2014/ http://cwebbbi.wordpress.com/2014/07/14/new-power-bi-features-shown-at-wpc/ http://cwebbbi.wordpress.com/2014/05/11/more-thoughts-on-the-new-power-bi features/ – – http://cwebbbi.wordpress.com/2014/05/08/power-bi-announcements-at-the-pass ba-conference/ WPC Demo (requires Windows Live ID sign-on - Power BI starts around 20 minute mark) – Rundown of new features: • New KPI/Dashboard Editor (possible replacement for PerformancePoint Services) • Support for new data visualizations • Support for SSRS Reports, which can point back to on-premises data sources (HUGE!!!) • Data Sources for SalesForce, MS Dynamics, Facebook, Google Analytics, Twitter
Intro to Power BI for Office 365 45
13: Power BI Future Enhancements/Reading
Deploy project to Power BI site Local SSRS project Power BI Site “in the cloud” Secured Data Gateway, so cloud reports can access on-premises data Users access reports in Power BI Dashboards Company on-premises database
Follow the blog of Chris Webb for details http://cwebbbi.wordpress.com/
Intro to Power BI for Office 365 46