1 / 42

Microsoft Power BI: Redmond’s BI Stack Goes Mobile and Cloud

Microsoft Power BI: Redmond’s BI Stack Goes Mobile and Cloud. Andrew Brust CEO and Founder Blue Badge Insights @ andrewbrust. Meet Andrew. CEO and Founder, Blue Badge Insights Microsoft Regional Director, MVP Co-chair VSLive ! and 18 years as a speaker

bess
Download Presentation

Microsoft Power BI: Redmond’s BI Stack Goes Mobile and Cloud

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. Microsoft Power BI: Redmond’s BI Stack Goes Mobile and Cloud Andrew Brust CEO and Founder Blue Badge Insights @andrewbrust

  2. Meet Andrew • CEO and Founder, Blue Badge Insights • Microsoft Regional Director, MVP • Co-chair VSLive! and 18 years as a speaker • Founder, Microsoft Business Intelligence + Big Data User Group of NYC (MSBIgDataNYC) • http://www.msbigdatanyc.com • Co-moderator, NYC .NET Developers Group • http://www.nycdotnetdev.com • “Redmond Review” columnist for Visual Studio Magazine and Redmond Developer News • brustblog.com, Twitter: @andrewbrust

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

  4. Read All About It!

  5. History of Data in Spreadsheets (Image: Wikimedia)

  6. Spreadsheet Genesis

  7. Excel Analytics Facilities

  8. Power BI’s Ingredients

  9. Excel Data Analysis Workflow

  10. External Data • Web (HTML tables) • File-based • Access, Text, XML • Data Connection Wizard • SQL Server, Analysis Services • Azure Marketplace, OData feeds • ODBC DSNs (Data Source Names) • Any OLE DB provider (including provider for ODBC) • MS Query (ODBC only; for backward-compatibility) • Can also get to ODBC data via OLE DB (using OLE DB provider for ODBC)

  11. Data Options Also: • Add connection • Add to model

  12. What is the Excel Data Model? • It’s a Power Pivot data model that is built implicitly • Way to have data in your workbook that isn’t in your worksheets • Read-only • Compatible with Power View • When workbook uploaded to SharePoint, compatible with Analysis Services clients (if Power Pivot for SharePoint installed) • Architecture • Columnar storage • In-memory query engine • xVelocity in-memory Analytics Engine, f.k.a. VertiPaq • Data highly compressed • Queries are very fast

  13. Adding Data to the Model

  14. Data Connection Wizard and Data Model • By default, data goes into worksheets and not into Data Model, but: • Importing multiple tables adds them to the model • Importing a single table gives you the option of adding to the model • Using multiple tables in a PivotTable or PivotChart puts them in the model • Visualizing a table in Power View adds it to the model • This is a Power View requirement • You can manually add a table to the model using the “Add to Data Model” button in the PowerPivot tab • Power Pivot add-in required; not available in all editions of Excel 2013 • Gets added as a linked table, so updates in the workbook replicate automatically • Won’t be linked to original data source • Once data is in model, you can delete it from worksheet/book

  15. Column-Oriented Stores • Imagine, instead of: • You have: • Perf: values you wish to aggregate are adjacent • Efficiency: great compression from identical or nearly-identical values in proximity • Fast aggregation and high compression means huge volumes of data can be stored and processed, in RAM

  16. Power Pivot Advantages (over Excel Data Model)

  17. How Do You Get Power Pivot? • 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: • Included in product, but only with: • Office Pro Plus retail product (under volume licensing) • Office 365 Pro Plus or Enterprise E3/E4 subscriptions • It’s still an add-in, installed but not activated by default • Same goes for Excel version of Power View

  18. Excel Data Model and Power Pivot DEMO

  19. Power View Overview • Data exploration and visualization client • Beautiful to look at • Animations add to user-friendliness • Visualizations work as filters, too • Design and view experiences are unified • Has SharePoint counterpart • With interesting PowerPoint export

  20. Power View Data Sources • Power View for Excel originally works only against BISM • Support for SSAS Multidimensional cubes added in SharePoint version • As of 5/31/2013, SharePoint version of Power View supports Multidimensional mode as part of SQL Server 2012 SP1 CU4(and now CU5) • http://blogs.msdn.com/b/sqlrsteamblog/archive/2013/05/31/power-view-for-multidimensional-models-released.aspx • Gist is this: Power View emits DAX expressions • Power Pivot and SSAS Tabular support DAX • SSAS Multidimensional supports DAX as of SQL Server 2012 SP1 CU4

  21. Create a Power View Report • In Excel 2013, click Power View button on Insert tab of ribbon • Make sure add-in is activated

  22. Power View DEMO

  23. Power Query: Overview • Imports data from both conventional and novel data sources • Brings the data into Excel worksheets and/or the Excel Data Model • Web page data import that is far superior to Excel’s built-in facility • Adds a lot of value to Power Pivot and Power View. • Provides access to additional data sources • Uses a sophisticated underlying query language which users can completely ignore, or use directly (or just observe) • Available for Excel 2010 or 2013. Data Model support only in 2013. • Product is in preview.

  24. Power Query: Caveats • Sometimes augments, sometimes replaces the Data tab. This can get confusing. • There are now three places to get at data: • Data Tab • Power Pivot window • Power Query

  25. Excel Data Model Tie-Ins • Add Power Query result set to Excel Data Model from Task Pane • Can also be done from PowerPivot tab of ribbon • Can optionally unload data from worksheet first • Once data’s in the model data can be further shaped/modeled • Power Pivot uses the Power Query-based workbook data source • Data refreshable in-model, if Enable Download turned on

  26. Power Query DEMO

  27. How Does it Work? • Each operation you perform generates a formula, that is applied in a named “step” • The formula can be shown or hidden • Steps can be renamed, re-ordered and deleted • Configuration of some steps can be edited • Formulas: • Can be edited • Are batched together in a script • Script: • Can be edited (allowing addition of custom steps) • Written from scratch • Uses a language called “M” • Remember project “Oslo?”

  28. Power Map: Overview • Mapping-based 3D data visualization tool • Built on Bing Maps • For Excel 2013 ProPlusonly. • Very flexible. Can work with latitude/longitude but also with address info, even if partial. • Allows recording of “tours,” made up of different “scenes” • Works with data in worksheets and Excel Data Model • 3 chart types: column, bubble and heat map • Product is in preview.

  29. Power Map: First steps • Install the add-in, then start Excel • Make certain your workbook has a dataset • Click to the Insert tab on the ribbon, then the Map button • In the fields list, select the columns containing your geo data • In the geography pane, configure the semantics of your columns (only necessary if Power Map’s guesses are off) • Click Map It button at lower-right • Observe geo-coding confidence level; make corrections • Configure chart…

  30. Layers • Layers allow multiple charts to be overlaid on same physical map • Add and delete at will • View in list • Adjust properties • Name • Display of zeros, negatives and nulls • Chart type-specific adjustments • Column: height and thickness • Bubble: size and thickness • Heat map: Color scale and radius of influence • All of which can have scale locked

  31. Scenes and Tours • “Scenes” are essentially screen capture videos • During which you can zoom, rotate, flatten, add labels, change themes or change the map in most any way • Can be added, deleted, re-ordered • Scene properties • Name, duration • Transition: • Duration • Effect • Effect speed • A “tour” is your map and its sequence of scenes • “Play Tour” button will play back, in full screen view with extra elements hidden • From Excel you can open a specific tour

  32. Power Map DEMO

  33. SharePoint/Power BI Tie-Ins

  34. Excel Services • Hosts cell ranges, PivotTables and PivotCharts • Can provide task pane for parameter value input • Fulfills drill-down, accommodates slicers and timelines • New in 2013 • Field list available • Fidelity of 3D charts • Also works in Excel Web App in Office 365, as do Excel Power View Reports

  35. Power Pivot • SharePoint version of Power Pivot puts engine on server • That plus Excel Services provides the browser solution • Allows Data Model to be queried by any Analysis Services client • IT dashboard calls attention to popular workbooks

  36. Power View • Silverlight Power View client available in Excel Web App, Excel Services for workbook-based reports • Can view, interact • Cannot changed tables/columns • With Power BI, HTML 5-based viewer now available • Less dynamic, no animations • Better device compatibility • Windows 8 app too • More in a moment

  37. Power BI in SharePoint DEMO

  38. The New Apps Model • Works in Office, Office Web Apps, SharePoint • Based on contained HTML/JavaScript • Public, Enterprise app store options available • JavaScript object model provides access to workbook • Potential channel for movement of data into Excel Data Model

  39. Power BI Tablet App • For Windows 8 and Windows RT • Native iPad app coming (I’ve seen it) • Touch-optimized • Combination of Excel Services viewer and Power View client • App available on the store • Authenticate into your O365/Power BI account, then browse • Can also just enable sample reports

  40. Power BI on Windows 8 DEMO

  41. Review • Excel’s historical core analytics capabilities are powerful • Excel Data Model makes the story much more solid • Power Pivot + Power View bring top-notch modeling, performance, data discover and visualization • Power Query adds access to newer, less structured data sources • Power Map adds very easy-to-use mapping visualizations • The tools work together • Power View uses the Excel Data Model • Power Pivot’s modeling niceties are picked up by Power View • Power Query feeds the Excel Data Model, Power Pivot and Power View • Power Map works with almost any data

  42. References • Power BI Preview – sign up: • http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx • Office Professional Plus 60-day trial: • http://technet.microsoft.com/en-us/evalcenter/jj192782.aspx • Power Query: • http://www.microsoft.com/en-us/download/details.aspx?id=39379 • Power Map: • http://www.microsoft.com/en-us/download/details.aspx?id=38395 • SQL Server 2012 SP1 CU5: • http://support.microsoft.com/kb/2861107 • Power BI team blog: • http://blogs.msdn.com/b/powerbi/

More Related