Demystifying Power BIKoen Verbeeck

Tweet and win an Ignite 2016 ticket #itproceed

WHO AM I?

AGENDA• the road so far…• the YouTube of workbooks• overview of the Power BI stack• conclusion

the road so far…• 2009 - 2010– Project Gemini– aka PowerPivot (without a space!)• SQL Server PowerPivot for Excel (add-in for

Office 2010)• SQL Server PowerPivot for SharePoint 2010

(integration through SQL Server 2008R2)

the road so far…• 2010 – 2011– Project Crescent– aka PowerView (quickly renamed to Power

View)– SharePoint only! (imagine the uproar)• on top of PowerPivot or Tabular

– Ships with SQL Server 2012 SSRS

the road so far…• 2012 – 2013– release of Office 2013– Power Pivot and Power View are included!

(note the spaces)• both have new features

– SharePoint 2013 also support Power Pivot and Power View• later: Power View supports SSAS Multidimensional

the road so far…• 2013– Data Explorer• aka Power Query (immediately with a space)

– Excel 2010 / 2013 add-in

No logo, sorry

the road so far…• 2013– GeoFlow• aka Power Map (also with a space)

– Excel 2013 add-in– the black sheep of the family

the road so far…• 2013 - 2014– Power BI for Office 365

• no fancy code name here

– Bundles Power Query, Power Pivot and Power View• no Power Map• has frequent updates / new features / bug fixes

– Introduces Q&A– More mobile friendly

the road so far…• 2015– Power BI v2• still in the cloud, but not Office 365• free vs paying

– Introduces Power BI Designer• New dashboarding experience• Power Query + Power View + Power Pivot

– Also has mobile apps

the road so far…• the future?– What will DataZen bring?– Power Query gets integrated into everything– More links with Azure / Big Data stuff– More R integration?– More out-of-the-box dashboards for Power BI– Great new features in Excel 2016– …

AGENDA• the road so far…• the YouTube of workbooks• overview of the Power BI stack• conclusion

Power BI• Managed self-service BI• Start in Excel• … end in the cloud (or in SharePoint)

• Umbrella term– Excel add-ins– Cloud environment

Power BI• Why would I need a paid cloud

service?

• Power BI is just like YouTube– But without the videos…

• How did you share a video in the ‘90s?

Power BI• Every awesome thing we do with

Power BI is saved into Excel

Source: http://www.powerpivotpro.com/2014/09/what-is-power-bi/

Power BI• Email is not really a good way to

share those workbooks

Source: http://www.powerpivotpro.com/2014/09/what-is-power-bi/

Power BI - recap• Excel add-ins

– Power Query– Power Pivot– Power Map– Power View

• YouTube for Workbooks– Cloud-based version of SharePoint portals for sharing Excel content– Data Management Gateway so IT controls data sources published on Power

BI sites (and user access)– Mobile BI support for Windows 8, Windows RT, iOS through either native app

or HTML5

AGENDA• the road so far…• the YouTube of workbooks• overview of the Power BI stack• conclusion

Power Pivot• Offers an easy-to use tabular

modelling environment– data is compressed• millions of rows possible• columnar storage

– new scripting language: DAX

Power Pivot• Design the datamodel

– Pull data from (different) sources– Add relationships

• Enrich the datamodel– Set Datatypes– Format Numbers / percentages– Add Calculations / Measures– Hide Overhead (link fields)

• Use the datamodel – Power Pivot Report

DEMOPower Pivot

Power Query• “Self-service” ETL tool• Can extract data from dozens of

source– list is growing each month

Power Query• Possible sources include

– SQL Server, SSAS– SharePoint Lists (& OData)– Excel & Flat files

• Some sources “fold” back to the server– E.g. filtering, sorting, grouping

• Power Query loads data– into Excel– into Power Pivot

DEMOPower Query

Power Query• Not supported currently in SharePoint– Power Pivots populated by Power Query

are a problem

• Supported in Office 365– But not every source out of the box– Data Management Gateway is needed

Power View• Intuitive and interactive

dashboarding• No code has to be written–Works on top of a data model• Power Pivot or SSAS

Power View• Supported in Office 365 & v2– As a Power View sheet in Excel– Result of Q&A

• Supported in SharePoint 2010 & 2013– SharePoint offers browser based editing– Export to PowerPoint feature

DEMOPower View

Power Map• Allows you to plot data on a globe– custom maps are possible

• You can make avideo showing offyour data

• Useful to tellstories

DEMOPower Map

Power BI for Office 365• YouTube for Workbooks• Share reports with colleagues

– (No organisational SharePoint / not the right SharePoint version / features)

• Interactive reports available via browser– Silverlight & HTML5

• Up to date report• Controlled Database load

– Refresh the data overnight

Power BI – Q&A• Natural Language query– aka English language query

• Ask a question, and Power BI will give you the answer in the correct format

• Retrieves data from uploaded Power Pivot models– create synonyms to refine model

DEMOPower BI for Office 365

Power BI v2• Currently in preview• Two subscriptions– one free, one paying

• More interactive dashboarding• Integration of (almost) all Power BI tools• Frequent changes and updates

Power BI Designer• Offline client to develop Power BI

reports / dashboards– For customers who don’t have the right

version of Excel

• Integrates Power Query, Power View and Power Pivot– Allows you to write DAX as well

DEMOPower BI v2

AGENDA• the road so far…• the YouTube of workbooks• overview of the Power BI stack• conclusion

Conclusion• It has been a bumpy road– but there’s been a lot of progress

• Microsoft is finally starting to unifying everything

• There are some amazing capabilities– but also some big shortcomings– these will be tackled in the future

THANKS FOR LISTENING

And win a Lumia 635

Feedback form will be sent to you by email

Give me feedback

Follow Technet Belgium@technetbelux

Subscribe to the TechNet newsletteraka.ms/benews

Be the first to know

Thank you!

Belgium’s biggest IT PRO Conference