september 17, 2014

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. GoffMicrosoft SQL Server MVP

Intro to Power BI for Office 365 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: [email protected] • My site/blog: www.KevinSGoff.Net (includes SQL/BI webcasts)• Releasing some SQL/BI video courseware in 2015

mailto:[email protected]

http://www.kevinsgoff.net/


• 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

Today’s Topic


1. What is Power BI and Power BI for Office 365?2. Power BI for Office 365 vs SharePoint On-Premises3. What can we build with Power BI for Office 365?4. Getting Started: Creating a Power BI for Office 365 site/account5. Creating Data Sources and Data Management Gateways6. PowerPivot7. PowerPivot DAX Formulas8. PowerView9. PowerMaps10. PowerQuery11. Deploying/Saving Content to Power BI for Office 365 sites12. Setting up Data Refreshes back to On-Premise Sources13. Recommended Reading/Links

Topics for today


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


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

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

Users then publish the Excel content to a

SharePoint site collection

Larger set of users interact with the data in

SharePoint

TOC


2: Power BI for Office 365 vs SharePoint On-Premises• But what if….???

SharePoint Enterprise Server


Galleries


Intelligence Data




Users then publish the Excel content to

a SharePoint site collection

Larger set of users interact with the data in SharePoint

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?

And what if mobile users could access this site with a

Surface table or an iPad?

TOC


2: Power BI for Office 365 vs SharePoint On-Premises• That’s the basis of Power BI for Office 365

Office 365 Server (with SharePoint support)


Galleries


Intelligence Data




Users then publish the Excel content to an Office 365 site

Larger set of corporate users interact with the data by accessing Office 365 site (either with desktop

or laptop or mobile device

Office 365 site, managed by Microsoft, that contains

stripped down version of SharePoint, with simpler

management features

TOC


2: Power BI for Office 365 vs SharePoint On-Premises• That’s the basis of Power BI for Office 365

Office 365 Server (with SharePoint support)


Galleries


Intelligence Data




Users then publish the Excel content to an Office 365 site

Larger set of corporate users interact with the data by accessing Office 365 site (either with desktop

or laptop or mobile device

Office 365 site does not contain ALL features of SharePoint

Enterprise (such as PerformancePoint Services)

Max workbook upload is 250 MB

TOC


2: Power BI for Office 365 vs SharePoint On-Premises

Step 1: Created PowerPivot / Power View content in Excel against SQL Server database

Power BI for Office 365 site

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 3: Users can access Excel

content on the site, in the browser or

from a mobile device, even if they

don’t have Excel

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

Deployed Excel sheet contains data (compressed using

xVelocity)

TOC


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


3: What can we build with Power BI for Office 365

DAX formulas for Moving Average

TOC


• 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

4: Getting Started with Power BI for Office 365

TOC

http://www.microsoft.com/en-us/bi/powerpivot.aspx

http://www.microsoft.com/en-us/bi/powerpivot.aspx

http://office.microsoft.com/en-us/excel-help/start-power-pivot-in-microsoft-excel-2013-add-in-HA102837097.aspx

http://www.microsoft.com/en-us/download/details.aspx?id=38395




http://blogs.msdn.com/b/powerbi/archive/2013/07/07/getting-started-with-pq-and-pm.aspx



http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/power-bi-for-office-365-overview-and-learning-HA104103581.aspx


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

http://www.microsoft.com/en-us/powerbi/default.aspx

http://www.microsoft.com/en-us/powerBI/bi-blog.aspx

http://office.microsoft.com/en-us/excel-help/power-bi-for-office-365-faq-HA104219793.aspx

http://www.microsoft.com/en-us/powerBI/pricing.aspx



Admin dashboard – need to add any additional users and

start services

TOC



Either use new domain (based on original ID) and

then add any additional users

TOC



Final checklist

Remember https://portal.office.com

TOC



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



Power BI Admin CenterOur 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


5: Creating a Data Source/Gateway

Step 1: Created PowerPivot / Power View content in Excel against SQL Server database

Power BI for Office 365 site

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 3: Users can access Excel

content on the site, in the browser or

from a mobile device, even if they

don’t have Excel

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

Deployed Excel sheet contains data (compressed using

xVelocity)

TOC



Back in the Power BI Admin Center, if you try to define a data source, it will prompt to install a Gateway first

TOC



Must download Microsoft Gateway Management client

When gateway client loads, enter the Gateway key from

above

TOC



Now that we have a gateway established, we can define an on-site data source for the Gateway

Define Connection, on-site database server, and

database name

Provide credentials for when Gateway reads “back” to on-

site source

TOC


6-PowerPivot

TOC

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


6-PowerPivot

Model can come from physical relational

tables or database views

Must create relationships if source

was views

Can implement dimensional hierarchies

This is somewhat like building SSAS OLAP cubes, except it doesn’t support advanced

fact/dimension relationships

TOC


6-PowerPivot

KPI scorecards in Excel, similar to other

SharePoint dashboarding tools

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”

User can look at

sales and quotas by Employee for a year

or a quarter

TOC


6-PowerPivot

Excel Generic Feature, % of Row Parent

Visual Slicers

Geography Hierarchy, and evaluating sales in 2 different sale channels (customer

internet and reseller/vendor

TOC


6-PowerPivot

Monthly Sales + 12 month moving average, plotted as a line chart

(Requires a set of DAX formulas)

TOC


6-PowerPivot

Sparkline (compressed graphic, a line chart with markers for high and low month)

More Visual Slicers

TOC


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


7-DAX Formulas

DAX formula to express % of Quota in terms of

one year ago

TOC


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


8: Power View

TOC

• 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


8: Power ViewPower View visualization against the Power Pivot

Data Model

User can filter on Country – State

Province

Scatter chart plotting city

observations of Sales revenue and

# of orders

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


8: Power View

We can even select a single city and plot the progression of

annual sales for a city over time

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

TOC


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


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


9: Power Maps

Can map based on geography labels or latitude / longitude

points

TOC


9: Power Maps

Can use nav controls to

zoom in/out

TOC


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


10: Power Query

We can point to a site, and then a table within

the site, and utilize Power Query syntax

TOC


10: Power Query

Regardless of whether opponents were scoring 15 points or 30 points, Chiefs

generally surrendered same number of yards

TOC


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


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

Verify the connection (there could be more than 1), and set

frequently of schedule

History page to show history of successful (or failed) refreshes

Notifications

TOC


13: Power BI Future Enhancements/Reading

• Power BI News from WPC:– http://www.jenunderwood.com/2014/07/14/microsoft-power-bi-news-from-wpc-20

14/

– 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-fe

atures/

– 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

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/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/



http://www.digitalwpc.com/Videos/Pages/Videos.aspx?g=4d5ef40c-dc5b-426d-9a7a-8dd6274bb42b#fbid=gaOpLt1jjcA

http://www.digitalwpc.com/Videos/Pages/Videos.aspx?g=4d5ef40c-dc5b-426d-9a7a-8dd6274bb42b#fbid=gaOpLt1jjcA


13: Power BI Future Enhancements/Reading

Power BI Site “in the

cloud”

Local SSRS project

Deploy project to Power BI site

Company on-premises database

Secured Data Gateway, so cloud reports can access on-premises data

Users access reports in Power BI

Dashboards

Follow the blog of Chris Webb for detailshttp://cwebbbi.wordpress.com/

TOC

http://cwebbbi.wordpress.com/

http://cwebbbi.wordpress.com/

september 17, 2014

Documents