NOTE: This information is good as of 6/2/2015 and is subject to change.
This will go over, what I refer to as, the Office 365 Bubble, or OBubble as I’m affectionately calling it. This is really to lay some foundation for some other items I want to post. It will be easier to refer back to this, but also easier if you understand this concept.
This came about while I was discussing how this worked with some of our engineers. When I described it this way, I saw/heard some light bulbs go off. Based on that, I wanted to share it out more broadly.
The Bubble
Here is how I will define the bubble, and the pieces that I want to talk about with this interaction. I think of the bubble kinda like an M&M. A hard shell on the outside and softer on the inside. It’s really more complex than that, but basically once you are in the Bubble, you can move around to the difference services that you have access to. It is getting into the bubble that causes some issues.
This really just boils down to Authentication. Specifically oAuth, or your Organization Account. This is what you use to sign into any of the Office 365 Services. For me, I use my asaxton@guyinacube.com credential.
We will go through each point on this to see what this looks like.
1. Excel pulling oData feed from O365
From Excel, we can pull OData from our services such as CRM Online, Project Online, or a SharePoint Online list. Within Power Query, you can easily use your Organization Account for authentication.
This data can then be stored within the Excel workbook’s Data Model and then published to Power BI for use for end users. To be clear, I’m not referring to any Data Refresh capabilities with this item. This is more about authoring the reports within Excel by getting the data. Then publishing to the cloud for use by end users.
2. Getting On-Premises Data
In the diagram above, I use CRM. It can really be anything. Usually we see this connecting to some SQL or Database back end. It could also be OData from something outside of the O365 Bubble. As long as it allows for either Anonymous or Windows Authentication on the OData side. For SQL Server, we could use either Windows Auth or SQL Auth to connect from Excel and pull the data into the Data Model.
3. Scheduled Data Refresh
Scheduled Data Refresh, from a Power BI for Office 365 perspective, does allow for refreshing from On-Prem data sources. This is done by way of access to the Data Management Gateway (DMG) which resides on-premisis. The DMG actually uses oAuth to authenticate with the cloud. This is configured when you set up the DMG.
4. Refresh of On-Premises Data Sources
We can then use Scheduled Data Refresh, by way of the DMG, to refresh data from On-Premises. This works for only certain authentication types. From the On-Premises CRM server example, we can do this with SQL Server data or with OData that uses Windows Authentication or Anonymous.
5. Scheduled Data Refresh with OData sources which reside in the O365 Bubble
This is where the pain point comes in. We can’t use Organization Account credentials with an OData data source via Power Query. This is just a limitation of the current infrastructure. This is usually where the pain points come in that we are seeing on the Support side.
Jon Burchel had posted on blog that touched on this.
When Not to Use OData with Power BIhttps://blogs.technet.com/b/powerbisupport/archive/2015/04/09/when-not-to-use-odata-with-power-bi.aspx
This is just a high level overview of the interactions. There are some definite limitations with Power BI for Office 365 and what you can do. Specifically around OData feeds within the Bubble that you want to refresh data with.
Next week I will look at this future and show you what you can do to get past this. It may seem like a dead end, but there is light at the end of the tunnel!
Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services@GuyInACube | YouTube | Facebook.com\guyinacube