Power BI in Office 365Michael TonderumSolution ArchitectAspect Software

Agenda

• What is Power BI?• What is Office 365?• Getting Started with the Power BI Preview• Power BI Sites (Apps)• Data Management Gateways and Power BI Admin

Sites• Data Steward Tools and Power Query• Additional Examples and Final Thoughts

Thank You!

For Sponsoring theBusiness Intelligence Track

My Profile Solution Architect with over 15 years of IT

experience, working in a variety of roles: Developer (C++, .NET, BI, SharePoint), SQL DBA, Software Architect, Team Lead, Business Analyst, Project Manager, and Sales Engineer. I currently work in the Education Practice of Aspect Software, where we deliver solutions for state/local governments and schools around the country. Our solutions are typically built using Microsoft technologies such as SharePoint, Office, SQL Server, Dynamics CRM, and custom .NET code.

michael.tonderum@aspect.com

Why Are We So Lucky?

Microsoft released the preview for Power BI for Office 365 just in time for SharePoint Saturday

What is Power BI?

What is “Power BI”?

Suite of Excel-Based, Self-Service BI Tools – Combined with the O365 Service Offering:

• Power Pivot – in memory tabular data warehouse and modeling engine

• Power View – interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting

• Power Map (a.k.a. “Geo Flow”) – geographic plotting tool for creating interactive 3D map visualizations and tours

• Power Query – tool for searching, discovering, combining, and refining external data

• Power BI Apps – for Office 365 and Windows 8

What is “Power BI”?

What is Office 365?

Office 365 – Cloud Service OfferingsNOTE: Details of Service Offerings Depend on Licensing Levels

• Outlook and Exchange – Email, Contacts, Calendar• Lync - IM, Presence, Voice, Video• SharePoint Online – one public website and multiple intranet site

collections• My Site – newsfeed, about me, blog, tasks• SkyDrive – personal cloud storage• Office Web Apps and Client Tools – client Excel, Word, require

higher licensing• Power BI – new! (preview only)

Office 365 – Login and Demo

https://login.microsoftonline.com

Getting Started with the Power

BI Preview

O365 Power BI Preview – Getting Started

• Register for the Preview:

http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx?CTT=5&origin=HA104097290

• Link to Documentation:

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

Provisioning Your Office 365 Tenant

Add Users and Assign Licenses

Add Users (Details Page)

Add Users (Settings Page)

Add Users (Licenses Page)

Power BI Sites (Apps)

What is a Power BI Site and What Makes it Different than a BI Center or other SP Site?

• A Power BI Site is an “app”- recall the three kinds of apps in SP 2013:- SharePoint Hosted, Provider-Hosted, and Azure-Hosted- Power BI Sites are Azure-Hosted Apps

• URL for a “Power BI” link:https://sites.powerbi.com/sharepointpages/SPIntegration/PowerBI/Pages/PowerBI.html?languageLocale=en%2DUS&regionalLocale=en%2DUS&SPHostUrl=https%3A%2F%2Faspectsoftware470%2Dpublic%2Esharepoint%2Ecom&appWeb=https%3A%2F%2FAspectSoftware470%2Dcc1715cd4a4f25%2Esharepoint%2Ecom%2Fsites%2FAppCatalog%2FPowerBI

• Workbooks are stored in the host site collection (documents library) and can be accessed and viewed directly there - Is that where most “regular” users will access and view them???

Using the O365 Power BI Site - Demo

Data Management

Gateways and

Power BI Admin Sites

Data Management Gateways The Data Management Gateway is a client agent that provides access to on-premises data sources in your organization. You must have at least one gateway installed in your corporate environment and register it with the Power BI Admin Center portal before creating data sources in the portal. The Power BI Admin Center lets you create multiple gateways, and download and install gateways on multiple on-premises computers. For example, you may choose to have one gateway per each department and assign all the data sources in a department to the gateway dedicated to that department. The gateway(s) installed in your organization must be connected to the Data Management Gateway service in the cloud for the clients to access on-premises data sources registered with the Admin Center.

Data Management Gateway – Use Cases• Allow data refresh of external data from workbooks• Allow Excel users to access external data from their desktop

and create new queries and reports

Provisioning a Gateway and Data Source - Demo

Power BI Admin Site – Summary of Tasks

• Manage gateways and data sources

• View system health and logs

• Manage co-administrators

Data Steward Tools and

Power Query

What is the Role of a Data Steward?• Identify and acquire new data sources.

• Create and maintain consistent reference data and master data definitions.

• Publish relevant data to appropriate users in an organization, and monitor the published data sources for usage/relevance/quality feedback.

• Create and manage business metadata for published data sources to ensure that it is easily discoverable, and meaningful to information workers.

• Resolve data integrity issues across stakeholders.

• Analyze data for quality and reconcile data issues.

Building Queries in Power Query - Demo

Power Query – Query Language “M”

Power Query – Query Language “M”

Data Steward Features in the Power BI App

• Manage data sources and access to data sources

• Sharing queries• Viewing usage statistics of queries

What about management of data definitions, documentation, and data business rules?

Additional Examples and Final Thoughts

Power Query and Power View - JSON

Power Query and Power View - JSON

Power Query – Other Sources• Traditional Database Servers• SSIS Packages• SharePoint Lists• Active Directory• Facebook• Wikipedia• Data.gov• Azure Marketplace• Azure HDInsight• Azure Blob Storage• Hadoop Distributed File Systems

Final Thoughts

• Power BI Beyond the Desktop – HTML 5 and the Power BI App for Windows 8

• Licensing and Full Release - ???

• Limitations for the Excel Based Tools- Not Available for Surface RT- Office Professional Plus and Office 365 Professional Plus Editions

Recap

• What is Power BI?• What is Office 365?• Getting Started with the Power BI Preview• Power BI Sites (Apps)• Data Management Gateways and Power BI Admin

Sites• Data Steward Tools and Power Query• Additional Examples and Final Thoughts