1 / 46

September 17, 2014

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.

iago
Download Presentation

September 17, 2014

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. September 17, 2014 13 Tips for Getting Started w/Power BI for Office 365 (Hosted by SQL PASS BI VC) The Baker’s Dozen Business Intelligence 13 SQL Server / Business Intelligence Productivity Tips Kevin S. Goff Microsoft SQL Server MVP

  2. 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 2nd book • Frequent speaker for SQL Server community events and SQL Live!360 Conferences • Email: kgoff@kevinsgoff.net • 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

  3. 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

  4. 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 • PowerPivot • PowerPivot DAX Formulas • PowerView • PowerMaps • PowerQuery • Deploying/Saving Content to Power BI for Office 365 sites • Setting up Data Refreshes back to On-Premise Sources • Recommended Reading/Links Intro to Power BI for Office 365

  5. 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 TOC Intro to Power BI for Office 365

  6. 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 Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Larger set of users interact with the data in SharePoint TOC Intro to Power BI for Office 365

  7. 2: Power BI for Office 365 vs SharePoint On-Premises • But what if….??? 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? Users then publish the Excel content to a SharePoint site collection SharePoint Enterprise Server Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Larger set of users interact with the data in SharePoint And what if mobile users could access this site with a Surface table or an iPad? TOC 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 Office 365 site, managed by Microsoft, that contains stripped down version of SharePoint, with simpler management features Users then publish the Excel content to an Office 365 site Office 365 Server (with SharePoint support) Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Larger set of corporate users interact with the data by accessing Office 365 site (either with desktop or laptop or mobile device TOC Intro to Power BI for Office 365

  9. 2: Power BI for Office 365 vs SharePoint On-Premises • That’s the basis of Power BI for Office 365 Office 365 site does not contain ALL features of SharePoint Enterprise (such as PerformancePoint Services) Users then publish the Excel content to an Office 365 site Office 365 Server (with SharePoint support) Site Collections for Excel, PowerPivot, Power View Galleries Other document libraries containing Business Intelligence Data Max workbook upload is 250 MB Business Users/Developers take data from SQL Server (or other sources), create Pivot Tables and Pivot Charts in Excel Larger set of corporate users interact with the data by accessing Office 365 site (either with desktop or laptop or mobile device TOC Intro to Power BI for Office 365

  10. 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? Power BI for Office 365 site 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 Deployed Excel sheet contains data (compressed using xVelocity) 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 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 TOC Intro to Power BI for Office 365

  11. 3: What can we build with 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! DAX formulas for KPIs, Return %, and Ranking TOC Intro to Power BI for Office 365

  12. 3: What can we build with Power BI for Office 365 DAX formulas for Moving Average TOC Intro to Power BI for Office 365

  13. 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 (Power Maps) • 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 TOC Intro to Power BI for Office 365

  14. 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 Main portal only has ADMIN enabled to start. Must do some provisioning TOC Intro to Power BI for Office 365

  15. 4: Getting Started with Power BI for Office 365 Admin dashboard – need to add any additional users and start services TOC Intro to Power BI for Office 365

  16. 4: Getting Started with Power BI for Office 365 Either use new domain (based on original ID) and then add any additional users TOC Intro to Power BI for Office 365

  17. 4: Getting Started with Power BI for Office 365 Final checklist Remember https://portal.office.com TOC Intro to Power BI for Office 365

  18. 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 TOC Intro to Power BI for Office 365

  19. 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 TOC Intro to Power BI for Office 365

  20. 5: Creating a Data Source/Gateway But when the content in the database changes, do we need to rebuild and redeploy the Excel spreadsheet? Power BI for Office 365 site 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 Deployed Excel sheet contains data (compressed using xVelocity) 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 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 TOC Intro to Power BI for Office 365

  21. 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 TOC Intro to Power BI for Office 365

  22. 5: Creating a Data Source/Gateway Must download Microsoft Gateway Management client When gateway client loads, enter the Gateway key from above TOC Intro to Power BI for Office 365

  23. 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 TOC Intro to Power BI for Office 365

  24. 6-PowerPivot • Users can point Excel to database content • Can create the equivalent of a “mini-cube”, compressed using xVelocity compression • The Power Pivot Data Model lives “inside” the Excel Sheet • Users can create many Pivot Tables or Pivot Charts off the Model • Users can also create Power View report visualizations off the data model Compressed Star Schema Model “in the basement” of the Excel Sheet TOC Intro to Power BI for Office 365

  25. 6-PowerPivot Must create relationships if source was views Model can come from physical relational tables or database views This is somewhat like building SSAS OLAP cubes, except it doesn’t support advanced fact/dimension relationships Can implement dimensional hierarchies TOC Intro to Power BI for Office 365

  26. 6-PowerPivot 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 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” TOC Intro to Power BI for Office 365

  27. 6-PowerPivot Geography Hierarchy, and evaluating sales in 2 different sale channels (customer internet and reseller/vendor Visual Slicers Excel Generic Feature, % of Row Parent TOC Intro to Power BI for Office 365

  28. 6-PowerPivot Monthly Sales + 12 month moving average, plotted as a line chart (Requires a set of DAX formulas) TOC Intro to Power BI for Office 365

  29. 6-PowerPivot Sparkline (compressed graphic, a line chart with markers for high and low month) More Visual Slicers TOC Intro to Power BI for Office 365

  30. 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. TOC Intro to Power BI for Office 365

  31. 7-DAX Formulas DAX formula to express % of Quota in terms of one year ago TOC Intro to Power BI for Office 365

  32. 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 TOC Intro to Power BI for Office 365

  33. 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 TOC Intro to Power BI for Office 365

  34. 8: Power View Power View visualization against the Power Pivot Data Model Scatter chart plotting city observations of Sales revenue and # of orders User can filter on Country – State Province 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 TOC Intro to Power BI for Office 365

  35. 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 TOC Intro to Power BI for Office 365

  36. 8: Power View Cross filtering – I can click on the pie slice for Australia, and the bar chart above shades the monthly sales just for Australia TOC Intro to Power BI for Office 365

  37. 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! TOC Intro to Power BI for Office 365

  38. 9: Power Maps Can map based on geography labels or latitude / longitude points TOC Intro to Power BI for Office 365

  39. 9: Power Maps Can use navcontrols to zoom in/out TOC Intro to Power BI for Office 365

  40. 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 TOC Intro to Power BI for Office 365

  41. 10: Power Query We can point to a site, and then a table within the site, and utilize Power Query syntax TOC Intro to Power BI for Office 365

  42. 10: Power Query Regardless of whether opponents were scoring 15 points or 30 points, Chiefs generally surrendered same number of yards TOC Intro to Power BI for Office 365

  43. 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 TOC Intro to Power BI for Office 365

  44. 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 Notifications TOC Intro to Power BI for Office 365

  45. 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 TOC Intro to Power BI for Office 365

  46. 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/ TOC Intro to Power BI for Office 365

More Related