***********************************
UPDATE September 2019: Please see the diagrams page on my Coates Data Strategies site for the latest diagram. The one shown below is severely out of date.
***********************************
***This post applies to Power BI for Office 365 (deprecated as of 12/31/2015). Though some concepts remain the same or similar, all details are not necessarily applicable to the new Power BI V2 service.***
The following diagram is an end-to-end overview of the major features of Power BI for Office 365.
The Excel Add-Ins + Office 365
Excel is the environment for data access, preparation, modeling, and visualization of the data. In addition to consuming data sources directly, there are Enterprise Data Search features to search for shared queries which have already been published to Office 365 - this makes the above diagram become very cyclical in nature as Excel is used to publish as well as consume.
Although you can use just the 4 Excel add-ins and get a lot of capabilities, there are some major pieces of functionality which become "unlocked" by integrating with Power BI in Office 365. SharePoint Online within O365 is the place for collaboration & sharing of workbooks, for scheduling data refreshes, as well as using Q&A to search for data across workbooks much like using a search engine. The ability to share and certify Power Queries also becomes available when the Excel user is signed into an O365 account with Power BI. The Mobile App, available from the Windows Store, also becomes a possibility when used with O365.
Within O365, there's primarily two main areas: the Power BI App where business users will view and interact with workbooks, as well as the Power BI Admin Center which is used by the system administrator to manage security and data connectivity to corporate data sources. To access corporate data, a business user needs to work with a system administrator to request a gateway and data source to be established; optionally, the administrator can set up an OData feed to expose corporate data for purposes of Enterprise Data Search.
The above diagram represents my understanding of the overall system - if you have any feedback or suggestions on it, I'd love to hear them in the comments.
What's the Additional Functionality Power BI Provides Over an E3 O365 Subscription?
It seems there's a bit of confusion on just what additional functionality Power BI provides over an E3 subscription. Essentially, you cannot create a Power BI Site without purchasing the Power BI subscription. When you have provisioned a Power BI Site in Office 365, the additional things you can do include:
Use the Power BI Site (which is really just a specialized document library) to share workbooks with coworkers
Perform Q&A searches on the workbooks
Schedule automatic data refresh
Utilize shared Power Queries (part of Enterprise Data Search)
Utilize certified Power Queries (part of Data Stewardship)
Open up corporate data sources via an OData feed (part of Enterprise Data Search)
Use the Mobile BI App
The chart on this Power BI Pricing page is another way to see how the Power BI subscription is an "add-on" to the E3 subscription. In the diagram above, the Office 365 box in the middle which contains the Power BI Site and Power BI Admin Center are opened up via the Power BI subscription.